MySQL Bugs: #7742: You can't reinvoke the same Temporary Table more than once.

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	4.1 +	OS:	Linux (Linux Red Hat 9)
Assigned to:		CPU Architecture:	Any

Description:
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'
)UNION(
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'
)UNION(
SELECT * FROM t_table_b WHERE kindof = '2'
)

THIS BUG IS SEVERE, BAD AND WRONG.

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 :(

Hello,

Thank you for the report, but this is not a bug.
You can't use temporary table more than once in the same query:
http://dev.mysql.com/doc/mysql/en/Temporary_table_problems.html

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.

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.

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.

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
SELECT * FROM t2 UNION SELECT *t1;

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.

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:

CREATE TEMPORARY TABLE t_table LIKE some_table
SELECT
* -- <-- B&R
FROM
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 t1 LIKE some_table
CREATE TEMPORARY TABLE t2 SELECT * FROM t1 WHERE t2.some_field = value
SELECT
* -- <-- B&R
FROM
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...

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!

This problem is still present on mysql-trunk.

Moving to 'verified' as we are discontinuing the use of 'to be fixed later'.

12 years passed. If this is still considered a bug, can you please let us know if there is any plan to fix it?