MySQL Bugs: #48814: CREATE TABLE IF NOT EXISTS <non-insertable view> SELECT does not fail

Bug #48814	CREATE TABLE IF NOT EXISTS <non-insertable view> SELECT does not fail
Submitted:	16 Nov 2009 19:14	Modified:	9 Sep 2010 17:44
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.0+, 5.5.0	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
If t is a non-insertable view, then "CREATE TABLE IF NOT EXISTS t SELECT" will not give an error message. Moreover, it will give a "Note" referring to a path instead of to a table.

Background:
* If t is a non-insertable view, then inserts into t give the error message "The target table t1 of the INSERT is not insertable-into". This is ok.

* If t is a table or view that can be inserted into, then "CREATE TABLE IF NOT EXISTS t SELECT ..." will not create a new object, but it will insert the result of the select into the existing object. This is ok too.

* Hence, if t is a non-insertable view, then we would expect that "CREATE TABLE IF NOT EXISTS t SELECT ..." does not try to create another object, then tries to insert the result of the SELECT into the view, and then fails with the error message "The target table t1 of the INSERT is not insertable-into".

How to repeat:
# Inserts into non-insertable views normally gives an error.
CREATE VIEW t1 AS SELECT 1;
--error ER_NON_INSERTABLE_TABLE
INSERT INTO t1 VALUES (1);

# "CREATE TABLE IF NOT EXISTS t SELECT" normally inserts into t if t exists
CREATE TABLE t2 (a INT);
CREATE TABLE IF NOT EXISTS t2 SELECT 2;
SELECT * FROM t2;

# "CREATE TABLE IF NOT EXISTS t SELECT" does not insert into t and
# does not give an error.
CREATE VIEW t3 AS SELECT 3;
CREATE TABLE IF NOT EXISTS t3 SELECT 4;
# The above line gives the error message:
# Note 1050 Table '/[...]/var/tmp/mysqld.1/#sql_3495_0' already exists
SELECT * FROM t3;

I disagree with this:

* If t is a table or view that can be inserted into, then "CREATE TABLE IF NOT EXISTS t SELECT ..." will not create a new object, but it will insert the result of the select into the existing object. This is ok too.

In my opinion it should simply return error 'table already exists'.

Thank you for the bug report.

Same result with 5.5:

openxs@suse:/home2/openxs/dbs/next-mr> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.0-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE VIEW t1 AS SELECT 1;
Query OK, 0 rows affected (0.04 sec)

mysql> --error ER_NON_INSERTABLE_TABLE
    -> INSERT INTO t1 VALUES (1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--error ER_NON_INSERTABLE_TABLE
INSERT INTO t1 VALUES (1)' at line 1
mysql>
mysql> # "CREATE TABLE IF NOT EXISTS t SELECT" normally inserts into t if t exists
mysql> CREATE TABLE t2 (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS t2 SELECT 2;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM t2;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> CREATE VIEW t3 AS SELECT 3;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS t3 SELECT 4;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1050
Message: Table '/tmp/#sql_1a01_0' already exists
1 row in set (0.00 sec)

mysql> select * FROM t3;
+---+
| 3 |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

mysql> show create table t3\G
*************************** 1. row ***************************
                View: t3
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t3` AS select 3 AS `3`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.01 sec)

This bug is updated in the spec. of wl5370.
So after wl5370 included, as per the spec. it should be 
- CREATE TABLE.. SELECT should produce ER_TABLE_EXISTS error
(it already does but the error should be corrected to include table name instead of path name.)
- CREATE TABLE IF NOT EXISTS..SELECT should produce warning. 
(it already does, but the warning should be corrected to include table name instead of path name.)

Should be fixed as part of wl5370 ?

It will be fixed by WL#5370.
After WL#5370, 'CREATE TABLE IF NOT EXISTS ... SELECT' will insert nothing with a warning that 'table already exits.' if a view of the same name already exists.

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.

It disappears after the patch for WL#5370. As behavior of CREATE TABLE IF NOT EXISTS SELECT has changed. It will never insert anything and not be binlogged
after the worklog.

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)

Noted in 5.1.51, 5.5.6, 5.6.1 changelogs.

As of MySQL 5.5.6, handling of CREATE TABLE IF NOT EXISTS ... SELECT
statements has been changed for the case that the destination table
already exists:

* Previously, for CREATE TABLE IF NOT EXISTS ... SELECT, MySQL produced
  a warning that the table exists, but inserted the rows and wrote the
  statement to the binary log anyway. By contrast, CREATE TABLE ...
  SELECT (without IF NOT EXISTS) failed with an error, but MySQL
  inserted no rows and did not write the statement to the binary log.

* MySQL now handles both statements the same way when the destination
  table exists, in that neither statement inserts rows or is written to
  the binary log. The difference between them is that MySQL produces a
  warning when IF NOT EXISTS is present and an error when it is not.

This change in handling of IF NOT EXISTS results in an
incompatibility for statement-based replication from a MySQL 5.1
master with the original behavior and a MySQL 5.5 slave with the new
behavior. Suppose that CREATE TABLE IF NOT EXISTS ... SELECT is
executed on the master and the destination table exists. The result
is that rows are inserted on the master but not on the slave.
(Row-based replication does not have this problem.)

To address this issue, statement-based binary logging for CREATE
TABLE IF NOT EXISTS ... SELECT is changed in MySQL 5.1 as of 5.1.51:

* If the destination table does not exist, there is no change: The
  statement is logged as is.

* If the destination table does exist, the statement is logged as the
  equivalent pair of CREATE TABLE IF NOT EXISTS and INSERT ... SELECT
  statements. (If the SELECT in the original statement is preceded by
  IGNORE or REPLACE, the INSERT becomes INSERT IGNORE or REPLACE,
  respectively.)

This change provides forward compatibility for statement-based
replication from MySQL 5.1 to 5.5 because when the destination table
exists, the rows will be inserted on both the master and slave. To
take advantage of this compatibility measure, the 5.1 server must be
at least 5.1.51 and the 5.5 server must be at least 5.5.6.

To upgrade an existing 5.1-to-5.5 replication scenario, upgrade the
master first to 5.1.51 or higher. Note that this differs from the
usual replication upgrade advice of upgrading the slave first.

A workaround for applications that wish to achieve the original
effect (rows inserted regardless of whether the destination table
exists) is to use CREATE TABLE IF NOT EXISTS and INSERT ... SELECT
statements rather than CREATE TABLE IF NOT EXISTS ... SELECT
statements. 

Along with the change just described, the following related change
was made: Previously, if an existing updatable view was named as the
destination table for CREATE TABLE IF NOT EXISTS ... SELECT, rows
were inserted into the underlying base table and the statement was
written to the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is
inserted or logged.