Why is a temporary table allowed to be referenced only once?

Question

Joining multiple times to a temporary table. Getting this error:

Error Code: 1137. Can't reopen table: 'a'

I googled it and found that there is a restriction of some kind on referencing the same temporary table multiple times in a query. Can anyone explain why this restriction exists?

Here is an exmaple of simple query that will cause this error:

CREATE TEMPORARY TABLE foo
SELECT * FROM shopify_us limit 10;

SELECT *
FROM (
SELECT *
FROM shopify_us
LIMIT 10
) boo
LEFT JOIN foo a ON a.customer_id = boo.customer_id
LEFT JOIN foo b on b.customer_id = boo.customer_id

However, if I simply remove the 2nd join, I no longer encounter the error.

I am not familiar with such an error. Perhaps you should show he query. — Gordon Linoff, Apr 23 '21 at 22:13
Does this answer your question? [Getting around MySQL "Can't reopen table" error](https://stackoverflow.com/questions/343402/getting-around-mysql-cant-reopen-table-error) — devlin carnate, Apr 23 '21 at 22:28
@devlincarnate I read that thread and found a different work around, but my question still stands. I don't understand the restriction. — Joseph Erickson, Apr 23 '21 at 22:29

score 3 · Accepted Answer · answered Apr 23 '21 at 22:29

The restriction is that you can't reference a temporary table multiple times in the same query. For example, doing a self-join on a temporary table cannot be done. But you can use a temporary table by multiple subsequent queries.

Here's the comment in the MySQL Server code that explains the reason, right before it raises this error:

/*
  We're trying to use the same temporary table twice in a query.
  Right now we don't support this because a temporary table is always
  represented by only one TABLE object in THD, and it can not be
  cloned. Emit an error for an unsupported behaviour.
*/

https://github.com/mysql/mysql-server/blob/8.0/sql/sql_base.cc#L7284-L7289

THD refers to the data structure for session-scoped information. Temporary tables are scoped to the session in which they are created. They aren't references to tables that can be shared by multiple sessions.

In other words, it's just a limitation in the code with respect to the data structure that tracks temporary tables.

See also the history of this as a bug report dating back to 2005: https://bugs.mysql.com/bug.php?id=10327

There is a workaround in MySQL 8.0: you can us a common table expression instead, for some kinds of uses for which you would have used a temp table.

Why is a temporary table allowed to be referenced only once?

1 Answers1