Bug #45405 LAST_INSERT_ID and Stored Functions and Triggers
Submitted: 9 Jun 2009 15:26 Modified: 7 Jul 2010 15:34
Reporter: William Chiquito Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.0.81, 5.0, 5.1 bzr OS:Any (MS Windows, Linux)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: LAST_INSERT_ID, Stored Functions, triggers

[9 Jun 2009 15:26] William Chiquito
Description:
Manual says:

"* For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value."

[url=http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id]...]

This is not true.

How to repeat:
create table `t1` (
  `id` int(11) not null auto_increment,
  `mydate` datetime default null,
  primary key  (`id`)
) engine=innodb;

delimiter $$

drop trigger /*!50032 if exists */ `trgbitest`$$

create trigger `trgbitest` before insert on `t1` 
for each row
begin
	set @testbefore = concat(new.id, ' - ', last_insert_id());
end$$

delimiter ;

delimiter $$

drop trigger /*!50032 if exists */ `trgaitest`$$

create trigger `trgaitest` after insert on `t1` 
for each row
begin
	set @testafter = concat(new.id, ' - ', last_insert_id());
end;
$$

delimiter ;

delimiter $$

create function `functest`()
returns int
begin
	insert into t1 (mydate) values (now());
	return last_insert_id();
end$$

delimiter ;

select @testbefore, @testafter;

insert into t1 (mydate) values (now());

select @testbefore, @testafter;

select functest();

select @testbefore, @testafter;
[9 Jun 2009 21:14] Sveta Smirnova
Thank you for the report.

If I understood your complain correctly this seems to be fixed in version 5.1:

select @testbefore, @testafter;
@testbefore     @testafter
NULL    NULL
insert into t1 (mydate) values (now());
select last_insert_id();
last_insert_id()
1
select @testbefore, @testafter;
@testbefore     @testafter
0 - 0   1 - 0
select functest();
functest()
2
select last_insert_id();
last_insert_id()
1
select @testbefore, @testafter;
@testbefore     @testafter
0 - 1   2 - 1

While in 5.0 I get results following:

select @testbefore, @testafter;
@testbefore     @testafter
NULL    NULL
insert into t1 (mydate) values (now());
select last_insert_id();
last_insert_id()
1
select @testbefore, @testafter;
@testbefore     @testafter
0 - 0   1 - 1
select functest();
functest()
2
select last_insert_id();
last_insert_id()
1
select @testbefore, @testafter;
@testbefore     @testafter
0 - 1   2 - 2

Please confirm or reject you think behavior of version 5.1 is same as described in the manual and no fix of docs needed.
[10 Jun 2009 9:48] William Chiquito
Manual says: "* For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value."

In version 5.0 I think that is not correct, both (trigger and stored function) see a changed value following statements and not the end.
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

In version 5.1 I think the documentation is correct for the case of triggers, but not the stored function.
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id
[10 Jun 2009 10:03] Sveta Smirnova
Thank you for the feedback.

Verified as described, but set as server bug, because this behavior changed and change was not documented. Although SELECT last_insert_id(); which follows any statement outputs correct value (doesn't see auto-incremented value) in all cases.
[10 Jun 2009 10:17] William Chiquito
Is correct, the only thing missing is the documented behavior of LAST_INSERT_ID() within the trigger that has changed from version 5.0 to 5.1. Thank you.
[25 Jun 2010 18:29] Paul DuBois
I don't see any trigger in the test case that changes the value.
[25 Jun 2010 18:50] Paul DuBois
For functions, here is a simpler test case:

drop function if exists functest;
create function functest()
  returns int deterministic
  return last_insert_id(15);

select last_insert_id(1);
select functest();
select last_insert_id();

It appears the behavior did change, a long time ago between MySQL 5.0.11 and 5.0.12:

MySQL 5.0.11:

+-------------------+
| last_insert_id(1) |
+-------------------+
|                 1 |
+-------------------+
+------------+
| functest() |
+------------+
|         15 |
+------------+
+------------------+
| last_insert_id() |
+------------------+
|               15 |
+------------------+

MySQL 5.0.12:

+-------------------+
| last_insert_id(1) |
+-------------------+
|                 1 |
+-------------------+
+------------+
| functest() |
+------------+
|         15 |
+------------+
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

Looks like a similar change took place for triggers in the same version.

Simplified test case:

drop table if exists t1; -- also drops triggers
create table t1 (i int);
delimiter $$
create trigger `trgbitest` before insert on `t1`
for each row
begin
    set new.i = last_insert_id(15);
end$$
delimiter ;

select last_insert_id(1);
insert into t1 () values();
select * from t1;        -- changed last_insert_id() value in trigger
select last_insert_id(); -- value seen after trigger

MySQL 5.0.11:

+-------------------+
| last_insert_id(1) |
+-------------------+
|                 1 |
+-------------------+
+------+
| i    |
+------+
|   15 |
+------+
+------------------+
| last_insert_id() |
+------------------+
|               15 |
+------------------+

MySQL 5.0.12:

+-------------------+
| last_insert_id(1) |
+-------------------+
|                 1 |
+-------------------+
+------+
| i    |
+------+
|   15 |
+------+
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

Based on these results, I am inclined to leave the 5.1 manual alone, and update the 5.0 manual to state that the LAST_INSERT_ID() value is restored following a stored function or trigger that changes it, for 5.0.12 and up. Before 5.0.11, the value is not restored and following statements will see the changed value.
[26 Jun 2010 17:25] William Chiquito
I think it right to update the manual for version 5.0
[7 Jul 2010 15:34] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated 5.0 manual as described.