MySQL Bugs: #45231: Cannot change LAST_INSERT_ID value within before update trigger

Bug #45231	Cannot change LAST_INSERT_ID value within before update trigger
Submitted:	1 Jun 2009 3:07	Modified:	1 Jun 2009 20:29
Reporter:	Dennis Birkholz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.60	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	LAST_INSERT_ID, trigger

Description:
From within before insert triggers it is possible to set the "auto_increment primary key" column (I name it ID) with "SET new.ID=XXX;" but it is not possible to populate the value used by using "SET new.ID=LAST_INSERT_ID(XXX);"

I know this behaviour is explained in the manual (http://dev.mysql.com/doc/refman/5.0/en/stored-routines-last-insert-id.html) but it is in case of "before insert" triggers by my understanding a bug!

As discussed at other locations, the innodb auto_increment behaviour (innodb recalculates the next auto_increment value from the values in the table and does not store it) can lead to enormous problems and i thought i found a workaround using "before insert" triggers but the LAST_INSERT_ID function prevents that!

How to repeat:
CREATE TABLE last_insert_id_test (id int unsigned auto_increment primary key);
INSERT INTO last_insert_id_test values (null);
SELECT LAST_INSERT_ID();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

CREATE TRIGGER last_insert_id_test_before_insert BEFORE INSERT ON last_insert_id_test FOR EACH ROW SET NEW.id=(SELECT MAX(id)+10 FROM last_insert_id_test);

INSERT INTO last_insert_id_test values (null);
SELECT LAST_INSERT_ID();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

SELECT * FROM last_insert_id_test;
+----+
| id |
+----+
|  1 |
| 11 |
+----+

Suggested fix:
Triggers that can modify auto_increment values should be able to change the value of LAST_INSERT_ID().

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

Please also read at http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id:

"LAST_INSERT_ID() (with no argument) returns the first  automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed  INSERT statement to affect such a column. "

In your case you set this value explicitly, so it can not be considered automatically generated.

Because a feature is documented it is not necessarily the correct behaviour, it may also be a bug to the majority of the users!

I am aware of the documentation on mysql.com, but it seems to me that not much people actually use triggers so this problem has not occured very often to other people.

I can live with a change to feature request with the following actual "request":
If LAST_INSERT_ID(XXX) is used inside a BEFORE INSERT trigger and the trigger sets an auto_increment column that normaly would be filled by a computed value, a call to LAST_INSERT_ID after the BEFORE INSERT trigger has finished should return the value written by the trigger.

Nevertheless thank you for a great product!

Thank you for the feedback.

Verified as feature request, although I personally like more simple and predictable behavior of functions.

Current workaround: use user variables (these which can be set like SET @last_insert_id) or temporary table and update it from the trigger.