1
drop table if exists t1,t2,t3,t4,t5,t6;
2
drop database if exists mysqltest;
3
create table t1 (a int not null primary key auto_increment, message char(20));
4
create table t2 (a int not null primary key auto_increment, message char(20));
5
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
6
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
7
create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2);
16
select * from t3 order by a desc;
25
insert into t1 select NULL,message from t2;
26
insert into t2 select NULL,message from t1;
27
insert into t1 select NULL,message from t2;
28
insert into t2 select NULL,message from t1;
29
insert into t1 select NULL,message from t2;
30
insert into t2 select NULL,message from t1;
31
insert into t1 select NULL,message from t2;
32
insert into t2 select NULL,message from t1;
33
insert into t1 select NULL,message from t2;
34
insert into t2 select NULL,message from t1;
35
insert into t1 select NULL,message from t2;
36
create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,test.t2);
37
explain select * from t3 where a < 10;
38
id select_type table type possible_keys key key_len ref rows Extra
39
1 SIMPLE t3 range a a 4 NULL 18 Using where
40
explain select * from t3 where a > 10 and a < 20;
41
id select_type table type possible_keys key key_len ref rows Extra
42
1 SIMPLE t3 range a a 4 NULL 17 Using where
43
select * from t3 where a = 10;
47
select * from t3 where a < 10;
67
select * from t3 where a > 10 and a < 20;
87
explain select a from t3 order by a desc limit 10;
88
id select_type table type possible_keys key key_len ref rows Extra
89
1 SIMPLE t3 index NULL a 4 NULL 1131 Using index
90
select a from t3 order by a desc limit 10;
102
select a from t3 order by a desc limit 300,10;
114
delete from t3 where a=3;
115
select * from t3 where a < 10;
133
delete from t3 where a >= 6 and a <= 8;
134
select * from t3 where a < 10;
146
update t3 set a=3 where a=9;
147
select * from t3 where a < 10;
159
update t3 set a=6 where a=7;
160
select * from t3 where a < 10;
172
show create table t3;
174
t3 CREATE TABLE `t3` (
175
`a` int(11) NOT NULL,
176
`b` char(20) DEFAULT NULL,
178
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
179
create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2);
181
ERROR HY000: All tables in the MERGE table are not identically defined
182
alter table t4 add column c int;
183
ERROR HY000: All tables in the MERGE table are not identically defined
186
ERROR HY000: All tables in the MERGE table are not identically defined
187
create database mysqltest;
188
create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
189
create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6);
190
show create table t5;
192
t5 CREATE TABLE `t5` (
193
`a` int(11) NOT NULL,
194
`b` char(20) DEFAULT NULL,
196
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`)
197
alter table t5 engine=myisam;
198
drop table t5, mysqltest.t6;
199
drop database mysqltest;
200
drop table t4,t3,t1,t2;
201
create table t1 (c char(10)) engine=myisam;
202
create table t2 (c char(10)) engine=myisam;
203
create table t3 (c char(10)) union=(t1,t2) engine=merge;
204
insert into t1 (c) values ('test1');
205
insert into t1 (c) values ('test1');
206
insert into t1 (c) values ('test1');
207
insert into t2 (c) values ('test2');
208
insert into t2 (c) values ('test2');
209
insert into t2 (c) values ('test2');
226
delete from t3 where 1=1;
232
CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr));
233
CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr));
234
CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
235
ENGINE=MERGE UNION=(t1,t2);
238
INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17);
239
INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32);
240
INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37);
241
INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30);
242
SELECT * from t3 where incr in (1,2,3,4) order by othr;
248
alter table t3 UNION=(t1);
249
select count(*) from t3;
252
alter table t3 UNION=(t1,t2);
253
select count(*) from t3;
256
alter table t3 ENGINE=MYISAM;
257
select count(*) from t3;
261
CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
262
ENGINE=MERGE UNION=(t1,t2);
263
show create table t3;
265
t3 CREATE TABLE `t3` (
266
`incr` int(11) NOT NULL,
267
`othr` int(11) NOT NULL,
269
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
270
alter table t3 drop primary key;
271
show create table t3;
273
t3 CREATE TABLE `t3` (
274
`incr` int(11) NOT NULL,
275
`othr` int(11) NOT NULL
276
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
278
create table t1 (a int not null, key(a)) engine=merge;
282
create table t1 (a int not null, b int not null, key(a,b));
283
create table t2 (a int not null, b int not null, key(a,b));
284
create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2);
285
insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6);
286
insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6);
288
select * from t3 where a=1 order by b limit 2;
293
create table t1 (a int not null, b int not null auto_increment, primary key(a,b));
294
create table t2 (a int not null, b int not null auto_increment, primary key(a,b));
295
create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO;
296
create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO;
297
create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
298
create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
299
show create table t3;
301
t3 CREATE TABLE `t3` (
302
`a` int(11) NOT NULL,
303
`b` int(11) NOT NULL,
305
) ENGINE=PBXT DEFAULT CHARSET=latin1
306
show create table t4;
308
t4 CREATE TABLE `t4` (
309
`a` int(11) NOT NULL,
310
`b` int(11) NOT NULL,
312
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
313
show create table t5;
315
t5 CREATE TABLE `t5` (
316
`a` int(11) NOT NULL,
317
`b` int(11) NOT NULL AUTO_INCREMENT,
318
PRIMARY KEY (`a`,`b`)
319
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`)
320
show create table t6;
322
t6 CREATE TABLE `t6` (
323
`a` int(11) NOT NULL,
324
`b` int(11) NOT NULL AUTO_INCREMENT,
325
PRIMARY KEY (`a`,`b`)
326
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
327
insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
328
insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
329
select * from t3 order by b,a limit 3;
331
select * from t4 order by b,a limit 3;
336
select * from t5 order by b,a limit 3,3;
341
select * from t6 order by b,a limit 6,3;
345
insert into t5 values (5,1),(5,2);
346
insert into t6 values (6,1),(6,2);
347
select * from t1 order by a,b;
355
select * from t2 order by a,b;
363
select * from t4 order by a,b;
377
insert into t3 values (3,1),(3,2),(3,3),(3,4);
378
select * from t3 order by a,b;
384
alter table t4 UNION=(t1,t2,t3);
385
show create table t4;
387
t4 CREATE TABLE `t4` (
388
`a` int(11) NOT NULL,
389
`b` int(11) NOT NULL,
391
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`,`t3`)
392
select * from t4 order by a,b;
410
alter table t4 INSERT_METHOD=FIRST;
411
show create table t4;
413
t4 CREATE TABLE `t4` (
414
`a` int(11) NOT NULL,
415
`b` int(11) NOT NULL,
417
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`)
418
insert into t4 values (4,1),(4,2);
419
select * from t1 order by a,b;
429
select * from t2 order by a,b;
437
select * from t3 order by a,b;
443
select * from t4 order by a,b;
463
select * from t5 order by a,b;
482
insert into t5 values (1,NULL),(5,NULL);
483
insert into t6 values (2,NULL),(6,NULL);
484
select * from t1 order by a,b;
496
select * from t2 order by a,b;
506
select * from t5 order by a,b;
526
select * from t6 order by a,b;
546
insert into t1 values (99,NULL);
547
select * from t4 where a+0 > 90;
550
insert t5 values (1,1);
551
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
552
insert t6 values (2,1);
553
ERROR 23000: Duplicate entry '2-1' for key 'PRIMARY'
554
insert t5 values (1,1) on duplicate key update b=b+10;
555
insert t6 values (2,1) on duplicate key update b=b+20;
556
select * from t5 where a < 3;
568
drop table t6, t5, t4, t3, t2, t1;
569
CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM;
570
INSERT INTO t1 VALUES (1,1), (2,1);
571
CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM;
572
INSERT INTO t2 VALUES (1,2), (2,2);
573
CREATE 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);
574
select max(b) from t3 where a = 2;
577
select max(b) from t1 where a = 2;
581
create table t1 (a int not null);
582
create table t2 (a int not null);
583
insert into t1 values (1);
584
insert into t2 values (2);
585
create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
590
create temporary table t4 (a int not null);
591
create temporary table t5 (a int not null);
592
insert into t4 values (1);
593
insert into t5 values (2);
594
create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5);
599
drop table t6, t3, t1, t2, t4, t5;
601
fileset_id tinyint(3) unsigned NOT NULL default '0',
602
file_code varchar(32) NOT NULL default '',
603
fileset_root_id tinyint(3) unsigned NOT NULL default '0',
604
PRIMARY KEY (fileset_id,file_code),
605
KEY files (fileset_id,fileset_root_id)
607
INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1),
608
(2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1),
609
(2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1);
611
fileset_id tinyint(3) unsigned NOT NULL default '0',
612
file_code varchar(32) NOT NULL default '',
613
fileset_root_id tinyint(3) unsigned NOT NULL default '0',
614
PRIMARY KEY (fileset_id,file_code),
615
KEY files (fileset_id,fileset_root_id)
616
) ENGINE=MRG_MyISAM UNION=(t1);
617
EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2
618
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
619
id select_type table type possible_keys key key_len ref rows Extra
620
1 SIMPLE t2 range PRIMARY PRIMARY 35 NULL 5 Using where
621
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
622
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
623
id select_type table type possible_keys key key_len ref rows Extra
624
1 SIMPLE t2 range PRIMARY,files PRIMARY 35 NULL 5 Using where
625
EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2
626
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
627
id select_type table type possible_keys key key_len ref rows Extra
628
1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 5 Using where
629
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
630
AND file_code = '0000000115' LIMIT 1;
631
id select_type table type possible_keys key key_len ref rows Extra
632
1 SIMPLE t2 ref PRIMARY,files PRIMARY 35 const,const 1 Using where
634
create table t1 (x int, y int, index xy(x, y));
635
create table t2 (x int, y int, index xy(x, y));
636
create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2);
637
insert into t1 values(1, 2);
638
insert into t2 values(1, 3);
639
select * from t3 where x = 1 and y < 5 order by y;
643
select * from t3 where x = 1 and y < 5 order by y desc;
648
create table t1 (a int);
649
create table t2 (a int);
650
insert into t1 values (0);
651
insert into t2 values (1);
652
create table t3 engine=merge union=(t1, t2) select * from t1;
653
ERROR HY000: You can't specify target table 't1' for update in FROM clause
654
create table t3 engine=merge union=(t1, t2) select * from t2;
655
ERROR HY000: You can't specify target table 't2' for update in FROM clause
656
create table t3 engine=merge union=(t1, t2) select (select max(a) from t2);
657
ERROR HY000: You can't specify target table 't2' for update in FROM clause
663
) engine=merge union=(t2,t3);
674
insert into t2 values ( null, '');
675
insert into t2 values ( 9999999999.999, '');
676
insert into t3 select * from t2;
677
select min(a), max(a) from t1;
679
9999999999.9990 9999999999.9990
681
select min(a), max(a) from t1;
683
9999999999.9990 9999999999.9990
684
drop table t1, t2, t3;
685
create table t1 (a int,b int,c int, index (a,b,c));
686
create table t2 (a int,b int,c int, index (a,b,c));
687
create table t3 (a int,b int,c int, index (a,b,c))
688
engine=merge union=(t1 ,t2);
689
insert into t1 (a,b,c) values (1,1,0),(1,2,0);
690
insert into t2 (a,b,c) values (1,1,1),(1,2,1);
691
explain select a,b,c from t3 force index (a) where a=1 order by a,b,c;
692
id select_type table type possible_keys key key_len ref rows Extra
693
1 SIMPLE t3 ref a a 5 const 2 Using where; Using index
694
select a,b,c from t3 force index (a) where a=1 order by a,b,c;
700
explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
701
id select_type table type possible_keys key key_len ref rows Extra
702
1 SIMPLE t3 ref a a 5 const 2 Using where; Using index
703
select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
710
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
711
t3 1 a 1 a A NULL NULL NULL YES BTREE
712
t3 1 a 2 b A NULL NULL NULL YES BTREE
713
t3 1 a 3 c A NULL NULL NULL YES BTREE
714
drop table t1, t2, t3;
715
CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) )
717
CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) )
718
ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST;
719
INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2;
720
INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3;
725
create table t1(a int);
726
create table t2(a int);
727
insert into t1 values (1);
728
insert into t2 values (2);
729
create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
734
insert t2 select * from t2;
739
insert t3 select * from t1;
746
insert t1 select * from t3;
770
Table Op Msg_type Msg_text
771
test.t1 check status OK
772
test.t2 check status OK
773
drop table t1, t2, t3;
774
create table t1 (b bit(1));
775
create table t2 (b bit(1));
776
create table tm (b bit(1)) engine = merge union = (t1,t2);
779
drop table tm, t1, t2;
780
create table t1 (a int) insert_method = last engine = merge;
781
insert into t1 values (1);
782
ERROR HY000: Table 't1' is read only
783
create table t2 (a int) engine = myisam;
784
alter table t1 union (t2);
785
insert into t1 values (1);
786
alter table t1 insert_method = no;
787
insert into t1 values (1);
788
ERROR HY000: Table 't1' is read only