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
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.19 (KHTML, like Gecko) Ubuntu/11.10 Chromium/18.0.1025.168 Chrome/18.0.1025.168 Safari/535.19
Build Identifier:
I am running an sql script which performs some joins that involve two tables, the one of which has an index. The problem is that when executing the join query in monetdb, i get zero results, even if there should be some.
Reproducible: Always
Steps to Reproduce:
I am running the following script:
CREATE TABLE R (
u int NOT NULL,
v int NOT NULL,
r int NOT NULL
);
CREATE TABLE trans (
s int NOT NULL,
t int NOT NULL,
comp int NOT NULL
);
SELECT TR.x, TR.z, comp
FROM
(SELECT TR1.u as x, TR1.v as y, TR2.v as z, TR1.r as rxy, TR2.r as ryz
FROM
R as TR1 JOIN R as TR2
ON (TR1.v = TR2.u AND TR1.u <> TR2.v)
) as TR
JOIN
trans
ON (TR.rxy = s AND TR.ryz = t);
Actual Results:
+---+---+------+
| x | z | comp |
+===+===+======+
+---+---+------+
The problem was caused by a multicolumn join expression over (3 tables).
The index table and 2 other tables. We now don't allow join's over more than 2 tables to be rewritten using the index column.
Date: 2012-11-09 13:12:40 +0100
From: Stella Giannakopoulou <>
To: SQL devs <>
Version: 11.13.3 (Oct2012)
CC: charnik, @njnes, sgian, @drstmane
Last updated: 2013-06-16 18:39:37 +0200
Comment 17925
Date: 2012-11-09 13:12:40 +0100
From: Stella Giannakopoulou <>
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.19 (KHTML, like Gecko) Ubuntu/11.10 Chromium/18.0.1025.168 Chrome/18.0.1025.168 Safari/535.19
Build Identifier:
I am running an sql script which performs some joins that involve two tables, the one of which has an index. The problem is that when executing the join query in monetdb, i get zero results, even if there should be some.
Reproducible: Always
Steps to Reproduce:
I am running the following script:
CREATE TABLE R (
u int NOT NULL,
v int NOT NULL,
r int NOT NULL
);
CREATE TABLE trans (
s int NOT NULL,
t int NOT NULL,
comp int NOT NULL
);
CREATE INDEX trans_st_idx ON trans (s, t);
INSERT INTO trans(s, t, comp) VALUES
(1, 2, 31),
(1, 16, 31),
(1, 3, 255),
(255, 3, 255);
INSERT INTO R (u, v, r) VALUES
(0, 1, 1),
(1, 2, 3),
(2, 4, 3),
(1, 4, 16),
(1, 3, 2),
(3, 2, 255);
SELECT TR.x, TR.z, comp
FROM
(SELECT TR1.u as x, TR1.v as y, TR2.v as z, TR1.r as rxy, TR2.r as ryz
FROM
R as TR1 JOIN R as TR2
ON (TR1.v = TR2.u AND TR1.u <> TR2.v)
) as TR
JOIN
trans
ON (TR.rxy = s AND TR.ryz = t);
Actual Results:
+---+---+------+
| x | z | comp |
+===+===+======+
+---+---+------+
Expected Results:
x | z | comp
---+---+------
0 | 3 | 31
0 | 2 | 255
0 | 4 | 31
3 | 4 | 255
(4 rows)
The problem disappears when i remove the statement that creates the index on table R:
CREATE INDEX trans_st_idx ON trans (s, t);
I have tested the same script in postgres and I get the expected results.
Comment 17926
Date: 2012-11-09 13:20:12 +0100
From: Stella Giannakopoulou <>
Created attachment 153
The script that returns the wrong results.
Comment 17927
Date: 2012-11-09 16:02:21 +0100
From: @drstmane
Confirmed with both Oct2012 & current development branch.
Work-around until we fix the bug: do not create the index ;-)
Comment 18007
Date: 2012-11-26 15:04:53 +0100
From: @njnes
Added test join_over_multitable_using_index.Bug-3181
Comment 18008
Date: 2012-11-26 15:06:57 +0100
From: @njnes
The problem was caused by a multicolumn join expression over (3 tables).
The index table and 2 other tables. We now don't allow join's over more than 2 tables to be rewritten using the index column.
Comment 18012
Date: 2012-11-26 16:19:23 +0100
From: @njnes
Changeset 28d08769660b 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=28d08769660b
Changeset description:
Comment 18380
Date: 2013-01-22 09:29:16 +0100
From: @sjoerdmullender
Oct2012-SP3 has been released.
Comment 18848
Date: 2013-06-16 18:39:37 +0200
From: MonetDB Mercurial Repository <>
Changeset 56d9e6140bfe 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=56d9e6140bfe
Changeset description:
The text was updated successfully, but these errors were encountered: