COLLATE doesn't work with parenthesized expressions (doc issue)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

COLLATE doesn't work with parenthesized expressions (doc issue)

Jens Alfke-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: COLLATE doesn't work with parenthesized expressions (doc issue)

Simon Slavin-3
On 17 Aug 2017, at 12:46am, Jens Alfke <[hidden email]> wrote:

> 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.

Is there a chance this isn’t doing what you think it’s doing ?  Perhaps it is evaluating the contents of the brackets first, then getting a true/false value, and applying the COLLATE to that value.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: COLLATE doesn't work with parenthesized expressions (doc issue)

Richard Hipp-3
In reply to this post by Jens Alfke-2
On 8/16/17, Jens Alfke <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: COLLATE doesn't work with parenthesized expressions (doc issue)

Jens Alfke-2
In reply to this post by Simon Slavin-3

> On Aug 16, 2017, at 4:52 PM, Simon Slavin <[hidden email]> wrote:
>
> Is there a chance this isn’t doing what you think it’s doing ?  Perhaps it is evaluating the contents of the brackets first, then getting a true/false value, and applying the COLLATE to that value.


That would make sense if COLLATE were a normal unary operator, but it isn’t. COLLATE doesn’t apply to a value, it has to be applied _during_ the computation of that value (the “=“ operation) because it affects the way the strings are compared. So it’s really not an operator, more of an annotation of an expression that affects its behavior.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: COLLATE doesn't work with parenthesized expressions (doc issue)

Jens Alfke-2
In reply to this post by Richard Hipp-3

> On Aug 16, 2017, at 4:55 PM, Richard Hipp <[hidden email]> wrote:
>
> 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

Oh, so collation is actually an attribute of a string value, that gets applied during comparisons involving that value? That’s very different from my mental model!

In that case, what is the meaning of
        ('foo' COLLATE binary) = ('FOO' COLLATE nocase)
i.e. which of the two competing collations does the = operator decide to use?

And is the collation attribute passed around with the string value, through intervening expressions? I.e. is this expression true?
        'FOO' = ifnull(null, 'foo' COLLATE nocase)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: COLLATE doesn't work with parenthesized expressions (doc issue)

Dan Kennedy-4
On 08/17/2017 07:07 AM, Jens Alfke wrote:

>> On Aug 16, 2017, at 4:55 PM, Richard Hipp <[hidden email]> wrote:
>>
>> 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
> Oh, so collation is actually an attribute of a string value, that gets applied during comparisons involving that value? That’s very different from my mental model!
>
> In that case, what is the meaning of
> ('foo' COLLATE binary) = ('FOO' COLLATE nocase)
> i.e. which of the two competing collations does the = operator decide to use?
>
> And is the collation attribute passed around with the string value, through intervening expressions? I.e. is this expression true?
> 'FOO' = ifnull(null, 'foo' COLLATE nocase)

"binary" and "yes". Full rules explained here:

   https://sqlite.org/datatype3.html#collating_sequences

Dan.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users