Date: 2015-06-15 13:39:35 +0200
From: Anthony Damico <>
To: SQL devs <>
Version: 11.19.15 (Oct2014-SP4)
CC: @njnes

Last updated: 2017-02-22 09:43:21 +0100

Comment 20922

Date: 2015-06-15 13:39:35 +0200
From: Anthony Damico <>

User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:38.0) Gecko/20100101 Firefox/38.0
Build Identifier:

a NULL inside the () gets no respect

Reproducible: Always

Steps to Reproduce:

correctly (i think) returns both

dbGetQuery( db , "SELECT * FROM ( VALUES ( 1 , 2 ) , ( 3 , NULL ) ) AS z ( L1 , L2 ) WHERE L2 IN ( 2 , NULL )" )
l1 l2
1 1 2
2 3 NA

INCORRECTLY recodes the second row as "no" even though it should be "yes"

dbGetQuery( db , "SELECT CASE WHEN L2 IN ( 2 , NULL ) THEN 'yes' ELSE 'no' END FROM ( VALUES ( 1 , 2 ) , ( 3 , NULL ) ) AS z ( L1 , L2 ) " )
ifthenelse_isnull_or_=_l2
1 yes
2 no

Actual Results:

see reproduction

Expected Results:

"yes" "yes"

nuthin. thanks!

Comment 20946

Date: 2015-06-28 10:17:09 +0200
From: @njnes

Anthony. The problems is the handling of nulls. Null != Null in (most) of sql's statements. We incorrectly matched null's in 'in' handling. This is fixed now in the jul2015 branch

Comment 21196

Date: 2015-08-28 13:41:57 +0200
From: @sjoerdmullender

Jul2015 has been released.

Comment 25052

Date: 2017-02-22 09:43:21 +0100
From: MonetDB Mercurial Repository <>

Changeset f5282a08aeef made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Generate algebra.thetasubselect instead of algebra.subselect.
thetaselect never matches nil, select may in certain conditions match
nil.  It's easier to use thetaselect than to work around select.
This causes test null_is_null.Bug-3739 to behave properly again.