Date: 2019-10-19 21:06:40 +0200
From: @yzchang
To: SQL devs <>
Version: -- development
CC: @PedroTadim, @yzchang

Last updated: 2019-11-28 10:00:04 +0100

Comment 27367

Date: 2019-10-19 21:06:40 +0200
From: @yzchang

In the Nov2019 branch, the combination of NOT IN with an AND clause which contains at least one OR clause gives wrong results (as shown in the results of the SELECT query below). This is because the NOT IN is translated in the logical plan into an IN (as shown in the results of the PLAN below).

CREATE TABLE t (i INT, s VARCHAR(32));
INSERT INTO t VALUES (-450, 'foo'), (29, 'bar'), (-250, 'foobar');

sql>SELECT i FROM t WHERE t.i NOT IN (-450 , 29) AND (t."s" <> 'xyz' OR t."s" IS NULL );
+------+
| i |
+======+
| -450 |
| 29 |
+------+

sql>PLAN SELECT i FROM t WHERE t.i NOT IN (-450 , 29) AND (t."s" <> 'xyz' OR t."s" IS NULL );
+-------------------------------------------------------------------------------------------+
| rel |
+===========================================================================================+
| project ( |
| | select ( |
| | | table(sys.t) [ "t"."i", "t"."s" ] COUNT |
| | ) [ ("t"."s" != varchar(32) "xyz") or (sys.isnull("t"."s") = boolean "true"), "t"."i" i |
: n (int[sys.sql_neg(smallint "450")], int "29") ] :
| ) [ "t"."i" ] |
+-------------------------------------------------------------------------------------------+

Comment 27368

Date: 2019-10-19 21:27:54 +0200
From: MonetDB Mercurial Repository <>

Changeset 8e63d332f667 made by Ying Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=8e63d332f667

Changeset description:

Added test for Bug #6775

Comment 27369

Date: 2019-10-19 23:23:55 +0200
From: @PedroTadim

The problem happens in the rel_select_cse optimizer.