List:Commits« Previous MessageNext Message »
From:Konstantin Osipov Date:July 2 2010 4:13pm
Subject:bzr push into mysql-trunk-runtime branch (kostja:3074 to 3075)
Bug#36171
View as plain text  
 3075 Konstantin Osipov	2010-07-02
      A fix and a test case for Bug#36171 "CREATE TEMPORARY TABLE and 
      MERGE engine".
      Backport the patch from 6.0 by Ingo Struewing:
      revid:ingo.struewing@stripped
      Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
      
      In former MySQL versions, up to 5.1.23/6.0.4 it was possible to create
      temporary MERGE tables with non-temporary MyISAM tables.
              
      This has been changed in the mentioned version due to Bug 19627
      (temporary merge table locking). MERGE children were locked through
      the parent table. If the parent was temporary, it was not locked and
      so the children were not locked either. Parallel use of the MyISAM
      tables corrupted them.
             
      Since 6.0.6 (WL 4144 - Lock MERGE engine children), the children are
      locked independently from the parent. Now it is possible to allow
      non-temporary children with a temporary parent. Even though the
      temporary MERGE table itself is not locked, each non-temporary
      MyISAM table is locked anyway.
              
      NOTE: Behavior change: In 5.1.23/6.0.4 we prohibited non-temporary
      children with a temporary MERGE table. Now we re-allow it.
      An important side-effect is that temporary tables, which overlay
      non-temporary MERGE children, overlay the children in the MERGE table.
     @ mysql-test/r/merge.result
        Update results (Bug#36171).
     @ mysql-test/r/merge_mmap.result
        Update results (Bug#36171).
     @ mysql-test/t/merge.test
        Add tests for Bug#36171
     @ mysql-test/t/merge_mmap.test
        Add tests for Bug#36171.
     @ storage/myisammrg/ha_myisammrg.cc
        Changed constraint for temporary state of tables.

    modified:
      mysql-test/r/merge.result
      mysql-test/r/merge_mmap.result
      mysql-test/t/merge.test
      mysql-test/t/merge_mmap.test
      storage/myisammrg/ha_myisammrg.cc
 3074 Konstantin Osipov	2010-07-02
      A test case for Bug#50788 "main.merge fails on HPUX",
      and a backport of relevant changes from the 6.0
      version of the fix done by Ingo Struewing.
      The bug itself was fixed by the patch for Bug#54811.
      
      
      MyISAMMRG engine would try to use MMAP on its children
      even on platforms that don't support it and even if
      myisam_use_mmap option was off.
      This lead to an infinite hang in INSERT ... SELECT into 
      a MyISAMMRG table when the destination MyISAM table
      was also selected from.
      
      A bug in duplicate detection fixed by 54811 was essential to 
      the hang - when a duplicate is detected, the optimizer 
      disables the use of memory mapped files, and it wasn't the case.
      
      The patch below is also to not turn on MMAP on children tables
      if myisam_use_mmap is off.
      A test case is added to cover MyISAMMRG and myisam_use_mmap
      option.
     @ mysql-test/r/merge_mmap.result
        Result file - Bug#50788.
     @ mysql-test/t/merge_mmap-master.opt
        An option file for the test for Bug#50788 -- use mmap.
     @ mysql-test/t/merge_mmap.test
        Try INSERT ... SELECT into a merge table when myisam_use_mmap is on (Bug#50788).
     @ storage/myisam/mi_statrec.c
        Fixed misinterpretation of the return value of my_b_read().
     @ storage/myisammrg/ha_myisammrg.cc
        Skip HA_EXTRA_MMAP if MyISAM memory mapping is disabled.

    added:
      mysql-test/r/merge_mmap.result
      mysql-test/t/merge_mmap-master.opt
      mysql-test/t/merge_mmap.test
    modified:
      storage/myisam/mi_statrec.c
      storage/myisammrg/ha_myisammrg.cc
=== modified file 'mysql-test/r/merge.result'
--- a/mysql-test/r/merge.result	2010-06-30 06:47:49 +0000
+++ b/mysql-test/r/merge.result	2010-07-02 16:07:57 +0000
@@ -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 t
 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 w
 # 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 un
 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
 #

=== modified file 'mysql-test/r/merge_mmap.result'
--- a/mysql-test/r/merge_mmap.result	2010-07-02 15:21:07 +0000
+++ b/mysql-test/r/merge_mmap.result	2010-07-02 16:07:57 +0000
@@ -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

=== modified file 'mysql-test/t/merge.test'
--- a/mysql-test/t/merge.test	2010-06-30 06:47:49 +0000
+++ b/mysql-test/t/merge.test	2010-07-02 16:07:57 +0000
@@ -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=
 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

=== modified file 'mysql-test/t/merge_mmap.test'
--- a/mysql-test/t/merge_mmap.test	2010-07-02 15:21:07 +0000
+++ b/mysql-test/t/merge_mmap.test	2010-07-02 16:07:57 +0000
@@ -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

=== modified file 'storage/myisammrg/ha_myisammrg.cc'
--- a/storage/myisammrg/ha_myisammrg.cc	2010-07-02 15:21:07 +0000
+++ b/storage/myisammrg/ha_myisammrg.cc	2010-07-02 16:07:57 +0000
@@ -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_chi
     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));


Attachment: [text/bzr-bundle] bzr/kostja@sun.com-20100702160757-z1yob8pogatt69jt.bundle
Thread
bzr push into mysql-trunk-runtime branch (kostja:3074 to 3075)Bug#36171Konstantin Osipov2 Jul