MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Alexey Botchkov Date:November 16 2009 4:09pm
Subject:bzr commit into mysql-5.6-next-mr-bugfixing branch (holyfoot:2952)
Bug#47132
View as plain text  
#At file:///home/hf/work/mysql_common/47132/ based on revid:holyfoot@stripped

 2952 Alexey Botchkov	2009-11-16
      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 case added, and the rest of the 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-16 16:08:41 +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-10-15 12:53:06 +0000
+++ b/mysql-test/r/create.result	2009-11-16 16:08:41 +0000
@@ -1,5 +1,6 @@
 drop table if exists t1,t2,t3,t4,t5;
 drop database if exists mysqltest;
+drop view if exists v1;
 create table t1 (b char(0));
 insert into t1 values (""),(null);
 select * from t1;
@@ -256,15 +257,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 +272,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 +785,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 +798,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 +809,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 +1549,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 +1581,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
@@ -1943,11 +1925,7 @@ CREATE TRIGGER f BEFORE INSERT ON t1 FOR
 BEGIN 
 INSERT INTO t1 ( `int` ) VALUES (4 ),( 8 ),( 2 ) ; 
 END ; |
-CREATE TABLE IF NOT EXISTS t1 ( 
-`pk` INTEGER NOT NULL AUTO_INCREMENT , 
-`int` INTEGER ,
-PRIMARY KEY ( `pk` ) 
-) SELECT `pk` , `int_key` FROM B ;
+INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ;
 ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
 CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW 
 BEGIN 
@@ -1955,4 +1933,25 @@ UPDATE A SET `pk`=1 WHERE `pk`=0 ; 
 END ;|
 ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
 DROP TABLE t1;
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int);
+INSERT INTO t1 VALUES (1), (1);
+INSERT INTO t2 VALUES (2), (2);
+CREATE VIEW v1 AS SELECT id FROM t2;
+CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1;
+Warnings:
+Note	1050	Table 'v1' already exists
+SHOW CREATE TABLE v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`id` AS `id` from `t2`	latin1	latin1_swedish_ci
+SELECT * FROM t2;
+id
+2
+2
+SELECT * FROM v1;
+id
+2
+2
+DROP VIEW v1;
+DROP TABLE t1, t2;
 DROP TABLE B;

=== modified file 'mysql-test/r/trigger.result'
--- a/mysql-test/r/trigger.result	2009-10-12 10:59:55 +0000
+++ b/mysql-test/r/trigger.result	2009-11-16 16:08:41 +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-16 16:08:41 +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-10-15 12:53:06 +0000
+++ b/mysql-test/t/create.test	2009-11-16 16:08:41 +0000
@@ -5,6 +5,7 @@
 --disable_warnings
 drop table if exists t1,t2,t3,t4,t5;
 drop database if exists mysqltest;
+drop view if exists v1;
 --enable_warnings
 
 create table t1 (b char(0));
@@ -216,7 +217,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 +232,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 +692,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 +707,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 +1182,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 +1224,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
@@ -1618,11 +1608,7 @@ END ; |
 
 --delimiter ;
 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
-CREATE TABLE IF NOT EXISTS t1 ( 
-  `pk` INTEGER NOT NULL AUTO_INCREMENT , 
-  `int` INTEGER ,
-  PRIMARY KEY ( `pk` ) 
-) SELECT `pk` , `int_key` FROM B ;
+INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ;
 
 --delimiter |
 --error ER_NOT_SUPPORTED_YET
@@ -1634,4 +1620,22 @@ END ;|
 --delimiter ;
 
 DROP TABLE t1;
+
+#
+#Bug #47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
+#
+
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int);
+INSERT INTO t1 VALUES (1), (1);
+INSERT INTO t2 VALUES (2), (2);
+
+CREATE VIEW v1 AS SELECT id FROM t2;
+CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1;
+SHOW CREATE TABLE v1;
+SELECT * FROM t2;
+SELECT * FROM v1;
+DROP VIEW v1;
+
+DROP TABLE t1, t2;
 DROP TABLE B;

=== modified file 'mysql-test/t/trigger.test'
--- a/mysql-test/t/trigger.test	2009-10-12 10:59:55 +0000
+++ b/mysql-test/t/trigger.test	2009-11-16 16:08:41 +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-11-05 20:28:35 +0000
+++ b/sql/sql_insert.cc	2009-11-16 16:08:41 +0000
@@ -3641,15 +3641,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,
@@ -3713,7 +3712,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;
@@ -3729,7 +3728,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/holyfoot@mysql.com-20091116160841-81vpo788r743ytl0.bundle
Thread
bzr commit into mysql-5.6-next-mr-bugfixing branch (holyfoot:2952)Bug#47132Alexey Botchkov17 Nov