MySQL Bugs: #49192: CREATE TABLE LIKE <TEMPORARY TABLE> does not function correctly

Bug #49192	CREATE TABLE LIKE <TEMPORARY TABLE> does not function correctly
Submitted:	30 Nov 2009 4:06	Modified:	16 Dec 2009 4:36
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
When creating a schema copy of a TEMPORARY table, the newly created table is not TEMPORARY.

The manual does not specify this exception at:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

How to repeat:
mysql> CREATE TEMPORARY TABLE tmptst (ID INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tmptst;
+--------+------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                         |
+--------+------------------------------------------------------------------------------------------------------+
| tmptst | CREATE TEMPORARY TABLE `tmptst` (
  `ID` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tmptst2 LIKE tmptst;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tmptst2;
+---------+---------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                |
+---------+---------------------------------------------------------------------------------------------+
| tmptst2 | CREATE TABLE `tmptst2` (
  `ID` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

----
Same results for InnoDB and MEMORY storage engines.

Suggested fix:
Update manual or change behavior?

Note that in the formal syntax on that manual page:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

TEMPORARY keyword is noted as optional. So, if you need temporary table of the same structure add TEMPORARY keyword. Otherwise why it is at all needed in this syntax?

Look:

mysql>  CREATE TEMPORARY TABLE tmptst (ID INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>  CREATE TABLE tmptst2 LIKE tmptst;
Query OK, 0 rows affected (0.07 sec)

mysql>  CREATE TEMPORARY TABLE tmptst3 LIKE tmptst;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tmptst2\G
*************************** 1. row ***************************
       Table: tmptst2
Create Table: CREATE TABLE `tmptst2` (
  `ID` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table tmptst3\G
*************************** 1. row ***************************
       Table: tmptst3
Create Table: CREATE TEMPORARY TABLE `tmptst3` (
  `ID` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

So, I'd say this is not a bug and I do not see any big need to write anything else about this case in the manual.

> TEMPORARY keyword is noted as optional. So, if you need temporary table of the same structure add TEMPORARY keyword. 

The manual also states:

'Use LIKE to create an empty table *based on the definition of another table*, including any column attributes and indexes defined in the original table...'

One should not need to specify TEMPORARY *again* when using LIKE - that would be like saying, all 'optional' attributes need to be specified again.

> Otherwise why it is at all needed in this syntax?

It is needed for creating a TEMPORARY table :)

Verified as D2 (Behavior contrary to specification or documentation, as per the above.)

In English, the term "based on" does not mean "the same as".

In standard SQL, if I say CREATE TABLE ... LIKE ...
instead of CREATE { GLOBAL | LOCAL } TEMPORARY TABLE ... LIKE ...,
then I get a persistent table not a temporary table
(SQL/Foundation, <table definition>, General Rule 1).
The LIKE clause, with no options, is for copying
column definitions.

There appears to be no reason for describing this as a bug.

According to Peter (see his comment), this is not a bug.

Reopening, because the bug reporter requested the following change to the docs:

Basically, the statement ...

'Use LIKE to create an empty table *based on the definition of another table*, including any column attributes and indexes defined in the original table...'

... needs to be amended.

Something like:

'Use LIKE to create an empty table based on the column column attributes and indexes defined in the original table (note that if the original table was a TEMPORARY table, that the TEMPORARY attribute is not copied to the new table; it will be made a normal base table.)'

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added:

If the original table is a TEMPORARY table, CREATE TABLE ... LIKE
does not preserve TEMPORARY. To create a TEMPORARY destination table,
use CREATE TEMPORARY TABLE ... LIKE.