Bug #22335 last_insert_id() in stored function fails

• Submitted: 14 Sep 2006 9:48
• Modified: 14 Sep 2006 12:20
• Reporter: Sedat Onur ORAKOGLU
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.23-community-max-nt
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[14 Sep 2006 9:48] Sedat Onur ORAKOGLU

Description:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table aa (id int auto_increment,f1 int,primary key (id));
Query OK, 0 rows affected (0.03 sec)

mysql> create table bb (id int auto_increment,f2 int,primary key (id));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into aa (f1) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bb (f2) values (last_insert_id());
Query OK, 1 row affected (0.00 sec)

mysql> select * from aa;
+----+------+
| id | f1   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from bb;
+----+------+
| id | f2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

mysql> insert into aa (f1) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into aa (f1) values (3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bb (f2) values (last_insert_id());
Query OK, 1 row affected (0.00 sec)

mysql> select * from aa;
+----+------+
| id | f1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from bb;
+----+------+
| id | f2   |
+----+------+
|  1 |    1 |
|  2 |    3 |
+----+------+
2 rows in set (0.00 sec)

mysql> delimiter $
mysql> create function test(aid int) returns int
    -> begin
    -> declare a1,a2 int;
    -> insert into aa (f1) values (4);
    -> set a1 = last_insert_id();
    -> insert into aa (f1) values (5);
    -> set a2 = last_insert_id();
    -> insert into bb (f2) values (a1),(a2);
    -> return a1;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select * from aa;
+----+------+
| id | f1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from bb;
+----+------+
| id | f2   |
+----+------+
|  1 |    1 |
|  2 |    3 |
+----+------+
2 rows in set (0.00 sec)

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

mysql> select * from aa;
+----+------+
| id | f1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from bb;
+----+------+
| id | f2   |
+----+------+
|  1 |    1 |
|  2 |    3 |
|  3 |    4 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

with the test function i insert the last two last_insert_id s to bb table
but the last_insert_id s on aa table are 4 and 5 not 4 and 4.

How to repeat:
always

[14 Sep 2006 12:20] Valeriy Kravchuk

Thank you for a problem report. This is a duplicate of bug #21812, and may be also related to bug #21726.