Bug #34073 stored function fails to return a value with a temporary table with auto increme
Submitted: 26 Jan 2008 2:40 Modified: 26 Jan 2008 20:45
Reporter: sfa afddf Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.45 OS:MacOS
Assigned to: CPU Architecture:Any
Tags: auto_increment, stored function, temporary table

[26 Jan 2008 2:40] sfa afddf
Description:
Within a stored function if a temporary table is created with a column set as auto_increment then the function fails to return a value, without any warning/error.

How to repeat:
DROP FUNCTION IF EXISTS `meow`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `meow`(seed  VARCHAR(100)) RETURNS int
BEGIN

DROP TEMPORARY TABLE IF EXISTS pfiltered;
CREATE TEMPORARY TABLE pfiltered(id INT AUTO_INCREMENT);

return 2;

END
$$

select meow("a");

Suggested fix:
Either return an error that  tables can't have auto_increment inside a stored function, or continue execution of the code normally
[26 Jan 2008 6:44] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior:

mysql> DELIMITER $$ mysql> CREATE FUNCTION `meow`(seed  VARCHAR(100)) RETURNS int
    -> BEGIN
    -> 
    -> DROP TEMPORARY TABLE IF EXISTS pfiltered;
    -> CREATE TEMPORARY TABLE pfiltered(id INT AUTO_INCREMENT primary key);
    -> 
    -> return 2;
    -> 
    -> END
    -> $$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> select meow('a')$$
+-----------+
| meow('a') |
+-----------+
|         2 | 
+-----------+
1 row in set, 1 warning (0.06 sec)

mysql> show warnings$$
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Note  | 1051 | Unknown table 'pfiltered' | 
+-------+------+---------------------------+
1 row in set (0.00 sec)

mysql>
[26 Jan 2008 20:45] sfa afddf
thanks.. i realized that when the primary key attribute is not set, the error is not displayed by the Mac OS X's Query Browser...