Date: 2016-12-16 20:13:49 +0100
From: Manuel <>
To: SQL devs <>
Version: 11.23.13 (Jun2016-SP2)
CC: @njnes

Last updated: 2017-01-26 14:56:38 +0100

Comment 24786

Date: 2016-12-16 20:13:49 +0100
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36
Build Identifier:

When I execute this query

select "t3e"."c22_store_phone_number" as "c3c_r_c"

from (
(select null as "c22_store_phone_number" from "test_join_right_table") union all
(select "store_phone_number" as "c22_store_phone_number" from "lineitem_denormalized_first1k")
) as "t3e"

where exists (select 1 as "one" from "test_join_right_table" as "t3d" where ("t3e"."c22_store_phone_number" = "t3d"."r_c" or ("t3e"."c22_store_phone_number" is null and "t3d"."r_c" is null)))

I get 4 extra records that should not be part of the result set.

Please find the DDL and all files necessary to reproduce the issue attached.

I expect only null values to be in the result set. Note that each condition in the final or produces the correct result.

Reproducible: Always

Steps to Reproduce:

1. Execute the sql statements in 'create_join_tables.sql' to create one of the two tables, and insert the records.

2. Execute the sql statement in 'create_lineitem_denormalized_first1k' to create the schema for the second table.

3. Populate this second table with the CSV contained in 'lineitem_denormalized_first1k_headerless.csv'. You should be able to do so by running a command like: mclient -d visokio -s "COPY INTO lineitem_denormalized_first1k FROM '/home/visokio/lineitem_denormalized_headerless.csv' USING DELIMITERS ',','\n','"'"

4. Run the query :

select "t3e"."c22_store_phone_number" as "c3c_r_c"

from (
(select null as "c22_store_phone_number" from "test_join_right_table") union all
(select "store_phone_number" as "c22_store_phone_number" from "lineitem_denormalized_first1k")
) as "t3e"

where exists (select 1 as "one" from "test_join_right_table" as "t3d" where ("t3e"."c22_store_phone_number" = "t3d"."r_c" or ("t3e"."c22_store_phone_number" is null and "t3d"."r_c" is null)))

The records

| +44-875-777-1712 |
| +44-875-777-1712 |
| +44-875-777-1712 |
| +44-875-777-1712 |

should not be part part of the output I think.

Actual Results:

+------------------+
| c3c_r_c |
+==================+
| null |
| null |
| null |
| null |
| +44-875-777-1712 |
| +44-875-777-1712 |
| +44-875-777-1712 |
| +44-875-777-1712 |
+------------------+

Expected Results:

+------------------+
| c3c_r_c |
+==================+
| null |
| null |
| null |
| null |
+------------------+

Comment 24787

Date: 2016-12-16 20:26:09 +0100
From: Manuel <>

Created attachment 521
DDL for one of the test table

Attached file: create_join_tables.sql (application/octet-stream, 358 bytes)
Description: DDL for one of the test table

Comment 24788

Date: 2016-12-16 20:26:34 +0100
From: Manuel <>

Created attachment 522
DDL for the other test table

Attached file: create_lineitem_denormalized_first1k.sql (application/octet-stream, 1238 bytes)
Description: DDL for the other test table

Comment 24789

Date: 2016-12-16 20:27:23 +0100
From: Manuel <>

Created attachment 523
CSV contained the data for the second test table

Attached file: lineitem_denormalized_first1k_headerless.csv (application/vnd.ms-excel, 319403 bytes)
Description: CSV contained the data for the second test table

Comment 24791

Date: 2016-12-16 20:27:44 +0100
From: Manuel <>

Created attachment 524
The query that produces the wrong result

Attached file: query.txt (text/plain, 449 bytes)
Description: The query that produces the wrong result

Comment 24870

Date: 2017-01-06 20:31:29 +0100
From: MonetDB Mercurial Repository <>

Changeset 8069d2353d05 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=8069d2353d05

Changeset description:

fix bug #6150, ie be more carefull with semijoin rewrites

Comment 24871

Date: 2017-01-06 20:32:06 +0100
From: @njnes

fixed, improved the semijoin push down rewriter

Comment 24872

Date: 2017-01-09 14:58:42 +0100
From: Manuel <>

(In reply to Niels Nes from comment 6)

fixed, improved the semijoin push down rewriter

Thanks a lot.

Comment 24917

Date: 2017-01-26 14:56:38 +0100
From: @kutsurak

Fixed in version Dec2016-SP1.