Bug #23014 Reusing of a temporary table in the same query call an error.
Submitted: 5 Oct 2006 14:54 Modified: 5 Oct 2006 15:10
Reporter: Dmitriy Buldakov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.22 OS:FreeBSD (FreeBSD 6.1)
Assigned to: CPU Architecture:Any
Tags: UNION

[5 Oct 2006 14:54] Dmitriy Buldakov
Description:
Double using of the same temporary table in a quary is unable. 
It call error message.

mysql> CREATE TEMPORARY TABLE `temporary_table_test` (
    ->   `i` INT);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `table_test` (
    ->   `i` INT);
ERROR 1050 (42S01): Table 'table_test' already exists
mysql>
mysql>
mysql> SELECT * FROM `table_test`
    -> UNION
    -> SELECT * FROM `table_test`;
Empty set (0.01 sec)

mysql>
mysql> SELECT * FROM `temporary_table_test`
    -> UNION
    -> SELECT * FROM `temporary_table_test`;
ERROR 1137 (HY000): Can't reopen table: 'temporary_table_test'
mysql>
mysql> SELECT * FROM `temporary_table_test` as `t1` CROSS JOIN `temporary_table_test` as `t2`;
ERROR 1137 (HY000): Can't reopen table: 't1'
mysql> Bye

How to repeat:
CREATE TEMPORARY TABLE `temporary_table_test` (
  `i` INT);

CREATE TABLE `table_test` (
  `i` INT);

SELECT * FROM `table_test`
UNION
SELECT * FROM `table_test`;

SELECT * FROM `temporary_table_test`
UNION
SELECT * FROM `temporary_table_test`;

SELECT * FROM `temporary_table_test` as `t1` CROSS JOIN `temporary_table_test` as `t2`;
[5 Oct 2006 15:10] Miguel Solorzano
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html