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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.22
• OS: FreeBSD (FreeBSD 6.1)
• 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] MySQL Verification Team

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