MySQL Bugs: #44144: INSERT INTO in Stored Function, outside LAST_INSERT

Bug #44144	INSERT INTO in Stored Function, outside LAST_INSERT_ID() return old ID
Submitted:	8 Apr 2009 9:17	Modified:	8 Apr 2009 12:40
Reporter:	Richard Teubel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.45-log, 5.0.51a-3ubuntu5.4-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	insert into, LAST_INSERT_ID, Stored Functions

Description:
Hello,

I should like to use INSERT INTO in a Stored-Function. This function returns a Varchar and I need the LAST_INSERT_ID(). Outside of function I get again and again the same old ID.

I can write the LAST_INSERT_ID in the function into a global variable (@my_lastinsertid) and work with this. But, I think it is a bug that I can't get the LAST_INSERT_ID outside a function.

kind regards
Richard

How to repeat:
CREATE TABLE `a_Table` (
  `ID` int(11) NOT NULL auto_increment,
  `field2` varchar(20) collate latin1_german2_ci default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

DELIMITER //

CREATE DEFINER = 'root'@'%' FUNCTION `a_insert`()
    RETURNS varchar(20) CHARSET latin1
    DETERMINISTIC
    MODIFIES SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  INSERT INTO a_Table
  SET field2 = 'X';

  RETURN 'mytext';
END //

DELIMITER ;

INSERT INTO a_Table
SET field2 = 'X';
Query OK, 1 row affected (0.01 sec)

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               34 |
+------------------+
1 row in set (0.00 sec)

SELECT a_insert();
+------------+
| a_insert() |
+------------+
| mytext     |
+------------+
1 row in set (0.02 sec)

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               34 | <-- wrong
+------------------+
1 row in set (0.00 sec)

SELECT MAX(ID) FROM a_Table;
+---------+
| MAX(ID) |
+---------+
|      35 |
+---------+
1 row in set (0.00 sec)

Sorry, but this is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id:

"Within the body of a stored routine (procedure or function) or a trigger, the value of LAST_INSERT_ID() changes the same way as for statements executed outside the body of these kinds of objects. The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:

    - If a stored procedure executes statements that change the value of LAST_INSERT_ID(), the changed value will be seen by statements that follow the procedure call.
    - 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."