MySQL Bugs: #3896: LAST_INSERT_ID() does not work as documented

Bug #3896	LAST_INSERT_ID() does not work as documented
Submitted:	26 May 2004 11:27	Modified:	26 May 2004 18:19
Reporter:	Tõnu Samuel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.20 official standard binary	OS:	Linux (Suse Linux 9.0)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Manual says (http://dev.mysql.com/doc/mysql/en/Information_functions.html)

"LAST_INSERT_ID() .... Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column."

That is not always true. 

How to repeat:
mysql> create table test  (a int  not null auto_increment primary key );
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into test values (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> select * from test;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

Testcase:

Actually last number is 3, not 1. But documentation says "last" 

Suggested fix:
I first explain what functionality I need, this makes it easier to understand. I am inserting big amount of records into table with single insert commands. I would like to have one big INSERT but I can't. I need to reuse LAST_INSERT_ID() values. I see no official way to recall all of them without inserting records one by one.

I suggest to fix it in way:

Just document it. It is even not so important if it returns first or last ID. It is important to say: "multi-record INSERT is atomic." and "You can calculate all ID numbers by incrementing LAST_INSERT_ID() .....up to number of records inserted...." Something like that.

Right now documentation does not provide glue for such case. Of course I can read the source and work it out but it MUST BE documented. Some users (like me) can reduce number of queries from 120 down to 5 (this is actual estimation of my problem). 4.0.20 is stable. So just document it. Later do whatever you like :)

Sorry, I missed latter "If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row"

Anyway would be nice to get documented way to reproduce all list of ID-s

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

As you note, it does function exactly as documented, so this is not a bug.

There is no third option currently (ie: to see all the #s which were generated).