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 ‘~’.”
What I’m now inferring from this behavior is that when the docs say "assigns a
collating sequence to an expression” they mean only the exact parse-tree node
the COLLATE node follows, not any node inside that. So in this case the
parentheses would be collated with nocase, but not the actual = comparison
within the parentheses.
To me this seems pretty abstruse; only someone working on a parser would draw a
distinction between ‘foo’=‘FOO’ and (‘foo’=‘FOO’)! Also, this behavior means a
collation can’t be applied to a grouped expression, e.g. "(x = ‘foo’ or y =
‘bar’) COLLATE nocase” which would otherwise be a useful shorthand.
Anyway, I can adjust my code accordingly to avoid generating parentheses around
a collated expression; but I’d like to request the docs be amended to point out
that the COLLATE keyword has to exactly follow the expression with no
intervening parentheses.
—Jens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
