Bug #1023 self join of temporary table

• Submitted: 10 Aug 2003 4:27
• Modified: 13 Aug 2003 10:12
• Reporter: Sergei Golubchik
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[10 Aug 2003 4:27] Sergei Golubchik

Description:
mysql> create temporary table t1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1, t1 as t2;
ERROR 1137: Can't reopen table: 't1'
mysql> 

How to repeat:

[13 Aug 2003 10:12] Oleksandr Byelkin

It is expected behaviour. 
Following is citation from our manual: 
 
You can't use temporary tables more than once in the same query. 
For example, the following doesn't work: 
 
mysql> SELECT * FROM temporary_table, temporary_table AS t2;

[1 Feb 2016 8:22] Guilhem Bichot

Three other examples, involving reads and writes:

mysql> create temporary table t(a int);
Query OK, 0 rows affected (0,00 sec)
mysql> insert into t select * from t;
ERROR 1137 (HY000): Can't reopen table: 't'
mysql> update t as t1, t as t2 set t1.a=t2.a;
ERROR 1137 (HY000): Can't reopen table: 't1'
mysql> delete t1.* from t as t1, t as t2;
ERROR 1137 (HY000): Can't reopen table: 't1'