MySQL Bugs: #10327: Can't reopen temporary table

Bug #10327	Can't reopen temporary table - should be allowed
Submitted:	3 May 2005 5:18	Modified:	11 May 2006 14:24
Reporter:	Jacek Becla	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0, 5.1	OS:	Linux (Redhat Enterprise Linux)
Assigned to:		CPU Architecture:	Any

Description:
Fails when I try to use the same temporary table more than once in WHERE.

How to repeat:
create temporary table tt (x int);
select * from tt as t1, tt as t2;

According the Manual is a documented issue:

A.7.3. TEMPORARY TABLE Problems

The following list indicates limitations on the use of TEMPORARY tables:

    *

      A TEMPORARY table can only be of type HEAP, ISAM, MyISAM, MERGE, or InnoDB.
    *

      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'

    *

      The SHOW TABLES statement does not list TEMPORARY tables.
    *

      You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead:

mysql> ALTER TABLE orig_name RENAME new_name;

Reclassifying this as feature request - it is usually requested change.

I just want to bump this issue.  It has been nearly 3 year since this was reported.  This limitation is causing performance roadblocks in our application.

I have a very complex recursive stored procedure.  It recurses over a directed cyclical graph where some properties from a parent node can inherit to the child node depending on their relationship.  To do this very quickly it requires the use of a table to store the parent node's properties.  When I navigate to the child node I have to insert into the table that stores the node's properties while selecting from it to get which properties inherited to it.  This is where the can't reopen temp table issue hits.  It forces me to use a non-temp table.  This means I cannot run the stored procedure at the same time.

I have to run it every time a node in the graph is moved... which can be done by many users at the same time.  I have to queue up executions of this stored procedure.  This will become a performance roadblock in the near future.  

I don't think this should be considered just a feature request.  This issue, which has been an issue for 3 years, is now causing performance issues without a suitable long-term workaround.

Customer is having performance issues that will affect scalability because of this.  They have a stored procedure, need to join a temp table to itself.  Tried copying the table to use 2 copies, performance was bad.  Now using regular tables, but has to serialize operation of stored procedure so that more than one thread doesn't try to use it at the same time.  Says will be very bad for scaling.

Monty has comments on this issue in the following:

http://forge.mysql.com/wiki/Overview_of_query_execution_and_use_of_temp_tables

For quick reference Monty recommends adding another clone method:

 [8:05:33 AM:] monty_: - Add a new option to 'clone()' to inform the handler how the table should
 [8:05:36 AM:] monty_:   be opened.
 [8:05:37 AM:] monty_: - Open a new table instance with 'clone(... keep-locks)'
 [8:05:41 AM:] monty_: - Use the table for the duration of the statement
 [8:05:44 AM:] monty_: - close the extra instance (instead of putting it back in
 [8:05:46 AM:] monty_:   the temporary table list) at end of statement

It is suggested that this feature may be in 5.2 and then back ported to 5.1 community branch later.

I have a problem reopening TEMP table in two successive SELECTs in a FUNCTION. Does it have something in common with this? Is it a bug?
See http://forums.mysql.com/read.php?98,209153,209153#msg-209153 .

Bug#18696 was marked a duplicate of this bug.

Is there anything new in MySQL 6.0 server version?

This issue is a problem when running MicroStrategy against a MySQL database because we have no control over the code it generates.  MicroStrategy is supposedly certified on the MySQL 5.0 platform, but I do not know how to work around this.  Does anyone with MicroStrategy experience have any suggestions?  Our MicroStrategy project works fine on all other database platforms, including SQL Server, Oracle, Netezza, Teradata, etc. but this could be a show-stopper for the MySQL platform.

Maybe I'm just confused, but what is the purpose of a TEMPORARY TABLE if you don't use it more than once? I feel like this bug makes the feature entirely useless.

If using MicroStrategy, the workaround is to configure it to use permanent tables instead of temporary tables. It is one of the MicroStrategy VLDB settings.

Geoffrey

I am having the same problem.

I understand that this is a "problem with TEMPORARY tables", as described here:

http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

@ [2 Nov 2009 4:03] Steve M:

You are indeed slightly confused. The problem is not using the temporary table more than once; the problem is REFERENCING the temporary table more than once IN THE SAME QUERY. See the above URL for more information.

It would be really nice is this actually worked as one would expect. Thanks!

This bug has been a thorn in my side for some time. In many many cases I've had to duplicate tables. Any update on getting this in a new version of mysql?

What's up mysql?  it's been 6 years and this still hasn't been fixed.  Temporary tables are pretty limited if you can't refer to them more than once in a query.

You've got to be kidding!  I've been a fairly vocal supporter of mySQL, but I just ran into this limitation, and it's the worst I've found so far.  

What's the deal?  Multiple uses of the same table in a query is a basic tenet of queries & set manipulation.  

Please fix this limitation on temp tables. I would really appreciate it.

Me too I have a stored procedure creating four copies of a temporary table as a workaround. Any update?

I vote for it too !

i'm also voting for it, 
for now i've got a procedure which creates 3 temporary tables for 4 times with several thousands of rows and it's a big memory hog ;/

It is impossible to execute UNION queries on temporary tables too.

Yes please fix this urgently! I cannot find a way around this.

Welcome to mysql, this will never be "fixed" but it can be worked around.

CREATE TEMPORARY TABLE temp_table_copy LIKE temp_table;
INSERT INTO temp_table_copy SELECT * FROM temp_table;

Query away...

Hi Kevin,

How do you suggest you add results from multiple queries into the temp table?

