SQLite Forum

Inconsistent results in the WHERE clause
Login

Inconsistent results in the WHERE clause

(1) By Yu Liang (LY1598773890) on 2021-05-12 18:40:13 [link] [source]

Hi all:

For query:

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:

  1. Remove DESC from v1 can fix the inconsistency.

  2. Remove the WITHOUT ROWID from v0 can fix the inconsistency.

  3. Remove the INDEX v3 can fix the inconsistency.

Looking forward to your reply.

(2) By Richard Hipp (drh) on 2021-05-12 22:04:59 in reply to 1 [link] [source]

(3) By Yu Liang (LY1598773890) on 2021-05-12 22:55:53 in reply to 2 [source]

Thank you for the information.