#At file:///home/hf/work/mysql_common/47132/ based on revid:holyfoot@stripped
2952 Alexey Botchkov 2010-02-04
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
The decision was made to simplify the CREATE TABLE IF NOT EXISTS. It is not going to do
anything if the table already exists. (Before it tried to insert the SELECT result into
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/extra/binlog_tests/mix_innodb_myisam_binlog.test
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
mysql-test/r/auto_increment.result
result updated
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
mysql-test/r/create.result
result updated
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
mysql-test/r/trigger.result
result updated
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
mysql-test/t/auto_increment.test
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
mysql-test/t/create.test
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
mysql-test/t/trigger.test
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
sql/sql_insert.cc
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
Do no insert if the table already exists.
Binlog the statement as it is for the statement format.
modified:
mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
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/extra/binlog_tests/mix_innodb_myisam_binlog.test'
--- a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test 2009-09-29 11:16:23 +0000
+++ b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test 2010-02-04 11:22:35 +0000
@@ -257,7 +257,6 @@ DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
INSERT INTO t1 VALUES (4,4);
---error ER_DUP_ENTRY
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
SELECT * from t2;
TRUNCATE table t2;
=== 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 2010-02-04 11:22:35 +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 2010-02-04 11:22:35 +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 2010-02-04 11:22:35 +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 2010-02-04 11:22:35 +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 2010-02-04 11:22:35 +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 2010-02-04 11:22:35 +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 2010-02-04 11:22:35 +0000
@@ -3641,15 +3641,24 @@ 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;
+ if (mysql_bin_log.is_open())
+ {
+ if (thd->current_stmt_binlog_row_based)
+ binlog_show_create_table(&(create_table->table), 1);
+ else
+ thd->binlog_query(THD::STMT_QUERY_TYPE,
+ thd->query(), thd->query_length(),
+ /* is_trans */ TRUE,
+ /* suppress_use */ FALSE,
+ 0);
+ }
+ ::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 +3722,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 +3738,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-20100204112235-mx9jw95fhf7poc89.bundle
