Bug #58635 Add a non-ODBC compatibility mode for SELECT NULL after auto increment id insert
Submitted: 1 Dec 2010 15:53 Modified: 1 Dec 2010 16:04
Reporter: Martin Tsachev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S4 (Feature request)
Version:5.0.51a OS:Linux (2.6.36.1)
Assigned to: CPU Architecture:Any

[1 Dec 2010 15:53] Martin Tsachev
Description:
If you have a table with an auto_increment id SELECT ... WHERE id IS NULL behaves like SELECT ... WHERE id = LAST_INSERT_ID().

It would be nice to have a new mode which disables this ODBC compatibility behaviour.

How to repeat:
CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY);
INSERT INTO test VALUES (NULL);
SELECT * FROM test WHERE id IS NULL; -- returns '1';

Suggested fix:
Return empty set if new mode is set.
[1 Dec 2010 16:04] MySQL Verification Team
Thank you for the bug report.
[7 Dec 2010 14:54] Peter Gulutzan
We already have a variable that controls this:
@@sql_auto_is_null