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

[17 Jul 2007 23:56] mike eldridge
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.
[17 Jul 2007 23:58] mike eldridge
one more thing that may be pertinent -- i'm running the AMD64 debian distribution
[17 Jul 2007 23:58] Jim Winstead
This is intentional behavior. See the SQL_AUTO_IS_NULL variable:

http://dev.mysql.com/doc/refman/5.0/en/set-option.html
[18 Jul 2007 0:28] mike eldridge
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!
[6 Dec 2008 22:12] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=41282 marked as duplicate of this one.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.