|Title:||COLLATE in BETWEEN expression is ignored|
|Last Modified:||2019-06-16 14:57:07|
|Version Found In:|
mrigger added on 2019-06-15 21:41:17:
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.