MySQL Bugs: #36657: Can't reopen TEMPORARY table in 2 independent SELECTs in a FUNCTION

Bug #36657	Can't reopen TEMPORARY table in 2 independent SELECTs in a FUNCTION
Submitted:	12 May 2008 1:53	Modified:	12 May 2008 7:58
Reporter:	Ondra Zizka	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.51a, 5.0.60	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	alias, reopen, temporary table

Description:
I've found a problem with TEMPORARY table that is not documented at http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html .

Two independent selects on a table must have the same alias for it, otherwise the error "Can't reopen table" is raised.

Thanks to Peter Brawley who created a test case.

How to repeat:
drop table if exists a;
create table a(i int);
insert into a values(1),(2);
create temporary table atemp select * from a;

drop function if exists fa;
delimiter |
create function fa() returns int begin
  declare x, y int default 0;
  select i into x from atemp AS aliasA where i=1;
  select i into y from atemp AS aliasB where i=1;
  return x;
end; |

If you comment out both aliases, or if you use the same or no alias in each SELECT, no runtime error occurs. If you use different aliases, you get "Can't open temp table twice".

Suggested fix:
Either document or fix. Thanks.

Thank you for a problem report. Verified just as described with 5.0.60:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists a;
Query OK, 0 rows affected (0.13 sec)

mysql> create table a(i int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into a values(1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create temporary table atemp select * from a;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> drop function if exists fa;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //
mysql> create function fa() returns int begin
    ->   declare x, y int default 0;
    ->   select i into x from atemp AS aliasA where i=1;
    ->   select i into y from atemp AS aliasB where i=1;
    ->   return x;
    -> end;
    -> //
Query OK, 0 rows affected (0.05 sec)

mysql> select fa()//
ERROR 1137 (HY000): Can't reopen table: 'aliasB'
mysql> drop function if exists fa//
Query OK, 0 rows affected (0.02 sec)

mysql> create function fa() returns int begin
    ->   declare x, y int default 0;
    ->   select i into x from atemp AS aliasA where i=1;
    ->   select i into y from atemp where i=1;
    ->   return x;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select fa()//
ERROR 1137 (HY000): Can't reopen table: 'atemp'
mysql> drop function if exists fa//
Query OK, 0 rows affected (0.00 sec)

mysql> create function fa() returns int begin
    ->   declare x, y int default 0;
    ->   select i into x from atemp AS aliasA where i=1;
    ->   select i into y from atemp AS aliasA where i=1;
    ->   return x;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select fa()//
+------+
| fa() |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

Manual (http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html) clearly says:

"You cannot refer to a TEMPORARY table more than once in the same query."

But it is not clear if entire stored function/procedure should be treated as "same query". If current behaviour is intended (I think so) then manual page quoted above should be clarified, with additional example like this.

This is a duplicate of Bug #36653 actually.

Ok. Sorry for the duplicate, but I wasn't able to find that - it does not have appropriate keywords in the title.

Ah, I see. We did it almost at the same time, therefore the duplication.

Note that it works well for a PROCEDURE. Procedure call is not treated as "same query"?

drop table if exists a;
create table a(i int);
insert into a values(1),(2);
create temporary table atemp select * from a;

drop function if exists fa;
delimiter |
create procedure fa() begin
  declare x, y int default 0;
  select i into x from atemp AS aliasA where i=1;
  select i into y from atemp AS aliasB where i=1;
end; |
delimiter ;

CALL fa();