SQLite

Consider the following test case:

<pre>
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
</pre>

Unexpectedly, the query fetches a row. I believe that it should not be fetched, because the predicate seems to evaluate to false:

<pre>
SELECT 1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0 FROM t0; -- 0
</pre>

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:

<blockquote><verbatim>
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
</verbatim></blockquote>

It appears that collating sequence for a generated column is forgotten
when that column is accessed through a covering index.