MySQL Bugs: #15230: can't use temporary table with union more then once

Bug #15230	can't use temporary table with union more then once
Submitted:	24 Nov 2005 15:03	Modified:	24 Nov 2005 15:22
Reporter:	Nizamettin OZPOLAT	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Linux (SuSE Linux 9.0 (i586) - Kernel 2)
Assigned to:	Jorge del Conde	CPU Architecture:	Any

Description:
with mysql 4.1.* it was possible to use temporary table with union more then once but with this version it gives error message.

How to repeat:
CREATE TABLE table1 (
  `id` smallint(6) NOT NULL default '0',
  `name` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM
;

insert into table1 values (1,'dfd'),(2,'weqwe'),(3,'asda'),(4,'qweqw'),(5,'f43r4r');

drop table if exists TMP;
create temporary table TMP
select * from table1
;

select *
from TMP limit 0,2

union

select *
from TMP limit 2,5

Hi!

This is a documented fact:
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'"

It is also said with the version 4.0.* and 4.1.* but it used to work. What has changed?

SELECT as in the Jorge's example never worked. UNION as in yours did work, because of the bug in the UNION - it did not lock tables correctly, making deadlocks possible. That bug was fixed since then.