Bug #62402 WHERE <autoinc-field> IS NULL Returns Inconsistent Results when

• Submitted: 10 Sep 2011 1:30
• Modified: 10 Sep 2011 18:11
• Reporter: James Pyrich
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.41
• OS: Linux
• CPU Architecture: Any

[10 Sep 2011 1:30] 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 4:12] MySQL Verification Team

Thank you for the bug report. Could you please read about the use of sql_auto_is_null:

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_auto_is_nul...

[miguel@ilde ~]$ dbs/5.1/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.60-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE "sql_auto_is_null";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_auto_is_null | ON    |
+------------------+-------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `t1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(15) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> INSERT INTO `t1` (`name`) VALUES ('name1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (`name`) VALUES ('name2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (`name`) VALUES ('name3');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT COUNT(*) FROM `t1` WHERE `t1`.`id` IS NULL;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM `t1` WHERE `t1`.`id` IS NULL;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql> 
mysql> SET sql_auto_is_null=0;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(15) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO `t1` (`name`) VALUES ('name1');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `t1` (`name`) VALUES ('name2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (`name`) VALUES ('name3');
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SELECT COUNT(*) FROM `t1` WHERE `t1`.`id` IS NULL;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM `t1` WHERE `t1`.`id` IS NULL;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>