9 files changed, 206 insertions, 9 deletions
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index fe8cfe70c4e..e4a7d1cd9af 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -226,6 +226,7 @@ drop table t1;
 # Test create table if not exists with duplicate key error
 #
 
+flush status;
 create table t1 (a int not null, b int, primary key (a));
 insert into t1 values (1,1);
 create table if not exists t1 select 2;
@@ -233,6 +234,8 @@ select * from t1;
 create table if not exists t1 select 3 as 'a',4 as 'b';
 --error 1062
 create table if not exists t1 select 3 as 'a',3 as 'b';
+show warnings;
+show status like "Opened_tables";
 select * from t1;
 drop table t1;
 
@@ -676,3 +679,37 @@ insert into t1 values('aaa');
 drop table t1;
 
 # End of 5.0 tests
+
+#
+# Test of behaviour with CREATE ... SELECT
+#
+
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+--error 1062
+CREATE TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+--error 1062
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+CREATE TABLE t2 (a int, b int, primary key (a));
+--error 1062
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+TRUNCATE table t2;
+--error 1062
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t2;
+
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
+--error 1062
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+TRUNCATE table t2;
+--error 1062
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t1,t2;
diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test
index 3c43fb1d1f9..f6799f3630a 100644
--- a/mysql-test/t/federated.test
+++ b/mysql-test/t/federated.test
@@ -1,6 +1,6 @@
 # should work with embedded server after mysqltest is fixed
--- source include/not_embedded.inc
-source include/federated.inc;
+--source include/not_embedded.inc
+--source include/federated.inc
 
 connection slave;
 DROP TABLE IF EXISTS federated.t1;
@@ -1310,6 +1310,57 @@ select * from federated.t1 where fld_parentid=0 and fld_delt=0;
 DROP TABLE federated.t1;
 connection slave;
 DROP TABLE federated.bug_17377_table;
+DROP TABLE federated.t1;
+
+#
+# Test multi updates and deletes without keys
+#
+
+# The following can be enabled when bug #19773 has been fixed
+--disable_parsing
+connection slave;
+create table federated.t1 (i1 int, i2 int, i3 int);
+create table federated.t2 (id int, c1 varchar(20), c2 varchar(20));
+connection master;
+eval create table federated.t1 (i1 int, i2 int, i3 int) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+eval create table federated.t2 (id int, c1 varchar(20), c2 varchar(20)) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2';
+insert into federated.t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
+insert into federated.t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
+select * from federated.t1 order by i1;
+select * from federated.t2;
+update federated.t1,federated.t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
+select * from federated.t1 order by i1;
+select * from federated.t2 order by id;
+delete   t1.*,t2.* from federated.t1,federated.t2 where t1.i2=t2.id;
+select * from federated.t1 order by i1;
+select * from federated.t2 order by id;
+drop table federated.t1, federated.t2;
+connection slave;
+drop table federated.t1, federated.t2;
+connection master;
 
+# Test multi updates and deletes with keys
+
+connection slave;
+create table federated.t1 (i1 int, i2 int, i3 int, primary key (i1));
+create table federated.t2 (id int, c1 varchar(20), c2 varchar(20), primary key (id));
+connection master;
+eval create table federated.t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1)) ENGINE=FEDERATED ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+eval create table federated.t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id)) ENGINE=FEDERATED ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2';
+insert into federated.t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
+insert into federated.t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
+select * from federated.t1 order by i1;
+select * from federated.t2 order by id;
+update federated.t1,federated.t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
+select * from federated.t1 order by i1;
+select * from federated.t2 order by id;
+delete t1.*,t2.* from federated.t1,federated.t2 where t1.i2=t2.id;
+select * from federated.t1 order by i1;
+select * from federated.t2 order by id;
+drop table federated.t1, federated.t2;
+connection slave;
+drop table federated.t1, federated.t2;
+connection master;
+--enable_parsing
 
-source include/federated_cleanup.inc;
+--source include/federated_cleanup.inc
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index fbfdfa3b5d0..7fd7edddf28 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -176,8 +176,8 @@ select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5)
 select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1 group by 1;
 
 # The following returns random results as we are sorting on blob addresses
