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]

Hello everyone,

Take this for example:

```SQL
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:

```SQL
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]

>(c0 == NULL)

NULL values are unique i.e. unequal to <b>any other</b> 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]

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]

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

3.35.4

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

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

[](https://sqlite.org/src/ci/40852ca8)

Dan.