/ View Ticket
Ticket Hash: e1e07ef2028c3c22de4ddc264d6b0f3767a7538d
Title: COLLATE in BETWEEN expression is ignored
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-06-16 14:57:07
Version Found In:
User Comments:
mrigger added on 2019-06-15 21:41:17:

Consider the following example:

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.