Bug #73236 CREATE TEMPORARY TABLE name LIKE same_name

• Submitted: 8 Jul 2014 21:00
• Modified: 9 Jul 2014 4:50
• Reporter: Federico Razzoli
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.6.20
• OS: Any
• CPU Architecture: Any

[8 Jul 2014 21:00] Federico Razzoli

Description:
If I run:

CREATE TABLE tab (c INT) ENGINE = InnoDB;
CREATE TEMPORARY TABLE tab LIKE tab;

I get error 1066. I suppose that this error should only appear if I omit the TEMPORARY keyword, or if a temporary table with that name already exists.

Also note that this statement works:

CREATE TEMPORARY TABLE tab AS SELECT * FROM tab;

How to repeat:
see above

[9 Jul 2014 4:50] MySQL Verification Team

Hello Federico,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

[9 Jul 2014 4:50] MySQL Verification Team

// 5.6

mysql> CREATE TABLE tab (c INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.35 sec)

mysql> CREATE TEMPORARY TABLE tab LIKE tab;
ERROR 1066 (42000): Not unique table/alias: 'tab'
mysql> show errors;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Error | 1066 | Not unique table/alias: 'tab' |
+-------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TEMPORARY TABLE tab AS SELECT * FROM tab;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

[9 Jul 2014 4:54] MySQL Verification Team

// 5.7.5

mysql> use test
Database changed
mysql>
mysql> CREATE TABLE tab (c INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TEMPORARY TABLE tab LIKE tab;
ERROR 1066 (42000): Not unique table/alias: 'tab'
mysql>
mysql> CREATE TEMPORARY TABLE tab AS SELECT * FROM tab;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.7.5-m15-enterprise-commercial-advanced-log |
+----------------------------------------------+
1 row in set (0.00 sec)