MySQL Bugs: #23992: "CREATE TABLE .. IF NOT EXISTS" confusion

Bug #23992	"CREATE TABLE .. IF NOT EXISTS" confusion
Submitted:	5 Nov 2006 23:10	Modified:	13 Nov 2006 9:26
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S3 (Non-critical)
Version:	5.0.30-BK, 5.0.26	OS:	Linux (Debian GNU/Linux Sid)
Assigned to:		CPU Architecture:	Any

Description:
It seems that "IF NOT EXISTS" is ignored in CREATE TABLE statements when using the engine type "memory".
A following SELECT statement does not fill a memory based table but the pre-existing table.

How to repeat:
mysql> CREATE TABLE t (i int);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS t (i int) ENGINE=Memory AS SELECT 42;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> SELECT * FROM t;
+------+
| i    |
+------+
|   42 | 
+------+
1 row in set (0.00 sec)

Suggested fix:
The warning should definetly be an error.

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

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.30-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t (i int);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS t (i int) ENGINE=Memory AS SELECT 42;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show warnings;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Note  | 1050 | Table 't' already exists |
+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> show create table t;
+-------+-----------------------------------------------------------------------
---------------+
| Table | Create Table
               |
+-------+-----------------------------------------------------------------------
---------------+
| t     | CREATE TABLE `t` (
  `i` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
---------------+
1 row in set (0.01 sec)

mysql> select * from t;
+------+
| i    |
+------+
|   42 |
+------+
1 row in set (0.00 sec)

The above is a bug also: we inserted data into existing(!) MyISAM table.

mysql> show variables like 'sql%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
| sql_mode        |       |
| sql_notes       | ON    |
| sql_warnings    | OFF   |
+-----------------+-------+
4 rows in set (0.00 sec)

It doesn't have anything to do with MEMORY tables. You are trying to create a table with the name of already existing table. A creation fails. Because of IF NOT EXISTS this is not an error. Then rows are inserted. According to the manual:

Note: If you use IF NOT EXISTS in a CREATE TABLE ... SELECT  statement, any rows selected by the SELECT  part are inserted regardless of whether the table already exists.
(http://dev.mysql.com/doc/refman/5.0/en/create-table.html)

See also WL#5370 where we're planning to change the semantics of CREATE TABLE IF NOT EXISTS ... SELECT for an existing table.

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/116060

3184 Li-Bing.Song@sun.com	2010-08-18
      WL#5370 Keep forward-compatibility when changing 
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#47132, BUG#47442, BUG49494, BUG#23992 and BUG#48814 will disappear
      automatically after the this patch.
      BUG#55617 is fixed by this patch too.
            
      This is the 5.5 part.
      It implements:
      - 'CREATE TABLE IF NOT EXISTS ... SELECT' statement will not insert
        anything and binlog anything if the table already exists.
        It only generate a warning that table already exists.
      - A couple of test cases for the behavior changing.

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/116062

3186 Li-Bing.Song@sun.com	2010-08-18
      WL#5370 Keep forward-compatibility when changing 
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#47132, BUG#47442, BUG49494, BUG#23992 and BUG#48814 will disappear
      automatically after the this patch.
      BUG#55617 is fixed by this patch too.
                  
      This is the 5.5 part.
      It implements:
      - 'CREATE TABLE IF NOT EXISTS ... SELECT' statement will not insert
        anything and binlog anything if the table already exists.
        It only generate a warning that table already exists.
      - A couple of test cases for the behavior changing.

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)