Bug #52673 Auto increment is null
Submitted: 7 Apr 2010 23:20 Modified: 8 Apr 2010 3:22
Reporter: Phil Taylor Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 - 5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: auto_increment, null

[7 Apr 2010 23:20] Phil Taylor
Description:
When a table is created with an auto increment field selecting all fields where the auto increment field is NULL returns the record with the last auto increment value.

I would have expected it not to return anything. This seems to be independent of the storage engine (I only tested InnoDB and MyISAM)

How to repeat:
mysql> CREATE TABLE `ivmy` (
  `id` int(11) NOT NULL auto_increment,
  `instance_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

mysql> INSERT INTO `ivmy` (`instance_id`) VALUES ('91');
Query OK, 1 row affected (0.00 sec)

mysql>  SELECT * FROM `ivmy` WHERE `id` IS NULL LIMIT 1;
+----+-------------+
| id | instance_id |
+----+-------------+
|  4 |          91 | 
+----+-------------+
1 row in set (0.00 sec)
[8 Apr 2010 3:22] Valeriy Kravchuk
This is intended and documented behavior. You can also switch it off. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_is-null.