Date: 2017-08-30 14:00:46 +0200
From: Manuel <>
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: @njnes

Last updated: 2017-10-26 14:01:37 +0200

Comment 25600

Date: 2017-08-30 14:00:46 +0200
From: Manuel <>

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

Given two tables T1, and T2, and a field f present in both T1, and T2, doing an outer join like

select * from T1 left outer join T2 on T1.f = T2.f

does not account for rows where both T1.f and T2.f are NULL. This is correct, according to the operation semantics.

However, if I want to have matches on null values, the appropriate solution would be a query like

select * from T1 left outer join T2 on (T1.f = T2.f OR (T1.f is NULL AND T2.f is NULL)).

Unfortunately this doesn't work: in fact, this query is the same as:

select * from T1 join T2 on (T1.f = T2.f OR (T1.f is NULL AND T2.f is NULL)).

ON conditions containing clauses on NULL values are supported in INNER joins, but not on LEFT/RIGHT OUTER joins.

I tried to overcame this issue by appending an inner join with a left anti join, but the performance is much worse, and it is a way less cleaner approach.

Reproducible: Always

Steps to Reproduce:

Create two different tables, for example:

create table "E" (
"intCol" bigint,
"stringCol" string
);

insert into "E" values (0, 'zero');
insert into "E" values (1, 'one');
insert into "E" values (2, 'two');
insert into "E" values (null, null);

create table "I" (
"intCol" bigint,
"stringCol" string
);
insert into "I" values (2, 'due');
insert into "I" values (4, 'quattro');
insert into "I" values (null, 'this is not null');

select * from "E" left outer join "I" on "E"."intCol" = "I"."intCol" or ("E"."intCol" is null and "I"."intCol" is null);

Actual Results:

intCol stringCol intCol stringCol

2 two 2 due
this is not null

Expected Results:

intCol stringCol intCol stringCol

2 two 2 due
0 zero
1 one
this is not null

Comment 25645

Date: 2017-09-17 21:33:30 +0200
From: MonetDB Mercurial Repository <>

Changeset 0260b270032c 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=0260b270032c

Changeset description:

fixed bug #6398, ie handle null matches in outer joins

Comment 25646

Date: 2017-09-17 21:34:10 +0200
From: @njnes

fixed both a crash and broken rewrites.