Auto-merge from mysql-trunk-bugfixing.

1 files changed, 449 insertions, 5 deletions

diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index a06da03cbcd..31bc8a5e881 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -2,6 +2,12 @@
 # Test of MERGE TABLES
 #
 
+# MERGE tables require MyISAM tables
+let $default=`select @@global.storage_engine`;
+set global storage_engine=myisam;
+set session storage_engine=myisam;
+
+# Clean up resources used in this test case.
 --disable_warnings
 drop table if exists t1,t2,t3,t4,t5,t6;
 drop database if exists mysqltest;
@@ -222,7 +228,6 @@ CREATE TABLE t2 (c1 INT NOT NULL);
 INSERT INTO t1 VALUES (1);
 INSERT INTO t2 VALUES (2);
 CREATE TEMPORARY TABLE t3 (c1 INT NOT NULL) ENGINE=MRG_MYISAM UNION=(t1,t2);
---error ER_WRONG_MRG_TABLE
 SELECT * FROM t3;
 CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL);
 CREATE TEMPORARY TABLE t5 (c1 INT NOT NULL);
@@ -254,7 +259,6 @@ create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
 select * from t3;
 drop table t3;
 create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
---error ER_WRONG_MRG_TABLE
 select * from t3;
 drop table t3, t2, t1;
 --echo # CREATE...SELECT is not implemented for MERGE tables.
@@ -891,12 +895,9 @@ INSERT INTO t4 VALUES (4);
 --echo # If the temporary MERGE table uses the locked children only,
 --echo # it can even be used.
 CREATE TEMPORARY TABLE t4 LIKE t3;
---error ER_WRONG_MRG_TABLE
 SHOW CREATE TABLE t4;
---error ER_WRONG_MRG_TABLE
 INSERT INTO t4 VALUES (4);
 UNLOCK TABLES;
---error ER_WRONG_MRG_TABLE
 INSERT INTO t4 VALUES (4);
 DROP TABLE t4;
 #
@@ -2117,6 +2118,325 @@ OPTIMIZE TABLE t1;
 DROP TABLE t1;
 
 --echo #
+--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+--echo # More tests with TEMPORARY MERGE table and permanent children.
+--echo # First without locked tables.
+--echo #
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, t3, t4, m1, m2;
+--enable_warnings
+#
+--echo #
+CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE m1;
+SELECT * FROM m1;
+INSERT INTO t1 VALUES (111, 121);
+INSERT INTO m1 VALUES (211, 221);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+#
+--echo #
+ALTER TABLE m1 RENAME m2;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+--error ER_TABLE_EXISTS_ERROR
+ALTER TABLE m2 RENAME m1;
+DROP TABLE m1;
+ALTER TABLE m2 RENAME m1;
+SHOW CREATE TABLE m1;
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 ADD COLUMN c3 INT;
+--error ER_WRONG_MRG_TABLE
+INSERT INTO m1 VALUES (212, 222, 232);
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m1;
+ALTER TABLE t1 ADD COLUMN c3 INT;
+ALTER TABLE t2 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 DROP COLUMN c3;
+--error ER_WRONG_MRG_TABLE
+INSERT INTO m1 VALUES (213, 223);
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m1;
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t2 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+SELECT * FROM m1;
+#
+--echo #
+CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3);
+INSERT INTO m1 VALUES (311, 321);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+#
+--echo #
+CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3,t4);
+INSERT INTO m1 VALUES (411, 421);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+SELECT * FROM t4;
+#
+--echo #
+ALTER TABLE m1 ENGINE=MyISAM;
+SHOW CREATE TABLE m1;
+INSERT INTO m1 VALUES (511, 521);
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+#
+--echo #
+CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (611, 621);
+SELECT * FROM m1;
+DROP TABLE t1;
+SELECT * FROM m1;
+#
+#
+--echo #
+--echo #
+SHOW CREATE TABLE m1;
+#
+--echo #
+CREATE TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST;
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+--error ER_WRONG_OBJECT
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST SELECT * FROM m1;
+#
+--echo #
+--error ER_WRONG_OBJECT
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST SELECT * FROM m1;
+#
+--echo #
+CREATE TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+#
+#
+--echo #
+--echo #
+LOCK TABLE m1 WRITE, m2 WRITE;
+SELECT * FROM m1,m2 WHERE m1.c1=m2.c1;
+UNLOCK TABLES;
+#
+DROP TABLE t1, t2, t3, t4, m1, m2;
+#
+#
+#
+--echo #
+--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+--echo # More tests with TEMPORARY MERGE table and permanent children.
+--echo # (continued) Now the same with locked table.
+--echo #
+CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE m1;
+SELECT * FROM m1;
+INSERT INTO t1 VALUES (111, 121);
+INSERT INTO m1 VALUES (211, 221);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+#
+--echo #
+LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE;
+#
+--echo #
+ALTER TABLE m1 RENAME m2;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+--error ER_TABLE_EXISTS_ERROR
+ALTER TABLE m2 RENAME m1;
+DROP TABLE m1;
+ALTER TABLE m2 RENAME m1;
+SHOW CREATE TABLE m1;
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 ADD COLUMN c3 INT;
+--error ER_WRONG_MRG_TABLE
+INSERT INTO m1 VALUES (212, 222, 232);
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m1;
+ALTER TABLE t1 ADD COLUMN c3 INT;
+ALTER TABLE t2 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 DROP COLUMN c3;
+--error ER_WRONG_MRG_TABLE
+INSERT INTO m1 VALUES (213, 223);
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m1;
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t2 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+SELECT * FROM m1;
+#
+--echo #
+UNLOCK TABLES;
+CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3);
+LOCK TABLE m1 WRITE;
+INSERT INTO m1 VALUES (311, 321);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+#
+--echo #
+CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3,t4);
+INSERT INTO m1 VALUES (411, 421);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+SELECT * FROM t4;
+#
+--echo #
+ALTER TABLE m1 ENGINE=MyISAM;
+SHOW CREATE TABLE m1;
+INSERT INTO m1 VALUES (511, 521);
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+#
+--echo #
+CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (611, 621);
+SELECT * FROM m1;
+DROP TABLE t1;
+SELECT * FROM m1;
+#
+#
+--echo #
+--echo #
+SHOW CREATE TABLE m1;
+--error ER_TABLE_NOT_LOCKED
+CREATE TABLE m2 SELECT * FROM m1;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST;
+SELECT * FROM m2;
+LOCK TABLE m1 WRITE, m2 WRITE;
+UNLOCK TABLES;
+DROP TABLE m2;
+LOCK TABLE m1 WRITE;
+#
+--echo #
+--echo # ER_TABLE_NOT_LOCKED is returned in ps-protocol
+--error ER_WRONG_OBJECT, ER_TABLE_NOT_LOCKED
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST SELECT * FROM m1;
+#
+--echo #
+--error ER_WRONG_OBJECT
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST SELECT * FROM m1;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+LOCK TABLE m1 WRITE, m2 WRITE;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST;
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+#
+--echo #
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4, m1, m2;
+
+--echo #
 --echo # Bug47098 assert in MDL_context::destroy on HANDLER
 --echo #          <damaged merge table> OPEN
 --echo #