-# select group_concat(c order by (select group_concat(c order by a) from t2 where t2.a=t1.a)) as grp from t1;
-# select group_concat(c order by (select group_concat(c) from t2 where a=t1.a)) as grp from t1;
+select group_concat(c order by (select concat(5-t1.c,group_concat(c order by a)) from t2 where t2.a=t1.a)) as grp from t1;
+select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1;
 
 select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp;
 drop table t1,t2;
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 4e4fb8f777a..229b24c73ca 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -379,6 +379,7 @@ select time_format('100:00:00', '%H %k %h %I %l');
 create table t1 (a timestamp default '2005-05-05 01:01:01',
                  b timestamp default '2005-05-05 01:01:01');
 delimiter //;
+drop function if exists t_slow_sysdate;
 create function t_slow_sysdate() returns timestamp
 begin
   do sleep(2);
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index b942b9fbc0d..c0fb3ab917b 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -1,5 +1,54 @@
 -- source include/have_innodb.inc
 
 --disable_warnings
-drop table if exists t1;
+drop table if exists t1.t2;
 --enable_warnings
+
+#
+# Test of behaviour with CREATE ... SELECT
+#
+
+set storage_engine=innodb;
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+--error 1062
+CREATE TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+--error 1062
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+CREATE TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+--error 1062
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error 1062
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t2;
+
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+--error 1062
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+COMMIT;
+BEGIN;
+INSERT INTO t2 values(101,101);
+--error 1062
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error 1062
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t1,t2;
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index f3dd8e7e199..e2514083ea7 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -9,8 +9,8 @@ drop table if exists t1,t2,t3;
 create table t1 (a int not null);
 insert into t1 values (1);
 insert into t1 values (a+2);
-insert into t1 values (a+3);
-insert into t1 values (4),(a+5);
+insert into t1 values (a+3),(a+4);
+insert into t1 values (5),(a+6);
 select * from t1;
 drop table t1;
 
@@ -176,3 +176,28 @@ insert into  t2 select t1.* from t1, t2 t, t3 where  t1.id1 = t.id2 and t.id2 =
 select count(*) from t2;
 drop table t1,t2,t3;
 
+#
+# Test different cases of duplicate fields
+#
+
+create table t1 (a int, b int);
+insert into t1 (a,b) values (a,b);
+insert into t1 SET a=1, b=a+1;
+insert into t1 (a,b) select 1,2;
+INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);
+prepare stmt1 from ' replace into t1 (a,a) select 100, ''hundred'' ';
+--error 1110
+execute stmt1;
+--error 1110
+insert into t1 (a,b,b) values (1,1,1);
+--error 1136
+insert into t1 (a,a) values (1,1,1);
+--error 1110
+insert into t1 (a,a) values (1,1);
+--error 1110
+insert into t1 SET a=1,b=2,a=1;
+--error 1110
+insert into t1 (b,b) select 1,2;
+--error 1110
+INSERT INTO t1 (b,b) SELECT 0,0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);
+drop table t1;
diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test
index fcd127e3e98..aff5efa51cb 100644
--- a/mysql-test/t/loaddata.test
+++ b/mysql-test/t/loaddata.test
@@ -92,6 +92,10 @@ load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, @b);
 select * from t1;
 select @a, @b;
 truncate table t1;
+# Reading of all columns with set
+load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 set c=b;
+select * from t1;
+truncate table t1;
 # now going to test fixed field-row file format
 load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow";
 select * from t1;
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index acc816ae921..04c33e9d709 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -532,3 +532,33 @@ select * from t1;
 select * from t2;
 drop view v1;
 drop table t1, t2;
+
+#
+# Test multi updates and deletes using primary key and without.
+#
+create table t1 (i1 int, i2 int, i3 int);
+create table t2 (id int, c1 varchar(20), c2 varchar(20));
+insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
+insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
+select * from t1 order by i1;
+select * from t2;
+update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
+select * from t1 order by i1;
+select * from t2 order by id;
+delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
+select * from t1 order by i1;
+select * from t2 order by id;
+drop table t1, t2;
+create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1));
+create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id));
+insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
+insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
+select * from t1 order by i1;
+select * from t2 order by id;
+update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
+select * from t1 order by i1;
+select * from t2 order by id;
+delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
+select * from t1 order by i1;
+select * from t2 order by id;
+drop table t1, t2;
diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test
index 8deff474587..e057ba537f6 100644
--- a/mysql-test/t/view_grant.test
+++ b/mysql-test/t/view_grant.test
@@ -3,7 +3,7 @@
 
 --disable_warnings
 drop database if exists mysqltest;
-drop view if exists v1;
+drop view if exists v1,v2,v3;
 --enable_warnings