Join query returns zero results when there is an index on the tables

[monetdb-team]

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.

Attached file: bug_monet.sql (text/x-sql, 643 bytes)
Description: 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:

add test for bug #3181

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:

new fix for bug #3181.