(text/x-fossil-wiki)
Consider the following example:
<pre>
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
</pre>
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 <b>works with respect to collating functions as if it were two separate comparisons</b>.
Accordingly, the following query should be equivalent to the one above:
<pre>
SELECT * FROM t0 WHERE (t0.c3 COLLATE NOCASE >= 1) AND (t0.c3 COLLATE NOCASE <= '5'); -- no row is fetched
</pre>
As expected for the first query, this one fetches no rows.
|