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: | |
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
[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.