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