MySQL Bugs: #60718: Stored Function: INSERT INTO TEMPORARY TABLE; Can't reopen table

Bug #60718	Stored Function: INSERT INTO TEMPORARY TABLE; Can't reopen table
Submitted:	1 Apr 2011 7:48	Modified:	1 Apr 2011 9:08
Reporter:	Richard Teubel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.5.10, 5.0.89	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, insert, reopen table, temporary table, temptable

Description:
Hello,

I should like to INSERT INTO a TEMPORARY TABLE in my Stored Function. It works fine. But if I INSERT in MainFunction and SubFunction into the same TEMPORARY TABLE, I get error: Can't reopen table

With regular tables it works without this error message.

How to repeat:
=> look SP_insert_temptable.sql

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS `myTempTable` (
    ->   `myValue` varchar(30) collate latin1_german2_ci NOT NULL
    -> )  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;

DELIMITER //;

CREATE FUNCTION `myInsert`()
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER //;
mysql>
mysql> CREATE FUNCTION `myInsert`()
    ->     RETURNS tinyint(4)
    ->     DETERMINISTIC
    ->     MODIFIES SQL DATA
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   INSERT INTO myTempTable
    ->   SET myValue = 'Insert from myInsert';
    ->
    ->   SELECT mySubInsert()
    ->   INTO @TEMPXXX;
    ->
    ->   RETURN 1;
    -> END; //;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE FUNCTION `mySubInsert`()
    ->     RETURNS tinyint(4)
    ->     DETERMINISTIC
    ->     MODIFIES SQL DATA
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   INSERT INTO myTempTable
    ->   SET myValue = 'Insert from mySubInsert';
    ->
    ->   RETURN 1;
    -> END; //;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

mysql> SELECT mySubInsert();
+---------------+
| mySubInsert() |
+---------------+
|             1 |
+---------------+
1 row in set (0.03 sec)

mysql>
mysql> SELECT * FROM myTempTable;
+-------------------------+
| myValue                 |
+-------------------------+
| Insert from mySubInsert |
+-------------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM myTempTable;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT myInsert();
ERROR 1137 (HY000): Can't reopen table: 'myTempTable'     <<<< !!!

mysql> SELECT * FROM myTempTable;
Empty set (0.00 sec)

mysql> DROP TABLE myTempTable;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE IF NOT EXISTS `myTempTable` (
    ->   `myValue` varchar(30) collate latin1_german2_ci NOT NULL
    -> )  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT myInsert();
+------------+
| myInsert() |
+------------+
|          1 |
+------------+
1 row in set (0.05 sec)

mysql> SELECT * FROM myTempTable;
+-------------------------+
| myValue                 |
+-------------------------+
| Insert from myInsert    |
| Insert from mySubInsert |
+-------------------------+
2 rows in set (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.5                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.10-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux2.6                     |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

copy and paste file

Attachment: SP_insert_temptable.sql (application/octet-stream, text), 1.15 KiB.

This is a documented limitation. Read http://dev.mysql.com/doc/refman/5.5/en/temporary-table-problems.html:

"- You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function."

See bug #10327 (request to remove this limitation).

Not sure why the OP accepted this solution.  Bug #10327 is unrelated to this (its regarding opening the same TEMP table more than once in a single query).  Also, you referenced the manual with the following passage:

"This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function."

However, this passage only says that you cannot access a TEMP table more than once under different aliases.  In the above post, no aliases are used.  In my mind this is a bug.  The reproduction steps of selecting from a TEMP table twice with different aliases in a stored procedure are different than the above.

I know this is an old case, but I actually receive this error in much stranger set of circumstances, which is what leads me to believe it is a bug.  I'm going to post that in another bug because my steps are significantly different, but I'll reference this bug.