Date: 2014-02-05 23:53:01 +0100
From: Anthony Damico <>
To: SQL devs <>
Version: 11.17.9 (Jan2014)
CC: ajdamico, @hannesmuehleisen, @njnes, @drstmane

Last updated: 2014-02-20 15:02:54 +0100

Comment 19552

Date: 2014-02-05 23:53:01 +0100
From: Anthony Damico <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0
Build Identifier:

here's an example of a join that works until you create an index on the merge-variables. this behavior shouldn't happen, right? my apologies if i'm overlooking something :) thanks for monetdb!!

Reproducible: Always

Steps to Reproduce:

CREATE TABLE TEST1 ( one VARCHAR(10) , two INT ) ;
CREATE TABLE TEST2 ( one VARCHAR(10) , two INT ) ;

INSERT INTO TEST1 VALUES ( 'a' , 1 ) ;
INSERT INTO TEST2 VALUES ( 'a' , 1 ) ;

SELECT count(*) FROM ( SELECT one , two FROM TEST1 ) AS a INNER JOIN ( SELECT one , two FROM TEST2 ) AS B on A.one = B.one AND A.two = B.two ;

CREATE INDEX onedex ON TEST1 ( one , two ) ;

SELECT count(*) FROM ( SELECT one , two FROM TEST1 ) AS a INNER JOIN ( SELECT one , two FROM TEST2 ) AS B on A.one = B.one AND A.two = B.two ;

Actual Results:

sql>CREATE TABLE TEST1 ( one VARCHAR(10) , two INT ) ;
operation successful (179.671ms)
sql>CREATE TABLE TEST2 ( one VARCHAR(10) , two INT ) ;
operation successful (71.839ms)
sql>
sql>INSERT INTO TEST1 VALUES ( 'a' , 1 ) ;
1 affected row (26.440ms)
sql>INSERT INTO TEST2 VALUES ( 'a' , 1 ) ;
1 affected row (25.405ms)
sql>
sql>SELECT count() FROM ( SELECT one , two FROM TEST1 ) AS a INNER JOIN ( SELEC
T one , two FROM TEST2 ) AS B on A.one = B.one AND A.two = B.two ;
+------+
| L1 |
+======+
| 1 |
+------+
1 tuple (1.890ms)
sql>
sql>CREATE INDEX onedex ON TEST1 ( one , two ) ;
operation successful (62.781ms)
sql>
sql>SELECT count() FROM ( SELECT one , two FROM TEST1 ) AS a INNER JOIN ( SELEC
T one , two FROM TEST2 ) AS B on A.one = B.one AND A.two = B.two ;
+------+
| L1 |
+======+
| 0 |
+------+
1 tuple (1.916ms)
sql>
sql>

Expected Results:

both before and after the INDEX gets set:

+------+
| L1 |
+======+
| 1 |
+------+

Comment 19553

Date: 2014-02-06 08:23:55 +0100
From: @hannesmuehleisen

Also confirmed on Linux, OSX, and Jan2014 branch.

Comment 19554

Date: 2014-02-06 08:25:48 +0100
From: MonetDB Mercurial Repository <>

Changeset eed2975df8f9 made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Test for bug #3435

Comment 19555

Date: 2014-02-06 08:38:17 +0100
From: @hannesmuehleisen

Issue also occurs on tables with only integer columns.

Comment 19556

Date: 2014-02-06 09:25:40 +0100
From: MonetDB Mercurial Repository <>

Changeset 33353c5ddd48 made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Expected output for Bug #3435

Comment 19557

Date: 2014-02-06 09:39:51 +0100
From: MonetDB Mercurial Repository <>

Changeset ebad38380185 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Enable test for bug #3435.

Comment 19558

Date: 2014-02-06 11:03:09 +0100
From: @drstmane

Created attachment 261
diff between "good" ("OK") and "bad" ("KO") plan, explain & trace

Attached file: OK-KO.html (text/html, 95113 bytes)
Description: diff between "good" ("OK") and "bad" ("KO") plan, explain & trace

Comment 19559

Date: 2014-02-06 11:05:16 +0100
From: @drstmane

attached diff between "good" ("OK") and "bad" ("KO") plan, explain, and trace
suggests that in the "bad" case, the join between the index (hash) of table test1 and the created two-column hash of table test2 find no matches.
Possibly, both hashes are created differently and thus not "compatible"/"comparable"?

Comment 19577

Date: 2014-02-12 10:34:41 +0100
From: @njnes

its seems the stored hash is incorrect.

Comment 19578

Date: 2014-02-12 12:59:08 +0100
From: @njnes

we now handle the index creation similar as an alter table for a key creation.
Then a key/index is created and the proper update statement is added to update the index column.

Comment 19584

Date: 2014-02-12 14:52:39 +0100
From: MonetDB Mercurial Repository <>

Changeset cc2f9c2ac8a2 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=cc2f9c2ac8a2

Changeset description:

fix for bug #3435. Indices created on tables already holding data
are properly initialized again.
fixed a bug when loading using LOCKED. The properties weren't properly checked

Comment 19612

Date: 2014-02-20 15:02:54 +0100
From: @sjoerdmullender

Jan2014 has been released.