Bug #35985 can't use a temporary table twice in a union

• Submitted: 11 Apr 2008 8:02
• Modified: 11 Apr 2008 10:42
• Reporter: Alexander Kjäll
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.38
• OS: Any
• CPU Architecture: Any
• Tags: SELECT, temporary table, UNION

[11 Apr 2008 8:02] Alexander Kjäll

Description:
If you try to use a temporary table more than once in a union select a error message appears.

How to repeat:
mysql> create temporary table tmp1 as select 1 as id;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select id from tmp1 union select id from tmp1;
ERROR 1137 (HY000): Can't reopen table: 'tmp1'

[11 Apr 2008 10:42] MySQL Verification Team

Thank you for the bug report. This a documented limitation, please read:

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