bzr push into mysql-trunk-runtime branch (Li-Bing.Song:3079 to 3080) Bug#55617

[Li-Bing.Song]

 3080 Li-Bing.Song@stripped	2010-08-02
      Bug#55617 Irrelevant error on attempt to override an existing view 
                by CREATE IF NOT EXISTS
      
      If a view v already exists and reads from a table t, and CREATE TABLE IF NOT EXISTS v AS
      SELECT * FROM t is attempted, it fails with ER_VIEW_PREVENT_UPDATE. It is not
      reasonable with the new logic, since the insert is not going to happen anyway
      
      After this patch, ER_VIEW_PREVENT_UPDATE is replaced by ER_TABLE_EXISTS_ERROR.
     @ mysql-test/t/create.test
        After WL#5370, the statements just generates a warning or an error
        that  ER_TABLE_EXISTS_ERROR instead of ER_UPDATE_TABLE_USED.
     @ mysql-test/t/merge.test
        After WL#5370, the statement just generates a warning that the table already exists
     @ mysql-test/t/union.test
        After WL#5370, the statement generates the ER_TABLE_EXISTS_ERROR error.
     @ sql/sql_parse.cc
        After WL#5370, it doesn't need to check whether the creating table is
        appearing in SELECT clause. As the statement will end immediately with
        an error or a warning if it already exists.

    modified:
      mysql-test/r/create.result
      mysql-test/r/merge.result
      mysql-test/r/union.result
      mysql-test/t/create.test
      mysql-test/t/merge.test
      mysql-test/t/union.test
      sql/sql_parse.cc
 3079 Li-Bing.Song@stripped	2010-07-21
      Postfix for BUG47321.
      We have to change the test after we changed the semantic of
      'CREATE TABLE IF NOT EXISTS ... SELECT'.

    modified:
      mysql-test/include/commit.inc
      mysql-test/r/commit_1innodb.result
=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result	2010-07-07 18:05:05 +0000
+++ b/mysql-test/r/create.result	2010-08-02 11:16:21 +0000
@@ -604,7 +604,7 @@ b
 drop table t1,t2;
 create table t1 (a int);
 create table t1 select * from t1;
-ERROR HY000: You can't specify target table 't1' for update in FROM clause
+ERROR 42S01: Table 't1' already exists
 create table t2 union = (t1) select * from t1;
 ERROR HY000: 'test.t2' is not BASE TABLE
 flush tables with read lock;
@@ -818,7 +818,8 @@ Note	1050	Table 't1' already exists
 select * from t1;
 i
 create table if not exists t1 select * from t1;
-ERROR HY000: You can't specify target table 't1' for update in FROM clause
+Warnings:
+Note	1050	Table 't1' already exists
 select * from t1;
 i
 drop table t1;

=== modified file 'mysql-test/r/merge.result'
--- a/mysql-test/r/merge.result	2010-07-02 16:07:57 +0000
+++ b/mysql-test/r/merge.result	2010-08-02 11:16:21 +0000
@@ -933,7 +933,8 @@ DROP TABLE tm1, t1, t2;
 CREATE TABLE t1(c1 INT);
 CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
 CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
-ERROR HY000: You can't specify target table 't1' for update in FROM clause
+Warnings:
+Note	1050	Table 't1' already exists
 DROP TABLE t1, t2;
 CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM;
 CREATE TABLE t2 LIKE t1;

=== modified file 'mysql-test/r/union.result'
--- a/mysql-test/r/union.result	2010-03-24 15:03:44 +0000
+++ b/mysql-test/r/union.result	2010-08-02 11:16:21 +0000
@@ -428,7 +428,7 @@ ERROR 42000: Incorrect usage/placement o
 create temporary table t1 select a from t1 union select a from t2;
 drop temporary table t1;
 create table t1 select a from t1 union select a from t2;
-ERROR HY000: You can't specify target table 't1' for update in FROM clause
+ERROR 42S01: Table 't1' already exists
 select a from t1 union select a from t2 order by t2.a;
 ERROR 42S22: Unknown column 't2.a' in 'order clause'
 drop table t1,t2;

=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test	2010-07-07 18:05:05 +0000
+++ b/mysql-test/t/create.test	2010-08-02 11:16:21 +0000
@@ -515,7 +515,7 @@ drop table t1,t2;
 # an improper fix is present.
 #
 create table t1 (a int);
---error 1093
+--error ER_TABLE_EXISTS_ERROR
 create table t1 select * from t1;
 --error ER_WRONG_OBJECT
 create table t2 union = (t1) select * from t1;
@@ -739,8 +739,7 @@ create table t1 (i int);
 create table t1 select 1 as i;
 create table if not exists t1 select 1 as i;
 select * from t1;
-# Error which is detected after successfull table open.
---error ER_UPDATE_TABLE_USED
+# After WL#5370, it just generates a warning that the table already exists
 create table if not exists t1 select * from t1;
 select * from t1;
 drop table t1;

=== modified file 'mysql-test/t/merge.test'
--- a/mysql-test/t/merge.test	2010-07-02 16:07:57 +0000
+++ b/mysql-test/t/merge.test	2010-08-02 11:16:21 +0000
@@ -579,7 +579,7 @@ DROP TABLE tm1, t1, t2;
 #
 CREATE TABLE t1(c1 INT);
 CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
---error ER_UPDATE_TABLE_USED
+# After WL#5370, it just generates a warning that the table already exists
 CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
 DROP TABLE t1, t2;
 

=== modified file 'mysql-test/t/union.test'
--- a/mysql-test/t/union.test	2010-01-19 16:36:14 +0000
+++ b/mysql-test/t/union.test	2010-08-02 11:16:21 +0000
@@ -253,7 +253,7 @@ SELECT * FROM t1 UNION SELECT * FROM t2 
 
 create temporary table t1 select a from t1 union select a from t2;
 drop temporary table t1;
---error 1093
+--error ER_TABLE_EXISTS_ERROR
 create table t1 select a from t1 union select a from t2;
 --error 1054
 select a from t1 union select a from t2 order by t2.a;

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2010-07-08 13:53:11 +0000
+++ b/sql/sql_parse.cc	2010-08-02 11:16:21 +0000
@@ -2674,29 +2674,9 @@ case SQLCOM_PREPARE:
 
       if (!(res= open_and_lock_tables(thd, lex->query_tables, TRUE, 0)))
       {
-        /*
-          Is table which we are changing used somewhere in other
-          parts of the query? Skip the check for tmp tables, since
-          they can't be opened twice anyway. Do the check even if the table
-          exists, to produce the error consistently regardless of
-          the state of the database.
-        */
-        if (!(create_info.options & HA_LEX_CREATE_TMP_TABLE))
-        {
-          TABLE_LIST *duplicate;
-          if ((duplicate= unique_table(thd, create_table, select_tables, 0)))
-          {
-            update_non_unique_table_error(create_table, "CREATE", duplicate);
-            res= 1;
-            goto end_with_restore_list;
-          }
-        }
+        /* The table already exists */
         if (create_table->table)
         {
-          /*
-            Table already exists and was open at open_and_lock_tables()
-            stage.
-          */
           if (create_info.options & HA_LEX_CREATE_IF_NOT_EXISTS)
           {
             push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
@@ -2712,6 +2692,7 @@ case SQLCOM_PREPARE:
           }
           goto end_with_restore_list;
         }
+
         /*
           Remove target table from main select and name resolution
           context. This can't be done earlier as it will break view merging in


Attachment: [text/bzr-bundle] bzr/[e-mail]