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.