Bug #52673 Auto increment is null

• Submitted: 7 Apr 2010 23:20
• Modified: 8 Apr 2010 3:22
• Reporter: Phil Taylor
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0 - 5.1
• OS: Any
• 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.