CAST takes implicit COLLATE of its operand
|User & Date:||mrigger 2019-06-12 15:37:21|
Consider the following example: <pre> 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 </pre> 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: <pre> 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. </pre> This works as expected for other functions and operators, for example: <pre> CREATE TABLE t0(c0 COLLATE NOCASE); INSERT INTO t0(c0) VALUES ('a'); SELECT * FROM t0 WHERE LOWER(t0.c0) = 'A'; -- no row is fetched </pre>
- login: "mrigger"
- mimetype: "text/x-fossil-wiki"
- severity changed to: "Critical"
- status changed to: "Open"
- title changed to: "CAST takes implicit COLLATE of its operand"
- type changed to: "Code_Defect"