Bug #15728 LAST_INSERT_ID function inside a stored function returns 0

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.

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.

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

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

Fixed in 5.0.21 and 5.1.10

Noted in 5.0.21, 5.1.10 changelogs.

<literal>LAST_INSERT_ID()</literal> in a stored function or
trigger returned zero. . (Bug #15728)