Bug #51163 Query with "WHERE key_field IS NULL" unexpect behaviour
Submitted: 13 Feb 2010 13:01 Modified: 13 Feb 2010 13:57
Reporter: Oleg Ivanov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.31, 5.0.83, 5.0.75 OS:Any
Assigned to: CPU Architecture:Any
Tags: IS NULL

[13 Feb 2010 13:01] Oleg Ivanov
Description:
Query:
select * from test where id IS NULL;
returns one row while there are no id with NULL value in table.

How to repeat:
Create test table:

CREATE TABLE `test` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`message` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert single row:

INSERT INTO `test` (`message`) VALUES ('old');

Execute same query 2 times (expect empty sets both times):

mysql> select * from test where id IS NULL;
+----+---------+
| id | message |
+----+---------+
| 1 | old |
+----+---------+
1 row in set (0.00 sec)

mysql> select * from test where id IS NULL;
Empty set (0.00 sec)

Same behaviour on update query.

"WHERE id = NULL" works fine.
[13 Feb 2010 13:07] Valeriy Kravchuk
Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_is-null:

"To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:

    - If sql_auto_is_null variable is set to 1 (the default), then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:

      SELECT * FROM tbl_name WHERE auto_col IS NULL

      If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function.
..."

This is not a bug.
[13 Feb 2010 13:57] Oleg Ivanov
Thank you for fast reply, but "IS NULL" behavior is still unclear for me.

If I use "IS NULL" in SELECT query mysql returns result only first time. When I execute the SAME query second time mysql returns empty set. Is it expected behavior?

mysql> INSERT INTO `test` (`message`) VALUES ('old');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where id IS NULL;
+----+---------+
| id | message |
+----+---------+
|  1 | old     | 
+----+---------+
1 row in set (0.00 sec)

mysql> select * from test where id IS NULL;
Empty set (0.00 sec)

With LAST_INSERT_ID() mysql returns one row on both queries:

mysql> INSERT INTO `test` (`message`) VALUES ('old');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where id = LAST_INSERT_ID();
+----+---------+
| id | message |
+----+---------+
|  1 | old     | 
+----+---------+
1 row in set (0.00 sec)

mysql> select * from test where id = LAST_INSERT_ID();
+----+---------+
| id | message |
+----+---------+
|  1 | old     | 
+----+---------+
1 row in set (0.00 sec)
[13 Feb 2010 14:07] Valeriy Kravchuk
Yes, this is expected behavior. IS NULL will return the value just inserted only immediately after INSERT. If you executed any other statement after that, including your first SELECT, IS NULL condition will return only row(s) where the value is really NULL, and you have none like this.

If you are confused by this behavior, please, set sql_auto_is_null variable to 0. You'll always get consistent results then.