insert into DBA_TREE (NAME,VALUE,PARENT,TYPE )
select query goes here...

insert into DBA_TREE (NAME,VALUE,PARENT, TYPE)
select query goes here...

insert into DBA_TREE (NAME,VALUE,PARENT, TYPE)
select query goes here...

oh I understand, sorry. I would basically create 2 copies of my first temp table.. hmm

Hopefully it won't have a performance hit.

Thanks

Pretty quick... until you want to do it five or so times then you need use real tables, maybe in a new "temp" database

Going on 10 years, this bug should have been squashed a long time ago.  In fact the whole temporary table system needs a lot of attention.  I've had no choice but to use a system of stored procedures for "permanent" temporary tables  because there are just too many limitations with the current system.

Happy New Year otherwise - here's to less old bugs in 2015!

Subscribed. Also affected.

Looking at the Severity of this bug - S4 (Feature request),  how come this is a feature request?  Should pump up the severity to S999.   

For my query, I had to create 5 temp tables to achieve something that can be easily done otherwise using CTE or Table variable within one single query.  Now I have break it up in chunks and it is hard to mainain.

Terrible BUG
I have a Stored Procedure with 2 Temp Tables.
I only get this for ONE of them !?!
(The one that I initially forgot to preceed with a 
DROP TEMPORARY TABLE IF EXISTS semrel_temp;
)
But even with he DROP I continually get this error.

Bug needs to be PRIORITY.

10 years for such an issue, seriously ???

This bug, for me, happens whenever I try to select on a temporary table multiple times.  So if I have a subquery like below:

SELECT column1, column2
FROM table1
WHERE column1 not in (select column1 from TempTable)
AND column2 not in (select column2 from TempTable)

This is when it bombs when trying to open the table multiple times.  

I agree that after 10 years, this issue should have been fixed.  I can do the same thing in SQL Server without any issues.  Don't make people switch to SQL Server just due to letting a known issue go on forever.

I cannot imagine why such a simple thing takes more than 10 years to solve (or to not solve). Or do the devs just dont care about this? Maybe it is time to stop using mysql?

As of MySQL 8.0.1, you can work around this issue if your query
permits use of a common table expression (CTE) rather than a
TEMPORARY table. For example, this fails with the "Can't reopen
table" error:

CREATE TEMPORARY TABLE t SELECT 1 AS col_a, 2 AS col_b;
SELECT * FROM t AS t1 JOIN t AS t2;

A workaround is to use a WITH clause to define a CTE that replaces
the TEMPORARY table:

WITH cte AS (SELECT 1 AS col_a, 2 AS col_b)
SELECT * FROM cte AS t1 JOIN cte AS t2;

More information about common table expresions:

https://dev.mysql.com/doc/refman/8.0/en/with.html

In our situation, the reason we use temporary table to store the piece of information which is expensive to compute and use that information multiple times later in the query. 

If we try to do that using Common table expression, we will be doing the expensive computation multiple times, which does not make sense.

I switched over from Microsoft SQL Server. The feature has been available with MS-SQL since ages.  I am not sure why MySQL team can't fix it.

Need to delete duplicates in an unindexed table with over a million records

Run into the same issue. It's weird that in nearly 15 years no-one though this is worth completing.  It would be.  Sadly, by the looks of it that will never happen. :(

There's an explanation (some details) of some use cases here: <https://bugs.mysql.com/bug.php?id=98458>

Also note that I was referring to version 8 and any OS and any architecture... I have given my explanation of why I believe this feature is required...

I am seeing some wired behaviour with temporary tables in different version. If someone can help me understand would really appreciate

Behaviour 1 (Version: 8.0.21)
*****************************
drop temporary table if exists t1;
create temporary table t1(a int, b int);
drop procedure p1;

delimiter $$
create procedure p1()
begin
    insert into t1  values(1,1);
    update t1 as t0  set a = 2;
    select * from t1 as t3;
    select * from t1 as t4;
end$$
delimiter ;

call p1();

The above call to the store procedure succeeds even though I have used different alias for the temp table. So it contradicts with the documentation
"The Can't reopen table 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. It may occur for temporary tables created outside stored functions and referred to across multiple calling and callee functions."
https://dev.mysql.com/doc/refman/8.0/en/temporary-table-problems.html

I believe this limitation applied to store procedure as well.

Calling the same store procedure with an binary input parameter where a user defined is used to convert a guid to binary fails with error
"Can't reopen table: 't0'"

drop temporary table if exists t1;
create temporary table t1(a int, b int);
drop procedure p2;

delimiter $$
create procedure p2(i1 binary(16))
begin
    insert into t1  values(1,1);
    update t1 as t0  set a = 2;
    select * from t1 as t3;
    select * from t1 as t4;
end$$
delimiter ;

call p2(GuidToBinary('a077fa39-f54c-41b3-bcbe-4350ba390977'));

delimiter $$
create function `guidtobinary`(guid varchar(36))
returns binary(16)
deterministic
no sql
begin
	declare _guid varchar(32);
	set _guid = replace(guid, '-', '');
	return unhex(concat(
		substring(_guid, 7, 2),
		substring(_guid, 5, 2),
		substring(_guid, 3, 2),
		substring(_guid, 1, 2), 
		substring(_guid, 11, 2),
		substring(_guid, 9, 2),
		substring(_guid, 15, 2),
		substring(_guid, 13, 2), 
		substring(_guid, 17, 16)
		));
end $$
delimiter ;

Behaviour 2 (Version: 5.7.31-log)
********************************
The same code above both version ( with and with input parameter) works fine in version Version: 5.7.31-log