On 8/16/17, Jens Alfke <j...@mooseyard.com> wrote:
> I have code that generates SQL queries that sometimes use collation, via the
> COLLATE expression. In some cases the collation doesn’t take effect; after
> some debugging I’ve narrowed the problem down to this simple test case in
> the SQLite 3.16 shell:
>
> sqlite> SELECT 1 WHERE 'foo' = 'FOO' COLLATE nocase;
> 1 = 1
> sqlite> SELECT 1 WHERE ('foo' = 'FOO') COLLATE nocase;
> sqlite>
>
> So it appears the COLLATE operator is ignored if the expression it applies
> to is parenthesized, which is surprising.
>
> The only documentation I can find says "The COLLATE operator is a unary
> postfix operator that assigns a collating sequence to an expression. The
> COLLATE operator has a higher precedence (binds more tightly) than any
> binary operator and any unary prefix operator except ‘~’.”
>
So when you say:
('xyz' = 'XYZ') COLLATE nocase
That is assigning the "nocase" collating sequence to the *result* of
'xyz'='XYZ', not the input operands. So the "nocase" does not take
effect until after the comparison.
Remember: COLLATE binds more tightly than ==. So
'xyz' = 'XYZ' COLLATE nocase
gets parsed out as
'xyz' = ('XYZ' COLLATE nocase)
And that expression has a different meaning from your
('xyz' = 'XYZ') COLLATE nocase
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
