Bug #62403 WHERE <autoinc-field> IS NULL Returns Inconsistent Results on Insert
Submitted: 10 Sep 2011 1:37 Modified: 10 Sep 2011 3:39
Reporter: James Pyrich Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.41 OS:Linux
Assigned to: CPU Architecture:Any

[10 Sep 2011 1:37] James Pyrich
Description:
When a SELECT query is executed, if an auto-increment field is tested for IS NOT NULL immediately after an INSERT statement where the field was not provided, the query will incorrectly return the last-inserted row.

When the auto-increment field is provided, the SELECT query performs as expected.

How to repeat:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `name` varchar(15) NOT NULL,
	  PRIMARY KEY (`id`)
);

INSERT INTO `t1` (`name`) VALUES ('name1');
INSERT INTO `t1` (`name`) VALUES ('name2');
INSERT INTO `t1` (`name`) VALUES ('name3');

-- The following query invalidly returns a count of 1 on the first run, 0 on the second run (correct)
SELECT COUNT(*) FROM `t1` WHERE `t1`.`id` IS NULL;
SELECT COUNT(*) FROM `t1` WHERE `t1`.`id` IS NULL;

-- It occurs again here.
INSERT INTO `t1` (`name`) VALUES ('name4');
SELECT COUNT(*) FROM `t1` WHERE `t1`.`id` IS NULL;
SELECT COUNT(*) FROM `t1` WHERE `t1`.`id` IS NULL;

Suggested fix:
Ensure that the value of the Auto-increment field is immediately available for queries when the INSERT operation is said to have completed.
[10 Sep 2011 2:00] Miguel Solorzano
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

http://bugs.mysql.com/bug.php?id=62402

Thank you for your interest in MySQL.