Bug #3117 LAST_INSERT_ID() works incorrectly inside stored procedure

• Submitted: 9 Mar 2004 8:23
• Modified: 26 Mar 2004 9:03
• Reporter: Victoria Reznichenko
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• Assigned to: Per-Erik Martin
• CPU Architecture: Any

[9 Mar 2004 8:23] Victoria Reznichenko

Description:
LAST_INSERT_ID() inside stored procedure returns incorrect result.

mysql> create procedure p1(out id int)
    -> begin
    -> insert into t1 values(NULL);
    -> select last_insert_id() into id;
    -> end |
Query OK, 0 rows affected (0.00 sec)

mysql> call p1 (@id)|
Query OK, 0 rows affected (0.00 sec)

mysql> select @id|
+------+
| @id  |
+------+
| 0    |
+------+
1 row in set (0.00 sec)

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

mysql> call p1 (@id)|
Query OK, 0 rows affected (0.00 sec)

mysql> select @id|
+------+
| @id  |
+------+
| 0    |
+------+
1 row in set (0.00 sec)

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

How to repeat:
create table t1(id int auto_increment primary key);

delimiter |

create procedure p1(out id int)
begin
insert into t1 values(NULL);
select last_insert_id() into id;
end |

call p1 (@id)|
select @id|

[11 Mar 2004 21:44] Norbert Seventeen

CREATE PROCEDURE spCustomer_Add (
	pFirstName varchar(100),
	pLastName varchar(100),
	pStatus int,
	pDateStatusChanged datetime,
	pDateCreated timestamp
)
BEGIN

	INSERT INTO Customers (
		FirstName,
		LastName,
		Status,
		DateStatusChanged,
		DateCreated
	)
	VALUES (
		pFirstName,
		pLastName,
		pStatus,
		pDateStatusChanged,
		pDateCreated
	);

	SELECT LAST_INSERT_ID() AS CustomerID;

END

[23 Mar 2004 10:01] Per-Erik Martin

This is not a stored procedure specific bug, the same thing
happens in 4.1 with prepared statements.
The problem is that LAST_INSERT_ID() is evaluated just once, in the parser.

[25 Mar 2004 9:46] Per-Erik Martin

This is fixed in version 4.1 (for the sake of prepared statements), and
will soon be merged into 5.0.

[26 Mar 2004 9:03] Michael Widenius

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

fix will be in 4.1.2 and 5.0.0