Bug #623 NOT / LIKE operator precedence
Submitted: 9 Jun 2003 6:57 Modified: 9 Jun 2003 10:02
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23.54 OS:Linux (Linux (PLD))
Assigned to: CPU Architecture:Any

[9 Jun 2003 6:57] [ name withheld ]
'NOT' operator has greater precedence than 'LIKE' in WHERE clause

How to repeat:
Create a query with a WHERE clause: WHERE NOT textfield like 'something'. Then create second one: WHERE NOT (textfield like 'something'). The results aren't the same.

Suggested fix:
LIKE operator should have greater precedence than NOT - probably nobody is using NOT operator against text data
[9 Jun 2003 10:02] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Sorry, but you are wrong. Being unary operator NOT has higher priority in Boolean Algebra as well as in all programming languages. Therefore results MUST be different. 

Consistant rules has to be defined for every boolean operator.

falls into same category as:

NOT a = b    <=>  (NOT a) = b
NOT a AND b  <=>  (NOT a) AND b
NOT a OR b   <=>  (NOT a) OR b

In all of the above cases NOT has higher priortiy than rightmost operator.

If you want to negate the result of rightmost operator in all cases you should use:

NOT (a = b)  
NOT (a AND b)
NOT (a OR b) 

and therefore
NOT (a LIKE b)
is also valid.

NOT (a = b) 

can be rewritten as:
a != b

In similar manner LIKE can be negated with NOT as:


This way there are consistant boolean algebra rules.
[10 Jun 2003 4:22] [ name withheld ]
>Being unary operator NOT has higher priority
>in Boolean Algebra as well as in all programming languages

Of course - that's right. But we're not talking about algebra nor other programming languages. We're talking about SQL and other databases. Please note, that in other DBMS NOT has greater precedence than LIKE in WHERE clause. (I checked MS SQL and Access because I could've done it quickly - I'm pretty sure that e.g Oracle does the same thing)