MySQL Bugs: #11453: 'not' in select statement

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:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

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

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

i changed it to:

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

but it is the same result.