MySQL Bugs: #29859: SELECT where IS NULL after INSERT on auto-inc column returns the inserted row

Bug #29859	SELECT where IS NULL after INSERT on auto-inc column returns the inserted row
Submitted:	17 Jul 2007 23:56	Modified:	18 Jul 2007 0:28
Reporter:	mike eldridge	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.32	OS:	Linux (debian etch)
Assigned to:		CPU Architecture:	Any

Description:
first of all, i'm sorry if this has already been reported and fixed.  i tried to find it by searching, but the keyphrases that come to mind aren't very unique.  i also was unable to locate anything in the change logs.

when inserting a row into a table with an auto-inc column and IMMEDIATELY selecting from that table where the id of that column is NULL, you get the INSERTed row back.  the column has the correct auto-inc value, not NULL.  subsequent identical queries return empty result sets as expected.

again, my apologies if this bug has already been reported and fixed.

How to repeat:
CREATE TABLE t1 (id SERIAL);
INSERT INTO t1 VALUES ();
SELECT * FROM t1 WHERE id IS NULL; /* 1 row in set */
SELECT * FROM t1 WHERE id IS NULL; /* Empty set */
SELECT * FROM t1 WHERE id IS NULL; /* Empty set */

this seems to be engine-agnostic, as it behaves identically with both InnoDB and MyISAM tables.  i originally faulted my ORM, but after hours of digging, it is definitely MySQL at fault.

i'm using mysql 5.0.32-Debian_7etch1-log, which i realize is not an official MySQL distribution, but the bug should be easy enough to verify.

Suggested fix:
not sure, i'm not familiar with mysql internals.

one more thing that may be pertinent -- i'm running the AMD64 debian distribution

This is intentional behavior. See the SQL_AUTO_IS_NULL variable:

http://dev.mysql.com/doc/refman/5.0/en/set-option.html

wow.  that's odd behavior.

i'm unable to find a method to disable it on a global scale.  if it is truly a session-only variable, then it appears that the only way to turn this function off is to send SET SQL_AUTO_IS_NULL=0 every time that my application connects or reconnects to the database.

please tell me i don't have to do this.

thanks for the quick response!  sorry for the spurious bug report!

Bug: http://bugs.mysql.com/bug.php?id=41282 marked as duplicate of this one.