I'm using mySQL server version 4.1.0-alpha-max-nt on Windows XP and have noticed the following erroneous behaviour:

The IN comparison operator seems to completely disregard case-sensitivity enforced by BINARY at table creation. The table was created like so:

CREATE TABLE mytable( mykey int NOT NULL, myfield char(1) BINARY);

myfield can contain integers, uppercase or lowercase letters i.e. [0-9]|[a-z]|[A-Z]

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.

Query (i) completely excludes rows containing 'D' - which it shouldn't do!!! It should only exclude 'd' as well as 'F' of course. Query (ii) seems to behave correctly.

Is this a bug or am I missing something here?

Thanks for looking into this.