Date: 2017-01-13 13:08:07 +0100
From: Manuel <>
To: SQL devs <>
Version: 11.23.13 (Jun2016-SP2)
CC: @mlkersten, @njnes

Last updated: 2017-03-03 10:24:54 +0100

Comment 24887

Date: 2017-01-13 13:08:07 +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:

The following query

select * from "test_join_left_table" as "ta" where not exists (select 1 as "one" from (select "t2"."A", "t2"."B", "t2"."C", "t2"."D", "t2"."l_c" from ((select "t0"."A" as "A", cast("B" as CLOB) as "B", "t0"."C" as "C", "t0"."D" as "D", null as "l_c" from "unitTestDontDelete" as "t0") union all (select "t1"."a" as "A", "t1"."b" as "B", cast(null as double) as "C", cast(null as timestamp) as "D", "t1"."l_c" as "l_c" from "test_join_left_table" as "t1")) as "t2") as "tb" where ("ta"."l_c" = "tb"."l_c" or ("ta"."l_c" is null and "tb"."l_c" is null)))

returns no data back instead of an empty result set as expected.

In the logs I can see:

.....
2017-01-13 11:35:23 ERR visokio[4975]: t2.B
2017-01-13 11:35:23 ERR visokio[4975]: t2.C
2017-01-13 11:35:24 ERR visokio[4975]: could not find L65.D
2017-01-13 11:35:24 ERR visokio[4975]: L55.A
2017-01-13 11:35:24 ERR visokio[4975]: L57.B
....

If I remove the null casting within the exists clause:

select * from "test_join_left_table" as "ta" where not exists (select 1 as "one" from (select "t2"."A", "t2"."B", "t2"."C", "t2"."D", "t2"."l_c" from ((select "t0"."A" as "A", cast("B" as CLOB) as "B", "t0"."C" as "C", "t0"."D" as "D", null as "l_c" from "unitTestDontDelete" as "t0") union all (select "t1"."a" as "A", "t1"."b" as "B", cast(null as double) as "C", null as "D", "t1"."l_c" as "l_c" from "test_join_left_table" as "t1")) as "t2") as "tb" where ("ta"."l_c" = "tb"."l_c" or ("ta"."l_c" is null and "tb"."l_c" is null)))

The query produced the expected results.

Reproducible: Always

Steps to Reproduce:

1. Create the example tables as in attachments
2. Run the query
   select * from "test_join_left_table" as "ta" where not exists (select 1 as "one" from (select "t2"."A", "t2"."B", "t2"."C", "t2"."D", "t2"."l_c" from ((select "t0"."A" as "A", cast("B" as CLOB) as "B", "t0"."C" as "C", "t0"."D" as "D", null as "l_c" from "unitTestDontDelete" as "t0") union all (select "t1"."a" as "A", "t1"."b" as "B", cast(null as double) as "C", cast(null as timestamp) as "D", "t1"."l_c" as "l_c" from "test_join_left_table" as "t1")) as "t2") as "tb" where ("ta"."l_c" = "tb"."l_c" or ("ta"."l_c" is null and "tb"."l_c" is null)))
3. No result set is returned instead of an empty result set

Actual Results:

nothing

Expected Results:

an empty result set:
+---+---+-----+
| a | b | l_c |
+===+===+=====+
+---+---+-----+

This will result in an error if the query is executed programmatically e.g., via a jdbc driver

Comment 24888

Date: 2017-01-13 13:10:11 +0100
From: Manuel <>

Created attachment 529
First table used in the example query

Attached file: unitTest.sql (application/octet-stream, 581 bytes)
Description: First table used in the example query

Comment 24889

Date: 2017-01-13 13:10:55 +0100
From: Manuel <>

Created attachment 530
Second table used in the example

Attached file: joinleft.sql (application/octet-stream, 261 bytes)
Description: Second table used in the example

Comment 24905

Date: 2017-01-23 17:15:47 +0100
From: @sjoerdmullender

Reproducible:

could not find L65.D
L55.A
L57.B
L62.C
L67.l_c
t2.A
t2.B
t2.C
mserver5: .../sql/backends/monet5/rel_bin.c:2404: rel2bin_project: Assertion `0' failed.

Thread 29 "mserver5" received signal SIGABRT, Aborted.

Comment 24906

Date: 2017-01-23 20:45:27 +0100
From: @mlkersten

Probably related to Bug #6177

Comment 24942

Date: 2017-02-01 21:23:54 +0100
From: @njnes

cannot repeat the problem on the current versions (ie default and dec2016)

Comment 24943

Date: 2017-02-01 22:36:07 +0100
From: MonetDB Mercurial Repository <>

Changeset 150bd238b590 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=150bd238b590

Changeset description:

fixed bug #6186, preperly rename apply operators

Comment 25130

Date: 2017-03-03 10:24:54 +0100
From: @sjoerdmullender

Dec2016-SP2 has been released, incorporating the fix.