MySQL Bugs: #623: NOT / LIKE operator precedence

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	3.23.54	OS:	Linux (Linux (PLD))
Assigned to:		CPU Architecture:	Any

Description:
'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

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.

expression 
NOT a LIKE b 
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:

a NOT LIKE b

This way there are consistant boolean algebra rules.

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