CAST takes implicit COLLATE of its operand
|User & Date:||mrigger 2019-06-12 15:37:21|
- Change icomment to:
Consider the following example:
CREATE TABLE t0(c0 COLLATE NOCASE); INSERT INTO t0(c0) VALUES ('a'); SELECT * FROM t0 WHERE CAST(t0.c0 AS TEXT) = 'A'; -- expected: no row is fetched, actual: a
I would expect the comparison to yield 0, since the left operand is 'a', and the right one 'A'. Neither operand has an explicit collating function assigned and only t0.c0 has an implicit one. It seems that the comparison takes the implicit collating function of c0, although the column is surrounded by the cast. The documentation on implicit collating functions, however, only mentions these cases:
If either operand is a column, then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence, a column name preceded by one or more unary "+" operators is still considered a column name.
This works as expected for other functions and operators, for example:
CREATE TABLE t0(c0 COLLATE NOCASE); INSERT INTO t0(c0) VALUES ('a'); SELECT * FROM t0 WHERE LOWER(t0.c0) = 'A'; -- no row is fetched
- Change login to "mrigger"
- Change mimetype to "text/x-fossil-wiki"
- Change severity to "Critical"
- Change status to "Open"
- Change title to "CAST takes implicit COLLATE of its operand"
- Change type to "Code_Defect"