SQLite

Consider the following example:

CREATE TABLE t0 (c3 TEXT);
INSERT INTO t0(c3) VALUES ('0');
SELECT * FROM t0 WHERE (t0.c3 COLLATE NOCASE) BETWEEN 1 AND '5'; -- expected: no row is fetched, actual: row is fetched

I expect that the BETWEEN operation yields 0, since the first comparison should yield 0 (the affinity conversion should convert 1 to TEXT and '0' >= '1' is 0). However, the row is fetched, which indicates that the expression yields 1. This also applies to the other COLLATE operators.

The documentation states the following: The expression "x BETWEEN y and z" is logically equivalent to two comparisons "x >= y AND x <= z" and works with respect to collating functions as if it were two separate comparisons.

Accordingly, the following query should be equivalent to the one above:

SELECT * FROM t0 WHERE (t0.c3 COLLATE NOCASE >= 1) AND (t0.c3 COLLATE NOCASE <= '5'); -- no row is fetched

As expected for the first query, this one fetches no rows.