The IN comparison operator seems to

[xencatx]

The IN comparison operator seems to completely disregard case-sensitivity enforced by BINARY at table creation.
Example, the queries:
(i) SELECT * FROM mytable WHERE myfield NOT IN ('F', 'd');
(ii) SELECT * FROM mytable WHERE (myfield != 'F') OR (myfield != 'd');
return completely different sets even though 'myfield' has been declared a BINARY field.
Hmmm! Is this a bug or am I doing something wrong here?
Any thoughts?

xencatx

 

[swampBoogie]

SELECT * FROM mytable WHERE (myfield != 'F') and (myfield != 'd');

is the same as (i)

 

[xencatx]

Sorry to disagree with you swampBoogie but the figures speak otherwise:

SELECT * FROM mytable WHERE myfield NOT IN ('F', 'd')
RETURNS 219 rows

SELECT * FROM mytable WHERE (myfield != 'F') AND (myfield != 'd')
RETURNS 260

SELECT * FROM mytable WHERE (myfield != 'F') OR (myfield != 'd')
RETURNS 260 rows

Cheers

xencatx

 

[swampBoogie]

The first two queries in your example should return the same result. So, this indicates an erroneous behaviour in Mysql.

 

[xencatx]

Aha! My point exactly - mySQL *is* behaving rather erroneously! Do you think I should report this to the DataKonsult guys?

 

[swampBoogie]

Sure, go ahead.