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 [link] [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 [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