MySQL returns last inserted when querying IS NULL

Question

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?

Answer 1

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.