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

CASE statements do not handle NULLs in the IN () operator properly #3739

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

CASE statements do not handle NULLs in the IN () operator properly #3739

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

Comments

@monetdb-team
Copy link

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

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.
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