@@ -2225,6 +2545,130 @@ show create table m1;
 show create table m2;
 drop tables m1, m2, t1;
 
+--echo #
+--echo # Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()"
+--echo # Check that unique_table() works correctly for merge tables.
+--echo #
+--disable_warnings
+drop table if exists t1, t2, t3, m1, m2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (b int);
+create view v1 as select * from t3,t1;
+create table m1 (a int) engine=merge union (t1, t2) insert_method=last;
+create table m2 (a int) engine=merge union (t1, t2) insert_method=first;
+create temporary table tmp (b int);
+insert into tmp (b) values (1);
+
+insert into t1 (a) values (1);
+insert into t3 (b) values (1);
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from m1));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from m2));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from t1));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from t2));
+
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from t3, m1));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from t3, m2));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from t3, t1));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from t3, t2));
+
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from tmp, m1));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from tmp, m2));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from tmp, t1));
+--error ER_UPDATE_TABLE_USED
+insert into m1 (a) values ((select max(a) from tmp, t2));
+ 
+--error ER_VIEW_PREVENT_UPDATE
+insert into m1 (a) values ((select max(a) from v1));
+--error ER_VIEW_PREVENT_UPDATE
+insert into m1 (a) values ((select max(a) from tmp, v1));
+
+
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from m1));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from m2));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from t1));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from t2));
+
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from t3, m1));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from t3, m2));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from t3, t1));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from t3, t2));
+
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from tmp, m1));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from tmp, m2));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from tmp, t1));
+--error ER_UPDATE_TABLE_USED
+update m1 set a = ((select max(a) from tmp, t2));
+ 
+--error ER_VIEW_PREVENT_UPDATE
+update m1 set a = ((select max(a) from v1));
+--error ER_VIEW_PREVENT_UPDATE
+update m1 set a = ((select max(a) from tmp, v1));
+
+
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from m1);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from m2);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from t1);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from t2);
+
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from t3, m1);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from t3, m2);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from t3, t1);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from t3, t2);
+
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from tmp, m1);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from tmp, m2);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from tmp, t1);
+--error ER_UPDATE_TABLE_USED
+delete from m1 where a = (select max(a) from tmp, t2);
+ 
+--error ER_VIEW_PREVENT_UPDATE
+delete from m1 where a = (select max(a) from v1);
+--error ER_VIEW_PREVENT_UPDATE
+delete from m1 where a = (select max(a) from tmp, v1);
+
+drop view v1;
+drop temporary table tmp;
+drop table t1, t2, t3, m1, m2;
 
 --echo End of 6.0 tests
 
+--disable_result_log
+--disable_query_log
+eval set global storage_engine=$default;
+--enable_result_log
+--enable_query_log