You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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>
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"?
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.
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
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:
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:
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:
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
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:
Comment 19612
Date: 2014-02-20 15:02:54 +0100
From: @sjoerdmullender
Jan2014 has been released.
The text was updated successfully, but these errors were encountered: