bzr commit into mysql-5.5-next-mr-bugfixing branch (holyfoot:2939) Bug#47132

[Alexey Botchkov]

#At file:///home/hf/work/mysql_common/47132/ based on
revid:jperkin@stripped

 2939 Alexey Botchkov	2009-11-04
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple
tables 
         if the created table already exists, we insert no rows in the existing tables.
         In the binary log we write CREATE TABLE command with the structure of the
existing
         table.
         Several tests have to be fixed then as CREATE TABLE IF EXISTS SELECT statements
         are replaced with the INSERT INTO SELECT
      
      per-file comments:
        mysql-test/r/auto_increment.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple
tables 
          result updated
        mysql-test/r/create.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple
tables 
          result updated
        mysql-test/r/trigger.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple
tables 
          result updated
        mysql-test/t/auto_increment.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple
tables 
          test fixed.
        mysql-test/t/create.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple
tables 
          test fixed.
        mysql-test/t/trigger.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple
tables 
          test fixed.
        sql/sql_insert.cc
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple
tables 
         do nothing if the table already exists.

    modified:
      mysql-test/r/auto_increment.result
      mysql-test/r/create.result
      mysql-test/r/trigger.result
      mysql-test/t/auto_increment.test
      mysql-test/t/create.test
      mysql-test/t/trigger.test
      sql/sql_insert.cc
=== modified file 'mysql-test/r/auto_increment.result'
--- a/mysql-test/r/auto_increment.result	2009-08-20 12:30:59 +0000
+++ b/mysql-test/r/auto_increment.result	2009-11-04 17:49:03 +0000
@@ -469,7 +469,7 @@ drop table t1;
 CREATE TABLE t1 ( a INT );
 INSERT INTO t1 VALUES (1), (1);
 CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
-CREATE TABLE IF NOT EXISTS t2 AS SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
 UPDATE t2 SET a = 2;
 SELECT a FROM t2;

=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result	2009-09-04 06:57:10 +0000
+++ b/mysql-test/r/create.result	2009-11-04 17:49:03 +0000
@@ -256,15 +256,14 @@ create table if not exists t1 select 1,2
 Warnings:
 Note	1050	Table 't1' already exists
 create table if not exists t1 select 1,2,3,4;
-ERROR 21S01: Column count doesn't match value count at row 1
+Warnings:
+Note	1050	Table 't1' already exists
 create table if not exists t1 select 1;
 Warnings:
 Note	1050	Table 't1' already exists
 select * from t1;
 1	2	3
 1	2	3
-0	1	2
-0	0	1
 drop table t1;
 flush status;
 create table t1 (a int not null, b int, primary key (a));
@@ -272,28 +271,21 @@ insert into t1 values (1,1);
 create table if not exists t1 select 2;
 Warnings:
 Note	1050	Table 't1' already exists
-Warning	1364	Field 'a' doesn't have a default value
 select * from t1;
 a	b
 1	1
-0	2
 create table if not exists t1 select 3 as 'a',4 as 'b';
 Warnings:
 Note	1050	Table 't1' already exists
-create table if not exists t1 select 3 as 'a',3 as 'b';
-ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
 show warnings;
 Level	Code	Message
 Note	1050	Table 't1' already exists
-Error	1062	Duplicate entry '3' for key 'PRIMARY'
 show status like "Opened_tables";
 Variable_name	Value
 Opened_tables	2
 select * from t1;
 a	b
 1	1
-0	2
-3	4
 drop table t1;
 create table `t1 `(a int);
 ERROR 42000: Incorrect table name 't1 '
@@ -792,7 +784,8 @@ create table t1 (primary key(a)) select 
 ERROR 42000: Key column 'a' doesn't exist in table
 create table t1 (a int);
 create table if not exists t1 select 1 as a, 2 as b;
-ERROR 21S01: Column count doesn't match value count at row 1
+Warnings:
+Note	1050	Table 't1' already exists
 drop table t1;
 create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
@@ -804,19 +797,10 @@ Warnings:
 Note	1050	Table 't1' already exists
 select * from t1;
 i
-1
 create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and
(latin1_bin,EXPLICIT) for operation 'coalesce'
 select * from t1;
 i
-1
-alter table t1 add primary key (i);
-create table if not exists t1 (select 2 as i) union all (select 2 as i);
-ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
-select * from t1;
-i
-1
-2
 drop table t1;
 create temporary table t1 (j int);
 create table if not exists t1 select 1;
@@ -824,7 +808,6 @@ Warnings:
 Note	1050	Table 't1' already exists
 select * from t1;
 j
-1
 drop temporary table t1;
 select * from t1;
 ERROR 42S02: Table 'test.t1' doesn't exist
@@ -1565,11 +1548,9 @@ CREATE TABLE t1 (a INTEGER AUTO_INCREMEN
 INSERT IGNORE INTO t1 (b) VALUES (5);
 CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
 SELECT a FROM t1;
-CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
-SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
-CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
-SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
 DROP TABLE t1, t2;
 #
@@ -1599,7 +1580,7 @@ drop table if exists t2;
 Warnings:
 Note	1051	Unknown table 't2'
 CREATE TABLE t2 (a int, b int, primary key (a));
-CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+INSERT INTO t2 select * from t1;
 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
 SELECT * from t2;
 a	b

=== modified file 'mysql-test/r/trigger.result'
--- a/mysql-test/r/trigger.result	2009-09-10 09:18:29 +0000
+++ b/mysql-test/r/trigger.result	2009-11-04 17:49:03 +0000
@@ -1420,9 +1420,7 @@ create trigger t1_ai after insert on t1 
 create table t2 (j int);
 insert into t2 values (1), (2);
 set @a:="";
-create table if not exists t1 select * from t2;
-Warnings:
-Note	1050	Table 't1' already exists
+insert into t1 select * from t2;
 select * from t1;
 i
 7
@@ -1434,9 +1432,7 @@ drop trigger t1_bi;
 drop trigger t1_ai;
 create table t3 (isave int);
 create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
-create table if not exists t1 select * from t2;
-Warnings:
-Note	1050	Table 't1' already exists
+insert into t1 select * from t2;
 select * from t1;
 i
 7
@@ -1622,10 +1618,8 @@ After DELETE, old=REPLACE, inserting a n
 After INSERT, new=REPLACE, deleting the duplicate
 truncate t1;
 truncate t1_op_log;
-create table if not exists t1
+insert into t1
 select NULL, "CREATE TABLE ... SELECT, inserting a new key";
-Warnings:
-Note	1050	Table 't1' already exists
 set @id=last_insert_id();
 select * from t1;
 id	operation
@@ -1635,10 +1629,8 @@ operation
 Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
 After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
 truncate t1_op_log;
-create table if not exists t1 replace
+replace into t1
 select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
-Warnings:
-Note	1050	Table 't1' already exists
 select * from t1;
 id	operation
 1	CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
@@ -1817,10 +1809,8 @@ After DELETE, old=REPLACE, inserting a n
 After INSERT, new=REPLACE, deleting the duplicate
 truncate t1;
 truncate t1_op_log;
-create table if not exists v1
+insert into v1
 select NULL, "CREATE TABLE ... SELECT, inserting a new key";
-Warnings:
-Note	1050	Table 'v1' already exists
 set @id=last_insert_id();
 select * from t1;
 id	operation
@@ -1830,10 +1820,8 @@ operation
 Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
 After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
 truncate t1_op_log;
-create table if not exists v1 replace
+replace into v1
 select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
-Warnings:
-Note	1050	Table 'v1' already exists
 select * from t1;
 id	operation
 1	CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
@@ -2083,7 +2071,8 @@ BEGIN 
 UPDATE a_nonextisting_table SET a = 1;
 END//
 CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1;
-ERROR 42S02: Table 'test.a_nonextisting_table' doesn't exist
+Warnings:
+Note	1050	Table 't2' already exists
 SELECT * FROM t2;
 a	b
 DROP TABLE t1, t2;

=== modified file 'mysql-test/t/auto_increment.test'
--- a/mysql-test/t/auto_increment.test	2009-08-20 12:30:59 +0000
+++ b/mysql-test/t/auto_increment.test	2009-11-04 17:49:03 +0000
@@ -334,7 +334,7 @@ INSERT INTO t1 VALUES (1), (1);
 
 CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
 --error ER_DUP_ENTRY
-CREATE TABLE IF NOT EXISTS t2 AS SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
 
 UPDATE t2 SET a = 2;
 

=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test	2009-09-04 06:57:10 +0000
+++ b/mysql-test/t/create.test	2009-11-04 17:49:03 +0000
@@ -216,7 +216,6 @@ drop table t1;
 
 create table t1 select 1,2,3;
 create table if not exists t1 select 1,2;
---error 1136
 create table if not exists t1 select 1,2,3,4;
 create table if not exists t1 select 1;
 select * from t1;
@@ -232,8 +231,6 @@ insert into t1 values (1,1);
 create table if not exists t1 select 2;
 select * from t1;
 create table if not exists t1 select 3 as 'a',4 as 'b';
---error ER_DUP_ENTRY
-create table if not exists t1 select 3 as 'a',3 as 'b';
 show warnings;
 show status like "Opened_tables";
 select * from t1;
@@ -694,7 +691,6 @@ create table t1 select coalesce('a' coll
 create table t1 (primary key(a)) select "b" as b;
 # Error in select_create::prepare() which is not related to table creation
 create table t1 (a int);
---error ER_WRONG_VALUE_COUNT_ON_ROW
 create table if not exists t1 select 1 as a, 2 as b;
 drop table t1;
 # Finally error which happens during insert
@@ -710,11 +706,6 @@ select * from t1;
 --error ER_CANT_AGGREGATE_2COLLATIONS
 create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
 select * from t1;
-# Error which happens during insertion of rows
-alter table t1 add primary key (i);
---error ER_DUP_ENTRY
-create table if not exists t1 (select 2 as i) union all (select 2 as i);
-select * from t1;
 drop table t1;
 
 
@@ -1190,11 +1181,9 @@ INSERT IGNORE INTO t1 (b) VALUES (5);
 CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
   SELECT a FROM t1;
 --error 1062
-CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
-  SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
 --error 1062
-CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
-  SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
 
 DROP TABLE t1, t2;
 
@@ -1234,7 +1223,7 @@ CREATE TEMPORARY TABLE t2 (primary key (
 drop table if exists t2;
 CREATE TABLE t2 (a int, b int, primary key (a));
 --error ER_DUP_ENTRY
-CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+INSERT INTO t2 select * from t1;
 SELECT * from t2;
 TRUNCATE table t2;
 --error ER_DUP_ENTRY

=== modified file 'mysql-test/t/trigger.test'
--- a/mysql-test/t/trigger.test	2009-06-22 12:51:33 +0000
+++ b/mysql-test/t/trigger.test	2009-11-04 17:49:03 +0000
@@ -1751,7 +1751,7 @@ create trigger t1_ai after insert on t1 
 create table t2 (j int);
 insert into t2 values (1), (2);
 set @a:="";
-create table if not exists t1 select * from t2;
+insert into t1 select * from t2;
 select * from t1;
 select @a;
 # Let us check that trigger that involves table also works ok.
@@ -1759,7 +1759,7 @@ drop trigger t1_bi;
 drop trigger t1_ai;
 create table t3 (isave int);
 create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
-create table if not exists t1 select * from t2;
+insert into t1 select * from t2;
 select * from t1;
 select * from t3;
 drop table t1, t2, t3;
@@ -1975,7 +1975,7 @@ select * from t1_op_log;
 truncate t1;
 truncate t1_op_log;
 
-create table if not exists t1
+insert into t1
 select NULL, "CREATE TABLE ... SELECT, inserting a new key";
 
 set @id=last_insert_id();
@@ -1984,7 +1984,7 @@ select * from t1;
 select * from t1_op_log;
 truncate t1_op_log;
 
-create table if not exists t1 replace
+replace into t1
 select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
 
 select * from t1;
@@ -2114,7 +2114,7 @@ select * from t1_op_log;
 truncate t1;
 truncate t1_op_log;
 
-create table if not exists v1
+insert into v1
 select NULL, "CREATE TABLE ... SELECT, inserting a new key";
 
 set @id=last_insert_id();
@@ -2123,7 +2123,7 @@ select * from t1;
 select * from t1_op_log;
 truncate t1_op_log;
 
-create table if not exists v1 replace
+replace into v1
 select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
 
 select * from t1;

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2009-10-20 07:30:41 +0000
+++ b/sql/sql_insert.cc	2009-11-04 17:49:03 +0000
@@ -3634,15 +3634,14 @@ select_create::prepare(List<Item> &value
       push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
                           ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
                           create_table->table_name);
-      if (thd->current_stmt_binlog_row_based)
-        binlog_show_create_table(&(create_table->table), 1);
-      table= create_table->table;
+      binlog_show_create_table(&(create_table->table), 1);
+      ::my_ok(thd);
     }
     else
     {
       my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
-      DBUG_RETURN(-1);
     }
+    DBUG_RETURN(-1);
   }
   else
     if (!(table= create_table_from_items(thd, create_info, create_table,
@@ -3706,7 +3705,7 @@ void
 select_create::binlog_show_create_table(TABLE **tables, uint count)
 {
   /*
-    Note 1: In RBR mode, we generate a CREATE TABLE statement for the
+    Note 1: We generate a CREATE TABLE statement for the
     created table by calling store_create_info() (behaves as SHOW
     CREATE TABLE).  In the event of an error, nothing should be
     written to the binary log, even if the table is non-transactional;
@@ -3722,7 +3721,6 @@ select_create::binlog_show_create_table(
     schema that will do a close_thread_tables(), destroying the
     statement transaction cache.
   */
-  DBUG_ASSERT(thd->current_stmt_binlog_row_based);
   DBUG_ASSERT(tables && *tables && count > 0);
 
   char buf[2048];


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