Date: 2018-04-18 10:50:55 +0200
From: Manuel <>
To: SQL devs <>
Version: 11.29.3 (Mar2018)
CC: @njnes

Last updated: 2018-06-20 11:02:17 +0200

Comment 26375

Date: 2018-04-18 10:50:55 +0200
From: Manuel <>

Created attachment 601
The sql to create the table used to reproduce the bug

I have spotted an issue on the join operator. I have attached the sql statements to reproduce the queries reported below.

The query:

SELECT "t9"."c6_Department"
FROM
(
SELECT "t8"."c2_Department" AS "c6_Department"
FROM
(SELECT "t0"."Department" AS "c5_Department" FROM "HumanResources_1272" AS "t0" WHERE "t0"."Department" = 'Technology' GROUP BY "Department") AS "t7"
RIGHT OUTER JOIN
(SELECT "t0"."Department" AS "c2_Department" FROM "HumanResources_1272" AS "t0" GROUP BY "t0"."Department") AS "t8"
ON (
"t8"."c2_Department" = "t7"."c5_Department"OR ( "t7"."c5_Department" IS NULL AND "t8"."c2_Department" IS NULL )
)
) AS "t9"
ORDER BY "c6_Department" ASC

returns

c6_Department

which is wrong, since no restriction on the values of Department is present on the right table.

The equivalent query:

SELECT "t9"."c6_Department"
FROM
(
SELECT "t8"."c2_Department" AS "c6_Department"
FROM
(SELECT "t0"."Department" AS "c5_Department" FROM "HumanResources_1272" AS "t0" WHERE "t0"."Department" = 'Technology' GROUP BY "Department") AS "t7"
RIGHT OUTER JOIN
(SELECT "t0"."Department" AS "c2_Department" FROM "HumanResources_1272" AS "t0" GROUP BY "t0"."Department") AS "t8"
ON (
"t8"."c2_Department" = "t7"."c5_Department"OR ( "t8"."c2_Department" IS NULL AND "t7"."c5_Department" IS NULL )
)
) AS "t9"
ORDER BY "c6_Department" ASC

returns

c6_Department

which is the correct result.

Similar results can be obtained with a left outer join.

Attached file: HumanResources_1272.sql (text/plain, 679579 bytes)
Description: The sql to create the table used to reproduce the bug

Comment 26398

Date: 2018-04-21 11:10:45 +0200
From: MonetDB Mercurial Repository <>

Changeset 68e680001a8f made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=68e680001a8f

Changeset description:

fixes for bug #6581