SQLite

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.

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.