SQLite Forum

useless constant term lead to incorrect output
Login

useless constant term lead to incorrect output

(1) By Wang Ke (krking) on 2021-04-10 13:34:51 [link] [source]

Hello everyone,

Take this for example:

CREATE TABLE t0(c0 PRIMARY KEY);
INSERT INTO t0(c0) VALUES (0);

SELECT * FROM t0 WHERE (c0 NOT NULL) OR (c0 == NULL);

The expected output is 0, and yes, the output is exactly 0.

But when adding a constant term "AND 1" to the term "c0 NOT NULL", the SQLite will get incorrect output:

SELECT * FROM t0 WHERE ((c0 NOT NULL) AND 1) OR (c0 == NULL);

Nothing will be printed.

I wonder whether it is a bug.

Looking forward to your early reply :)

(2) By anonymous on 2021-04-10 14:07:03 in reply to 1 [source]

(c0 == NULL)

NULL values are unique i.e. unequal to any other value, even another NULL.

Try

(c0 IS NULL)

(5) By Wang Ke (krking) on 2021-04-10 14:49:32 in reply to 2 [link] [source]

I don't think this is the point, the expected output is not NULL.

(3) By TripeHound on 2021-04-10 14:11:20 in reply to 1 [link] [source]

Which version are you on? It works as you expect with 3.32.2:

sqlite> CREATE TABLE t0(c0 PRIMARY KEY);
sqlite> INSERT INTO t0(c0) VALUES (0);
sqlite> SELECT * FROM t0 WHERE (c0 NOT NULL) OR (c0 == NULL);
0
sqlite> SELECT * FROM t0 WHERE ((c0 NOT NULL) AND 1) OR (c0 == NULL);
0

(4) By Wang Ke (krking) on 2021-04-10 14:17:22 in reply to 3 [link] [source]

3.35.4

(6) By Dan Kennedy (dan) on 2021-04-10 15:03:57 in reply to 1 [link] [source]

This is a bug. Thanks for reporting it. Now fixed here:

https://sqlite.org/src/ci/40852ca8

Dan.