Bug #11453 'not' in select statement
Submitted: 20 Jun 2005 10:57 Modified: 20 Jun 2005 17:13
Reporter: Udo Korda Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.1.9 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[20 Jun 2005 10:57] Udo Korda
Description:
An example from select:

select * from uort
where uplz >'40000' and uplz <'49999'
and not uistpf='N';

In the field uistpf are 3 variants : 'J', 'N','1'
it's and alphanumeric(1)  field.
By this example i got only the rows that contains '1' in the field uistpf,
but not the rows with value 'J' ?
This script works properly for example in Oracle.
The Version of mysql  is 4.1.11

How to repeat:
the script does not work anytime
[20 Jun 2005 17:13] Hartmut Holzgraefe
In MySQL versions before 5.0.2 the NOT operator has a higher precedence than '=', 
so your statement is evaluated as

  select ... where ... and ((not uistpf)='N');

and not as

  select ... where ... and (not (uistpf='N'));

as expected. 

This has been changed starting with MySQL 5.0.2, see

http://dev.mysql.com/doc/mysql/en/operator-precedence.html
[21 Jun 2005 7:20] Udo Korda
i changed it to:

select * from uort
where  uplz >'40000' and uplz<'49999'
and ((not uistpf)='N');

but it is the same result.