Inconsistent results in the WHERE clause
(1) By Yu Liang (LY1598773890) on 2021-05-12 18:40:13 [link] [source]
CREATE TABLE v0 ( v1 INT PRIMARY KEY DESC, v2 INT ) WITHOUT ROWID; INSERT INTO v0 VALUES ( 10, 10 ); CREATE INDEX v3 ON v0 ( v2 ); /* STMT 1 */ SELECT * FROM v0 WHERE v2 = 10 AND v1 = 10; /* Outputs 10|10 */ /* STMT 2 */ SELECT * FROM v0 WHERE v2 = 10 AND v1 < 11; /* Outputs (empty) */
The results are inconsistent between STMT 1 and STMT 2. Additionally, we observe that:
Remove DESC from v1 can fix the inconsistency.
Remove the WITHOUT ROWID from v0 can fix the inconsistency.
Remove the INDEX v3 can fix the inconsistency.
Looking forward to your reply.
Fixed by check-in f65c929bf189cf5c.