Unexpected results with IS NOT NULL

(1.1) Originally by Yu Liang (LY1598773890) with edits by Richard Hipp (drh) on 2021-05-29 22:05:45 from 1.0 [source]

Hi all.

For query:

CREATE TABLE v0 ( v2 INT, v1 INT);
INSERT INTO v0 VALUES ( 10, 10 );
CREATE UNIQUE INDEX v4 ON v0 ( v1 ) WHERE v2 > NULL;

SELECT * FROM v0;
/* 10 | 10 */
SELECT * FROM v0 WHERE v0.v2 IS NOT NULL;
/* EMPTY */

The second SELECT statement failed to output the row. Fossil bisecting results show the bug introduced commit is: fc98218cf69e63bdb9e5f154521a341508502cd8cfe04cb870cabee2d99e0cb3.

Looking forward to your reply.

(2) By Yu Liang (LY1598773890) on 2021-05-29 23:39:46 in reply to 1.1 [link] [source]

Seems like the problem has been fixed in: 8cc23931d61b7d78. Thank you for the updates and the patch information provided.

(3) By L Carl (lcarlp) on 2021-05-30 17:47:03 in reply to 1.1 [link] [source]

Does that CREATE INDEX statment work? I'd expect "V2 > NULL" to be always false, like any normal comparison to NULL. I would expect the clause should be "WHERE V2 IS NOT NULL".

(4) By Richard Hipp (drh) on 2021-05-30 17:50:39 in reply to 3 [link] [source]

Yes, the CREATE INDEX works in the sense that it always creates an index. But since the condition "V2 > NULL"is always NULL, no rows are ever inserted into the index, and so the index is utterly useless.