1set global default_storage_engine=myisam; 2set session default_storage_engine=myisam; 3drop table if exists t1,t2,t3,t4,t5,t6; 4drop database if exists mysqltest; 5create table t1 (a int not null primary key auto_increment, message char(20)); 6create table t2 (a int not null primary key auto_increment, message char(20)); 7INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); 8INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2"); 9create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2); 10select * from t3; 11a b 121 Testing 132 table 143 t1 151 Testing 162 table 173 t2 18select * from t3 order by a desc; 19a b 203 t1 213 t2 222 table 232 table 241 Testing 251 Testing 26drop table t3; 27insert into t1 select NULL,message from t2; 28insert into t2 select NULL,message from t1; 29insert into t1 select NULL,message from t2; 30insert into t2 select NULL,message from t1; 31insert into t1 select NULL,message from t2; 32insert into t2 select NULL,message from t1; 33insert into t1 select NULL,message from t2; 34insert into t2 select NULL,message from t1; 35insert into t1 select NULL,message from t2; 36insert into t2 select NULL,message from t1; 37insert into t1 select NULL,message from t2; 38create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,test.t2); 39explain select * from t3 where a < 10; 40id select_type table partitions type possible_keys key key_len ref rows filtered Extra 411 SIMPLE t3 NULL range a a 4 NULL 18 100.00 Using where 42Warnings: 43Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where (`test`.`t3`.`a` < 10) 44explain select * from t3 where a > 10 and a < 20; 45id select_type table partitions type possible_keys key key_len ref rows filtered Extra 461 SIMPLE t3 NULL range a a 4 NULL 17 100.00 Using where 47Warnings: 48Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where ((`test`.`t3`.`a` > 10) and (`test`.`t3`.`a` < 20)) 49select * from t3 where a = 10; 50a b 5110 Testing 5210 Testing 53select * from t3 where a < 10; 54a b 551 Testing 561 Testing 572 table 582 table 593 t1 603 t2 614 Testing 624 Testing 635 table 645 table 656 t2 666 t1 677 Testing 687 Testing 698 table 708 table 719 t2 729 t2 73select * from t3 where a > 10 and a < 20; 74a b 7511 table 7611 table 7712 t1 7812 t1 7913 Testing 8013 Testing 8114 table 8214 table 8315 t2 8415 t2 8516 Testing 8616 Testing 8717 table 8817 table 8918 t2 9018 t2 9119 Testing 9219 Testing 93explain select a from t3 order by a desc limit 10; 94id select_type table partitions type possible_keys key key_len ref rows filtered Extra 951 SIMPLE t3 NULL index NULL a 4 NULL 10 100.00 Using index 96Warnings: 97Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` order by `test`.`t3`.`a` desc limit 10 98select a from t3 order by a desc limit 10; 99a 100699 101698 102697 103696 104695 105694 106693 107692 108691 109690 110select a from t3 order by a desc limit 300,10; 111a 112416 113415 114415 115414 116414 117413 118413 119412 120412 121411 122delete from t3 where a=3; 123select * from t3 where a < 10; 124a b 1251 Testing 1261 Testing 1272 table 1282 table 1294 Testing 1304 Testing 1315 table 1325 table 1336 t2 1346 t1 1357 Testing 1367 Testing 1378 table 1388 table 1399 t2 1409 t2 141delete from t3 where a >= 6 and a <= 8; 142select * from t3 where a < 10; 143a b 1441 Testing 1451 Testing 1462 table 1472 table 1484 Testing 1494 Testing 1505 table 1515 table 1529 t2 1539 t2 154update t3 set a=3 where a=9; 155select * from t3 where a < 10; 156a b 1571 Testing 1581 Testing 1592 table 1602 table 1613 t2 1623 t2 1634 Testing 1644 Testing 1655 table 1665 table 167update t3 set a=6 where a=7; 168select * from t3 where a < 10; 169a b 1701 Testing 1711 Testing 1722 table 1732 table 1743 t2 1753 t2 1764 Testing 1774 Testing 1785 table 1795 table 180show create table t3; 181Table Create Table 182t3 CREATE TABLE `t3` ( 183 `a` int(11) NOT NULL, 184 `b` char(20) DEFAULT NULL, 185 KEY `a` (`a`) 186) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) 187create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2); 188select * from t4; 189ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 190alter table t4 add column c int; 191ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 192flush tables; 193select * from t4; 194ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 195create database mysqltest; 196create table mysqltest.t6 (a int not null primary key auto_increment, message char(20)); 197create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6); 198show create table t5; 199Table Create Table 200t5 CREATE TABLE `t5` ( 201 `a` int(11) NOT NULL, 202 `b` char(20) DEFAULT NULL, 203 KEY `a` (`a`) 204) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`) 205alter table t5 engine=myisam; 206drop table t5, mysqltest.t6; 207drop database mysqltest; 208drop table t4,t3,t1,t2; 209create table t1 (c char(10)) engine=myisam; 210create table t2 (c char(10)) engine=myisam; 211create table t3 (c char(10)) union=(t1,t2) engine=merge; 212insert into t1 (c) values ('test1'); 213insert into t1 (c) values ('test1'); 214insert into t1 (c) values ('test1'); 215insert into t2 (c) values ('test2'); 216insert into t2 (c) values ('test2'); 217insert into t2 (c) values ('test2'); 218select * from t3; 219c 220test1 221test1 222test1 223test2 224test2 225test2 226select * from t3; 227c 228test1 229test1 230test1 231test2 232test2 233test2 234delete from t3 where 1=1; 235select * from t3; 236c 237select * from t1; 238c 239drop table t3,t2,t1; 240CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr)); 241CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr)); 242CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) 243ENGINE=MERGE UNION=(t1,t2); 244SELECT * from t3; 245incr othr 246INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17); 247INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32); 248INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37); 249INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30); 250SELECT * from t3 where incr in (1,2,3,4) order by othr; 251incr othr 2521 10 2532 24 2544 33 2553 53 256alter table t3 UNION=(t1); 257select count(*) from t3; 258count(*) 25910 260alter table t3 UNION=(t1,t2); 261select count(*) from t3; 262count(*) 26320 264alter table t3 ENGINE=MYISAM; 265select count(*) from t3; 266count(*) 26720 268drop table t3; 269CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) 270ENGINE=MERGE UNION=(t1,t2); 271show create table t3; 272Table Create Table 273t3 CREATE TABLE `t3` ( 274 `incr` int(11) NOT NULL, 275 `othr` int(11) NOT NULL, 276 PRIMARY KEY (`incr`) 277) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) 278alter table t3 drop primary key; 279show create table t3; 280Table Create Table 281t3 CREATE TABLE `t3` ( 282 `incr` int(11) NOT NULL, 283 `othr` int(11) NOT NULL 284) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) 285drop table t3,t2,t1; 286create table t1 (a int not null, key(a)) engine=merge; 287select * from t1; 288a 289drop table t1; 290create table t1 (a int not null, b int not null, key(a,b)); 291create table t2 (a int not null, b int not null, key(a,b)); 292create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2); 293insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6); 294insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6); 295flush tables; 296select * from t3 where a=1 order by b limit 2; 297a b 2981 1 2991 2 300drop table t3,t1,t2; 301create table t1 (a int not null, b int not null auto_increment, primary key(a,b)); 302create table t2 (a int not null, b int not null auto_increment, primary key(a,b)); 303create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; 304create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; 305create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; 306create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; 307show create table t3; 308Table Create Table 309t3 CREATE TABLE `t3` ( 310 `a` int(11) NOT NULL, 311 `b` int(11) NOT NULL, 312 KEY `a` (`a`,`b`) 313) ENGINE=MyISAM DEFAULT CHARSET=latin1 314show create table t4; 315Table Create Table 316t4 CREATE TABLE `t4` ( 317 `a` int(11) NOT NULL, 318 `b` int(11) NOT NULL, 319 KEY `a` (`a`,`b`) 320) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`) 321show create table t5; 322Table Create Table 323t5 CREATE TABLE `t5` ( 324 `a` int(11) NOT NULL, 325 `b` int(11) NOT NULL AUTO_INCREMENT, 326 PRIMARY KEY (`a`,`b`) 327) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`) 328show create table t6; 329Table Create Table 330t6 CREATE TABLE `t6` ( 331 `a` int(11) NOT NULL, 332 `b` int(11) NOT NULL AUTO_INCREMENT, 333 PRIMARY KEY (`a`,`b`) 334) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 335insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL); 336insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL); 337select * from t3 order by b,a limit 3; 338a b 339select * from t4 order by b,a limit 3; 340a b 3411 1 3422 1 3431 2 344select * from t5 order by b,a limit 3,3; 345a b 3462 2 3471 3 3482 3 349select * from t6 order by b,a limit 6,3; 350a b 3511 4 3522 4 353insert into t5 values (5,1),(5,2); 354insert into t6 values (6,1),(6,2); 355select * from t1 order by a,b; 356a b 3571 1 3581 2 3591 3 3601 4 3615 1 3625 2 363select * from t2 order by a,b; 364a b 3652 1 3662 2 3672 3 3682 4 3696 1 3706 2 371select * from t4 order by a,b; 372a b 3731 1 3741 2 3751 3 3761 4 3772 1 3782 2 3792 3 3802 4 3815 1 3825 2 3836 1 3846 2 385insert into t3 values (3,1),(3,2),(3,3),(3,4); 386select * from t3 order by a,b; 387a b 3883 1 3893 2 3903 3 3913 4 392alter table t4 UNION=(t1,t2,t3); 393show create table t4; 394Table Create Table 395t4 CREATE TABLE `t4` ( 396 `a` int(11) NOT NULL, 397 `b` int(11) NOT NULL, 398 KEY `a` (`a`,`b`) 399) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`,`t3`) 400select * from t4 order by a,b; 401a b 4021 1 4031 2 4041 3 4051 4 4062 1 4072 2 4082 3 4092 4 4103 1 4113 2 4123 3 4133 4 4145 1 4155 2 4166 1 4176 2 418alter table t4 INSERT_METHOD=FIRST; 419show create table t4; 420Table Create Table 421t4 CREATE TABLE `t4` ( 422 `a` int(11) NOT NULL, 423 `b` int(11) NOT NULL, 424 KEY `a` (`a`,`b`) 425) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`) 426insert into t4 values (4,1),(4,2); 427select * from t1 order by a,b; 428a b 4291 1 4301 2 4311 3 4321 4 4334 1 4344 2 4355 1 4365 2 437select * from t2 order by a,b; 438a b 4392 1 4402 2 4412 3 4422 4 4436 1 4446 2 445select * from t3 order by a,b; 446a b 4473 1 4483 2 4493 3 4503 4 451select * from t4 order by a,b; 452a b 4531 1 4541 2 4551 3 4561 4 4572 1 4582 2 4592 3 4602 4 4613 1 4623 2 4633 3 4643 4 4654 1 4664 2 4675 1 4685 2 4696 1 4706 2 471select * from t5 order by a,b; 472a b 4731 1 4741 2 4751 3 4761 4 4772 1 4782 2 4792 3 4802 4 4814 1 4824 2 4835 1 4845 2 4856 1 4866 2 487select 1; 4881 4891 490insert into t5 values (1,NULL),(5,NULL); 491insert into t6 values (2,NULL),(6,NULL); 492select * from t1 order by a,b; 493a b 4941 1 4951 2 4961 3 4971 4 4981 5 4994 1 5004 2 5015 1 5025 2 5035 3 504select * from t2 order by a,b; 505a b 5062 1 5072 2 5082 3 5092 4 5102 5 5116 1 5126 2 5136 3 514select * from t5 order by a,b; 515a b 5161 1 5171 2 5181 3 5191 4 5201 5 5212 1 5222 2 5232 3 5242 4 5252 5 5264 1 5274 2 5285 1 5295 2 5305 3 5316 1 5326 2 5336 3 534select * from t6 order by a,b; 535a b 5361 1 5371 2 5381 3 5391 4 5401 5 5412 1 5422 2 5432 3 5442 4 5452 5 5464 1 5474 2 5485 1 5495 2 5505 3 5516 1 5526 2 5536 3 554insert into t1 values (99,NULL); 555select * from t4 where a+0 > 90; 556a b 55799 1 558insert t5 values (1,1); 559ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' 560insert t6 values (2,1); 561ERROR 23000: Duplicate entry '2-1' for key 'PRIMARY' 562insert t5 values (1,1) on duplicate key update b=b+10; 563insert t6 values (2,1) on duplicate key update b=b+20; 564select * from t5 where a < 3; 565a b 5661 2 5671 3 5681 4 5691 5 5701 11 5712 2 5722 3 5732 4 5742 5 5752 21 576drop table t6, t5, t4, t3, t2, t1; 577CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM; 578INSERT INTO t1 VALUES (1,1), (2,1); 579CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM; 580INSERT INTO t2 VALUES (1,2), (2,2); 581CREATE TABLE t3 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) ENGINE=MRG_MyISAM UNION=(t1,t2); 582select max(b) from t3 where a = 2; 583max(b) 5842 585select max(b) from t1 where a = 2; 586max(b) 5871 588drop table t3,t1,t2; 589CREATE TABLE t1 (c1 INT NOT NULL); 590CREATE TABLE t2 (c1 INT NOT NULL); 591INSERT INTO t1 VALUES (1); 592INSERT INTO t2 VALUES (2); 593CREATE TEMPORARY TABLE t3 (c1 INT NOT NULL) ENGINE=MRG_MYISAM UNION=(t1,t2); 594SELECT * FROM t3; 595c1 5961 5972 598CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MyISAM; 599CREATE TEMPORARY TABLE t5 (c1 INT NOT NULL) ENGINE=MyISAM; 600INSERT INTO t4 VALUES (4); 601INSERT INTO t5 VALUES (5); 602CREATE TEMPORARY TABLE t6 (c1 INT NOT NULL) ENGINE=MRG_MYISAM UNION=(t4,t5); 603SELECT * FROM t6; 604c1 6054 6065 607DROP TABLE t6, t3, t1, t2, t4, t5; 608create temporary table t1 (a int not null); 609create temporary table t2 (a int not null); 610insert into t1 values (1); 611insert into t2 values (2); 612create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); 613select * from t3; 614ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 615drop table t3, t2, t1; 616create table t1 (a int not null); 617create temporary table t2 (a int not null) engine=myisam; 618insert into t1 values (1); 619insert into t2 values (2); 620create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); 621select * from t3; 622ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 623drop table t3; 624create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); 625select * from t3; 626a 6271 6282 629drop table t3, t2, t1; 630# CREATE...SELECT is not implemented for MERGE tables. 631CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL) ENGINE=MyISAM; 632CREATE TEMPORARY TABLE t2 (c1 INT NOT NULL) ENGINE=MyISAM; 633CREATE TABLE t3 (c1 INT NOT NULL); 634INSERT INTO t3 VALUES (3), (33); 635LOCK TABLES t3 READ; 636CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2) 637INSERT_METHOD=LAST SELECT * FROM t3; 638ERROR HY000: 'test.t4' is not BASE TABLE 639SELECT * FROM t4; 640ERROR HY000: Table 't4' was not locked with LOCK TABLES 641UNLOCK TABLES; 642CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2) 643INSERT_METHOD=LAST; 644INSERT INTO t4 SELECT * FROM t3; 645# Alter temporary MERGE table. 646ALTER TABLE t4 UNION=(t1); 647LOCK TABLES t4 WRITE; 648# Alter temporary MERGE table under LOCk tables. 649ALTER TABLE t4 UNION=(t1,t2); 650UNLOCK TABLES; 651# MERGE table and function. 652CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3); 653SELECT * FROM t4 WHERE c1 < f1(); 654c1 6553 656DROP FUNCTION f1; 657DROP TABLE t4, t3, t2, t1; 658CREATE TABLE t1 ( 659fileset_id tinyint(3) unsigned NOT NULL default '0', 660file_code varchar(32) NOT NULL default '', 661fileset_root_id tinyint(3) unsigned NOT NULL default '0', 662PRIMARY KEY (fileset_id,file_code), 663KEY files (fileset_id,fileset_root_id) 664) ENGINE=MyISAM; 665INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1), 666(2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1), 667(2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1); 668CREATE TABLE t2 ( 669fileset_id tinyint(3) unsigned NOT NULL default '0', 670file_code varchar(32) NOT NULL default '', 671fileset_root_id tinyint(3) unsigned NOT NULL default '0', 672PRIMARY KEY (fileset_id,file_code), 673KEY files (fileset_id,fileset_root_id) 674) ENGINE=MRG_MyISAM UNION=(t1); 675EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2 676AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; 677id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6781 SIMPLE t2 NULL range PRIMARY PRIMARY 35 NULL 5 100.00 Using where 679Warnings: 680Note 1003 /* select#1 */ select `test`.`t2`.`fileset_id` AS `fileset_id`,`test`.`t2`.`file_code` AS `file_code`,`test`.`t2`.`fileset_root_id` AS `fileset_root_id` from `test`.`t2` IGNORE INDEX (`files`) where ((`test`.`t2`.`fileset_id` = 2) and (`test`.`t2`.`file_code` between '0000000115' and '0000000120')) limit 1 681EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 682AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; 683id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6841 SIMPLE t2 NULL range PRIMARY,files PRIMARY 35 NULL 5 100.00 Using where 685Warnings: 686Note 1003 /* select#1 */ select `test`.`t2`.`fileset_id` AS `fileset_id`,`test`.`t2`.`file_code` AS `file_code`,`test`.`t2`.`fileset_root_id` AS `fileset_root_id` from `test`.`t2` where ((`test`.`t2`.`fileset_id` = 2) and (`test`.`t2`.`file_code` between '0000000115' and '0000000120')) limit 1 687EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2 688AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; 689id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6901 SIMPLE t1 NULL range PRIMARY,files PRIMARY 35 NULL 5 100.00 Using index condition 691Warnings: 692Note 1003 /* select#1 */ select `test`.`t1`.`fileset_id` AS `fileset_id`,`test`.`t1`.`file_code` AS `file_code`,`test`.`t1`.`fileset_root_id` AS `fileset_root_id` from `test`.`t1` where ((`test`.`t1`.`fileset_id` = 2) and (`test`.`t1`.`file_code` between '0000000115' and '0000000120')) limit 1 693EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 694AND file_code = '0000000115' LIMIT 1; 695id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6961 SIMPLE t2 NULL const PRIMARY,files PRIMARY 35 const,const 1 100.00 NULL 697Warnings: 698Note 1003 /* select#1 */ select '2' AS `fileset_id`,'0000000115' AS `file_code`,'1' AS `fileset_root_id` from `test`.`t2` where 1 limit 1 699DROP TABLE t2, t1; 700create table t1 (x int, y int, index xy(x, y)); 701create table t2 (x int, y int, index xy(x, y)); 702create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2); 703insert into t1 values(1, 2); 704insert into t2 values(1, 3); 705select * from t3 where x = 1 and y < 5 order by y; 706x y 7071 2 7081 3 709select * from t3 where x = 1 and y < 5 order by y desc; 710x y 7111 3 7121 2 713drop table t1,t2,t3; 714create table t1 (a int); 715create table t2 (a int); 716insert into t1 values (0); 717insert into t2 values (1); 718create table t3 engine=merge union=(t1, t2) select * from t1; 719ERROR HY000: 'test.t3' is not BASE TABLE 720create table t3 engine=merge union=(t1, t2) select * from t2; 721ERROR HY000: 'test.t3' is not BASE TABLE 722create table t3 engine=merge union=(t1, t2) select (select max(a) from t2); 723ERROR HY000: 'test.t3' is not BASE TABLE 724drop table t1, t2; 725create table t1 ( 726a double(14,4), 727b varchar(10), 728index (a,b) 729) engine=merge union=(t2,t3); 730create table t2 ( 731a double(14,4), 732b varchar(10), 733index (a,b) 734) engine=myisam; 735create table t3 ( 736a double(14,4), 737b varchar(10), 738index (a,b) 739) engine=myisam; 740insert into t2 values ( null, ''); 741insert into t2 values ( 9999999999.999, ''); 742insert into t3 select * from t2; 743select min(a), max(a) from t1; 744min(a) max(a) 7459999999999.9990 9999999999.9990 746flush tables; 747select min(a), max(a) from t1; 748min(a) max(a) 7499999999999.9990 9999999999.9990 750drop table t1, t2, t3; 751create table t1 (a int,b int,c int, index (a,b,c)); 752create table t2 (a int,b int,c int, index (a,b,c)); 753create table t3 (a int,b int,c int, index (a,b,c)) 754engine=merge union=(t1 ,t2); 755insert into t1 (a,b,c) values (1,1,0),(1,2,0); 756insert into t2 (a,b,c) values (1,1,1),(1,2,1); 757explain select a,b,c from t3 force index (a) where a=1 order by a,b,c; 758id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7591 SIMPLE t3 NULL ref a a 5 const 2 100.00 Using where; Using index 760Warnings: 761Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` FORCE INDEX (`a`) where (`test`.`t3`.`a` = 1) order by `test`.`t3`.`a`,`test`.`t3`.`b`,`test`.`t3`.`c` 762select a,b,c from t3 force index (a) where a=1 order by a,b,c; 763a b c 7641 1 0 7651 1 1 7661 2 0 7671 2 1 768explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc; 769id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7701 SIMPLE t3 NULL ref a a 5 const 2 100.00 Using where; Using index 771Warnings: 772Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` FORCE INDEX (`a`) where (`test`.`t3`.`a` = 1) order by `test`.`t3`.`a` desc,`test`.`t3`.`b` desc,`test`.`t3`.`c` desc 773select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc; 774a b c 7751 2 1 7761 2 0 7771 1 1 7781 1 0 779show index from t3; 780Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 781t3 1 a 1 a A NULL NULL NULL YES BTREE 782t3 1 a 2 b A NULL NULL NULL YES BTREE 783t3 1 a 3 c A NULL NULL NULL YES BTREE 784drop table t1, t2, t3; 785CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) ) 786ENGINE=MyISAM; 787CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) ) 788ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST; 789INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2; 790INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3; 791SELECT b FROM t2; 792b 7933 794DROP TABLE t1, t2; 795create table t1(a int); 796create table t2(a int); 797insert into t1 values (1); 798insert into t2 values (2); 799create table t3 (a int) engine=merge union=(t1, t2) insert_method=first; 800select * from t3; 801a 8021 8032 804insert t2 select * from t2; 805select * from t2; 806a 8072 8082 809insert t3 select * from t1; 810select * from t3; 811a 8121 8131 8142 8152 816insert t1 select * from t3; 817select * from t1; 818a 8191 8201 8211 8221 8232 8242 825select * from t2; 826a 8272 8282 829select * from t3; 830a 8311 8321 8331 8341 8352 8362 8372 8382 839check table t1, t2; 840Table Op Msg_type Msg_text 841test.t1 check status OK 842test.t2 check status OK 843drop table t1, t2, t3; 844CREATE TABLE t1(a INT); 845INSERT INTO t1 VALUES(2),(1); 846CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1); 847SELECT * FROM t2 WHERE a=2; 848ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 849DROP TABLE t1, t2; 850CREATE TABLE t1(a INT) ENGINE=MEMORY; 851CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1); 852SELECT * FROM t2; 853ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 854DROP TABLE t1, t2; 855CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3); 856SELECT * FROM t2; 857ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 858DROP TABLE t2; 859CREATE TABLE t1(a INT, b TEXT); 860CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1); 861SELECT * FROM tm1; 862ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 863DROP TABLE t1, tm1; 864CREATE TABLE t1(a SMALLINT, b SMALLINT); 865CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1); 866SELECT * FROM tm1; 867ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 868DROP TABLE t1, tm1; 869CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(a, b)); 870CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1); 871SELECT * FROM tm1; 872ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 873DROP TABLE t1, tm1; 874CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(b)); 875CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1); 876SELECT * FROM tm1; 877ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 878DROP TABLE t1, tm1; 879CREATE TABLE t1(c1 VARCHAR(1)); 880CREATE TABLE m1 LIKE t1; 881ALTER TABLE m1 ENGINE=MERGE UNION=(t1); 882SELECT * FROM m1; 883c1 884DROP TABLE t1, m1; 885CREATE TABLE t1(c1 VARCHAR(4), c2 TINYINT, c3 TINYINT, c4 TINYINT, 886c5 TINYINT, c6 TINYINT, c7 TINYINT, c8 TINYINT, c9 TINYINT); 887CREATE TABLE m1 LIKE t1; 888ALTER TABLE m1 ENGINE=MERGE UNION=(t1); 889SELECT * FROM m1; 890c1 c2 c3 c4 c5 c6 c7 c8 c9 891DROP TABLE t1, m1; 892CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci, 893b INT, INDEX(a,b)); 894CREATE TABLE t2 LIKE t1; 895CREATE TABLE t3 LIKE t1; 896ALTER TABLE t3 ENGINE=MERGE UNION=(t1,t2); 897INSERT INTO t1 VALUES ('ss',1); 898INSERT INTO t2 VALUES ('ss',2),(0xDF,2); 899SELECT COUNT(*) FROM t3 WHERE a=0xDF AND b=2; 900COUNT(*) 9012 902DROP TABLE t1,t2,t3; 903create table t1 (b bit(1)); 904create table t2 (b bit(1)); 905create table tm (b bit(1)) engine = merge union = (t1,t2); 906select * from tm; 907b 908drop table tm, t1, t2; 909create table t1 (a int) insert_method = last engine = merge; 910insert into t1 values (1); 911ERROR HY000: Table 't1' is read only 912create table t2 (a int) engine = myisam; 913alter table t1 union (t2); 914insert into t1 values (1); 915alter table t1 insert_method = no; 916insert into t1 values (1); 917ERROR HY000: Table 't1' is read only 918drop table t2; 919drop table t1; 920CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2); 921SELECT * FROM tm1; 922ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 923CHECK TABLE tm1; 924Table Op Msg_type Msg_text 925test.tm1 check Error Table 'test.t1' is differently defined or of non-MyISAM type or doesn't exist 926test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist 927test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 928test.tm1 check error Corrupt 929CREATE TABLE t1(a INT); 930SELECT * FROM tm1; 931ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 932CHECK TABLE tm1; 933Table Op Msg_type Msg_text 934test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist 935test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 936test.tm1 check error Corrupt 937CREATE TABLE t2(a BLOB); 938SELECT * FROM tm1; 939ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 940CHECK TABLE tm1; 941Table Op Msg_type Msg_text 942test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist 943test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 944test.tm1 check error Corrupt 945ALTER TABLE t2 MODIFY a INT; 946SELECT * FROM tm1; 947a 948CHECK TABLE tm1; 949Table Op Msg_type Msg_text 950test.tm1 check status OK 951DROP TABLE tm1, t1, t2; 952CREATE TABLE t1(c1 INT); 953CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; 954CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; 955Warnings: 956Note 1050 Table 't1' already exists 957DROP TABLE t1, t2; 958CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM; 959CREATE TABLE t2 LIKE t1; 960INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); 961INSERT INTO t1 SELECT * FROM t2; 962INSERT INTO t1 SELECT * FROM t2; 963CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE 964UNION(t1); 965SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; 966id ref ref 9674 4 5 9684 4 5 9694 4 5 9704 4 5 971SELECT * FROM t3; 972id ref 9731 3 9742 1 9753 2 9764 5 9774 4 9781 3 9792 1 9803 2 9814 5 9824 4 983DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; 984SELECT * FROM t3; 985id ref 9861 3 9872 1 9883 2 9894 5 9901 3 9912 1 9923 2 9934 5 994DROP TABLE t1, t2, t3; 995CREATE TABLE t1(a INT); 996CREATE TABLE m1(a INT) ENGINE=MERGE; 997SHOW CREATE TABLE m1; 998Table Create Table 999m1 CREATE TABLE `m1` ( 1000 `a` int(11) DEFAULT NULL 1001) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 1002DROP TABLE m1; 1003CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(); 1004SHOW CREATE TABLE m1; 1005Table Create Table 1006m1 CREATE TABLE `m1` ( 1007 `a` int(11) DEFAULT NULL 1008) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 1009ALTER TABLE m1 UNION=(t1); 1010ALTER TABLE m1 UNION=(); 1011SHOW CREATE TABLE m1; 1012Table Create Table 1013m1 CREATE TABLE `m1` ( 1014 `a` int(11) DEFAULT NULL 1015) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 1016DROP TABLE t1, m1; 1017CREATE TABLE t1(a INT, KEY(a)) ENGINE=merge; 1018SELECT MAX(a) FROM t1; 1019MAX(a) 1020NULL 1021DROP TABLE t1; 1022CREATE TABLE t1(a INT); 1023CREATE TABLE t2(a VARCHAR(10)); 1024CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2); 1025CREATE TABLE m2(a INT) ENGINE=MERGE UNION=(t1); 1026SELECT * FROM t1; 1027a 1028SELECT * FROM m1; 1029ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 1030SELECT * FROM m2; 1031a 1032DROP TABLE t1, t2, m1, m2; 1033End of 5.0 tests 1034create table t1 (c1 int, index(c1)); 1035create table t2 (c1 int, index(c1)) engine=merge union=(t1); 1036insert into t1 values (1); 1037flush tables; 1038select * from t2; 1039c1 10401 1041flush tables; 1042truncate table t1; 1043insert into t1 values (1); 1044flush tables; 1045select * from t2; 1046c1 10471 1048truncate table t1; 1049insert into t1 values (1); 1050drop table t1,t2; 1051# 1052# Extra tests for TRUNCATE. 1053# 1054# Truncate MERGE table. 1055CREATE TABLE t1 (c1 INT, INDEX(c1)); 1056CREATE TABLE t2 (c1 INT, INDEX(c1)); 1057CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2); 1058INSERT INTO t1 VALUES (1); 1059INSERT INTO t2 VALUES (2); 1060SELECT * FROM t3; 1061c1 10621 10632 1064TRUNCATE TABLE t3; 1065SELECT * FROM t3; 1066c1 1067# 1068# Truncate child table. 1069INSERT INTO t1 VALUES (1); 1070INSERT INTO t2 VALUES (2); 1071TRUNCATE TABLE t1; 1072SELECT * FROM t3; 1073c1 10742 1075# 1076# Truncate MERGE table under locked tables. 1077LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE; 1078INSERT INTO t1 VALUES (1); 1079TRUNCATE TABLE t3; 1080SELECT * FROM t3; 1081c1 1082UNLOCK TABLES; 1083SELECT * FROM t1; 1084c1 1085SELECT * FROM t2; 1086c1 1087# 1088# Truncate child table under locked tables. 1089LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE; 1090INSERT INTO t1 VALUES (1); 1091INSERT INTO t2 VALUES (2); 1092TRUNCATE TABLE t1; 1093SELECT * FROM t3; 1094c1 10952 1096UNLOCK TABLES; 1097DROP TABLE t1, t2, t3; 1098# 1099# Truncate temporary MERGE table. 1100CREATE TEMPORARY TABLE t1 (c1 INT, INDEX(c1)) ENGINE=MyISAM; 1101CREATE TEMPORARY TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MyISAM; 1102CREATE TEMPORARY TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2); 1103INSERT INTO t1 VALUES (1); 1104INSERT INTO t2 VALUES (2); 1105SELECT * FROM t3; 1106c1 11071 11082 1109TRUNCATE TABLE t3; 1110SELECT * FROM t3; 1111c1 1112# 1113# Truncate temporary child table. 1114INSERT INTO t1 VALUES (1); 1115INSERT INTO t2 VALUES (2); 1116TRUNCATE TABLE t1; 1117SELECT * FROM t3; 1118c1 11192 1120# 1121# Truncate temporary MERGE table under locked tables. 1122INSERT INTO t1 VALUES (1); 1123CREATE TABLE t4 (c1 INT, INDEX(c1)); 1124LOCK TABLE t4 WRITE; 1125TRUNCATE TABLE t3; 1126SELECT * FROM t3; 1127c1 1128SELECT * FROM t1; 1129c1 1130SELECT * FROM t2; 1131c1 1132# 1133# Truncate temporary child table under locked tables. 1134INSERT INTO t1 VALUES (1); 1135INSERT INTO t2 VALUES (2); 1136TRUNCATE TABLE t1; 1137SELECT * FROM t3; 1138c1 11392 1140SELECT * FROM t1; 1141c1 1142SELECT * FROM t2; 1143c1 11442 1145UNLOCK TABLES; 1146DROP TABLE t1, t2, t3, t4; 1147CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; 1148CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST; 1149REPAIR TABLE t1; 1150INSERT INTO t2 VALUES (1); 1151Table Op Msg_type Msg_text 1152test.t1 repair status OK 1153DROP TABLE t1, t2; 1154CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; 1155CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST; 1156LOCK TABLE t1 WRITE; 1157INSERT INTO t2 VALUES (1); 1158REPAIR TABLE t1; 1159Table Op Msg_type Msg_text 1160test.t1 repair status OK 1161UNLOCK TABLES; 1162DROP TABLE t1, t2; 1163CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; 1164LOCK TABLE t1 WRITE; 1165INSERT INTO t1 VALUES (1); 1166FLUSH TABLES; 1167FLUSH TABLES; 1168SELECT * FROM t1; 1169c1 1170UNLOCK TABLES; 1171DROP TABLE t1; 1172# 1173# Extra tests for Bug#26379 - Combination of FLUSH TABLE and 1174# REPAIR TABLE corrupts a MERGE table 1175# 1176# CREATE ... SELECT is disabled for MERGE tables. 1177# 1178CREATE TABLE t1(c1 INT); 1179INSERT INTO t1 VALUES (1); 1180CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; 1181CREATE TABLE t3 ENGINE=MRG_MYISAM INSERT_METHOD=LAST SELECT * FROM t2; 1182ERROR HY000: Table 't3' is read only 1183SHOW CREATE TABLE t3; 1184ERROR 42S02: Table 'test.t3' doesn't exist 1185CREATE TABLE t3 ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST 1186SELECT * FROM t2; 1187ERROR HY000: 'test.t3' is not BASE TABLE 1188SHOW CREATE TABLE t3; 1189ERROR 42S02: Table 'test.t3' doesn't exist 1190DROP TABLE t1, t2; 1191# 1192# Bug#37371 "CREATE TABLE LIKE merge loses UNION parameter" 1193# Demonstrate that this is no longer the case. 1194# 1195# 1. Create like. 1196CREATE TABLE t1 (c1 INT); 1197CREATE TABLE t2 (c1 INT); 1198CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) 1199INSERT_METHOD=LAST; 1200INSERT INTO t1 VALUES (1); 1201INSERT INTO t2 VALUES (2); 1202INSERT INTO t3 VALUES (3); 1203CREATE TABLE t4 LIKE t3; 1204SHOW CREATE TABLE t4; 1205Table Create Table 1206t4 CREATE TABLE `t4` ( 1207 `c1` int(11) DEFAULT NULL 1208) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 1209INSERT INTO t4 VALUES (4); 1210DROP TABLE t4; 1211# 1212# 1. Create like with locked tables. 1213LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE; 1214CREATE TABLE t4 LIKE t3; 1215ERROR HY000: Table 't4' was not locked with LOCK TABLES 1216SHOW CREATE TABLE t4; 1217ERROR HY000: Table 't4' was not locked with LOCK TABLES 1218INSERT INTO t4 VALUES (4); 1219ERROR HY000: Table 't4' was not locked with LOCK TABLES 1220# Temporary tables can be created in spite of LOCK TABLES. 1221# If the temporary MERGE table uses the locked children only, 1222# it can even be used. 1223CREATE TEMPORARY TABLE t4 LIKE t3; 1224SHOW CREATE TABLE t4; 1225Table Create Table 1226t4 CREATE TEMPORARY TABLE `t4` ( 1227 `c1` int(11) DEFAULT NULL 1228) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 1229INSERT INTO t4 VALUES (4); 1230UNLOCK TABLES; 1231INSERT INTO t4 VALUES (4); 1232DROP TABLE t4; 1233# 1234# Rename child. 1235# 1236# 1. Normal rename of non-MERGE table. 1237CREATE TABLE t4 (c1 INT); 1238INSERT INTO t4 VALUES (4); 1239SELECT * FROM t4 ORDER BY c1; 1240c1 12414 1242RENAME TABLE t4 TO t5; 1243SELECT * FROM t5 ORDER BY c1; 1244c1 12454 1246RENAME TABLE t5 TO t4; 1247SELECT * FROM t4 ORDER BY c1; 1248c1 12494 1250DROP TABLE t4; 1251# 1252# 2. Normal rename. 1253SELECT * FROM t3 ORDER BY c1; 1254c1 12551 12562 12573 12584 12594 12604 1261RENAME TABLE t2 TO t5; 1262SELECT * FROM t3 ORDER BY c1; 1263ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 1264RENAME TABLE t5 TO t2; 1265SELECT * FROM t3 ORDER BY c1; 1266c1 12671 12682 12693 12704 12714 12724 1273# 1274# 3. Normal rename with locked tables. 1275LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; 1276SELECT * FROM t3 ORDER BY c1; 1277c1 12781 12792 12803 12814 12824 12834 1284RENAME TABLE t2 TO t5; 1285ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 1286SELECT * FROM t3 ORDER BY c1; 1287c1 12881 12892 12903 12914 12924 12934 1294RENAME TABLE t5 TO t2; 1295ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 1296SELECT * FROM t3 ORDER BY c1; 1297c1 12981 12992 13003 13014 13024 13034 1304UNLOCK TABLES; 1305# 1306# 4. Alter table rename. 1307ALTER TABLE t2 RENAME TO t5; 1308SELECT * FROM t3 ORDER BY c1; 1309ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 1310ALTER TABLE t5 RENAME TO t2; 1311SELECT * FROM t3 ORDER BY c1; 1312c1 13131 13142 13153 13164 13174 13184 1319# 1320# 5. Alter table rename with locked tables. 1321LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; 1322ALTER TABLE t2 RENAME TO t5; 1323SELECT * FROM t3 ORDER BY c1; 1324ERROR HY000: Table 't2' was not locked with LOCK TABLES 1325ALTER TABLE t5 RENAME TO t2; 1326ERROR HY000: Table 't5' was not locked with LOCK TABLES 1327UNLOCK TABLES; 1328ALTER TABLE t5 RENAME TO t2; 1329SELECT * FROM t3 ORDER BY c1; 1330c1 13311 13322 13333 13344 13354 13364 1337# 1338# Rename parent. 1339# 1340# 1. Normal rename with locked tables. 1341LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; 1342SELECT * FROM t3 ORDER BY c1; 1343c1 13441 13452 13463 13474 13484 13494 1350RENAME TABLE t3 TO t5; 1351ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 1352SELECT * FROM t3 ORDER BY c1; 1353c1 13541 13552 13563 13574 13584 13594 1360RENAME TABLE t5 TO t3; 1361ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 1362SELECT * FROM t3 ORDER BY c1; 1363c1 13641 13652 13663 13674 13684 13694 1370# 1371# 5. Alter table rename with locked tables. 1372ALTER TABLE t3 RENAME TO t5; 1373SELECT * FROM t5 ORDER BY c1; 1374ERROR HY000: Table 't5' was not locked with LOCK TABLES 1375ALTER TABLE t5 RENAME TO t3; 1376ERROR HY000: Table 't5' was not locked with LOCK TABLES 1377UNLOCK TABLES; 1378ALTER TABLE t5 RENAME TO t3; 1379SELECT * FROM t3 ORDER BY c1; 1380c1 13811 13822 13833 13844 13854 13864 1387DROP TABLE t1, t2, t3; 1388# 1389# Drop locked tables. 1390# 1391# 1. Drop parent. 1392CREATE TABLE t1 (c1 INT, INDEX(c1)); 1393CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1) 1394INSERT_METHOD=LAST; 1395LOCK TABLES t1 WRITE, t2 WRITE; 1396INSERT INTO t1 VALUES (1); 1397DROP TABLE t2; 1398SELECT * FROM t2; 1399ERROR HY000: Table 't2' was not locked with LOCK TABLES 1400SELECT * FROM t1; 1401c1 14021 1403UNLOCK TABLES; 1404# 2. Drop child. 1405CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1) 1406INSERT_METHOD=LAST; 1407LOCK TABLES t1 WRITE, t2 WRITE; 1408INSERT INTO t1 VALUES (1); 1409DROP TABLE t1; 1410SELECT * FROM t2; 1411ERROR HY000: Table 't1' was not locked with LOCK TABLES 1412SELECT * FROM t1; 1413ERROR HY000: Table 't1' was not locked with LOCK TABLES 1414UNLOCK TABLES; 1415DROP TABLE t2; 1416# 1417# ALTER TABLE. Change child list. 1418# 1419CREATE TABLE t1 (c1 INT, INDEX(c1)); 1420CREATE TABLE t2 (c1 INT, INDEX(c1)); 1421CREATE TABLE t3 (c1 INT, INDEX(c1)); 1422INSERT INTO t1 VALUES (1); 1423INSERT INTO t2 VALUES (2); 1424INSERT INTO t3 VALUES (3); 1425CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t3,t2) 1426INSERT_METHOD=LAST; 1427# Shrink child list. 1428ALTER TABLE t4 UNION=(t3); 1429SHOW CREATE TABLE t4; 1430Table Create Table 1431t4 CREATE TABLE `t4` ( 1432 `c1` int(11) DEFAULT NULL, 1433 KEY `c1` (`c1`) 1434) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`) 1435SELECT * FROM t4 ORDER BY c1; 1436c1 14373 1438# Extend child list. 1439ALTER TABLE t4 UNION=(t3,t2); 1440SHOW CREATE TABLE t4; 1441Table Create Table 1442t4 CREATE TABLE `t4` ( 1443 `c1` int(11) DEFAULT NULL, 1444 KEY `c1` (`c1`) 1445) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`,`t2`) 1446SELECT * FROM t4 ORDER BY c1; 1447c1 14482 14493 1450# 1451# ALTER TABLE under LOCK TABLES. Change child list. 1452# 1453LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE; 1454# Shrink child list. 1455ALTER TABLE t4 UNION=(t3); 1456ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 1457# Extend child list within locked tables. 1458ALTER TABLE t4 UNION=(t3,t2); 1459ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 1460# Extend child list beyond locked tables. 1461ALTER TABLE t4 UNION=(t3,t2,t1); 1462ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 1463SHOW CREATE TABLE t4; 1464Table Create Table 1465t4 CREATE TABLE `t4` ( 1466 `c1` int(11) DEFAULT NULL, 1467 KEY `c1` (`c1`) 1468) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`,`t2`) 1469SELECT * FROM t4 ORDER BY c1; 1470c1 14712 14723 1473UNLOCK TABLES; 1474DROP TABLE t4; 1475# 1476# ALTER TABLE under LOCK TABLES. Grave change, table re-creation. 1477# 1478CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2,t3) 1479INSERT_METHOD=LAST; 1480# Lock parent first and then children. 1481LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE; 1482ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1); 1483SELECT * FROM t4 ORDER BY c1; 1484c1 14851 14862 14873 1488ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1); 1489SELECT * FROM t4 ORDER BY c1; 1490c1 14911 14922 14933 1494UNLOCK TABLES; 1495# Lock children first and then parent. 1496LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE; 1497ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1); 1498SELECT * FROM t4 ORDER BY c1; 1499c1 15001 15012 15023 1503ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1); 1504SELECT * FROM t4 ORDER BY c1; 1505c1 15061 15072 15083 1509UNLOCK TABLES; 1510# Lock parent between children. 1511LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1512ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1); 1513SELECT * FROM t4 ORDER BY c1; 1514c1 15151 15162 15173 1518ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1); 1519SELECT * FROM t4 ORDER BY c1; 1520c1 15211 15222 15233 1524UNLOCK TABLES; 1525DROP TABLE t1, t2, t3, t4; 1526# 1527# ALTER TABLE under LOCK TABLES. Simple change, no re-creation. 1528# 1529CREATE TABLE t1 (c1 INT); 1530CREATE TABLE t2 (c1 INT); 1531CREATE TABLE t3 (c1 INT); 1532CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3) 1533INSERT_METHOD=LAST; 1534INSERT INTO t1 VALUES (1); 1535INSERT INTO t2 VALUES (2); 1536INSERT INTO t3 VALUES (3); 1537# Lock parent first and then children. 1538LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE; 1539ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44; 1540SELECT * FROM t4 ORDER BY c1; 1541c1 15421 15432 15443 1545ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22; 1546SELECT * FROM t4 ORDER BY c1; 1547c1 15481 15492 15503 1551UNLOCK TABLES; 1552# Lock children first and then parent. 1553LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE; 1554ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44; 1555SELECT * FROM t4 ORDER BY c1; 1556c1 15571 15582 15593 1560ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22; 1561SELECT * FROM t4 ORDER BY c1; 1562c1 15631 15642 15653 1566UNLOCK TABLES; 1567# Lock parent between children. 1568LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1569ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44; 1570SELECT * FROM t4 ORDER BY c1; 1571c1 15721 15732 15743 1575ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22; 1576SELECT * FROM t4 ORDER BY c1; 1577c1 15781 15792 15803 1581UNLOCK TABLES; 1582# 1583# FLUSH TABLE under LOCK TABLES. 1584# 1585# Lock parent first and then children. 1586LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE; 1587FLUSH TABLE t4; 1588SELECT * FROM t4 ORDER BY c1; 1589c1 15901 15912 15923 1593FLUSH TABLE t2; 1594SELECT * FROM t4 ORDER BY c1; 1595c1 15961 15972 15983 1599FLUSH TABLES; 1600SELECT * FROM t4 ORDER BY c1; 1601c1 16021 16032 16043 1605UNLOCK TABLES; 1606# Lock children first and then parent. 1607LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE; 1608FLUSH TABLE t4; 1609SELECT * FROM t4 ORDER BY c1; 1610c1 16111 16122 16133 1614FLUSH TABLE t2; 1615SELECT * FROM t4 ORDER BY c1; 1616c1 16171 16182 16193 1620FLUSH TABLES; 1621SELECT * FROM t4 ORDER BY c1; 1622c1 16231 16242 16253 1626UNLOCK TABLES; 1627# Lock parent between children. 1628LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1629FLUSH TABLE t4; 1630SELECT * FROM t4 ORDER BY c1; 1631c1 16321 16332 16343 1635FLUSH TABLE t2; 1636SELECT * FROM t4 ORDER BY c1; 1637c1 16381 16392 16403 1641FLUSH TABLES; 1642SELECT * FROM t4 ORDER BY c1; 1643c1 16441 16452 16463 1647UNLOCK TABLES; 1648# 1649# Triggers 1650# 1651# Trigger on parent 1652DELETE FROM t4 WHERE c1 = 4; 1653CREATE TRIGGER t4_ai AFTER INSERT ON t4 FOR EACH ROW SET @a=1; 1654SET @a=0; 1655INSERT INTO t4 VALUES (4); 1656SELECT @a; 1657@a 16581 1659SELECT * FROM t4 ORDER BY c1; 1660c1 16611 16622 16633 16644 1665DROP TRIGGER t4_ai; 1666# Trigger on parent under LOCK TABLES 1667LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1668CREATE TRIGGER t4_ai AFTER INSERT ON t4 FOR EACH ROW SET @a=1; 1669SET @a=0; 1670INSERT INTO t4 VALUES (4); 1671SELECT @a; 1672@a 16731 1674SELECT * FROM t4 ORDER BY c1; 1675c1 16761 16772 16783 16794 16804 1681DROP TRIGGER t4_ai; 1682UNLOCK TABLES; 1683# 1684# Trigger on child 1685DELETE FROM t4 WHERE c1 = 4; 1686CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW SET @a=1; 1687SET @a=0; 1688INSERT INTO t4 VALUES (4); 1689SELECT @a; 1690@a 16910 1692INSERT INTO t3 VALUES (33); 1693SELECT @a; 1694@a 16951 1696SELECT * FROM t4 ORDER BY c1; 1697c1 16981 16992 17003 17014 170233 1703DROP TRIGGER t3_ai; 1704# Trigger on child under LOCK TABLES 1705LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1706CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW SET @a=1; 1707SET @a=0; 1708INSERT INTO t4 VALUES (4); 1709SELECT @a; 1710@a 17110 1712INSERT INTO t3 VALUES (33); 1713SELECT @a; 1714@a 17151 1716SELECT * FROM t4 ORDER BY c1; 1717c1 17181 17192 17203 17214 17224 172333 172433 1725DELETE FROM t4 WHERE c1 = 33; 1726DROP TRIGGER t3_ai; 1727UNLOCK TABLES; 1728# 1729# Trigger with table use on child 1730DELETE FROM t4 WHERE c1 = 4; 1731CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22); 1732INSERT INTO t4 VALUES (4); 1733SELECT * FROM t4 ORDER BY c1; 1734c1 17351 17362 17373 17384 1739INSERT INTO t3 VALUES (33); 1740SELECT * FROM t4 ORDER BY c1; 1741c1 17421 17432 17443 17454 174622 174733 1748DELETE FROM t4 WHERE c1 = 22; 1749DELETE FROM t4 WHERE c1 = 33; 1750DROP TRIGGER t3_ai; 1751# Trigger with table use on child under LOCK TABLES 1752LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1753CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22); 1754INSERT INTO t4 VALUES (4); 1755SELECT * FROM t4 ORDER BY c1; 1756c1 17571 17582 17593 17604 17614 1762INSERT INTO t3 VALUES (33); 1763SELECT * FROM t4 ORDER BY c1; 1764c1 17651 17662 17673 17684 17694 177022 177133 1772DROP TRIGGER t3_ai; 1773DELETE FROM t4 WHERE c1 = 22; 1774DELETE FROM t4 WHERE c1 = 33; 1775UNLOCK TABLES; 1776# 1777# Repair 1778# 1779REPAIR TABLE t4; 1780Table Op Msg_type Msg_text 1781test.t4 repair note The storage engine for the table doesn't support repair 1782REPAIR TABLE t2; 1783Table Op Msg_type Msg_text 1784test.t2 repair status OK 1785SELECT * FROM t4 ORDER BY c1; 1786c1 17871 17882 17893 17904 17914 1792LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1793REPAIR TABLE t4; 1794Table Op Msg_type Msg_text 1795test.t4 repair note The storage engine for the table doesn't support repair 1796REPAIR TABLE t2; 1797Table Op Msg_type Msg_text 1798test.t2 repair status OK 1799SELECT * FROM t4 ORDER BY c1; 1800c1 18011 18022 18033 18044 18054 1806UNLOCK TABLES; 1807# 1808# Optimize 1809# 1810OPTIMIZE TABLE t4; 1811Table Op Msg_type Msg_text 1812test.t4 optimize note The storage engine for the table doesn't support optimize 1813OPTIMIZE TABLE t2; 1814Table Op Msg_type Msg_text 1815test.t2 optimize status OK 1816SELECT * FROM t4 ORDER BY c1; 1817c1 18181 18192 18203 18214 18224 1823LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1824OPTIMIZE TABLE t4; 1825Table Op Msg_type Msg_text 1826test.t4 optimize note The storage engine for the table doesn't support optimize 1827OPTIMIZE TABLE t2; 1828Table Op Msg_type Msg_text 1829test.t2 optimize status Table is already up to date 1830SELECT * FROM t4 ORDER BY c1; 1831c1 18321 18332 18343 18354 18364 1837UNLOCK TABLES; 1838# 1839# Checksum 1840# 1841CHECKSUM TABLE t4; 1842Table Checksum 1843test.t4 46622073 1844CHECKSUM TABLE t2; 1845Table Checksum 1846test.t2 3700403066 1847SELECT * FROM t4 ORDER BY c1; 1848c1 18491 18502 18513 18524 18534 1854LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; 1855CHECKSUM TABLE t4; 1856Table Checksum 1857test.t4 46622073 1858CHECKSUM TABLE t2; 1859Table Checksum 1860test.t2 3700403066 1861SELECT * FROM t4 ORDER BY c1; 1862c1 18631 18642 18653 18664 18674 1868UNLOCK TABLES; 1869DROP TABLE t1, t2, t3, t4; 1870# 1871# Recursive inclusion of merge tables in their union clauses. 1872# 1873CREATE TABLE t1 (c1 INT, INDEX(c1)); 1874CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1) 1875INSERT_METHOD=LAST; 1876CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t2,t1) 1877INSERT_METHOD=LAST; 1878ALTER TABLE t2 UNION=(t3,t1); 1879SELECT * FROM t2; 1880ERROR HY000: Table 't3' is differently defined or of non-MyISAM type or doesn't exist 1881DROP TABLE t1, t2, t3; 1882CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 1883CREATE TABLE t2 (c1 INT) ENGINE=MyISAM; 1884CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2); 1885INSERT INTO t1 VALUES (1); 1886INSERT INTO t2 VALUES (2); 1887SELECT * FROM t3; 1888c1 18891 18902 1891TRUNCATE TABLE t1; 1892SELECT * FROM t3; 1893c1 18942 1895DROP TABLE t1, t2, t3; 1896CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER); 1897SET @rnd_max= 2147483647; 1898SET @rnd= RAND(); 1899SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1900SET @id_rev= @rnd_max - @id; 1901SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1902INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1903SET @rnd= RAND(); 1904SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1905SET @id_rev= @rnd_max - @id; 1906SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1907INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1908SET @rnd= RAND(); 1909SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1910SET @id_rev= @rnd_max - @id; 1911SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1912INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1913SET @rnd= RAND(); 1914SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1915SET @id_rev= @rnd_max - @id; 1916SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1917INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1918SET @rnd= RAND(); 1919SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1920SET @id_rev= @rnd_max - @id; 1921SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1922INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1923SET @rnd= RAND(); 1924SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1925SET @id_rev= @rnd_max - @id; 1926SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1927INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1928SET @rnd= RAND(); 1929SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1930SET @id_rev= @rnd_max - @id; 1931SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1932INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1933SET @rnd= RAND(); 1934SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1935SET @id_rev= @rnd_max - @id; 1936SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1937INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1938SET @rnd= RAND(); 1939SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1940SET @id_rev= @rnd_max - @id; 1941SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1942INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1943SET @rnd= RAND(); 1944SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); 1945SET @id_rev= @rnd_max - @id; 1946SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 1947INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 1948set @@read_buffer_size=2*1024*1024; 1949CREATE TABLE t2 SELECT * FROM t1; 1950INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; 1951INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; 1952INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; 1953INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; 1954INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; 1955CREATE TABLE t3 (id INTEGER, grp TINYINT, id_rev INTEGER) 1956ENGINE= MRG_MYISAM UNION= (t1, t2); 1957SELECT COUNT(*) FROM t1; 1958COUNT(*) 1959130 1960SELECT COUNT(*) FROM t2; 1961COUNT(*) 196280 1963SELECT COUNT(*) FROM t3; 1964COUNT(*) 1965210 1966SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2 1967WHERE a1.id = a2.id GROUP BY a2.grp; 1968TRUNCATE TABLE t1; 1969SELECT COUNT(*) FROM t1; 1970COUNT(*) 19710 1972SELECT COUNT(*) FROM t2; 1973COUNT(*) 197480 1975SELECT COUNT(*) FROM t3; 1976COUNT(*) 197780 1978DROP TABLE t1, t2, t3; 1979CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 1980CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; 1981INSERT INTO t2 VALUES (1); 1982SELECT * FROM t2; 1983c1 19841 1985LOCK TABLES t2 WRITE, t1 WRITE; 1986FLUSH TABLES; 1987REPAIR TABLE t1; 1988Table Op Msg_type Msg_text 1989test.t1 repair status OK 1990CHECK TABLE t1; 1991Table Op Msg_type Msg_text 1992test.t1 check status OK 1993REPAIR TABLE t1; 1994Table Op Msg_type Msg_text 1995test.t1 repair status OK 1996UNLOCK TABLES; 1997CHECK TABLE t1 EXTENDED; 1998Table Op Msg_type Msg_text 1999test.t1 check status OK 2000LOCK TABLES t2 WRITE, t1 WRITE; 2001REPAIR TABLE t1; 2002Table Op Msg_type Msg_text 2003test.t1 repair status OK 2004CHECK TABLE t1; 2005Table Op Msg_type Msg_text 2006test.t1 check status OK 2007REPAIR TABLE t1; 2008Table Op Msg_type Msg_text 2009test.t1 repair status OK 2010UNLOCK TABLES; 2011CHECK TABLE t1 EXTENDED; 2012Table Op Msg_type Msg_text 2013test.t1 check status OK 2014DROP TABLE t1, t2; 2015CREATE TABLE t1 ( a INT ) ENGINE=MyISAM; 2016CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1); 2017LOCK TABLES t1 WRITE, m1 WRITE; 2018FLUSH TABLE t1; 2019UNLOCK TABLES; 2020DROP TABLE m1, t1; 2021CREATE TABLE t1 ( a INT ) ENGINE=MyISAM; 2022CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1); 2023LOCK TABLES m1 WRITE, t1 WRITE; 2024FLUSH TABLE t1; 2025UNLOCK TABLES; 2026DROP TABLE m1, t1; 2027CREATE TABLE t1 (c1 INT, c2 INT) ENGINE= MyISAM; 2028CREATE TABLE t2 (c1 INT, c2 INT) ENGINE= MyISAM; 2029CREATE TABLE t3 (c1 INT, c2 INT) ENGINE= MRG_MYISAM UNION(t1, t2); 2030INSERT INTO t1 VALUES (1, 1); 2031INSERT INTO t2 VALUES (2, 2); 2032SELECT * FROM t3; 2033c1 c2 20341 1 20352 2 2036ALTER TABLE t1 ENGINE= MEMORY; 2037INSERT INTO t1 VALUES (0, 0); 2038SELECT * FROM t3; 2039ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2040DROP TABLE t1, t2, t3; 2041CREATE TABLE t1 (c1 INT, KEY(c1)); 2042CREATE TABLE t2 (c1 INT, KEY(c1)) ENGINE=MRG_MYISAM UNION=(t1) 2043INSERT_METHOD=FIRST; 2044LOCK TABLE t1 WRITE, t2 WRITE; 2045FLUSH TABLES t2, t1; 2046OPTIMIZE TABLE t1; 2047Table Op Msg_type Msg_text 2048test.t1 optimize status Table is already up to date 2049FLUSH TABLES t1; 2050UNLOCK TABLES; 2051FLUSH TABLES; 2052INSERT INTO t1 VALUES (1); 2053LOCK TABLE t1 WRITE, t2 WRITE; 2054FLUSH TABLES t2, t1; 2055OPTIMIZE TABLE t1; 2056Table Op Msg_type Msg_text 2057test.t1 optimize status OK 2058FLUSH TABLES t1; 2059UNLOCK TABLES; 2060DROP TABLE t1, t2; 2061CREATE TABLE t1 (ID INT) ENGINE=MYISAM; 2062CREATE TABLE m1 (ID INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST; 2063INSERT INTO t1 VALUES (); 2064INSERT INTO m1 VALUES (); 2065LOCK TABLE t1 WRITE, m1 WRITE; 2066FLUSH TABLES m1, t1; 2067OPTIMIZE TABLE t1; 2068Table Op Msg_type Msg_text 2069test.t1 optimize status OK 2070FLUSH TABLES m1, t1; 2071UNLOCK TABLES; 2072DROP TABLE t1, m1; 2073CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST; 2074SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE 2075TABLE_SCHEMA = 'test' and TABLE_NAME='tm1'; 2076TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT 2077def test tm1 BASE TABLE NULL NULL NULL # # # # # # # # # # NULL # # Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2078Warnings: 2079Warning 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2080DROP TABLE tm1; 2081CREATE TABLE t1(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; 2082CREATE TABLE t2(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; 2083CREATE TABLE t3(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; 2084CREATE TABLE t4(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) 2085ENGINE=MRG_MYISAM UNION=(t1, t2, t3); 2086INSERT INTO t1 VALUES (1,1), (1,2),(1,3), (1,4); 2087INSERT INTO t2 VALUES (2,1), (2,2),(2,3), (2,4); 2088INSERT INTO t3 VALUES (3,1), (3,2),(3,3), (3,4); 2089EXPLAIN SELECT COUNT(*) FROM t1; 2090id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20911 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2092Warnings: 2093Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` 2094EXPLAIN SELECT COUNT(*) FROM t4; 2095id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20961 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2097Warnings: 2098Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t4` 2099DROP TABLE t1, t2, t3, t4; 2100CREATE TABLE t1(a INT, KEY(a)); 2101INSERT INTO t1 VALUES(0),(1),(2),(3),(4); 2102ANALYZE TABLE t1; 2103Table Op Msg_type Msg_text 2104test.t1 analyze status OK 2105CREATE TABLE m1(a INT, KEY(a)) ENGINE=MERGE UNION=(t1); 2106SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; 2107CARDINALITY 21085 2109SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; 2110CARDINALITY 21115 2112SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; 2113CARDINALITY 21145 2115SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; 2116CARDINALITY 21175 2118DROP TABLE t1, m1; 2119# 2120# Bug #40675 MySQL 5.1 crash with index merge algorithm and Merge tables 2121# 2122# create MYISAM table t1 and insert values into it 2123CREATE TABLE t1(a INT); 2124INSERT INTO t1 VALUES(1); 2125# create MYISAM table t2 and insert values into it 2126CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)); 2127INSERT INTO t2(a,b) VALUES 2128(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2129(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2130(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2131(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2132(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2133(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2134(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2135(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2136(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2137(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2138(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2139(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2140(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2141(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2142(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2143(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2144(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2145(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 2146(1,2); 2147# Create the merge table t3 2148CREATE TABLE t3(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)) 2149ENGINE=MERGE UNION=(t2) INSERT_METHOD=FIRST; 2150# Lock tables t1 and t3 for write 2151LOCK TABLES t1 WRITE, t3 WRITE; 2152# Insert values into the merge table t3 2153INSERT INTO t3(a,b) VALUES(1,2); 2154# select from the join of t2 and t3 (The merge table) 2155SELECT t3.a FROM t1,t3 WHERE t3.b=2 AND t3.a=1; 2156a 21571 21581 2159# Unlock the tables 2160UNLOCK TABLES; 2161# drop the created tables 2162DROP TABLE t1, t2, t3; 2163# insert duplicate value in child table while merge table doesn't have key 2164create table t1 ( 2165col1 int(10), 2166primary key (col1) 2167) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2168CREATE TABLE m1 ( 2169col1 int(10) NOT NULL 2170) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1); 2171insert into m1 (col1) values (1); 2172insert into m1 (col1) values (1); 2173ERROR 23000: Duplicate entry '' for key '*UNKNOWN*' 2174drop table m1, t1; 2175# 2176# Bug#45800 crash when replacing into a merge table and there is a duplicate 2177# 2178# Replace duplicate value in child table when merge table doesn't have key 2179CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM; 2180CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); 2181INSERT INTO m1 VALUES (666); 2182SELECT * FROM m1; 2183c1 2184666 2185# insert the duplicate value into the merge table 2186REPLACE INTO m1 VALUES (666); 2187SELECT * FROM m1; 2188c1 2189666 2190DROP TABLE m1, t1; 2191# Insert... on duplicate key update (with duplicate values in the table) 2192CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM; 2193CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); 2194INSERT INTO m1 VALUES (666); 2195SELECT * FROM m1; 2196c1 2197666 2198# insert the duplicate value into the merge table 2199INSERT INTO m1 VALUES (666) ON DUPLICATE KEY UPDATE c1=c1+1; 2200SELECT * FROM m1; 2201c1 2202667 2203DROP TABLE m1, t1; 2204# Insert duplicate value on MERGE table, where, MERGE has a key but MyISAM has more keys 2205CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1), UNIQUE (c2)); 2206CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c1)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); 2207INSERT INTO m1 VALUES (1,2); 2208# insert the duplicate value into the merge table 2209INSERT INTO m1 VALUES (3,2); 2210ERROR 23000: Duplicate entry '' for key '*UNKNOWN*' 2211DROP TABLE m1,t1; 2212# Try to define MERGE and MyISAM with keys on different columns 2213CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1)); 2214CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c2)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); 2215# Try accessing the merge table for inserts (error occurs) 2216INSERT INTO m1 VALUES (1,2); 2217ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2218INSERT INTO m1 VALUES (1,4); 2219ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2220DROP TABLE m1,t1; 2221CREATE TABLE t1 ( 2222col1 INT(10) 2223) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2224CREATE VIEW v1 as SELECT * FROM t1; 2225CREATE TABLE m1 ( 2226col1 INT(10) 2227)ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(v1); 2228#Select should detect that the child table is a view and fail. 2229SELECT * FROM m1; 2230ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2231DROP VIEW v1; 2232DROP TABLE m1, t1; 2233# 2234# Bug #45796: invalid memory reads and writes when altering merge and 2235# base tables 2236# 2237CREATE TABLE t1(c1 INT) ENGINE=MyISAM; 2238CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1); 2239ALTER TABLE m1 ADD INDEX idx_c1(c1); 2240SELECT * FROM m1; 2241ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2242ALTER TABLE t1 ADD INDEX idx_c1(c1); 2243SELECT * FROM m1; 2244c1 2245DROP TABLE m1; 2246DROP TABLE t1; 2247# 2248# Bug45781 infinite hang/crash in "opening tables" after handler tries to 2249# open merge table 2250# 2251DROP TABLE IF EXISTS m1,t1; 2252CREATE TABLE t1(a int)engine=myisam; 2253CREATE TABLE t2(a int)engine=myisam; 2254CREATE TABLE t3(a int)engine=myisam; 2255CREATE TABLE t4(a int)engine=myisam; 2256CREATE TABLE t5(a int)engine=myisam; 2257CREATE TABLE t6(a int)engine=myisam; 2258CREATE TABLE t7(a int)engine=myisam; 2259CREATE TABLE m1(a int)engine=merge union=(t1,t2,t3,t4,t5,t6,t7); 2260SELECT 1 FROM m1; 22611 2262HANDLER m1 OPEN; 2263ERROR HY000: Table storage engine for 'm1' doesn't have this option 2264DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7; 2265SELECT 1 FROM m1; 2266ERROR 42S02: Table 'test.m1' doesn't exist 2267# 2268# Bug #46614: Assertion in show_create_trigger() 2269# 2270CREATE TABLE t1(a int); 2271CREATE TABLE t2(a int); 2272CREATE TABLE t3(a int) ENGINE = MERGE UNION(t1, t2); 2273CREATE TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo(); 2274SHOW CREATE TRIGGER tr1; 2275Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created 2276tr1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo() latin1 latin1_swedish_ci latin1_swedish_ci # 2277DROP TRIGGER tr1; 2278DROP TABLE t1, t2, t3; 2279# 2280# BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40) 2281# 2282CREATE DATABASE `test/1`; 2283CREATE TABLE `test/1`.`t/1`(a INT); 2284CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); 2285SELECT * FROM m1; 2286a 2287SHOW CREATE TABLE m1; 2288Table Create Table 2289m1 CREATE TABLE `m1` ( 2290 `a` int(11) DEFAULT NULL 2291) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`test/1`.`t/1`) 2292DROP TABLE m1; 2293CREATE TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); 2294SELECT * FROM `test/1`.m1; 2295a 2296SHOW CREATE TABLE `test/1`.m1; 2297Table Create Table 2298m1 CREATE TABLE `m1` ( 2299 `a` int(11) DEFAULT NULL 2300) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t/1`) 2301DROP TABLE `test/1`.m1; 2302DROP TABLE `test/1`.`t/1`; 2303CREATE TEMPORARY TABLE `test/1`.`t/1`(a INT) ENGINE=MyISAM; 2304CREATE TEMPORARY TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); 2305SELECT * FROM m1; 2306a 2307SHOW CREATE TABLE m1; 2308Table Create Table 2309m1 CREATE TEMPORARY TABLE `m1` ( 2310 `a` int(11) DEFAULT NULL 2311) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`test/1`.`t/1`) 2312DROP TABLE m1; 2313CREATE TEMPORARY TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); 2314SELECT * FROM `test/1`.m1; 2315a 2316SHOW CREATE TABLE `test/1`.m1; 2317Table Create Table 2318m1 CREATE TEMPORARY TABLE `m1` ( 2319 `a` int(11) DEFAULT NULL 2320) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t/1`) 2321DROP TABLE `test/1`.m1; 2322DROP TABLE `test/1`.`t/1`; 2323DROP DATABASE `test/1`; 2324CREATE TABLE `t@1`(a INT); 2325SELECT * FROM m1; 2326a 2327SHOW CREATE TABLE m1; 2328Table Create Table 2329m1 CREATE TABLE `m1` ( 2330 `a` int(11) DEFAULT NULL 2331) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t@1`) 2332DROP TABLE `t@1`; 2333CREATE DATABASE `test@1`; 2334CREATE TABLE `test@1`.`t@1`(a INT); 2335FLUSH TABLE m1; 2336SELECT * FROM m1; 2337a 2338SHOW CREATE TABLE m1; 2339Table Create Table 2340m1 CREATE TABLE `m1` ( 2341 `a` int(11) DEFAULT NULL 2342) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`test@1`.`t@1`) 2343DROP TABLE m1; 2344DROP TABLE `test@1`.`t@1`; 2345DROP DATABASE `test@1`; 2346# 2347# Bug#51494c rash with join, explain and 'sounds like' operator 2348# 2349CREATE TABLE t1 (a INT) ENGINE=MYISAM; 2350INSERT INTO t1 VALUES(1); 2351CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL, 2352KEY idx0 (d, c)) ENGINE=MERGE; 2353EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN 2354t2 WHERE b SOUNDS LIKE e AND d = 1; 2355id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23561 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 2357Warnings: 2358Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on(1) where ((soundex(`test`.`t2`.`b`) = soundex(`test`.`t2`.`e`)) and multiple equal(1, `test`.`t2`.`d`)) 2359DROP TABLE t2, t1; 2360# 2361# Bug#46339 - crash on REPAIR TABLE merge table USE_FRM 2362# 2363DROP TABLE IF EXISTS m1, t1; 2364CREATE TABLE t1 (c1 INT) ENGINE=MYISAM; 2365CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST; 2366LOCK TABLE m1 READ; 2367REPAIR TABLE m1 USE_FRM; 2368Table Op Msg_type Msg_text 2369test.m1 repair Error Table 'm1' was locked with a READ lock and can't be updated 2370test.m1 repair status Operation failed 2371UNLOCK TABLES; 2372REPAIR TABLE m1 USE_FRM; 2373Table Op Msg_type Msg_text 2374test.m1 repair note The storage engine for the table doesn't support repair 2375DROP TABLE m1,t1; 2376CREATE TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1); 2377REPAIR TABLE m1 USE_FRM; 2378Table Op Msg_type Msg_text 2379test.m1 repair Warning Can't open table 2380test.m1 repair error Corrupt 2381CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM; 2382REPAIR TABLE m1 USE_FRM; 2383Table Op Msg_type Msg_text 2384test.m1 repair note The storage engine for the table doesn't support repair 2385REPAIR TABLE m1; 2386Table Op Msg_type Msg_text 2387test.m1 repair note The storage engine for the table doesn't support repair 2388DROP TABLE m1, t1; 2389CREATE TEMPORARY TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1); 2390REPAIR TABLE m1 USE_FRM; 2391Table Op Msg_type Msg_text 2392test.m1 repair Error Table 'test.m1' doesn't exist 2393test.m1 repair error Corrupt 2394CREATE TEMPORARY TABLE t1 (f1 BIGINT) ENGINE=MyISAM; 2395REPAIR TABLE m1 USE_FRM; 2396Table Op Msg_type Msg_text 2397m1 repair error Cannot repair temporary table from .frm file 2398REPAIR TABLE m1; 2399Table Op Msg_type Msg_text 2400test.m1 repair note The storage engine for the table doesn't support repair 2401DROP TABLE m1, t1; 2402End of 5.1 tests 2403# 2404# An additional test case for Bug#27430 Crash in subquery code 2405# when in PS and table DDL changed after PREPARE 2406# 2407# Test merge table with too many merge children. 2408# 2409drop table if exists t_parent; 2410set @save_table_definition_cache=@@global.table_definition_cache; 2411set @save_table_open_cache=@@global.table_open_cache; 2412set @@global.table_open_cache=400; 2413# 2414# Set @@global.table_definition_cache to minimum 2415# 2416set @@global.table_definition_cache=400; 2417set @a=null; 2418# 2419# Create 400 merge children 2420# 2421set @a=concat("create table t_parent (a int) union(", @a, 2422") insert_method=first engine=mrg_myisam"); 2423prepare stmt from @a; 2424execute stmt; 2425prepare stmt from "select * from t_parent"; 2426execute stmt; 2427ERROR HY000: Prepared statement needs to be re-prepared 2428execute stmt; 2429ERROR HY000: Prepared statement needs to be re-prepared 2430execute stmt; 2431ERROR HY000: Prepared statement needs to be re-prepared 2432deallocate prepare stmt; 2433# 2434# Create merge parent 2435# 2436# 2437# Cleanup 2438# 2439drop table t_parent; 2440set @@global.table_definition_cache=@save_table_definition_cache; 2441set @@global.table_open_cache=@save_table_open_cache; 2442DROP DATABASE IF EXISTS mysql_test1; 2443CREATE DATABASE mysql_test1; 2444CREATE TABLE t1 ... DATA DIRECTORY=... INDEX DIRECTORY=... 2445CREATE TABLE mysql_test1.t2 ... DATA DIRECTORY=... INDEX DIRECTORY=... 2446CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,mysql_test1.t2) 2447INSERT_METHOD=LAST; 2448INSERT INTO t1 VALUES (1); 2449INSERT INTO mysql_test1.t2 VALUES (2); 2450SELECT * FROM m1; 2451c1 24521 24532 2454DROP TABLE t1, mysql_test1.t2, m1; 2455DROP DATABASE mysql_test1; 2456CREATE TABLE t1 (c1 INT); 2457CREATE TABLE t2 (c1 INT); 2458INSERT INTO t1 (c1) VALUES (1); 2459CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) INSERT_METHOD=FIRST; 2460CREATE TABLE t3 (c1 INT); 2461INSERT INTO t3 (c1) VALUES (1); 2462CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM t3); 2463CREATE VIEW v1 AS SELECT foo.c1 c1, f1() c2, bar.c1 c3, f1() c4 2464FROM tm1 foo, tm1 bar, t3; 2465SELECT * FROM v1; 2466c1 c2 c3 c4 24671 1 1 1 2468DROP FUNCTION f1; 2469DROP VIEW v1; 2470DROP TABLE tm1, t1, t2, t3; 2471CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; 2472CREATE TEMPORARY TABLE t2 (c1 INT) ENGINE=MyISAM; 2473CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) 2474INSERT_METHOD=FIRST; 2475CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM tm1); 2476INSERT INTO tm1 (c1) VALUES (1); 2477SELECT f1() FROM (SELECT 1) AS c1; 2478f1() 24791 2480DROP FUNCTION f1; 2481DROP TABLE tm1, t1, t2; 2482CREATE FUNCTION f1() RETURNS INT 2483BEGIN 2484CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; 2485CREATE TEMPORARY TABLE t2 (c1 INT) ENGINE=MyISAM; 2486CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2); 2487INSERT INTO t1 (c1) VALUES (1); 2488RETURN (SELECT MAX(c1) FROM tm1); 2489END| 2490SELECT f1() FROM (SELECT 1 UNION SELECT 1) c1; 2491f1() 24921 2493DROP FUNCTION f1; 2494DROP TABLE tm1, t1, t2; 2495CREATE TEMPORARY TABLE t1 (c1 INT); 2496INSERT INTO t1 (c1) VALUES (1); 2497CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1); 2498CREATE FUNCTION f1() RETURNS INT 2499BEGIN 2500CREATE TEMPORARY TABLE t2 (c1 INT); 2501ALTER TEMPORARY TABLE tm1 UNION=(t1,t2); 2502INSERT INTO t2 (c1) VALUES (2); 2503RETURN (SELECT MAX(c1) FROM tm1); 2504END| 2505ERROR 0A000: ALTER VIEW is not allowed in stored procedures 2506DROP TABLE tm1, t1; 2507CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 2508CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; 2509INSERT INTO tm1 VALUES (1); 2510SELECT * FROM tm1; 2511c1 25121 2513DROP TABLE tm1, t1; 2514CREATE FUNCTION f1() RETURNS INT 2515BEGIN 2516INSERT INTO tm1 VALUES (1); 2517RETURN (SELECT MAX(c1) FROM tm1); 2518END| 2519CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 2520CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; 2521SELECT f1(); 2522f1() 25231 2524DROP FUNCTION f1; 2525DROP TABLE tm1, t1; 2526CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 2527CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; 2528LOCK TABLE tm1 WRITE; 2529INSERT INTO tm1 VALUES (1); 2530SELECT * FROM tm1; 2531c1 25321 2533UNLOCK TABLES; 2534DROP TABLE tm1, t1; 2535CREATE FUNCTION f1() RETURNS INT 2536BEGIN 2537INSERT INTO tm1 VALUES (1); 2538RETURN (SELECT MAX(c1) FROM tm1); 2539END| 2540CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 2541CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; 2542LOCK TABLE tm1 WRITE; 2543SELECT f1(); 2544f1() 25451 2546UNLOCK TABLES; 2547DROP FUNCTION f1; 2548DROP TABLE tm1, t1; 2549CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 2550CREATE TABLE t2 (c1 INT) ENGINE=MyISAM; 2551CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; 2552CREATE TRIGGER t2_ai AFTER INSERT ON t2 2553FOR EACH ROW INSERT INTO tm1 VALUES(11); 2554LOCK TABLE t2 WRITE; 2555INSERT INTO t2 VALUES (2); 2556SELECT * FROM tm1; 2557c1 255811 2559SELECT * FROM t2; 2560c1 25612 2562UNLOCK TABLES; 2563DROP TRIGGER t2_ai; 2564DROP TABLE tm1, t1, t2; 2565CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; 2566CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) 2567INSERT_METHOD=LAST; 2568INSERT INTO tm1 VALUES (1); 2569SELECT * FROM tm1; 2570c1 25711 2572DROP TABLE tm1, t1; 2573CREATE FUNCTION f1() RETURNS INT 2574BEGIN 2575INSERT INTO tm1 VALUES (1); 2576RETURN (SELECT MAX(c1) FROM tm1); 2577END| 2578CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; 2579CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) 2580INSERT_METHOD=LAST; 2581SELECT f1(); 2582f1() 25831 2584DROP FUNCTION f1; 2585DROP TABLE tm1, t1; 2586CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; 2587CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) 2588INSERT_METHOD=LAST; 2589CREATE TABLE t9 (c1 INT) ENGINE=MyISAM; 2590LOCK TABLE t9 WRITE; 2591INSERT INTO tm1 VALUES (1); 2592SELECT * FROM tm1; 2593c1 25941 2595UNLOCK TABLES; 2596DROP TABLE tm1, t1, t9; 2597CREATE FUNCTION f1() RETURNS INT 2598BEGIN 2599INSERT INTO tm1 VALUES (1); 2600RETURN (SELECT MAX(c1) FROM tm1); 2601END| 2602CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; 2603CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) 2604INSERT_METHOD=LAST; 2605CREATE TABLE t9 (c1 INT) ENGINE=MyISAM; 2606LOCK TABLE t9 WRITE; 2607SELECT f1(); 2608f1() 26091 2610UNLOCK TABLES; 2611DROP FUNCTION f1; 2612DROP TABLE tm1, t1, t9; 2613CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; 2614CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) 2615INSERT_METHOD=LAST; 2616CREATE TABLE t2 (c1 INT) ENGINE=MyISAM; 2617CREATE TRIGGER t2_ai AFTER INSERT ON t2 2618FOR EACH ROW INSERT INTO tm1 VALUES(11); 2619LOCK TABLE t2 WRITE; 2620INSERT INTO t2 VALUES (2); 2621SELECT * FROM tm1; 2622c1 262311 2624SELECT * FROM t2; 2625c1 26262 2627UNLOCK TABLES; 2628DROP TRIGGER t2_ai; 2629DROP TABLE tm1, t1, t2; 2630# 2631# Don't allow an update of a MERGE child in a trigger 2632# if the table's already being modified by the main 2633# statement. 2634# 2635CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 2636CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) 2637INSERT_METHOD=LAST; 2638CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 2639FOR EACH ROW INSERT INTO t1 VALUES(11); 2640LOCK TABLE tm1 WRITE, t1 WRITE; 2641INSERT INTO tm1 VALUES (1); 2642ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 2643SELECT * FROM tm1; 2644c1 26451 2646UNLOCK TABLES; 2647LOCK TABLE t1 WRITE, tm1 WRITE; 2648INSERT INTO tm1 VALUES (1); 2649ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 2650SELECT * FROM tm1; 2651c1 26521 26531 2654UNLOCK TABLES; 2655DROP TRIGGER tm1_ai; 2656DROP TABLE tm1, t1; 2657# 2658# Don't select MERGE child when trying to get a prelocked table. 2659# 2660# Due to a limitation demonstrated by the previous test 2661# we can no longer use a write-locked prelocked table. 2662# The test is kept for historical purposes. 2663# 2664CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 2665CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) 2666INSERT_METHOD=LAST; 2667CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 2668FOR EACH ROW SELECT max(c1) FROM t1 INTO @var; 2669LOCK TABLE tm1 WRITE, t1 WRITE; 2670INSERT INTO tm1 VALUES (1); 2671SELECT * FROM tm1; 2672c1 26731 2674UNLOCK TABLES; 2675LOCK TABLE t1 WRITE, tm1 WRITE; 2676INSERT INTO tm1 VALUES (1); 2677SELECT * FROM tm1; 2678c1 26791 26801 2681UNLOCK TABLES; 2682DROP TRIGGER tm1_ai; 2683DROP TABLE tm1, t1; 2684CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; 2685CREATE TABLE t2 (c1 INT) ENGINE=MyISAM; 2686CREATE TABLE t3 (c1 INT) ENGINE=MyISAM; 2687CREATE TABLE t4 (c1 INT) ENGINE=MyISAM; 2688CREATE TABLE t5 (c1 INT) ENGINE=MyISAM; 2689CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3,t4,t5) 2690INSERT_METHOD=LAST; 2691CREATE TRIGGER t2_au AFTER UPDATE ON t2 2692FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var; 2693CREATE FUNCTION f1() RETURNS INT 2694RETURN (SELECT MAX(c1) FROM t4); 2695LOCK TABLE tm1 WRITE, t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE, t5 WRITE; 2696INSERT INTO t1 VALUES(1); 2697INSERT INTO t2 VALUES(2); 2698INSERT INTO t3 VALUES(3); 2699INSERT INTO t4 VALUES(4); 2700INSERT INTO t5 VALUES(5); 2701UPDATE t2, tm1 SET t2.c1=f1(); 2702FLUSH TABLES; 2703FLUSH TABLES; 2704UNLOCK TABLES; 2705SELECT * FROM tm1; 2706c1 27071 27084 27093 27104 27115 2712DROP TRIGGER t2_au; 2713DROP FUNCTION f1; 2714DROP TABLE tm1, t1, t2, t3, t4, t5; 2715# 2716# Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE 2717# 2718CREATE TEMPORARY TABLE t1 (c1 INT); 2719CREATE TEMPORARY TABLE t2 (c1 INT); 2720ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists, t2); 2721OPTIMIZE TABLE t1; 2722Table Op Msg_type Msg_text 2723test.t1 optimize Error Table 'test.t_not_exists' doesn't exist 2724test.t1 optimize Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2725test.t1 optimize error Corrupt 2726DROP TABLE t1, t2; 2727# 2728# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine 2729# More tests with TEMPORARY MERGE table and permanent children. 2730# First without locked tables. 2731# 2732DROP TABLE IF EXISTS t1, t2, t3, t4, m1, m2; 2733# 2734CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; 2735CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM; 2736CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) 2737INSERT_METHOD=LAST; 2738SHOW CREATE TABLE t1; 2739Table Create Table 2740t1 CREATE TABLE `t1` ( 2741 `c1` int(11) DEFAULT NULL, 2742 `c2` int(11) DEFAULT NULL 2743) ENGINE=MyISAM DEFAULT CHARSET=latin1 2744SHOW CREATE TABLE m1; 2745Table Create Table 2746m1 CREATE TEMPORARY TABLE `m1` ( 2747 `c1` int(11) DEFAULT NULL, 2748 `c2` int(11) DEFAULT NULL 2749) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 2750SELECT * FROM m1; 2751c1 c2 2752INSERT INTO t1 VALUES (111, 121); 2753INSERT INTO m1 VALUES (211, 221); 2754SELECT * FROM m1; 2755c1 c2 2756111 121 2757211 221 2758SELECT * FROM t1; 2759c1 c2 2760111 121 2761SELECT * FROM t2; 2762c1 c2 2763211 221 2764# 2765ALTER TABLE m1 RENAME m2; 2766SHOW CREATE TABLE m2; 2767Table Create Table 2768m2 CREATE TEMPORARY TABLE `m2` ( 2769 `c1` int(11) DEFAULT NULL, 2770 `c2` int(11) DEFAULT NULL 2771) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 2772SELECT * FROM m2; 2773c1 c2 2774111 121 2775211 221 2776# 2777CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) 2778INSERT_METHOD=LAST; 2779ALTER TABLE m2 RENAME m1; 2780ERROR 42S01: Table 'm1' already exists 2781DROP TABLE m1; 2782ALTER TABLE m2 RENAME m1; 2783SHOW CREATE TABLE m1; 2784Table Create Table 2785m1 CREATE TEMPORARY TABLE `m1` ( 2786 `c1` int(11) DEFAULT NULL, 2787 `c2` int(11) DEFAULT NULL 2788) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 2789SELECT * FROM m1; 2790c1 c2 2791111 121 2792211 221 2793# 2794ALTER TABLE m1 ADD COLUMN c3 INT; 2795INSERT INTO m1 VALUES (212, 222, 232); 2796ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2797SELECT * FROM m1; 2798ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2799ALTER TABLE t1 ADD COLUMN c3 INT; 2800ALTER TABLE t2 ADD COLUMN c3 INT; 2801INSERT INTO m1 VALUES (212, 222, 232); 2802SELECT * FROM m1; 2803c1 c2 c3 2804111 121 NULL 2805211 221 NULL 2806212 222 232 2807# 2808ALTER TABLE m1 DROP COLUMN c3; 2809INSERT INTO m1 VALUES (213, 223); 2810ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2811SELECT * FROM m1; 2812ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2813ALTER TABLE t1 DROP COLUMN c3; 2814ALTER TABLE t2 DROP COLUMN c3; 2815INSERT INTO m1 VALUES (213, 223); 2816SELECT * FROM m1; 2817c1 c2 2818111 121 2819211 221 2820212 222 2821213 223 2822# 2823CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM; 2824ALTER TABLE m1 UNION=(t1,t2,t3); 2825INSERT INTO m1 VALUES (311, 321); 2826SELECT * FROM m1; 2827c1 c2 2828111 121 2829211 221 2830212 222 2831213 223 2832311 321 2833SELECT * FROM t1; 2834c1 c2 2835111 121 2836SELECT * FROM t2; 2837c1 c2 2838211 221 2839212 222 2840213 223 2841SELECT * FROM t3; 2842c1 c2 2843311 321 2844# 2845CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM; 2846ALTER TABLE m1 UNION=(t1,t2,t3,t4); 2847INSERT INTO m1 VALUES (411, 421); 2848SELECT * FROM m1; 2849c1 c2 2850111 121 2851211 221 2852212 222 2853213 223 2854311 321 2855411 421 2856SELECT * FROM t1; 2857c1 c2 2858111 121 2859SELECT * FROM t2; 2860c1 c2 2861211 221 2862212 222 2863213 223 2864SELECT * FROM t3; 2865c1 c2 2866311 321 2867SELECT * FROM t4; 2868c1 c2 2869411 421 2870# 2871ALTER TABLE m1 ENGINE=MyISAM; 2872SHOW CREATE TABLE m1; 2873Table Create Table 2874m1 CREATE TEMPORARY TABLE `m1` ( 2875 `c1` int(11) DEFAULT NULL, 2876 `c2` int(11) DEFAULT NULL 2877) ENGINE=MyISAM DEFAULT CHARSET=latin1 2878INSERT INTO m1 VALUES (511, 521); 2879SELECT * FROM m1; 2880c1 c2 2881111 121 2882211 221 2883212 222 2884213 223 2885311 321 2886411 421 2887511 521 2888# 2889ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2) 2890INSERT_METHOD=LAST; 2891SELECT * FROM m1; 2892c1 c2 2893111 121 2894211 221 2895212 222 2896213 223 2897SELECT * FROM t1; 2898c1 c2 2899111 121 2900SELECT * FROM t2; 2901c1 c2 2902211 221 2903212 222 2904213 223 2905# 2906CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; 2907INSERT INTO t1 VALUES (611, 621); 2908SELECT * FROM m1; 2909c1 c2 2910611 621 2911211 221 2912212 222 2913213 223 2914DROP TABLE t1; 2915SELECT * FROM m1; 2916c1 c2 2917111 121 2918211 221 2919212 222 2920213 223 2921# 2922# 2923SHOW CREATE TABLE m1; 2924Table Create Table 2925m1 CREATE TEMPORARY TABLE `m1` ( 2926 `c1` int(11) DEFAULT NULL, 2927 `c2` int(11) DEFAULT NULL 2928) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 2929# 2930CREATE TABLE m2 SELECT * FROM m1; 2931SHOW CREATE TABLE m2; 2932Table Create Table 2933m2 CREATE TABLE `m2` ( 2934 `c1` int(11) DEFAULT NULL, 2935 `c2` int(11) DEFAULT NULL 2936) ENGINE=MyISAM DEFAULT CHARSET=latin1 2937SELECT * FROM m2; 2938c1 c2 2939111 121 2940211 221 2941212 222 2942213 223 2943DROP TABLE m2; 2944# 2945CREATE TEMPORARY TABLE m2 ENGINE=MyISAM SELECT * FROM m1; 2946SHOW CREATE TABLE m2; 2947Table Create Table 2948m2 CREATE TEMPORARY TABLE `m2` ( 2949 `c1` int(11) DEFAULT NULL, 2950 `c2` int(11) DEFAULT NULL 2951) ENGINE=MyISAM DEFAULT CHARSET=latin1 2952SELECT * FROM m2; 2953c1 c2 2954111 121 2955211 221 2956212 222 2957213 223 2958DROP TABLE m2; 2959# 2960CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) 2961INSERT_METHOD=LAST; 2962SELECT * FROM m2; 2963ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 2964DROP TABLE m2; 2965# 2966CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) 2967INSERT_METHOD=LAST SELECT * FROM m1; 2968ERROR HY000: 'test.m2' is not BASE TABLE 2969# 2970CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) 2971INSERT_METHOD=LAST SELECT * FROM m1; 2972ERROR HY000: 'test.m2' is not BASE TABLE 2973# 2974CREATE TABLE m2 LIKE m1; 2975SHOW CREATE TABLE m2; 2976Table Create Table 2977m2 CREATE TABLE `m2` ( 2978 `c1` int(11) DEFAULT NULL, 2979 `c2` int(11) DEFAULT NULL 2980) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 2981SELECT * FROM m2; 2982c1 c2 2983111 121 2984211 221 2985212 222 2986213 223 2987INSERT INTO m2 SELECT * FROM m1; 2988SELECT * FROM m2; 2989c1 c2 2990111 121 2991211 221 2992212 222 2993213 223 2994111 121 2995211 221 2996212 222 2997213 223 2998DROP TABLE m2; 2999# 3000CREATE TEMPORARY TABLE m2 LIKE m1; 3001SHOW CREATE TABLE m2; 3002Table Create Table 3003m2 CREATE TEMPORARY TABLE `m2` ( 3004 `c1` int(11) DEFAULT NULL, 3005 `c2` int(11) DEFAULT NULL 3006) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 3007SELECT * FROM m2; 3008c1 c2 3009111 121 3010211 221 3011212 222 3012213 223 3013111 121 3014211 221 3015212 222 3016213 223 3017INSERT INTO m2 SELECT * FROM m1; 3018SELECT * FROM m2; 3019c1 c2 3020111 121 3021211 221 3022212 222 3023213 223 3024111 121 3025211 221 3026212 222 3027213 223 3028111 121 3029211 221 3030212 222 3031213 223 3032111 121 3033211 221 3034212 222 3035213 223 3036DROP TABLE m2; 3037# 3038CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) 3039INSERT_METHOD=LAST; 3040INSERT INTO m2 SELECT * FROM m1; 3041SELECT * FROM m2; 3042c1 c2 3043311 321 3044411 421 3045111 121 3046211 221 3047212 222 3048213 223 3049111 121 3050211 221 3051212 222 3052213 223 3053111 121 3054211 221 3055212 222 3056213 223 3057111 121 3058211 221 3059212 222 3060213 223 3061# 3062# 3063LOCK TABLE m1 WRITE, m2 WRITE; 3064SELECT * FROM m1,m2 WHERE m1.c1=m2.c1; 3065c1 c2 c1 c2 3066111 121 111 121 3067111 121 111 121 3068111 121 111 121 3069111 121 111 121 3070211 221 211 221 3071211 221 211 221 3072211 221 211 221 3073211 221 211 221 3074212 222 212 222 3075212 222 212 222 3076212 222 212 222 3077212 222 212 222 3078213 223 213 223 3079213 223 213 223 3080213 223 213 223 3081213 223 213 223 3082111 121 111 121 3083111 121 111 121 3084111 121 111 121 3085111 121 111 121 3086211 221 211 221 3087211 221 211 221 3088211 221 211 221 3089211 221 211 221 3090212 222 212 222 3091212 222 212 222 3092212 222 212 222 3093212 222 212 222 3094213 223 213 223 3095213 223 213 223 3096213 223 213 223 3097213 223 213 223 3098111 121 111 121 3099111 121 111 121 3100111 121 111 121 3101111 121 111 121 3102211 221 211 221 3103211 221 211 221 3104211 221 211 221 3105211 221 211 221 3106212 222 212 222 3107212 222 212 222 3108212 222 212 222 3109212 222 212 222 3110213 223 213 223 3111213 223 213 223 3112213 223 213 223 3113213 223 213 223 3114111 121 111 121 3115111 121 111 121 3116111 121 111 121 3117111 121 111 121 3118211 221 211 221 3119211 221 211 221 3120211 221 211 221 3121211 221 211 221 3122212 222 212 222 3123212 222 212 222 3124212 222 212 222 3125212 222 212 222 3126213 223 213 223 3127213 223 213 223 3128213 223 213 223 3129213 223 213 223 3130UNLOCK TABLES; 3131DROP TABLE t1, t2, t3, t4, m1, m2; 3132# 3133# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine 3134# More tests with TEMPORARY MERGE table and permanent children. 3135# (continued) Now the same with locked table. 3136# 3137CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; 3138CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM; 3139CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) 3140INSERT_METHOD=LAST; 3141SHOW CREATE TABLE t1; 3142Table Create Table 3143t1 CREATE TABLE `t1` ( 3144 `c1` int(11) DEFAULT NULL, 3145 `c2` int(11) DEFAULT NULL 3146) ENGINE=MyISAM DEFAULT CHARSET=latin1 3147SHOW CREATE TABLE m1; 3148Table Create Table 3149m1 CREATE TEMPORARY TABLE `m1` ( 3150 `c1` int(11) DEFAULT NULL, 3151 `c2` int(11) DEFAULT NULL 3152) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 3153SELECT * FROM m1; 3154c1 c2 3155INSERT INTO t1 VALUES (111, 121); 3156INSERT INTO m1 VALUES (211, 221); 3157SELECT * FROM m1; 3158c1 c2 3159111 121 3160211 221 3161SELECT * FROM t1; 3162c1 c2 3163111 121 3164SELECT * FROM t2; 3165c1 c2 3166211 221 3167# 3168LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE; 3169# 3170ALTER TABLE m1 RENAME m2; 3171SHOW CREATE TABLE m2; 3172Table Create Table 3173m2 CREATE TEMPORARY TABLE `m2` ( 3174 `c1` int(11) DEFAULT NULL, 3175 `c2` int(11) DEFAULT NULL 3176) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 3177SELECT * FROM m2; 3178c1 c2 3179111 121 3180211 221 3181# 3182CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) 3183INSERT_METHOD=LAST; 3184ALTER TABLE m2 RENAME m1; 3185ERROR 42S01: Table 'm1' already exists 3186DROP TABLE m1; 3187ALTER TABLE m2 RENAME m1; 3188SHOW CREATE TABLE m1; 3189Table Create Table 3190m1 CREATE TEMPORARY TABLE `m1` ( 3191 `c1` int(11) DEFAULT NULL, 3192 `c2` int(11) DEFAULT NULL 3193) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 3194SELECT * FROM m1; 3195c1 c2 3196111 121 3197211 221 3198# 3199ALTER TABLE m1 ADD COLUMN c3 INT; 3200INSERT INTO m1 VALUES (212, 222, 232); 3201ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3202SELECT * FROM m1; 3203ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3204ALTER TABLE t1 ADD COLUMN c3 INT; 3205ALTER TABLE t2 ADD COLUMN c3 INT; 3206INSERT INTO m1 VALUES (212, 222, 232); 3207SELECT * FROM m1; 3208c1 c2 c3 3209111 121 NULL 3210211 221 NULL 3211212 222 232 3212# 3213ALTER TABLE m1 DROP COLUMN c3; 3214INSERT INTO m1 VALUES (213, 223); 3215ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3216SELECT * FROM m1; 3217ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3218ALTER TABLE t1 DROP COLUMN c3; 3219ALTER TABLE t2 DROP COLUMN c3; 3220INSERT INTO m1 VALUES (213, 223); 3221SELECT * FROM m1; 3222c1 c2 3223111 121 3224211 221 3225212 222 3226213 223 3227# 3228UNLOCK TABLES; 3229CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM; 3230ALTER TABLE m1 UNION=(t1,t2,t3); 3231LOCK TABLE m1 WRITE; 3232INSERT INTO m1 VALUES (311, 321); 3233SELECT * FROM m1; 3234c1 c2 3235111 121 3236211 221 3237212 222 3238213 223 3239311 321 3240SELECT * FROM t1; 3241c1 c2 3242111 121 3243SELECT * FROM t2; 3244c1 c2 3245211 221 3246212 222 3247213 223 3248SELECT * FROM t3; 3249c1 c2 3250311 321 3251# 3252CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM; 3253ALTER TABLE m1 UNION=(t1,t2,t3,t4); 3254INSERT INTO m1 VALUES (411, 421); 3255SELECT * FROM m1; 3256c1 c2 3257111 121 3258211 221 3259212 222 3260213 223 3261311 321 3262411 421 3263SELECT * FROM t1; 3264c1 c2 3265111 121 3266SELECT * FROM t2; 3267c1 c2 3268211 221 3269212 222 3270213 223 3271SELECT * FROM t3; 3272c1 c2 3273311 321 3274SELECT * FROM t4; 3275c1 c2 3276411 421 3277# 3278ALTER TABLE m1 ENGINE=MyISAM; 3279SHOW CREATE TABLE m1; 3280Table Create Table 3281m1 CREATE TEMPORARY TABLE `m1` ( 3282 `c1` int(11) DEFAULT NULL, 3283 `c2` int(11) DEFAULT NULL 3284) ENGINE=MyISAM DEFAULT CHARSET=latin1 3285INSERT INTO m1 VALUES (511, 521); 3286SELECT * FROM m1; 3287c1 c2 3288111 121 3289211 221 3290212 222 3291213 223 3292311 321 3293411 421 3294511 521 3295# 3296ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2) 3297INSERT_METHOD=LAST; 3298SELECT * FROM m1; 3299c1 c2 3300111 121 3301211 221 3302212 222 3303213 223 3304SELECT * FROM t1; 3305c1 c2 3306111 121 3307SELECT * FROM t2; 3308c1 c2 3309211 221 3310212 222 3311213 223 3312# 3313CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; 3314INSERT INTO t1 VALUES (611, 621); 3315SELECT * FROM m1; 3316c1 c2 3317611 621 3318211 221 3319212 222 3320213 223 3321DROP TABLE t1; 3322SELECT * FROM m1; 3323c1 c2 3324111 121 3325211 221 3326212 222 3327213 223 3328# 3329# 3330SHOW CREATE TABLE m1; 3331Table Create Table 3332m1 CREATE TEMPORARY TABLE `m1` ( 3333 `c1` int(11) DEFAULT NULL, 3334 `c2` int(11) DEFAULT NULL 3335) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 3336CREATE TABLE m2 SELECT * FROM m1; 3337ERROR HY000: Table 'm2' was not locked with LOCK TABLES 3338# 3339CREATE TEMPORARY TABLE m2 ENGINE=MyISAM SELECT * FROM m1; 3340SHOW CREATE TABLE m2; 3341Table Create Table 3342m2 CREATE TEMPORARY TABLE `m2` ( 3343 `c1` int(11) DEFAULT NULL, 3344 `c2` int(11) DEFAULT NULL 3345) ENGINE=MyISAM DEFAULT CHARSET=latin1 3346SELECT * FROM m2; 3347c1 c2 3348111 121 3349211 221 3350212 222 3351213 223 3352DROP TABLE m2; 3353# 3354CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) 3355INSERT_METHOD=LAST; 3356SELECT * FROM m2; 3357c1 c2 3358311 321 3359411 421 3360LOCK TABLE m1 WRITE, m2 WRITE; 3361UNLOCK TABLES; 3362DROP TABLE m2; 3363LOCK TABLE m1 WRITE; 3364# 3365# ER_TABLE_NOT_LOCKED is returned in ps-protocol 3366CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) 3367INSERT_METHOD=LAST SELECT * FROM m1; 3368Got one of the listed errors 3369# 3370CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) 3371INSERT_METHOD=LAST SELECT * FROM m1; 3372ERROR HY000: 'test.m2' is not BASE TABLE 3373# 3374CREATE TEMPORARY TABLE m2 LIKE m1; 3375SHOW CREATE TABLE m2; 3376Table Create Table 3377m2 CREATE TEMPORARY TABLE `m2` ( 3378 `c1` int(11) DEFAULT NULL, 3379 `c2` int(11) DEFAULT NULL 3380) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 3381LOCK TABLE m1 WRITE, m2 WRITE; 3382SHOW CREATE TABLE m2; 3383Table Create Table 3384m2 CREATE TEMPORARY TABLE `m2` ( 3385 `c1` int(11) DEFAULT NULL, 3386 `c2` int(11) DEFAULT NULL 3387) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`) 3388SELECT * FROM m2; 3389c1 c2 3390111 121 3391211 221 3392212 222 3393213 223 3394INSERT INTO m2 SELECT * FROM m1; 3395SELECT * FROM m2; 3396c1 c2 3397111 121 3398211 221 3399212 222 3400213 223 3401111 121 3402211 221 3403212 222 3404213 223 3405DROP TABLE m2; 3406# 3407CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) 3408INSERT_METHOD=LAST; 3409LOCK TABLE m1 WRITE, m2 WRITE; 3410INSERT INTO m2 SELECT * FROM m1; 3411SELECT * FROM m2; 3412c1 c2 3413311 321 3414411 421 3415111 121 3416211 221 3417212 222 3418213 223 3419111 121 3420211 221 3421212 222 3422213 223 3423# 3424UNLOCK TABLES; 3425DROP TABLE t1, t2, t3, t4, m1, m2; 3426# 3427# Bug47098 assert in MDL_context::destroy on HANDLER 3428# <damaged merge table> OPEN 3429# 3430# Test that merge tables are closed correctly when opened using 3431# HANDLER ... OPEN. 3432# The general case. 3433DROP TABLE IF EXISTS t1, t2, t3; 3434# Connection con1. 3435CREATE TABLE t1 (c1 int); 3436CREATE TABLE t2 (c1 int); 3437CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2); 3438START TRANSACTION; 3439HANDLER t3 OPEN; 3440ERROR HY000: Table storage engine for 't3' doesn't have this option 3441DROP TABLE t1, t2, t3; 3442# Connection default. 3443# Disconnecting con1, all mdl_tickets must have been released. 3444# The bug-specific case. 3445# Connection con1. 3446CREATE TABLE t1 (c1 int); 3447CREATE TABLE t2 (c1 int); 3448CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2); 3449DROP TABLE t2; 3450START TRANSACTION; 3451HANDLER t3 OPEN; 3452ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3453DROP TABLE t1, t3; 3454# Connection default. 3455# Disconnecting con1, all mdl_tickets must have been released. 3456# 3457# A test case for Bug#47648 main.merge fails sporadically 3458# 3459# Make sure we correctly maintain lex->query_tables_last_own. 3460# 3461create table t1 (c1 int not null); 3462create table t2 (c1 int not null); 3463create table t3 (c1 int not null); 3464create function f1 () returns int return (select max(c1) from t3); 3465create table t4 (c1 int not null) engine=merge union=(t1,t2) insert_method=last ; 3466select * from t4 where c1 < f1(); 3467c1 3468prepare stmt from "select * from t4 where c1 < f1()"; 3469execute stmt; 3470c1 3471execute stmt; 3472c1 3473execute stmt; 3474c1 3475drop function f1; 3476execute stmt; 3477ERROR 42000: FUNCTION test.f1 does not exist 3478execute stmt; 3479ERROR 42000: FUNCTION test.f1 does not exist 3480drop table t4, t3, t2, t1; 3481# 3482# Bug#51240 ALTER TABLE of a locked MERGE table fails 3483# 3484DROP TABLE IF EXISTS m1, t1; 3485CREATE TABLE t1 (c1 INT); 3486CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); 3487LOCK TABLE m1 WRITE; 3488ALTER TABLE m1 ADD INDEX (c1); 3489UNLOCK TABLES; 3490DROP TABLE m1, t1; 3491# 3492# Locking the merge table won't implicitly lock children. 3493# 3494CREATE TABLE t1 (c1 INT); 3495CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); 3496LOCK TABLE m1 WRITE; 3497ALTER TABLE t1 ADD INDEX (c1); 3498ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 3499LOCK TABLE m1 WRITE, t1 WRITE; 3500ALTER TABLE t1 ADD INDEX (c1); 3501UNLOCK TABLES; 3502DROP TABLE m1, t1; 3503# 3504# Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter" 3505# 3506drop tables if exists t1, m1, m2; 3507create table t1 (i int) engine=myisam; 3508create table m1 (i int) engine=mrg_myisam union=(t1) insert_method=first; 3509create table m2 like m1; 3510# Table definitions should match 3511show create table m1; 3512Table Create Table 3513m1 CREATE TABLE `m1` ( 3514 `i` int(11) DEFAULT NULL 3515) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`) 3516show create table m2; 3517Table Create Table 3518m2 CREATE TABLE `m2` ( 3519 `i` int(11) DEFAULT NULL 3520) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`) 3521drop tables m1, m2, t1; 3522# 3523# Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()" 3524# Check that unique_table() works correctly for merge tables. 3525# 3526drop table if exists t1, t2, t3, m1, m2; 3527create table t1 (a int); 3528create table t2 (a int); 3529create table t3 (b int); 3530create view v1 as select * from t3,t1; 3531create table m1 (a int) engine=merge union (t1, t2) insert_method=last; 3532create table m2 (a int) engine=merge union (t1, t2) insert_method=first; 3533create temporary table tmp (b int); 3534insert into tmp (b) values (1); 3535insert into t1 (a) values (1); 3536insert into t3 (b) values (1); 3537insert into m1 (a) values ((select max(a) from m1)); 3538ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3539insert into m1 (a) values ((select max(a) from m2)); 3540ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3541insert into m1 (a) values ((select max(a) from t1)); 3542ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3543insert into m1 (a) values ((select max(a) from t2)); 3544ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3545insert into m1 (a) values ((select max(a) from t3, m1)); 3546ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3547insert into m1 (a) values ((select max(a) from t3, m2)); 3548ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3549insert into m1 (a) values ((select max(a) from t3, t1)); 3550ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3551insert into m1 (a) values ((select max(a) from t3, t2)); 3552ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3553insert into m1 (a) values ((select max(a) from tmp, m1)); 3554ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3555insert into m1 (a) values ((select max(a) from tmp, m2)); 3556ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3557insert into m1 (a) values ((select max(a) from tmp, t1)); 3558ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3559insert into m1 (a) values ((select max(a) from tmp, t2)); 3560ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3561insert into m1 (a) values ((select max(a) from v1)); 3562ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. 3563insert into m1 (a) values ((select max(a) from tmp, v1)); 3564ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. 3565update m1 set a = ((select max(a) from m1)); 3566ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3567update m1 set a = ((select max(a) from m2)); 3568ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3569update m1 set a = ((select max(a) from t1)); 3570ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3571update m1 set a = ((select max(a) from t2)); 3572ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3573update m1 set a = ((select max(a) from t3, m1)); 3574ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3575update m1 set a = ((select max(a) from t3, m2)); 3576ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3577update m1 set a = ((select max(a) from t3, t1)); 3578ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3579update m1 set a = ((select max(a) from t3, t2)); 3580ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3581update m1 set a = ((select max(a) from tmp, m1)); 3582ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3583update m1 set a = ((select max(a) from tmp, m2)); 3584ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3585update m1 set a = ((select max(a) from tmp, t1)); 3586ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3587update m1 set a = ((select max(a) from tmp, t2)); 3588ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3589update m1 set a = ((select max(a) from v1)); 3590ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. 3591update m1 set a = ((select max(a) from tmp, v1)); 3592ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. 3593delete from m1 where a = (select max(a) from m1); 3594ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3595delete from m1 where a = (select max(a) from m2); 3596ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3597delete from m1 where a = (select max(a) from t1); 3598ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3599delete from m1 where a = (select max(a) from t2); 3600ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3601delete from m1 where a = (select max(a) from t3, m1); 3602ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3603delete from m1 where a = (select max(a) from t3, m2); 3604ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3605delete from m1 where a = (select max(a) from t3, t1); 3606ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3607delete from m1 where a = (select max(a) from t3, t2); 3608ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3609delete from m1 where a = (select max(a) from tmp, m1); 3610ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3611delete from m1 where a = (select max(a) from tmp, m2); 3612ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3613delete from m1 where a = (select max(a) from tmp, t1); 3614ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3615delete from m1 where a = (select max(a) from tmp, t2); 3616ERROR HY000: You can't specify target table 'm1' for update in FROM clause 3617delete from m1 where a = (select max(a) from v1); 3618ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'. 3619delete from m1 where a = (select max(a) from tmp, v1); 3620ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'. 3621drop view v1; 3622drop temporary table tmp; 3623drop table t1, t2, t3, m1, m2; 3624# 3625# Bug#56494 Segfault in upgrade_shared_lock_to_exclusive() for 3626# REPAIR of merge table 3627# 3628DROP TABLE IF EXISTS t1, t2, t_not_exists; 3629CREATE TABLE t1(a INT); 3630ALTER TABLE t1 engine= MERGE UNION (t_not_exists); 3631ANALYZE TABLE t1; 3632Table Op Msg_type Msg_text 3633test.t1 analyze Error Table 'test.t_not_exists' doesn't exist 3634test.t1 analyze Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3635test.t1 analyze error Corrupt 3636CHECK TABLE t1; 3637Table Op Msg_type Msg_text 3638test.t1 check Error Table 'test.t_not_exists' is differently defined or of non-MyISAM type or doesn't exist 3639test.t1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3640test.t1 check error Corrupt 3641CHECKSUM TABLE t1; 3642Table Checksum 3643test.t1 NULL 3644Warnings: 3645Error 1146 Table 'test.t_not_exists' doesn't exist 3646Error 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3647OPTIMIZE TABLE t1; 3648Table Op Msg_type Msg_text 3649test.t1 optimize Error Table 'test.t_not_exists' doesn't exist 3650test.t1 optimize Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3651test.t1 optimize error Corrupt 3652REPAIR TABLE t1; 3653Table Op Msg_type Msg_text 3654test.t1 repair Error Table 'test.t_not_exists' is differently defined or of non-MyISAM type or doesn't exist 3655test.t1 repair Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3656test.t1 repair error Corrupt 3657REPAIR TABLE t1 USE_FRM; 3658Table Op Msg_type Msg_text 3659test.t1 repair Warning Can't open table 3660test.t1 repair error Corrupt 3661DROP TABLE t1; 3662CREATE TABLE t1(a INT); 3663CREATE TABLE t2(a INT) engine= MERGE UNION (t1); 3664REPAIR TABLE t2 USE_FRM; 3665Table Op Msg_type Msg_text 3666test.t2 repair note The storage engine for the table doesn't support repair 3667DROP TABLE t1, t2; 3668# 3669# Bug#57002 Assert in upgrade_shared_lock_to_exclusive() 3670# for ALTER TABLE + MERGE tables 3671# 3672DROP TABLE IF EXISTS t1, m1; 3673CREATE TABLE t1(a INT) engine=myisam; 3674CREATE TABLE m1(a INT) engine=merge UNION(t1); 3675LOCK TABLES t1 READ, m1 WRITE; 3676ALTER TABLE t1 engine=myisam; 3677ERROR HY000: Table 't1' was locked with a READ lock and can't be updated 3678UNLOCK TABLES; 3679DROP TABLE m1, t1; 3680# 3681# Test for bug #11754210 - "45777: CHECK TABLE DOESN'T SHOW ALL 3682# PROBLEMS FOR MERGE TABLE COMPLIANCE IN 5.1" 3683# 3684drop tables if exists t1, t2, t3, t4, m1; 3685create table t1(id int) engine=myisam; 3686create view t3 as select 1 as id; 3687create table t4(id int) engine=memory; 3688create table m1(id int) engine=merge union=(t1,t2,t3,t4); 3689select * from m1; 3690ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3691# The below CHECK and REPAIR TABLE statements should 3692# report all problems with underlying tables: 3693# - absence of 't2', 3694# - missing base table for 't3', 3695# - wrong engine of 't4'. 3696check table m1; 3697Table Op Msg_type Msg_text 3698test.m1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist 3699test.m1 check Error Table 'test.t3' is differently defined or of non-MyISAM type or doesn't exist 3700test.m1 check Error Table 'test.t4' is differently defined or of non-MyISAM type or doesn't exist 3701test.m1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3702test.m1 check error Corrupt 3703repair table m1; 3704Table Op Msg_type Msg_text 3705test.m1 repair Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist 3706test.m1 repair Error Table 'test.t3' is differently defined or of non-MyISAM type or doesn't exist 3707test.m1 repair Error Table 'test.t4' is differently defined or of non-MyISAM type or doesn't exist 3708test.m1 repair Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3709test.m1 repair error Corrupt 3710# Clean-up. 3711drop tables m1, t1, t4; 3712drop view t3; 3713End of 5.5 tests 3714# 3715# Additional coverage for refactoring which is made as part 3716# of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege 3717# to allow temp table operations". 3718# 3719# Check that prelocking works correctly for various variants of 3720# merge tables. 3721drop table if exists t1, t2, m1; 3722drop function if exists f1; 3723create table t1 (j int); 3724insert into t1 values (1); 3725create function f1() returns int return (select count(*) from m1); 3726create temporary table t2 (a int) engine=myisam; 3727insert into t2 values (1); 3728create temporary table m1 (a int) engine=merge union=(t2); 3729select f1() from t1; 3730f1() 37311 3732drop tables t2, m1; 3733create table t2 (a int) engine=myisam; 3734insert into t2 values (1); 3735create table m1 (a int) engine=merge union=(t2); 3736select f1() from t1; 3737f1() 37381 3739drop table m1; 3740create temporary table m1 (a int) engine=merge union=(t2); 3741select f1() from t1; 3742f1() 37431 3744drop tables t1, t2, m1; 3745drop function f1; 3746# 3747# Check that REPAIR/CHECK and CHECKSUM statements work correctly 3748# for various variants of merge tables. 3749create table t1 (a int) engine=myisam; 3750insert into t1 values (1); 3751create table m1 (a int) engine=merge union=(t1); 3752check table m1; 3753Table Op Msg_type Msg_text 3754test.m1 check status OK 3755repair table m1; 3756Table Op Msg_type Msg_text 3757test.m1 repair note The storage engine for the table doesn't support repair 3758checksum table m1; 3759Table Checksum 3760test.m1 3459908756 3761drop tables t1, m1; 3762create temporary table t1 (a int) engine=myisam; 3763insert into t1 values (1); 3764create temporary table m1 (a int) engine=merge union=(t1); 3765check table m1; 3766Table Op Msg_type Msg_text 3767test.m1 check status OK 3768repair table m1; 3769Table Op Msg_type Msg_text 3770test.m1 repair note The storage engine for the table doesn't support repair 3771checksum table m1; 3772Table Checksum 3773test.m1 3459908756 3774drop tables t1, m1; 3775create table t1 (a int) engine=myisam; 3776insert into t1 values (1); 3777create temporary table m1 (a int) engine=merge union=(t1); 3778check table m1; 3779Table Op Msg_type Msg_text 3780test.m1 check status OK 3781repair table m1; 3782Table Op Msg_type Msg_text 3783test.m1 repair note The storage engine for the table doesn't support repair 3784checksum table m1; 3785Table Checksum 3786test.m1 3459908756 3787drop tables t1, m1; 3788DROP TABLE IF EXISTS t1; 3789DROP TABLE IF EXISTS m1; 3790DROP TRIGGER IF EXISTS trg1; 3791DROP TABLE IF EXISTS q1; 3792DROP TABLE IF EXISTS q2; 3793CREATE TABLE t1(a INT); 3794CREATE TABLE m1(a INT) ENGINE = MERGE UNION (q1, q2); 3795CREATE TRIGGER trg1 BEFORE DELETE ON t1 3796FOR EACH ROW 3797INSERT INTO m1 VALUES (1); 3798DELETE FROM t1; 3799ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 3800DROP TRIGGER trg1; 3801DROP TABLE t1; 3802DROP TABLE m1; 3803# 3804# Test for bug #11764786 - 57657: TEMPORARY MERGE TABLE WITH TEMPORARY 3805# UNDERLYING TABLE, IS BROKEN BY ALTER. 3806# 3807DROP TABLES IF EXISTS t1, t2, t3, t4; 3808CREATE TEMPORARY TABLE t1(i INT) ENGINE= MyISAM; 3809CREATE TEMPORARY TABLE t2(i INT) ENGINE= MERGE UNION= (t1) INSERT_METHOD= LAST; 3810ALTER TABLE t2 INSERT_METHOD= FIRST; 3811CHECK TABLE t2; 3812Table Op Msg_type Msg_text 3813test.t2 check status OK 3814CREATE TABLE t3(i INT) ENGINE= MyISAM; 3815CREATE TABLE t4(i int) ENGINE= MERGE UNION= (t3) INSERT_METHOD= LAST; 3816ALTER TABLE t4 INSERT_METHOD= FIRST; 3817CHECK TABLE t4; 3818Table Op Msg_type Msg_text 3819test.t4 check status OK 3820# Clean-up 3821DROP TABLES t1, t2, t3, t4; 3822# End of bug #11764786 - 57657 3823