MySQL Bugs: #49494: CREATE TABLE IF NOT EXISTS does wrong insert when view exists with the name

Bug #49494	CREATE TABLE IF NOT EXISTS does wrong insert when view exists with the name
Submitted:	7 Dec 2009 9:52	Modified:	9 Sep 2010 17:43
Reporter:	Mats Kindahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.37, 5.0, next-mr	OS:	Linux (Ubuntu 9.10)
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
If CREATE TABLE IF NOT EXISTS with SELECT is executed and there is a view with the same name, the insertion does not update the underlying table correctly.

How to repeat:
mysql> create table t3 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> create view t1 as select a from t3;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> select * from t3;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL | 
|    2 | NULL | 
+------+------+
2 rows in set (0.00 sec)

mysql> create table if not exists t1 select * from t2;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL | 
|    2 | NULL | 
| NULL |    3 | 
| NULL |    4 | 
+------+------+
4 rows in set (0.00 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
|    2 | 
| NULL | 
| NULL | 
+------+
4 rows in set (0.00 sec)

Suggested fix:
Ensure that the inserted data ends up the the first column and not the second.

also read:
http://bugs.mysql.com/bug.php?id=47132

Thank you for the bug report. Have you copied/pasted the correct how to repeat steps?. Thanks in advance:

c:\mysql-5.1.37-winx64>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37-community MySQL Community Server (GPL)

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

mysql> create table t3 (a int, b int);
Query OK, 0 rows affected (0.12 sec)

mysql> create view t1 as select a from t3;
Query OK, 0 rows affected (0.11 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t3;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> create table if not exists t1 select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

Sorry about that, there were a missing definition of t2. The real sequence is:

mysql> create table t3 (a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> create view t1 as select a from t3;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> select * from t3;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL | 
|    2 | NULL | 
+------+------+
2 rows in set (0.00 sec)

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values (3),(4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table if not exists t1 select * from t2;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL | 
|    2 | NULL | 
| NULL |    3 | 
| NULL |    4 | 
+------+------+
4 rows in set (0.00 sec)

Thank you for the report.

Verified as described.

It seems the general problem here is that "create table if not exists ... select ..." operate directly on the underlying table without taking the view definition
into account.  Look at this example where a view reorders the columns of a table:

mysql> create table t1 (a int, b int);
Query OK, 0 rows affected (0.02 sec)

mysql> create view v1 as select b, a from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1,2);
Query OK, 1 row affected (0.04 sec)

mysql> insert into v1 values (3,4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 | 
|    4 |    3 | 
+------+------+
2 rows in set (0.00 sec)

mysql> create table t3 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t3 values (5,6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into v1 select * from t3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 | 
|    4 |    3 | 
|    6 |    5 | 
+------+------+
3 rows in set (0.00 sec)

mysql> create table if not exists v1 select * from t3;
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 'v1' already exists | 
+-------+------+---------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 | 
|    4 |    3 | 
|    6 |    5 | 
|    5 |    6 | 
+------+------+
4 rows in set (0.00 sec)

Note that the column values from "create table ... select ..." is stored in opposite order of "insert into ... select ..."

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

3086 oystein.grovlen@sun.com	2010-02-08
      BUG#49494 CREATE TABLE IF NOT EXISTS does wrong insert when view exists
                with the same name
      
      Problem observed was that with CREATE TABLE IF NOT EXISTS ... SELECT ...
      when a view with the same name as the table to be created, already existed.
      The values from the query was not inserted into the correct columns of
      the underlying tables of the view.
      
      The problem was that select_create::prepare did not take views into account,
      but inserted values directly into the underlying table in the original
      column order, not view column order.
      
      The fix has two major parts:
      1. If table already exists, call mysql_insert_select_prepare() before
         calling handle_select(). (As is done for "INSERT INTO ... SELECT ...").
         This will set up tables and views, including preparing the translation
         table for view columns.
      
      2. In order to be able to handle views, the select_create is changed
         to work on a List<Item_field> instead of directly on the list of
         fields of a table. Then, setup_fields() and check_insert_fields()
         can be called from select_create::prepare() to set up the right
         mapping to the Field objects of the underlying table. (Similar to
         select_insert::prepare())
      
      Since the check for table existence now has to be done before calling
      handle_select(), it makes sense to also generate a warning/error due to
      table existence at the same place.  Hence, this code has been moved from
      select_create::prepare().  This causes "Table already exists" errors to
      be generated earlier than before, and a few test cases had to be changed
      in order to make sure they still test for the same errors as before.
      
      Also added some comments to document select_create class.
     @ mysql-test/r/create.result
        Updated result file.
     @ mysql-test/r/union.result
        Updated result file.
     @ mysql-test/t/create.test
        Added test cases for Bug#49494.
        
        Changed a few test cases to avoid that one get "Table already exists" 
        instead of error we want to test for.  (Table existence is now checked 
        at an earlier stage.)
     @ mysql-test/t/union.test
        Changed test case to avoid that one get "Table already exists" instead
        error we want to test for.  (Table existence is now checked at an earlier
        stage.)
     @ sql/sql_class.h
         - Pass table_arg to select_insert constructor. Makes sure 
           insert_into_view is set up correctly.
         - Rename select_fields to fields_arg since it is now what it is used for.
         - Removed select_create::field since it is not used anymore.
         - Removed select_create::group since it was not in use before this change.
         - Added some comments to document select_create class.
     @ sql/sql_insert.cc
         - Change select_create::prepare() and select_create::store_values() to
           work on a List<Item_field> instead of directly on the list of
           fields of the table.
         - In case fields list has not been set up earlier, do so by calling
           TABLE::fill_item_list and also update the table's write_set.
           (Will not be done by check_insert_fields() in this case since Item_field
            objects have already been fixed.)
         - If fewer values are supplied by query than candidate columns, values 
           should go into right-most columns.  Hence, pop necessary number of
           left-most columns from fields list.
         - Call setup_fields/check_insert_fields which will do the necessary setup
           of fields and values, including translating view columns into table
           columns.
         - Moved error/warning handling of table existence to mysql_execute_command()
     @ sql/sql_parse.cc
         - If table already exists when executing a 
           "CREATE TABLE IF NOT EXISTS ... SELECT ..." statement,
           call mysql_insert_select_prepare() before calling handle_select()
           in order to set up tables and views. Also, tell handle_select() that
           tables has been set up, by passing OPTION_SETUP_TABLES_DONE.
         - By calling mysql_insert_select_prepare(), lex->field_list will be
           populated with item for table fields. Pass this list to the select_create
           constructor. (select_lex_item_list that was passed earlier did not
           seem to be used for anything.) 
         - Make sure to initialize lex->field_list so that it is empty in cases 
           where it is not populated by mysql_insert_select_prepare()

It will be fixed by bug#47132.
After the patch for bug#47132, 'CREATE TABLE IF NOT EXISTS ... SELECT' will do nothing and binlog nothing if the table exists.

Please only close after a test case is in.

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.

The patch has been pushed into main trees.
See bug#47132, they were fixed in the same patch.

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.