|Title:||Incorrect result for BETWEEN and generated column|
|Last Modified:||2019-12-20 22:47:02|
|Version Found In:||3.31.0 alpha|
mrigger added on 2019-12-20 15:54:21:
Consider the following test case:
CREATE TABLE t0(c0 REAL AS(1) UNIQUE, c1 INT); INSERT INTO t0 VALUES(''); SELECT * FROM t0 WHERE (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0); -- unexpected: row is fetched
Unexpectedly, the query fetches a row. I believe that it should not be fetched, because the predicate seems to evaluate to false:
SELECT 1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0 FROM t0; -- 0
When removing the UNIQUE, the query executes as expected.
drh added on 2019-12-20 20:56:35:
The original problem reported above is fixed by check-in [728ad39e3bd07a25]. However, there is a related problem demonstrated as follows:
CREATE TABLE t1(a TEXT AS(b) COLLATE nocase, b TEXT, c INT, d DEFAULT 1); INSERT INTO t1(b,c) VALUES('abc',11),('DEF',22),('ghi',33); SELECT a FROM t1 WHERE b='DEF' AND a='def'; -- returns one row as expected. CREATE INDEX t1bca ON t1(b,c,a); SELECT a FROM t1 WHERE b='DEF' AND a='def'; -- returns no rows - error
It appears that collating sequence for a generated column is forgotten when that column is accessed through a covering index.