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?

possible duplicate of [Getting around MySQL "Can't reopen table" error](http://stackoverflow.com/questions/343402/getting-around-mysql-cant-reopen-table-error) — LittleBobbyTables - Au Revoir, Aug 25 '12 at 12:37
@LittleBobby- Don't see any applicable solutions in that one — Yarin, Aug 25 '12 at 12:52

score 18 · Accepted Answer · edited Jun 10 '21 at 00:01

18

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

edited Jun 10 '21 at 00:01

informatik01

16,038
10
74
104

answered Aug 25 '12 at 12:40

heretolearn

6,387
4
30
53

1

This works, but replicating temp tables seems no better than creating 2 from the source? – Yarin Aug 25 '12 at 12:59
The temporary table gets deleted when the current client session terminates.See http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm – heretolearn Aug 25 '12 at 13:04
I've come up with a good [answer](http://stackoverflow.com/a/12122220/165673) based on this- thanks for steering me in the right direction – Yarin Aug 25 '12 at 13:11

score 10 · Answer 2 · edited May 23 '17 at 11:47

10

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

Create an empty temp table
Insert the results we want to UNION into the table separately
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)

edited May 23 '17 at 11:47

Community

1
1

answered Aug 25 '12 at 13:04

Yarin

173,523
149
402
512

4

Or just `CREATE TEMPORARY TABLE tmp SELECT * FROM people UNION ALL SELECT * FROM people` ? – eggyal Jan 21 '13 at 13:04

score 6 · Answer 3 · answered Aug 25 '12 at 12:39

6

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.

answered Aug 25 '12 at 12:39

eggyal

122,705
18
212
237

9

REALLY? That just seems to blow away 90% of their usefulness. Am I missing an obvious workaround? – Yarin Aug 25 '12 at 12:41
Sam- how would you do that in this case? – Yarin Aug 25 '12 at 12:44

score 0 · Answer 4 · answered Feb 11 '20 at 14:07

As others may wander past this question/solution thread... if they have an older Ubuntu 16.04LTS machine or similar.

The limitation exists in Ubuntu 16.04, mysql 5.7, as documented here, like eggyal reported above. The bug/feature was logged here, and ignored for more than a decade. Similarly, it was also logged against mariadb, and was resolved in version 10.2.1. Since Ubuntu 16.04LTS uses mariadb 10.0, the feature is out of easy reach without upgrading to 18.04 etc. You have to download from external repo and install directly.

Can't UNION ALL on a temporary table?

4 Answers4

Useful reading