| Bug #15728 | LAST_INSERT_ID function inside a stored function returns 0 | ||
|---|---|---|---|
| Submitted: | 14 Dec 2005 4:01 | Modified: | 2 May 2006 1:29 |
| Reporter: | Elizalde Baguinon | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S1 (Critical) |
| Version: | 5.0.18-BK, 5.0.15 | OS: | Linux (Linux, Windows 2000) |
| Assigned to: | Tomash Brechko | CPU Architecture: | Any |
[14 Dec 2005 13:30]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 5.0.18-BK (ChangeSet@1.1977, 2005-12-13 17:33:19+01:00) on Linux:
...
mysql> #test our stored function
mysql> SELECT SCOPE_IDENTITY();
+------------------+
| SCOPE_IDENTITY() |
+------------------+
| 0 |
+------------------+
1 row in set (0,00 sec)
mysql> SELECT LAST_INSERT_ID(); #returns the expected output
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0,00 sec)
mysql> INSERT INTO `test`.`TestTable` (Name) values ('Foo');
Query OK, 1 row affected (0,01 sec)
mysql> SELECT SCOPE_IDENTITY();
+------------------+
| SCOPE_IDENTITY() |
+------------------+
| 0 |
+------------------+
1 row in set (0,00 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0,00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18 |
+-----------+
1 row in set (0,02 sec)
There is a similar bug report already, marked as "Not a bug",
http://bugs.mysql.com/bug.php?id=5017. But it looks like the real bug is not fixed yet.
[18 Apr 2006 16:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/5096
[21 Apr 2006 14:53]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/5306
[25 Apr 2006 21:34]
Dmitry Lenev
Fixed in 5.0.21 and 5.1.10
[2 May 2006 1:29]
Paul DuBois
Noted in 5.0.21, 5.1.10 changelogs. <literal>LAST_INSERT_ID()</literal> in a stored function or trigger returned zero. . (Bug #15728)
Description: I made a stored function called SCOPE_IDENTITY that returns an integer from a result of a call of LAST_INSERT_ID. The stored function I made returned 0 after I inserted a record on a table. The stored procedure using the same produces expected results otherwise. How to repeat: USE test; #create a table for inserts DROP TABLE IF EXISTS `test`.`TestTable`; CREATE TABLE `TestTable` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(15) default NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; #create the stored function DROP FUNCTION IF EXISTS `test`.`SCOPE_IDENTITY`; CREATE FUNCTION `SCOPE_IDENTITY`() RETURNS int(11) RETURN LAST_INSERT_ID(); #notice the LAST_INSERT_ID() function #after the table and the stored function has been created, lets test: INSERT INTO `test`.`TestTable` (Name) values ('Foo'); #test our stored function SELECT SCOPE_IDENTITY(); #returns 0, expected output is the last inserted id #after testing it, the data returned is 0, whereas if we use the #LAST_INSERT_ID function, it still can retrieve the last inserted id SELECT LAST_INSERT_ID(); #returns the expected output Suggested fix: Internal.