Wrong NULL behavior in EXCEPT and INTERSECT

[monetdb-team]

Date: 2012-02-23 15:32:37 +0100
From: Gatis Ozolins <<g.ozolins>>
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes, sellam

Last updated: 2015-01-28 15:59:44 +0100

Comment 16982

Date: 2012-02-23 15:32:37 +0100
From: Gatis Ozolins <<g.ozolins>>

User-Agent: Mozilla/5.0 (Windows; Windows NT 6.1; rv:10.0) Gecko/20100101 Firefox/9.0
Build Identifier: Dec2011

According to: https://en.wikipedia.org/wiki/Null_%28SQL%29
In grouping and sorting "any two values that are equal to one another, or any two Nulls" should be "not distinct".

Reproducible: Always

Steps to Reproduce:

select null
EXCEPT
select null

Actual Results:

Expected Results:

0 rows

Comment 17212

Date: 2012-05-02 21:09:34 +0200
From: @njnes

The except is implemented as a combination of binary algebra group, count and join. The Group/count has the proper (in this case) NULL handling (ie non-distinct). For join this isn't the case as for normal relational joins the NULL != NULL should hold.

We need to investigate if the join code could be made proper NULL semantics aware.

Comment 17690

Date: 2012-08-24 14:56:04 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18200

Date: 2012-11-28 13:45:35 +0100
From: sellam

Changeset 7af551ae9f42 made by Thibault Sellam sellam@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=7af551ae9f42

Changeset description:

Added test for bug #3040

Comment 19100

Date: 2013-08-28 16:27:46 +0200
From: @njnes

fixed by using the new subjoin with nil=nil semantics

Comment 19101

Date: 2013-08-28 16:40:44 +0200
From: MonetDB Mercurial Repository <>

Changeset 974c4105edf4 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=974c4105edf4

Changeset description:

fixed bug #3040, ie use subjoin with nil = nil to fix except/intersect semantics problem.

also cleanup of sql_statement* code

Comment 19620

Date: 2014-02-20 15:03:02 +0100
From: @sjoerdmullender

Jan2014 has been released.

Comment 20591

Date: 2015-01-28 15:59:44 +0100
From: MonetDB Mercurial Repository <>

Changeset f6971e8244d5 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=f6971e8244d5

Changeset description:

fixed typo (ie properly pass 'nil' == 'nil' semantics, fixes old  bug #3040 (null except null)
properly find sub*joins (now including theta,band,anti and range joins)