SQLite

View Ticket
Login
Ticket Hash: b148fa61059fb6c2e0299b812e64ca20bbea932f
Title: CAST takes implicit COLLATE of its operand
Status: Fixed Type: Documentation
Severity: Minor Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-06-12 20:32:42
Version Found In:
User Comments:
mrigger added on 2019-06-12 15:37:21: (text/x-fossil-wiki)
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>

drh added on 2019-06-12 20:32:42: (text/x-fossil-wiki)
Documentation error fixed here:
[https://www.sqlite.org/docsrc/info/9f887f15e57978df]