Hi all,
The problem is actually not one of precedence. The problem is that
MySQL does not honour a true boolean type (internally for computation
- not talking about storage)
Look here:
<boolean value expression> ::=
<boolean term>
| <boolean value expression> OR <boolean term>
<boolean term> ::=
<boolean factor>
| <boolean term> AND <boolean factor>
<boolean factor> ::= [ NOT ] <boolean test>
So, in standard SQL, NOT has a higher precedence than AND and OR,
which is reasonable:
NOT isnull(col) AND a = b
should executed as:
(NOT isnull(col)) AND a = b
and this is what everybody expects. In oldskool MySQL behaviour, you get this:
NOT (isnull(col)) AND a = b)
which is....ermm....*very* confusing IMO.
Now the real problem. MySQL works like: Ah, I see NOT, whatever
follows not *must be* a boolean expression, and tada....MySQl happily
converts whatever comes first after it to a boolean. The result of
that boolean operator NOT is again not a true boolean, but just a
number, and this allows this:
NOT a BETWEEN b
to be parsed as
(NOT a) BETWEEN b.
In standard sql this can never occur, because the BETWEEN operator is
not defined for booleans, and NOT is not defined for anything but
booleans. But because MySQL automatically pretends it's all integers
anyway, you get this strange and IMO counterintuitive behavio.
SO in my opinion, drizzle (MySQL too) should simply honour an internal
boolean type, and use "high" not precedence (conform the standard SQL)
I hope this helps,
kind regards
On Fri, Sep 12, 2008 at 11:15 AM, <[EMAIL PROTECTED]> wrote:
> I'm a little confused (and fairly new to the list), so please forgive me if
> I've got the wrong end of the stick...
>
> So the SQL standard states that NOT has high precedence so
> NOT a BETWEEN b AND c
> evaluates to
> (NOT a) BETWEEN b AND c
> (does anyone have a link to the SQL standard?)
>
> But according to the MySQL docs:
>
>> From MySQL 5.0.2 on, the precedence of the NOT operator is such that
>> expressions such as NOT a BETWEEN b AND c are parsed as
>> NOT (a BETWEEN b AND c). Before MySQL 5.0.2, the expression is parsed as
>> (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be
> obtained
>> by enabling the HIGH_NOT_PRECEDENCE SQL mode.
>
> So as of MySQL 5.0.2, the default syntax deviates from the standard? Or am
> I
> mistaken?
>
>> If we implement the SQL standard behaviour in Drizzle, there is no
>> workaround to enable the old behaviour.
>
> Wouldn't the workaround be to add parens around the between?
> NOT (a BETWEEN b AND c)
> Or use the more natural syntax of
> a NOT BETWEEN b AND c
>
> As the whole effort behind Drizzle seems to be about doing things right
> (I'll be glad to see the back of multiple character encodings), then I
> would vote to go with the standard whenever possible.
>
>
> http://www.mintel.com
> providing insight + impact
>
> PS Discover and develop genuinely new ideas first with Mintel Inspire -
> your global trends resource.
>
> Preview our Trendscape and get on the fast track to productive ideation.
> http://www.mintel.com/inspire/
>
> London Office:
> Mintel International Group Ltd (Mintel)
> 18-19 Long Lane
> London
> EC1A 9PL
> UK
>
> Registered in England: Number 1475918.
> VAT Number: GB 232 9342 72
>
> Tel: 020 7606 4533
> Fax: 020 7606 5932
>
>
> Chicago Office:
> Mintel International Group Ltd (Mintel)
> Floor 8
> 351 West Hubbard Street
> Chicago
> IL 60610
> USA
>
> Tel: 312 932 0400
> Fax: 312 932 0469
>
> Notice
> ********
> This email may contain information that is privileged,
> confidential or otherwise protected from disclosure. It
> must not be used by, or its contents copied or disclosed
> to, persons other than the addressee. If you have received
> this email in error please notify the sender immediately
> and delete the email. Any views or opinions expressed in
> this message are solely those of the author, and do not
> necessarily reflect those of Mintel.
>
> No Mintel staff are authorised to make purchases using
> email or over the internet, and any contracts so performed
> are invalid.
>
> Contact: [EMAIL PROTECTED]
>
> Warning
> **********
> It is the responsibility of the recipient to ensure that
> the onward transmission, opening or use of this message
> and any attachments will not adversely affect their systems
> or data. Please carry out such virus and other checks, as
> you consider appropriate.
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~drizzle-discuss
> Post to : [email protected]
> Unsubscribe : https://launchpad.net/~drizzle-discuss
> More help : https://help.launchpad.net/ListHelp
>
--
Roland Bouman
http://rpbouman.blogspot.com/
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
