MySQL Bugs: #21431: Incomplete support of temporary tables

Bug #21431	Incomplete support of temporary tables
Submitted:	3 Aug 2006 16:17	Modified:	8 Sep 2006 5:23
Reporter:	Tomash Brechko	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	4.1.22-BK, 5.0-BK	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL Reference Manual says that "The existing table [with the same name] is hidden until the temporary table is dropped".  However not all statements obey that, some try to perform operation on the ordinary (now hidden) table with the same name.

How to repeat:
CREATE TABLE t1 (ordinary int);
CREATE TEMPORARY TABLE t1 (temporary int);

-- Temporary table hides ordinary table:
DESC t1;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| temporary | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

INSERT INTO t1 VALUES (1);

-- Note column name:
SELECT * FROM t1;
+-----------+
| temporary |
+-----------+
|         1 |
+-----------+

But then:

INSERT DELAYED INTO t1 VALUES (2);
Query OK, 1 row affected (0.00 sec)

-- Ooops, nothing was inserted into temporary table:
SELECT * FROM t1;
+-----------+
| temporary |
+-----------+
|         1 |
+-----------+

RENAME TABLE t1 TO t2;

-- Ooops, seems like we renamed ordinary table, and INSERT DELAYED worked
-- on it before:
SELECT * FROM t2;
+----------+
| ordinary |
+----------+
|        2 |
+----------+

-- Temporary table is still under t1 name:
SELECT * FROM t1;
+-----------+
| temporary |
+-----------+
|         1 |
+-----------+

Suggested fix:
Either to fix shadowing on a per-statement basis, or forbid having temporary table with the same name as an existing ordinary table.

Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux.

At the very least, we need to make sure this is thoroughly documented for existing releases.

One comment which might be helpful.  While RENAME TABLE refuses to see the TEMPORARY table, ALTER TABLE ... RENAME does work fine.

mysql> create temporary table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert delayed into t2 values (3);
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> insert into t2 values (3);
Query OK, 1 row affected (0.00 sec)

mysql> rename table t2 to t3;
ERROR 1017 (HY000): Can't find file: './test/t2.frm' (errno: 2)
mysql> alter table t2 rename to t3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> rename table t3 to t2;
ERROR 1017 (HY000): Can't find file: './test/t3.frm' (errno: 2)

Omer, when you discuss this bug, take a look at WL#934 
 Temporary Tables In The Standard Way.

We don't have to do the entire worklog, but at the end of it Peter (and me) collected the full list of inconsistencies and feature requests against our temporary tables, in addition to this bug:

Bug#15609 ALTER TEMPORARY TABLE
Bug#20001 Support for temp-tables in INFORMATION_SCHEMA
Bug#7742  You can't reinvoke the same Temporary Table more than once.
Bug#30099 rename/drop database ignores temp tables