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

NOT IN with an AND containing an OR gives wrong result #6775

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

NOT IN with an AND containing an OR gives wrong result #6775

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

Comments

@monetdb-team
Copy link

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

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.

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