MySQL Bugs: #52651: 'WHERE' clause not working for 'INT DEFAULT 0' values cointaining 'NULL'

Bug #52651	'WHERE' clause not working for 'INT DEFAULT 0' values cointaining 'NULL'
Submitted:	7 Apr 2010 10:33	Modified:	7 Apr 2010 22:09
Reporter:	Marco Hauer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.22-rc-community	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Default NULL, innodb, SELECT, where

Description:
After I altered the default value of a integer column the 'WHERE' Clause didn't work with 'NULL' values.

'NULL' values are not found with the 'WHERE' clause on a integer where the default is set to 0.

How to repeat:
mysql> CREATE TABLE showbug(intbug INT DEFAULT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO showbug VALUES(5), (''), (NULL);
Query OK, 3 rows affected, 1 warning (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM showbug;
+--------+
| intbug |
+--------+
|      5 |
|      0 |
|   NULL |
+--------+
3 rows in set (0.00 sec)

mysql> UPDATE showbug SET intbug=20 WHERE intbug < 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM showbug;
+--------+
| intbug |
+--------+
|      5 |
|     20 |
|   NULL |
+--------+
3 rows in set (0.00 sec)

mysql> UPDATE showbug SET intbug=20 WHERE intbug = NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM showbug;
+--------+
| intbug |
+--------+
|      5 |
|     20 |
|   NULL |
+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM showbug WHERE intbug = NULL;
Empty set (0.00 sec)

Suggested fix:
Fix the engine ;-)

Try replace "WHERE intbug = NULL" with "WHERE intbug IS NULL"

"WHERE intbug = NULL" will match nothing.  Nothing can be = NULL as NULL is NULL (ie. it is not defined what NULL is and that is why is is denoted NULL).

http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html lists
IS NULL:	NULL value test

Peter
(Not a MySQL person - but somebody who fell here 5 years ago himself!)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

mysql 5.1 >SELECT * FROM showbug;
+--------+
| intbug |
+--------+
|      5 |
|      0 |
|   NULL |
+--------+
3 rows in set (0.00 sec)

mysql 5.1 >UPDATE showbug SET intbug=20 WHERE intbug IS NULL;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql 5.1 >SELECT * FROM showbug;
+--------+
| intbug |
+--------+
|      5 |
|      0 |
|     20 |
+--------+
3 rows in set (0.00 sec)

mysql 5.1 >

Ok thanks for the quick reply, now I remember. I didn't use this syntax for a long time and forgot about it. Sorry for the false alarm.