MySQL Bugs: #14553: NULL in WHERE resets LAST_INSERT

Bug #14553	NULL in WHERE resets LAST_INSERT_ID
Submitted:	1 Nov 2005 22:37	Modified:	20 Jul 2006 17:43
Reporter:	Scott Noyes (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.16-BK, 5.0.15	OS:	Linux (Linux, Windows XP)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
A SELECT statement with a NULL in the WHERE clause sets the value returned by LAST_INSERT_ID() to 0.  Other tested SELECT statements have no effect on the value returned by LAST_INSERT_ID().

How to repeat:
CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY);
INSERT INTO test VALUES (NULL);
SELECT LAST_INSERT_ID(); -- correctly returns '1'
SELECT * FROM test WHERE id IS NULL; -- returns '1'; I guess this correct
SELECT LAST_INSERT_ID(); -- incorrectly returns '0'

Thank you for a bug report. I was able to verify it just as you described, both with 5.0.15-nt and on Linux, with 5.0.16-BK (ChangeSet@1.1957, 2005-10-29 13:11:34+04:00, konstantin@mysql.com), with InnoDB and MyISAM tables. 

But, I think, the main problem is the SELECT result itself:

mysql> CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO test VALUES (NULL);
Query OK, 1 row affected (0,00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0,03 sec)

That is OK, expected and described. But the following:

mysql> SELECT * FROM test WHERE id IS NULL;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0,00 sec)

is WEIRD, WRONG (from my point of view). You are asking for rows with NULL id, and got row with id=1? It is a BUG, even if it is a documented feature!!!

Then:

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0,00 sec)

mysql> SELECT * FROM test WHERE id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0,01 sec)

mysql> SELECT * FROM test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0,00 sec)

mysql> SELECT * FROM test WHERE id IS NULL;
Empty set (0,00 sec)

The results magically changed. After a couple of SELECTs... It is a BUG!

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0,00 sec)

mysql> INSERT INTO test VALUES (NULL);
Query OK, 1 row affected (0,00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0,00 sec)

mysql> SELECT * FROM test;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0,00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0,00 sec)

You are right, the other SELECTs do not influence the LAST_INSERT_ID() results...

mysql> SELECT * FROM test WHERE id IS NULL;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0,00 sec)

But, NULL is still = 2 here. It is a BUG!

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16    |
+-----------+
1 row in set (0,01 sec)

mysql> show create table test;
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| Table | Create Table
                                                     |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
1 row in set (0,00 sec)

mysql> show variables like 'sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+

It would seem that the extra SELECT queries are not even required to produce that WHERE id IS NULL bug.  Two in a row produce different results.

mysql> CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY);
mysql> INSERT INTO test VALUES (NULL);
mysql> SELECT * FROM test WHERE id IS NULL;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE id IS NULL;
Empty set (0.00 sec)

Actually this is a documented feature. The Reference manual (chapther 13.1.5) says :
"To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query:

SELECT * FROM tbl_name WHERE auto_col IS NULL"

Yes, that part is a documented feature.  But running that query modifies the value returned by LAST_INSERT_ID(), which is the point of this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6437

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8996

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9002

Reviewed by email with a few minor notes.

To make MySQL compatible with some ODBC applications, you can find
the AUTO_INCREMENT value for the last inserted row with the following query:
SELECT * FROM tbl_name WHERE auto_col IS NULL.
This is done with a special code that replaces 'auto_col IS NULL' with
'auto_col = LAST_INSERT_ID'.
However this also resets the LAST_INSERT_ID to 0 as it uses it for a flag
so as to ensure that only the first SELECT ... WHERE auto_col IS NULL
after an INSERT has this special behaviour.

Fixed in 4.1.22, 5.0.25

Noted in 4.1.22, 5.0.25 changelogs.

The use of WHERE col_name IS NULL in SELECT statements
reset the value of LAST_INSERT_ID() to zero.