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

Bug #10327	Can't reopen temporary table - should be allowed
Submitted:	3 May 2005 7:18	Modified:	11 May 2006 16:24
Reporter:	Jacek Becla
Status:	Verified
Category:	Server: DML	Severity:	S4 (Feature request)
Version:	5.0, 5.1	OS:	Linux (Redhat Enterprise Linux)
Assigned to:		Target Version:
Triage:	Triaged: D5 (Feature request)

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.