Bug #2079 IS Null teturns wrong result
Submitted: 10 Dec 2003 9:03 Modified: 10 Dec 2003 10:38
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.17 (bk) OS:Linux (linux)
Assigned to: Dean Ellis CPU Architecture:Any

[10 Dec 2003 9:03] Peter Zaitsev 
Description:
This bug is taken from MySQL Gotchas page.

It looks like IS NULL sometimes returns wrong value:

How to repeat:
mysql> CREATE TABLE mysql (
    ->        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->        interesting ENUM('0','1')
    ->      );
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO mysql (interesting) VALUES ('0');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM mysql WHERE id IS NULL AND interesting=1;
+----+-------------+
| id | interesting |
+----+-------------+
|  1 | 0           |
+----+-------------+
1 row in set (0.07 sec)

mysql> SELECT * FROM mysql WHERE id IS NULL;
Empty set (0.00 sec)
[10 Dec 2003 9:10] Dean Ellis 
Read:

http://www.mysql.com/doc/en/ODBC_and_last_insert_id.html
[10 Dec 2003 10:38] Sergei Golubchik 
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