Bug #7742 You can't reinvoke the same Temporary Table more than once.
Submitted: 8 Jan 2005 11:06 Modified: 8 Jan 2014 14:52
Reporter: Carlos Uldérico Cirello Filho Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:4.1 + OS:Linux (Linux Red Hat 9)
Assigned to: CPU Architecture:Any

[8 Jan 2005 11:06] Carlos Uldérico Cirello Filho
You cannot invoke a temp table more than once in the same query.

How to repeat:
CREATE TEMPORARY TABLE t_table LIKE another_table;

SELECT * FROM t_table WHERE kindof = '1'
SELECT * FROM t_table WHERE kindof = '2'

Error #1137: Can't reopen 't_table'

Suggested fix:
The work arround is to create several temp tables so as to allow to enable the query like this:

CREATE TEMPORARY TABLE t_table_a LIKE another_table;
CREATE TEMPORARY TABLE t_table_b LIKE t_table_b; -- as to keep the same DB snapshot.

SELECT * FROM t_table_a WHERE kindof = '1'
SELECT * FROM t_table_b WHERE kindof = '2'


First, in MySQL 4.0 it works just fine. So why has not this change widely SPREAD when 4.1 was turned into GA?

Second, what is it the point of having a temp table if you can't work with it? Temporary tables are not just for displaying (like "SELECT * FROM t_table"). Even so, someone might fall in a situation that the same table is INNER JOIN more than once (namely data mining).

Temp tables are very useful when you want to analyze a great bunch of data but without falling in its changes while doing the analysis, thus Temp Tables works as tables snapshots which enables safe data mining.

MySQL limped on that one... Sorry :(
[10 Jan 2005 13:55] MySQL Verification Team

Thank you for the report, but this is not a bug.
You can't use temporary table more than once in the same query:

UNION implementation is a bit different in 4.0, that is why you don't receive any error using version 4.0.
Since 4.1 UNION with temporary tables works as described in the MySQL manual.
[10 Jan 2005 13:58] Carlos Uldérico Cirello Filho
So, what is it the argument for such a change???

It makes no sense this modification... It'll lead to a lot of application backward incompatibilities.
[10 Jan 2005 19:53] MySQL Verification Team
This change was forced due to internal changes in 4.1, which resulted in a constraint that temp. table can't be opened more then once.

This is a bug, but it is scheduled to be fixed in 5.1, not before.
[11 Jan 2005 18:53] Sergei Golubchik
Just to explain why it was changed.

It was a side-effect of fixing a deadlock bug in UNION. Earlier one was able to deadlock two unions SELECT * FROM t1 UNION SELECT * FROM t2; and

MySQL is deadlock free because it opens all tables at once, in the beginning of query (it was a bug that union did not do it). And it cannot open temporary table twice - e.g. you cannot join temp table to itself.
[11 Jan 2005 19:23] Carlos Uldérico Cirello Filho
Well, I am not aware of the deep magic beneath Temp Tables and UNION logic.

But, could the developers release a parameter ou an environment variable to allow this feature to work? Of course, offering the messages and warning of all the evils that it would take? It'd be disabled by default.

On a second though, I wonder whether the FROM clausule should, of course, tell to the daemon what to open BUT it should also be smart enough to understand what's invoked more than once and properly deal with UNION and recursive INNER JOINs of a same table, opening ONCE using (copying) MANY TIMES.

And this DEAD LOCK prevention leads automatically to an enlargenment of the required TEMP space on disks, as the user doubles BY HAND what MySQL could have doubled for him. Thus, the DEAD LOCKS would not exist because instead of reopenings the daemon would offer optimized snapshots of a initial temporary table, like this:

* -- <-- B&R
t_table t1 INNER JOIN t_table t2 (t1.id = t2.id and t2.some_field = value)

The MySQL could automagically make the 
t1 --> the whole copy of t_table or just reference to it.
t2 --> the partial copy "WHERE t2.some_field = value" of t_table

Turning the query, indeed, into this:

CREATE TEMPORARY TABLE t2 SELECT * FROM t1 WHERE t2.some_field = value
* -- <-- B&R
t1 INNER JOIN t2 (t1.id = t2.id and t2.some_field = value) -- the final part could be diked?

This way, MySQL would create temp. tables, open them, rewrite and execute the query properly.

Of course, this is mho...
[11 Jan 2005 20:07] Sergei Golubchik
Of course, we will fix it. But big changes like those you propose cannot be done in the stable branch, unfortunately. That's why we'll fix it most probably in 5.1.

And thanks for your ideas!
[8 Jan 2014 14:52] Erlend Dahl
This problem is still present on mysql-trunk.

Moving to 'verified' as we are discontinuing the use of 'to be fixed later'.
[11 Nov 2017 20:47] Federico Razzoli
12 years passed. If this is still considered a bug, can you please let us know if there is any plan to fix it?