MySQL Bugs: #26979: locking of memory tables prevents 2nd tablename reference in the same query

Bug #26979	locking of memory tables prevents 2nd tablename reference in the same query
Submitted:	9 Mar 2007 0:40	Modified:	9 Mar 2007 1:47
Reporter:	Dewey Gaedcke	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	can't reopen table, memory table, self join, UNION

Description:
"memory tables" (I've tested with temporary only) become locked at the first table name reference in a query.  As such, you cannot mention the same table later in the SAME query.  This applies to self-joins, unions or

"insert into T1(c1) select c1 from T1;" 

Seems to occur with any query that references the same table twice or more.

I consider this a bug as standard queries are not supported.
Please advise!!

How to repeat:
create temp table in a stored proc, and then attempt to select from and insert into that table from the same query---you should see the "can't reopen table" error

Thank you for the bug report. This is a documented behavior of temporary
table: http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

"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'"

Just because the manual documents the problem does not mean it should not be addressed.  Without proper array support, temp tables are all that we have, and it's poor form to not support standard SQL on all supported storage engines.

Is there any plan to solve this in a future version??