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
• Status: Can't repeat
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.45
• OS: MacOS
• 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...