|Title:||Row is not fetched in table with INTEGER PRIMARY KEY|
|Last Modified:||2019-08-22 00:55:07|
|Version Found In:||3.29.0|
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).