/ Ticket Change Details
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference
Overview

Artifact ID: 985e96ae550519fad354cb22c9c3244029ca5670ddca2c0b9470113323f3ee86
Ticket: b148fa61059fb6c2e0299b812e64ca20bbea932f
CAST takes implicit COLLATE of its operand
User & Date: mrigger 2019-06-12 15:37:21
Changes

  1. 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
    
  2. Change login to "mrigger"
  3. Change mimetype to "text/x-fossil-wiki"
  4. Change severity to "Critical"
  5. Change status to "Open"
  6. Change title to "CAST takes implicit COLLATE of its operand"
  7. Change type to "Code_Defect"