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
Triage: Triaged: D5 (Feature request)

[3 May 2005 5:18] Jacek Becla
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;
[3 May 2005 14:51] Miguel Solorzano
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;
[11 May 2006 14:24] Domas Mituzas
Reclassifying this as feature request - it is usually requested change.
[21 Jan 2008 17:22] Jason Clawson
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.
[22 Jan 2008 20:13] Ben Krug
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.
[22 Jan 2008 20:14] Ben Krug
Monty has comments on this issue in the following:

http://forge.mysql.com/wiki/Overview_of_query_execution_and_use_of_temp_tables
[22 Jan 2008 20:32] Jason Clawson
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.
[11 May 2008 0:54] Ondra Zizka
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 .
[4 Oct 2008 19:49] Konstantin Osipov
Bug#18696 was marked a duplicate of this bug.
[2 Dec 2008 14:00] Miljan Radovic
Is there anything new in MySQL 6.0 server version?
[3 Dec 2008 17:45] Matt Hamm
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.
[2 Nov 2009 3:03] Steve M
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.
[8 Dec 2009 16:17] Geoffrey Falk
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
[25 Jan 2010 0:48] Ben J
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!
[2 Dec 2010 18:45] Colin MacKenzie
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?
[4 Aug 2011 17:10] Douglas Coulson
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.
[24 Dec 2011 16:00] Scott Jilek
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.
[15 Jun 2012 2:25] Kevin Dyer
Me too I have a stored procedure creating four copies of a temporary table as a workaround. Any update?
[15 Jun 2012 12:59] Cyril SCETBON
I vote for it too !
[13 Jul 2012 21:59] Marcin Szalowicz
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 ;/
[5 Apr 2013 19:16] Justinas Rumbinas
It is impossible to execute UNION queries on temporary tables too.
[8 Apr 2014 21:32] Nick Rawlins
Yes please fix this urgently! I cannot find a way around this.
[8 Apr 2014 22:09] Kevin Dyer
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...
[8 Apr 2014 22:12] Nick Rawlins
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...
[8 Apr 2014 22:14] Nick Rawlins
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
[8 Apr 2014 22:17] Kevin Dyer
Pretty quick... until you want to do it five or so times then you need use real tables, maybe in a new "temp" database
[1 Jan 2015 22:13] Alan Givati
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!
[10 Mar 2015 11:34] wilson lundgren
Subscribed. Also affected.
[18 Jun 2015 19:30] Wilson Lau
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.
[23 May 2016 20:45] Alan Smith
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.
[23 May 2016 20:51] Cyril SCETBON
10 years for such an issue, seriously ???
[22 Jul 2016 19:57] John German
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.
[29 Jul 2016 5:27] tai man chann
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?
[12 Apr 2017 17:07] Paul Dubois
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
[14 Sep 21:39] Laloo Yadav
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.