Bug #6465 Query fails when using multiple unions on same temporary table

• Submitted: 5 Nov 2004 15:20
• Modified: 6 Nov 2004 15:55
• Reporter: Bjarte Andre Eide
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.7
• OS: Linux (Linux (Fedora Core2))
• CPU Architecture: Any

[5 Nov 2004 15:20] Bjarte Andre Eide

Description:
When unioning a temporary table multiple time n the same statement, MySQL fails with the following Error:
ERROR 1137 (HY000): Can't reopen table: 'tbl_tmp2'

Test statement:
insert into tbl_tmp1
select * from tbl_tmp where tmp_id = 1
union
select * from tbl_tmp where tmp_id = 2;

Expected result: 
MySQL inserts the result of the union into tbl_tmp1.

Actual result:
ERROR 1137 (HY000): Can't reopen table: 'tbl_tmp2'

I would expect MySQL to support this, since when using normal (not temporary) tables, no error occurs. Also this works fine on MySQL 4.0.21 on identical environment.

How to repeat:
drop table if exists tbl_tmp1;
drop table if exists tbl_tmp2;

create table tbl_tmp1 (tmp1_id int(11));
create temporary table tbl_tmp2 (tmp2_id int(11));

# This one fails
insert into tbl_tmp1
select * from tbl_tmp2 where tmp2_id = 1
union
select * from tbl_tmp2 where tmp2_id = 2;

[6 Nov 2004 15:55] Alexander Keremidarski

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is documented behaviour of TEMPORARY tables. 
A temporary table cannot be refered more than once in a single query