Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Null Matches in outer join are not supported #6398

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Null Matches in outer join are not supported #6398

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant