Date: 2015-05-08 08:19:49 +0200
From: Vijay Krishna <>
To: SQL devs <>
Version: 11.19.11 (Oct2014-SP3)
CC: @njnes

Last updated: 2015-06-22 22:32:13 +0200

Comment 20853

Date: 2015-05-08 08:19:49 +0200
From: Vijay Krishna <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.135 Safari/537.36
Build Identifier:

I am using MonetDB Jan2014 SP1, running on MacOS. I have a table like this.

sql>select * from two;
+------+----------------------------------------------------------------------------------------------
| id | name | istrue | joindate | jointime | count |
+======+====================================================
| 1 | null | false | 2017-12-08 | 2014-06-30 14:05:31.000000 | 2 |
| 2 | vijay | false | 2017-12-08 | 2014-06-30 14:05:59.000000 | 3 |
| 3 | krish | true | 2017-12-08 | 2014-06-30 14:06:17.000000 | 5 |
| 4 | bat | true | null | null | null |
| 5 | gotham | false | null | null | null |
| 6 | wayne | false | null | null | 3 |
+------+-------------------------------------------------------------------------------------------------
6 tuples (3.504ms)

I tried to do a join on this table with the same table with the following query and got this result.

sql>select two.id, two.name, two.count, twoAlias.id as id2, twoAlias.name as name2 from two left join two twoAlias on two.count=twoAlias.id;
+------+--------------------------------------------------------------------------------------------------+

| id | name | count | id2 | name2

+=============================================================+

| 1 | null | 2 | 2 | vijay |

| 2 | vijay | 3 | 3 | krish |

| 3 | krish | 5 | 5 | gotham |

| 4 | bat | null | 3 | krish |

| 5 | gotham | null | null | null |

| 6 | wayne | 3 | null | null |

+------+----------------------------------------------------------------------------------------------------+

6 tuples (5.605ms)

As it could be seen clearly, this join is clearly resulting in a wrong output at the 4th row. The join criterion two.count=twoAlias.id is satisfied in the first 3 rows, but are clearly violated in the 4th row, where in the values in the join columns (count and id2 here) are clearly found to be different. Hence the join rows are not matching.

Reproducible: Always

Steps to Reproduce:

1.Create a table as above.
2.Do a select query by joining with the same table.
Eg - select two.id, two.name, two.count, twoAlias.id as id2, twoAlias.name as name2 from two left join two twoAlias on two.count=twoAlias.id;
3.Results will not be correct.

Actual Results:

sql>select two.id, two.name, two.count, twoAlias.id as id2, twoAlias.name as name2 from two left join two twoAlias on two.count=twoAlias.id;
+------+--------------------------------------------------------------------------------------------------+

| id | name | count | id2 | name2

+=============================================================+

| 1 | null | 2 | 2 | vijay |

| 2 | vijay | 3 | 3 | krish |

| 3 | krish | 5 | 5 | gotham |

| 4 | bat | null | 3 | krish |

| 5 | gotham | null | null | null |

| 6 | wayne | 3 | null | null |

+------+----------------------------------------------------------------------------------------------------+

Expected Results:

I expected that the output would be
+------+--------------------------------------------------------------------------------------------------+

| id | name | count | id2 | name2

+=============================================================+

| 1 | null | 2 | 2 | vijay |

| 2 | vijay | 3 | 3 | krish |

| 3 | krish | 5 | 5 | gotham |

| 4 | bat | null | null | null |

| 5 | gotham | null | null | null |

| 6 | wayne | 3 | 3 | krish |

+------+----------------------------------------------------------------------------------------------------+

6 tuples (5.605ms)

Comment 20866

Date: 2015-05-15 12:05:03 +0200
From: MonetDB Mercurial Repository <>

Changeset 443209cc4b86 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=443209cc4b86

Changeset description:

added test for bug #3720

Comment 20871

Date: 2015-05-20 18:31:05 +0200
From: @njnes

Test added and runs correct