Can't UNION ALL on a temporary table?

Question

I'm trying to run the following simple test- creating a temp table, and then UNIONing two different selections:

CREATE TEMPORARY TABLE tmp 
SELECT * FROM people;

SELECT * FROM tmp
UNION ALL
SELECT * FROM tmp;

But get a #1137 - Can't reopen table: 'tmp'

I thought temp tables were supposed to last the session. What's the problem here?

Answer 1

This error indicates that the way in which MySQL tables manages the temporary tables has been changed which in turn affects the joins, unions as well as subqueries. To fix MySQL error "can’t reopen table", try out the following solution:

mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals;

After this you can perform the union operation.

---

Useful reading

• http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html
• http://www.mysqlrepair.org/mysqlrepair/cant-reopen-table.php

Answer 2

Figured it out thanks to sshekar's answer- the solution in this case would be

1. Create an empty temp table
2. Insert the results we want to UNION into the table separately
3. Query the temp table

SQL:

CREATE TEMPORARY TABLE tmp LIKE people;

INSERT INTO tmp SELECT * FROM people; /* First half of UNION */
INSERT INTO tmp SELECT * FROM people; /* Second half of UNION */
SELECT * FROM tmp; 

(See Using MySQL Temporary Tables to save your brain)

Answer 3

As documented under TEMPORARY Table Problems:

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.