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
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.41
• OS: Linux
• 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] MySQL Verification Team

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.