MySQL Bugs: #65849: LAST_INSERT_ID() returns multiple rows under certain circumstances

Bug #65849	LAST_INSERT_ID() returns multiple rows under certain circumstances
Submitted:	9 Jul 2012 10:09	Modified:	9 Jul 2012 14:47
Reporter:	Ian Wright	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.24	OS:	Linux (Ubuntu 12.04)
Assigned to:		CPU Architecture:	Any

Description:
Under certain circumstances LAST_INSERT_ID returns 1 row for each row in the table after a successful insert

How to repeat:
See attached dump then run

INSERT INTO `location` (countryId,countryRegion, lat,lon)
            SELECT 15, NULL, 32.018836975097656, -25.975967407226562;

    SELECT LAST_INSERT_ID() FROM location;

I do not see any dump attached yet.

This is what I get:

mysql> INSERT INTO `location` (countryId,countryRegion, lat,lon)
    ->             SELECT 15, NULL, 32.018836975097656, -25.975967407226562;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT LAST_INSERT_ID() FROM location;
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              482 |
|              482 |
...
|              482 |
+------------------+
239 rows in set (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              482 |
+------------------+
1 row in set (0.00 sec)

So, what? Why is this a bug? 

Our manual, http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id, clearly says:

"LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted."

There were no INSERTs after that one that generated value 482, so it's expected for last_insert_id() to return it every time, no matter how many times you call it.

I should not have left the FROM location in the example query - this was an attempt to get the select to return only one row and a misreading of the docs (I thought that the FROM here would ensure the it was the LAST_INSERT_ID from the location table)

The vast majority of the time LAST_INSERT_ID returns one row therefore the code below will work:

 DECLARE locationId1 int(11);
 SELECT LAST_INSERT_ID() INTO locationId1;

However sometimes SELECT LAST_INSERT_ID() returns multiple rows then the code above will not work as it's not possible to select multiple rows into the variable.
Error Code: 1172. Result consisted of more than one row

Apart from anything else it should be consistent - I accept that the behaviour is undefined when no insertion has been made but here there has been an insertion.

It's not quite as easily repeatable as I first thought so I'm attaching a fuller example testdb1.sql 

If you run CALL createLocations(); then it will always stop at the same point with this error

db dump containing createLocations procedure

Attachment: testdb1.sql (text/x-sql), 61.11 KiB.

Wrong diagnosis - sorry