Bug #6947 UNION query works on 4.0 fails on 4.1
Submitted: 2 Dec 2004 14:32 Modified: 2 Dec 2004 17:36
Reporter: Jorge del Conde Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:Any (All)
Assigned to: Sergey Petrunya CPU Architecture:Any

[2 Dec 2004 14:32] Jorge del Conde 
Description:
A UNION that functions fine on 4.0.20 needed split to two queries on 4.1, The query works fine with a real table, but if we use a temporay table it fails with the following error message:

ERROR 1137 (HY000): Can't reopen table: 'my_temp'

How to repeat:
drop temporary table if exists my_temp;
create temporary table my_temp (i int);
insert into my_temp values(1), (2), (3);
select * from my_temp where i = 1
union 
select * from my_temp where i > 1;
[2 Dec 2004 17:36] Sergei Golubchik 
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:

http://dev.mysql.com/doc/mysql/en/Temporary_table_problems.html