MySQL Bugs: #23932: Using LAST_INSERT_ID() in a function doesn't completely work

Bug #23932	Using LAST_INSERT_ID() in a function doesn't completely work
Submitted:	3 Nov 2006 10:53	Modified:	6 Sep 2007 12:56
Reporter:	Tomash Brechko	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.21, 5.0.26, 5.1.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After the fix of bug#21726 the following problem remained: if the statement doesn't call LAST_INSERT_ID(), but calls functions that use LAST_INSERT_ID(), then the value returned in those functions is affected by auto increment values generated for current statement.  See "How to repeat".

How to repeat:
DROP TABLE IF EXISTS t1, t2;
DROP FUNCTION IF EXISTS f1;

CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
CREATE TABLE t2 (i INT);

delimiter |
CREATE FUNCTION f1() RETURNS INT
BEGIN
  INSERT INTO t2 VALUES (LAST_INSERT_ID());
  RETURN 0;
END |
delimiter ;

INSERT INTO t1 VALUES (NULL, NULL);

# Statement A
INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, f1()), (NULL, f1());
# Statement B
INSERT INTO t1 VALUES (NULL, NULL), (NULL, f1()), (NULL, f1());

SELECT * FROM t2;
#+------+
#| i    |
#+------+
#|    1 | <= First two values are equal because statement A called
#|    1 |    LAST_INSERT_ID().
#|    5 | <= This and the following should be 4, but the values were affected
#|    6 |    by current statement B, which doesn't call LAST_INSERT_ID().
#+------+

Waiting for the bug to be verified first.

With newer version I've got different results:

mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected (0.20 sec)

mysql> DROP FUNCTION IF EXISTS f1;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t2 (i INT);
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter |
mysql> CREATE FUNCTION f1() RETURNS INT
    -> BEGIN
    ->   INSERT INTO t2 VALUES (LAST_INSERT_ID());
    ->   RETURN 0;
    -> END |
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter ;
mysql> INSERT INTO t1 VALUES (NULL, NULL);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, f1()), (NULL, f1()
);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 VALUES (NULL, NULL), (NULL, f1()), (NULL, f1());
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| i    |
+------+
|    2 |
|    3 |
|    5 |
|    6 |
+------+
4 rows in set (0.00 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.44-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)

So, please, check if we still have this bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".