MySQL Bugs: #49193: CREATE TABLE reacts differently depending on whether data is selected or not

Bug #49193	CREATE TABLE reacts differently depending on whether data is selected or not
Submitted:	30 Nov 2009 4:24	Modified:	18 Jun 2010 1:20
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.1.42-bzr, 5.5.0	OS:	Any
Assigned to:	Magne Mæhre	CPU Architecture:	Any
Tags:	regression

Description:
When a TEMPORARY table exists, a similar CREATE TABLE reacts differently depending on whether data is selected or not:

--------
mysql> CREATE TEMPORARY TABLE t2 (ID INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t2 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TEMPORARY TABLE t1 (ID INT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t1 (ID INT); /* Creates 't1' table */
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 SELECT * FROM t2;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> DROP TEMPORARY TABLE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE t1 (ID INT);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1 SELECT * FROM t2; /* Similar CREATE TABLE instruction with data select, but does not create 't1' table - ref. warning */
ERROR 1050 (42S01): Table 't1' already exists
mysql> SHOW WARNINGS;
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Error | 1050 | Table 't1' already exists |
+-------+------+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

mysql> DROP TEMPORARY TABLE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1;
ERROR 1146 (42S02): Table 'db.t1' doesn't exist
--------

How to repeat:
CREATE TEMPORARY TABLE t2 (ID INT);
INSERT INTO t2 VALUES (1),(2),(3);
CREATE TEMPORARY TABLE t1 (ID INT);
CREATE TABLE t1 (ID INT); /* Creates 't1' table */
INSERT INTO t1 SELECT * FROM t2;
SELECT * FROM t1;
DROP TEMPORARY TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (ID INT);
CREATE TABLE t1 SELECT * FROM t2; /* Similar CREATE TABLE instruction with data select, but does not create 't1' table - ref. warning */
SHOW WARNINGS;
SELECT * FROM t1;
DROP TEMPORARY TABLE t1;
SELECT * FROM t1;

Verified just as described. Although I think that the bug is here:

mysql> CREATE TEMPORARY TABLE t1 (ID INT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t1 (ID INT); /* Creates 't1' table */
Query OK, 0 rows affected (0.04 sec)

I mean, CREATE TABLE should always fail if any (temporary or not) table with the same name already exists, in all cases.

There's more. If using 'IF NOT EXISTS' on both CREATE TABLE statements, the output is different.

Testcase (same as above, except for 'IF NOT EXISTS' x 2):
-----
CREATE TEMPORARY TABLE t2 (ID INT);
INSERT INTO t2 VALUES (1),(2),(3);
CREATE TEMPORARY TABLE t1 (ID INT);
CREATE TABLE IF NOT EXISTS t1 (ID INT);
INSERT INTO t1 SELECT * FROM t2;
SELECT * FROM t1;
DROP TEMPORARY TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (ID INT);
CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
SHOW WARNINGS;
SELECT * FROM t1;
DROP TEMPORARY TABLE t1;
SELECT * FROM t1;
-----

Without IF NOT EXISTS, the second select outputs:

-----
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
-----

With IF NOT EXISTS, the second select outputs:

-----
mysql> SELECT * FROM t1;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
-----

i.e. data got inserted into the TEMPORARY TABLE. No error is generated, but a warning:

-----
mysql> CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Note  | 1050 | Table 't1' already exists |
+-------+------+---------------------------+
1 row in set (0.00 sec)
----

This looks like a regression of bug #24508.

See also bug#47418. 
The patch for bug#47418 probably fixed this bug together.
Bug#47418 is fixed with the following resolution:
Temporary table and base table are not in the same name space. When executing 'CREATE TABLE [IF NOT EXISTS]' t1 SELECT ...', even though a temporary table exists with the same name, the base table shall be created and the results of 'SELECT...' part are always insert into the base table.

Magne, this is fixed in 5.5, please add a test case and close the bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107578

3013 Magne Mahre	2010-05-05
      Bug#49193 CREATE TABLE reacts differently depending on whether 
                data is selected or not
      
      Temporary and permanent tables should live in different 
      namespaces.  In this case, resolving a permanent table
      name gave the temporary table, resulting in a name
      collision.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Patch is for test case. No changelog entry needed.