SQLite

View Ticket
Login
Ticket Hash: d9f584e936c7a8d0fe8bef9a6e47832678dd9565
Title: Row is not fetched in table with INTEGER PRIMARY KEY
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-08-22 00:55:07
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-08-21 21:34:41:

Consider the following test case:

CREATE TABLE t0(c0 INTEGER PRIMARY KEY, c1 TEXT);
INSERT INTO t0(c0, c1) VALUES (1, 'a');
SELECT * FROM t0 WHERE '-1' BETWEEN 0 AND t0.c0; -- expected: 1|a, actual: no row is fetched

I would expect the query to be equivalent to the following:

SELECT * FROM t0 WHERE '-1' >= 0 AND '-1' <= t0.c0;

However, for the first query no row is fetched, while one is fetched for the second one.


drh added on 2019-08-22 00:55:07:

The malfunction only occurs if the t0.c0 column in an INTEGER PRIMARY KEY or rowid in the table. This same anomalous behavior is displayed by every version of SQLite going back to 3.1.0 (2005-01-21).