MySQL returns last inserted when querying IS NULL
Whenever I do a SELECT statement with WHERE id is NULL directly after an INSERT , I get the last inserted row.
I am using MySQL 5.1.73.
It happens directly in the MySQL shell; here is my console:
mysql> CREATE TABLE testing (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> name VARCHAR(200),
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO testing (name) VALUES ('test');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM testing WHERE id IS NULL;
+----+------+
| id | name |
+----+------+
| 1 | test |
+----+------+
1 row in set (0.01 sec)
Can anyone tell me what's going on here? Is this a bug or is it a setting I am missing?
I have found the answer myself. My version of MySQL (5.1.73, the last available on CentOS 6) has the setting sql_auto_is_null by default on, while newer versions don't:
╔═════════════════════════════╦══════════════════╦══════════════════╗ ║ System Variable (<= 5.5.2) ║ Name ║ sql_auto_is_null ║ ║ ║ Variable Scope ║ Session ║ ║ ║ Dynamic Variable ║ Yes ║ ╠═════════════════════════════╬══════════════════╬══════════════════╣ ║ System Variable (>= 5.5.3) ║ Name ║ sql_auto_is_null ║ ║ ║ Variable Scope ║ Global, Session ║ ║ ║ Dynamic Variable ║ Yes ║ ╠═════════════════════════════╬══════════════════╬══════════════════╣ ║ Permitted Values (<= 5.5.2) ║ Type ║ boolean ║ ║ ║ Default ║ 1 ║ ╠═════════════════════════════╬══════════════════╬══════════════════╣ ║ Permitted Values (>= 5.5.3) ║ Type ║ boolean ║ ║ ║ Default ║ 0 ║ ╚═════════════════════════════╩══════════════════╩══════════════════╝
If this 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. For details, including the return value after a multiple-row insert, see Section 12.14, “Information Functions”. If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.
The behavior of retrieving an AUTO_INCREMENT value by using an IS NULL comparison is used by some ODBC programs, such as Access. See Obtaining Auto-Increment Values. This behavior can be disabled by setting sql_auto_is_null to 0.
The default value of sql_auto_is_null is 0 as of MySQL 5.5.3, and 1 for earlier versions.