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

[24 Nov 2005 15:03] Nizamettin OZPOLAT
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
[24 Nov 2005 15:14] Jorge del Conde
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'"
[24 Nov 2005 15:22] Nizamettin OZPOLAT
It is also said with the version 4.0.* and 4.1.* but it used to work. What has changed?
[25 Nov 2005 9:08] Sergei Golubchik
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.