When table/column names conflicts, data ends in multiple tables!

[monetdb-team]

Date: 2016-01-28 11:05:26 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: 11.21.13 (Jul2015-SP2)
CC: @njnes, pcoustillas

Last updated: 2016-03-25 09:59:47 +0100

Comment 21754

Date: 2016-01-28 11:05:26 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:43.0) Gecko/20100101 Firefox/43.0
Build Identifier:

Conflicting column names between tables might corrupt data.

Say:

TABLE foo, COLUMN bar_baz
TABLE foo_bar, COLUMN baz

The internal name is computed by concatenating the table name and column name.

In the example above, this produces the same internal name: "foo_bar_baz".

MonetDB seems to mix up things in this case.

Reproducible: Always

Steps to Reproduce:

sql> create table foo_bar (baz int);
sql> create table foo (bar_baz int);
sql> insert into foo values (1);
sql> insert into foo_bar values (2);
sql> select * from foo_bar;
+------+
| baz |
+======+
| 2 |
+------+
1 tuple (0.981ms)
sql>select * from foo;
+---------+
| bar_baz |
+=========+
| 1 |
+---------+
1 tuple (2.071ms)

So far, so good.

However, after restarting the MonetDB server (forcing to flush the journal), when we perform the same queries again, we see corrupted data:

sql>select * from foo;
+---------+
| bar_baz |
+=========+
| 1 |
| 2 |
+---------+
2 tuples (1.111ms)
sql>select * from foo_bar;
+------+
| baz |
+======+
| 1 |
| 2 |
+------+
2 tuples (1.002ms)

It behave as if both columns where somewhat merged internally.

We do notice that reverting the order of the CREATE TABLE gives us the following error:

sql>create table foo (bar_baz int);
operation successful (6.975ms)
sql>create table foo_bar (baz int);
CREATE TABLE: sys_foo_bar_baz conflicts

Maybe this error should be given in the other case.

Comment 21770

Date: 2016-02-05 18:10:42 +0100
From: Pierre Coustillas <>

reproducible bug on version 11.21.13 (Jul2015 SP2), linux debian 8.2

Comment 21771

Date: 2016-02-05 19:41:40 +0100
From: Pierre Coustillas <>

In transaction mode it's different, but bug also :

Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2)
Database: MonetDB v11.21.13 (Jul2015-SP2), 'mapi:monetdb://rd:50000/lemonde'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>START TRANSACTION;
auto commit mode: off
sql>create table foo_bar (baz int); create table foo (bar_baz int); insert into foo values (1); insert into foo_bar values (2);
operation successful (1.134ms)
operation successful (1.149ms)
1 affected row (1.153ms)
1 affected row (1.157ms)
sql>SELECT * FROM foo_bar;
+------+
| baz |
+======+
| 2 |
+------+
1 tuple (2.903ms)
sql>commit;
auto commit mode: on
sql>SELECT * FROM foo_bar;
+------+
| baz |
+======+
| 2 |
+------+
1 tuple (3.015ms)

Restart monetdb with : service monetdb5-sql stop ; sleep 5 ; service monetdb5-sql start

Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2)
Database: MonetDB v11.21.13 (Jul2015-SP2), 'mapi:monetdb://rd:50000/lemonde'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>SELECT * FROM foo_bar;
+------+
| baz |
+======+
| 1 |
+------+
1 tuple (1.407ms)

???
2 become 1 ?

Pierre

Comment 21775

Date: 2016-02-11 10:01:05 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

Changing the version number. Very often my choice reverts to an old version when I enter a bug. I don't know why.

Comment 21808

Date: 2016-02-21 21:11:17 +0100
From: MonetDB Mercurial Repository <>

Changeset e2aafc304c04 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=e2aafc304c04

Changeset description:

fixed bug #3912, improved check for name conflicts

Comment 21810

Date: 2016-02-23 08:21:54 +0100
From: @njnes

improved the test for name conflicts

Comment 21811

Date: 2016-02-23 08:26:08 +0100
From: MonetDB Mercurial Repository <>

Changeset e8c4edcbfabb made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=e8c4edcbfabb

Changeset description:

added tests for bug #3910 and 3912.

Fixed a problem with copy into with header information

Comment 21975

Date: 2016-03-25 09:59:47 +0100
From: @sjoerdmullender

Jul2015-SP3 has been released.