summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/merge.result738
-rw-r--r--mysql-test/r/merge_mmap.result126
-rw-r--r--mysql-test/t/merge.test335
-rw-r--r--mysql-test/t/merge_mmap.test91
-rw-r--r--storage/myisammrg/ha_myisammrg.cc23
5 files changed, 1291 insertions, 22 deletions
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 43f456a6d95..f7f0cea3b19 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -1,3 +1,5 @@
+set global storage_engine=myisam;
+set session storage_engine=myisam;
drop table if exists t1,t2,t3,t4,t5,t6;
drop database if exists mysqltest;
create table t1 (a int not null primary key auto_increment, message char(20));
@@ -584,7 +586,9 @@ INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
CREATE TEMPORARY TABLE t3 (c1 INT NOT NULL) ENGINE=MRG_MYISAM UNION=(t1,t2);
SELECT * FROM t3;
-ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+c1
+1
+2
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL);
CREATE TEMPORARY TABLE t5 (c1 INT NOT NULL);
INSERT INTO t4 VALUES (4);
@@ -613,7 +617,9 @@ ERROR HY000: Unable to open underlying table which is differently defined or of
drop table t3;
create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
select * from t3;
-ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+a
+1
+2
drop table t3, t2, t1;
# CREATE...SELECT is not implemented for MERGE tables.
CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL);
@@ -1196,12 +1202,13 @@ ERROR HY000: Table 't4' was not locked with LOCK TABLES
# it can even be used.
CREATE TEMPORARY TABLE t4 LIKE t3;
SHOW CREATE TABLE t4;
-ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+Table Create Table
+t4 CREATE TEMPORARY TABLE `t4` (
+ `c1` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
INSERT INTO t4 VALUES (4);
-ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
UNLOCK TABLES;
INSERT INTO t4 VALUES (4);
-ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t4;
#
# Rename child.
@@ -1229,6 +1236,8 @@ c1
2
3
4
+4
+4
RENAME TABLE t2 TO t5;
SELECT * FROM t3 ORDER BY c1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
@@ -1239,6 +1248,8 @@ c1
2
3
4
+4
+4
#
# 3. Normal rename with locked tables.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
@@ -1248,6 +1259,8 @@ c1
2
3
4
+4
+4
RENAME TABLE t2 TO t5;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3 ORDER BY c1;
@@ -1256,6 +1269,8 @@ c1
2
3
4
+4
+4
RENAME TABLE t5 TO t2;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3 ORDER BY c1;
@@ -1264,6 +1279,8 @@ c1
2
3
4
+4
+4
UNLOCK TABLES;
#
# 4. Alter table rename.
@@ -1277,6 +1294,8 @@ c1
2
3
4
+4
+4
#
# 5. Alter table rename with locked tables.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
@@ -1293,6 +1312,8 @@ c1
2
3
4
+4
+4
#
# Rename parent.
#
@@ -1304,6 +1325,8 @@ c1
2
3
4
+4
+4
RENAME TABLE t3 TO t5;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3 ORDER BY c1;
@@ -1312,6 +1335,8 @@ c1
2
3
4
+4
+4
RENAME TABLE t5 TO t3;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3 ORDER BY c1;
@@ -1320,6 +1345,8 @@ c1
2
3
4
+4
+4
#
# 5. Alter table rename with locked tables.
ALTER TABLE t3 RENAME TO t5;
@@ -1335,6 +1362,8 @@ c1
2
3
4
+4
+4
DROP TABLE t1, t2, t3;
#
# Drop locked tables.
@@ -2650,6 +2679,705 @@ test.t1 optimize Error Unable to open underlying table which is differently defi
test.t1 optimize note The storage engine for the table doesn't support optimize
DROP TABLE t1;
#
+# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+# More tests with TEMPORARY MERGE table and permanent children.
+# First without locked tables.
+#
+DROP TABLE IF EXISTS t1, t2, t3, t4, m1, m2;
+#
+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;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE m1;
+Table Create Table
+m1 CREATE TEMPORARY TABLE `m1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m1;
+c1 c2
+INSERT INTO t1 VALUES (111, 121);
+INSERT INTO m1 VALUES (211, 221);
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+SELECT * FROM t1;
+c1 c2
+111 121
+SELECT * FROM t2;
+c1 c2
+211 221
+#
+ALTER TABLE m1 RENAME m2;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TEMPORARY TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+#
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+ALTER TABLE m2 RENAME m1;
+ERROR 42S01: Table 'm1' already exists
+DROP TABLE m1;
+ALTER TABLE m2 RENAME m1;
+SHOW CREATE TABLE m1;
+Table Create Table
+m1 CREATE TEMPORARY TABLE `m1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+#
+ALTER TABLE m1 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+SELECT * FROM m1;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ALTER TABLE t1 ADD COLUMN c3 INT;
+ALTER TABLE t2 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+SELECT * FROM m1;
+c1 c2 c3
+111 121 NULL
+211 221 NULL
+212 222 232
+#
+ALTER TABLE m1 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+SELECT * FROM m1;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t2 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+#
+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;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+311 321
+SELECT * FROM t1;
+c1 c2
+111 121
+SELECT * FROM t2;
+c1 c2
+211 221
+212 222
+213 223
+SELECT * FROM t3;
+c1 c2
+311 321
+#
+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;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+311 321
+411 421
+SELECT * FROM t1;
+c1 c2
+111 121
+SELECT * FROM t2;
+c1 c2
+211 221
+212 222
+213 223
+SELECT * FROM t3;
+c1 c2
+311 321
+SELECT * FROM t4;
+c1 c2
+411 421
+#
+ALTER TABLE m1 ENGINE=MyISAM;
+SHOW CREATE TABLE m1;
+Table Create Table
+m1 CREATE TEMPORARY TABLE `m1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO m1 VALUES (511, 521);
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+311 321
+411 421
+511 521
+#
+ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+SELECT * FROM t1;
+c1 c2
+111 121
+SELECT * FROM t2;
+c1 c2
+211 221
+212 222
+213 223
+#
+CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (611, 621);
+SELECT * FROM m1;
+c1 c2
+611 621
+211 221
+212 222
+213 223
+DROP TABLE t1;
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+#
+#
+SHOW CREATE TABLE m1;
+Table Create Table
+m1 CREATE TEMPORARY TABLE `m1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+#
+CREATE TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+DROP TABLE m2;
+#
+CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TEMPORARY TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+DROP TABLE m2;
+#
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST;
+SELECT * FROM m2;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+DROP TABLE m2;
+#
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST SELECT * FROM m1;
+ERROR HY000: 'test.m2' is not BASE TABLE
+#
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST SELECT * FROM m1;
+ERROR HY000: 'test.m2' is not BASE TABLE
+#
+CREATE TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+DROP TABLE m2;
+#
+CREATE TEMPORARY TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TEMPORARY TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+DROP TABLE m2;
+#
+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;
+c1 c2
+311 321
+411 421
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+#
+#
+LOCK TABLE m1 WRITE, m2 WRITE;
+SELECT * FROM m1,m2 WHERE m1.c1=m2.c1;
+c1 c2 c1 c2
+111 121 111 121
+111 121 111 121
+111 121 111 121
+111 121 111 121
+211 221 211 221
+211 221 211 221
+211 221 211 221
+211 221 211 221
+212 222 212 222
+212 222 212 222
+212 222 212 222
+212 222 212 222
+213 223 213 223
+213 223 213 223
+213 223 213 223
+213 223 213 223
+111 121 111 121
+111 121 111 121
+111 121 111 121
+111 121 111 121
+211 221 211 221
+211 221 211 221
+211 221 211 221
+211 221 211 221
+212 222 212 222
+212 222 212 222
+212 222 212 222
+212 222 212 222
+213 223 213 223
+213 223 213 223
+213 223 213 223
+213 223 213 223
+111 121 111 121
+111 121 111 121
+111 121 111 121
+111 121 111 121
+211 221 211 221
+211 221 211 221
+211 221 211 221
+211 221 211 221
+212 222 212 222
+212 222 212 222
+212 222 212 222
+212 222 212 222
+213 223 213 223
+213 223 213 223
+213 223 213 223
+213 223 213 223
+111 121 111 121
+111 121 111 121
+111 121 111 121
+111 121 111 121
+211 221 211 221
+211 221 211 221
+211 221 211 221
+211 221 211 221
+212 222 212 222
+212 222 212 222
+212 222 212 222
+212 222 212 222
+213 223 213 223
+213 223 213 223
+213 223 213 223
+213 223 213 223
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4, m1, m2;
+#
+# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+# More tests with TEMPORARY MERGE table and permanent children.
+# (continued) Now the same with locked table.
+#
+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;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE m1;
+Table Create Table
+m1 CREATE TEMPORARY TABLE `m1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m1;
+c1 c2
+INSERT INTO t1 VALUES (111, 121);
+INSERT INTO m1 VALUES (211, 221);
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+SELECT * FROM t1;
+c1 c2
+111 121
+SELECT * FROM t2;
+c1 c2
+211 221
+#
+LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE;
+#
+ALTER TABLE m1 RENAME m2;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TEMPORARY TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+#
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+ALTER TABLE m2 RENAME m1;
+ERROR 42S01: Table 'm1' already exists
+DROP TABLE m1;
+ALTER TABLE m2 RENAME m1;
+SHOW CREATE TABLE m1;
+Table Create Table
+m1 CREATE TEMPORARY TABLE `m1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+#
+ALTER TABLE m1 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+SELECT * FROM m1;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ALTER TABLE t1 ADD COLUMN c3 INT;
+ALTER TABLE t2 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+SELECT * FROM m1;
+c1 c2 c3
+111 121 NULL
+211 221 NULL
+212 222 232
+#
+ALTER TABLE m1 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+SELECT * FROM m1;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t2 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+#
+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;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+311 321
+SELECT * FROM t1;
+c1 c2
+111 121
+SELECT * FROM t2;
+c1 c2
+211 221
+212 222
+213 223
+SELECT * FROM t3;
+c1 c2
+311 321
+#
+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;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+311 321
+411 421
+SELECT * FROM t1;
+c1 c2
+111 121
+SELECT * FROM t2;
+c1 c2
+211 221
+212 222
+213 223
+SELECT * FROM t3;
+c1 c2
+311 321
+SELECT * FROM t4;
+c1 c2
+411 421
+#
+ALTER TABLE m1 ENGINE=MyISAM;
+SHOW CREATE TABLE m1;
+Table Create Table
+m1 CREATE TEMPORARY TABLE `m1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO m1 VALUES (511, 521);
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+311 321
+411 421
+511 521
+#
+ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+SELECT * FROM t1;
+c1 c2
+111 121
+SELECT * FROM t2;
+c1 c2
+211 221
+212 222
+213 223
+#
+CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (611, 621);
+SELECT * FROM m1;
+c1 c2
+611 621
+211 221
+212 222
+213 223
+DROP TABLE t1;
+SELECT * FROM m1;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+#
+#
+SHOW CREATE TABLE m1;
+Table Create Table
+m1 CREATE TEMPORARY TABLE `m1` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+CREATE TABLE m2 SELECT * FROM m1;
+ERROR HY000: Table 'm2' was not locked with LOCK TABLES
+#
+CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TEMPORARY TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+DROP TABLE m2;
+#
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST;
+SELECT * FROM m2;
+c1 c2
+311 321
+411 421
+LOCK TABLE m1 WRITE, m2 WRITE;
+UNLOCK TABLES;
+DROP TABLE m2;
+LOCK TABLE m1 WRITE;
+#
+# ER_TABLE_NOT_LOCKED is returned in ps-protocol
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST SELECT * FROM m1;
+Got one of the listed errors
+#
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST SELECT * FROM m1;
+ERROR HY000: 'test.m2' is not BASE TABLE
+#
+CREATE TEMPORARY TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TEMPORARY TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+LOCK TABLE m1 WRITE, m2 WRITE;
+SHOW CREATE TABLE m2;
+Table Create Table
+m2 CREATE TEMPORARY TABLE `m2` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1 c2
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+DROP TABLE m2;
+#
+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;
+c1 c2
+311 321
+411 421
+111 121
+211 221
+212 222
+213 223
+111 121
+211 221
+212 222
+213 223
+#
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4, m1, m2;
+#
# Bug47098 assert in MDL_context::destroy on HANDLER
# <damaged merge table> OPEN
#
diff --git a/mysql-test/r/merge_mmap.result b/mysql-test/r/merge_mmap.result
index d975a1be377..e8014259a4a 100644
--- a/mysql-test/r/merge_mmap.result
+++ b/mysql-test/r/merge_mmap.result
@@ -32,6 +32,66 @@ c1
DROP TABLE m2, m1, t2, t1;
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
+CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1
+1
+2
+3
+4
+1
+2
+3
+4
+DROP TABLE m2, m1, t2, t1;
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1
+1
+2
+3
+4
+1
+2
+3
+4
+DROP TABLE m2, m1, t2, t1;
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1
+1
+2
+3
+4
+1
+2
+3
+4
+DROP TABLE m2, m1, t2, t1;
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
@@ -61,4 +121,70 @@ c1
4
UNLOCK TABLES;
DROP TABLE m2, m1, t2, t1;
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1
+1
+2
+3
+4
+1
+2
+3
+4
+UNLOCK TABLES;
+DROP TABLE m2, m1, t2, t1;
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1
+1
+2
+3
+4
+1
+2
+3
+4
+UNLOCK TABLES;
+DROP TABLE m2, m1, t2, t1;
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1
+1
+2
+3
+4
+1
+2
+3
+4
+UNLOCK TABLES;
+DROP TABLE m2, m1, t2, t1;
End of 6.0 tests
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index 252495cc1be..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 #
@@ -2347,3 +2667,8 @@ 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
diff --git a/mysql-test/t/merge_mmap.test b/mysql-test/t/merge_mmap.test
index 3468702ca45..c97b029754d 100644
--- a/mysql-test/t/merge_mmap.test
+++ b/mysql-test/t/merge_mmap.test
@@ -30,6 +30,48 @@ INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
SELECT * FROM t2;
DROP TABLE m2, m1, t2, t1;
+#
+# INSERT-SELECT with TEMPORARY select table.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2, m1, t2, t1;
+#
+# INSERT-SELECT with TEMPORARY insert table.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2, m1, t2, t1;
+#
+# INSERT-SELECT with TEMPORARY both tables.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2, m1, t2, t1;
####################
## With LOCK TABLES.
@@ -51,6 +93,55 @@ SELECT * FROM m2;
SELECT * FROM t2;
UNLOCK TABLES;
DROP TABLE m2, m1, t2, t1;
+#
+# INSERT-SELECT with TEMPORARY select table.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+UNLOCK TABLES;
+DROP TABLE m2, m1, t2, t1;
+#
+# INSERT-SELECT with TEMPORARY insert table.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+UNLOCK TABLES;
+DROP TABLE m2, m1, t2, t1;
+#
+# INSERT-SELECT with TEMPORARY both tables.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+ INSERT_METHOD=LAST;
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (3), (4);
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+UNLOCK TABLES;
+DROP TABLE m2, m1, t2, t1;
+
--echo End of 6.0 tests
--disable_result_log
diff --git a/storage/myisammrg/ha_myisammrg.cc b/storage/myisammrg/ha_myisammrg.cc
index 1681f062117..08aa9dfd0e4 100644
--- a/storage/myisammrg/ha_myisammrg.cc
+++ b/storage/myisammrg/ha_myisammrg.cc
@@ -476,10 +476,7 @@ int ha_myisammrg::add_children_list(void)
child_l->parent_l= parent_l;
/* Copy select_lex. Used in unique_table() at least. */
child_l->select_lex= parent_l->select_lex;
- /*
- Set the expected table version, to not cause spurious re-prepare.
- @todo: revise after the fix for Bug#36171
- */
+ /* Set the expected table version, to not cause spurious re-prepare. */
child_l->set_table_ref_id(mrg_child_def->get_child_table_ref_type(),
mrg_child_def->get_child_def_version());
/* Link TABLE_LIST object into the children list. */
@@ -618,15 +615,17 @@ extern "C" MI_INFO *myisammrg_attach_children_callback(void *callback_param)
param->need_compat_check= TRUE;
/*
- If parent is temporary, children must be temporary too and vice
- versa. This check must be done for every child on every open because
- the table def version can overlap between temporary and
- non-temporary tables. We need to detect the case where a
- non-temporary table has been replaced with a temporary table of the
- same version. Or vice versa. A very unlikely case, but it could
- happen.
+ If child is temporary, parent must be temporary as well. Other
+ parent/child combinations are allowed. This check must be done for
+ every child on every open because the table def version can overlap
+ between temporary and non-temporary tables. We need to detect the
+ case where a non-temporary table has been replaced with a temporary
+ table of the same version. Or vice versa. A very unlikely case, but
+ it could happen. (Note that the condition was different from
+ 5.1.23/6.0.4(Bug#19627) to 5.5.6 (Bug#36171): child->s->tmp_table !=
+ parent->s->tmp_table. Tables were required to have the same status.)
*/
- if (child->s->tmp_table != parent->s->tmp_table)
+ if (child->s->tmp_table && !parent->s->tmp_table)
{
DBUG_PRINT("error", ("temporary table mismatch parent: %d child: %d",
parent->s->tmp_table, child->s->tmp_table));
View Lorry Controller Status