null handling in some sql statements is incorrect

[monetdb-team]

Date: 2016-01-06 14:29:24 +0100
From: Kevin Boulain <<kevin.boulain>>
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: @njnes

Last updated: 2016-03-25 09:59:53 +0100

Comment 21715

Date: 2016-01-06 14:29:24 +0100
From: Kevin Boulain <<kevin.boulain>>

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:42.0) Gecko/20100101 Firefox/42.0
Build Identifier:

NULL literal support has recently been added but we discovered that using it within a IN or a BETWEEN may invalid the query.
Maybe others are impacted.

Reproducible: Always

Steps to Reproduce:

In mclient, the following queries produce unexpected results:

1. sql>select null in (null);
   TypeException:user.s14_2[4]:'calc.==' undefined in: X_7:bit := calc.==(X_5:void,X_6:void);
   program contains errors

2. sql>select null in (1);
   types tinyint(8,0) and any(0,0) are not equal

3. sql>select null between null and null;
   TypeException:user.s18_2[4]:'calc.>=' undefined in: X_8:bit := calc.>=(X_6:void,X_7:void);
   program contains errors

Expected Results:

I think that all of the queries should return NULL.

The mserver5 --version outputs:
MonetDB 5 server v11.21.12 (64-bit, 64-bit oids, 128-bit integers)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 378.6GiB available memory, 56 available cpu cores
Libraries:
libpcre: 8.35 2014-04-04 (compiled with 8.35)
openssl: OpenSSL 1.0.1j 15 Oct 2014 (compiled with OpenSSL 1.0.1j 15 Oct 2014)
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: root@builder (x86_64-unknown-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code -D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64

Comment 21892

Date: 2016-03-14 08:21:22 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixes for bug #3900, ie handle null (type any) in in/between expressions

Comment 21893

Date: 2016-03-14 08:22:11 +0100
From: @njnes

Fixed in the 2015 branch, ie handle null(type any) in 'in expressions'

Comment 21978

Date: 2016-03-25 09:59:53 +0100
From: @sjoerdmullender

Jul2015-SP3 has been released.