merge.result 52.5 KB
Newer Older
1 2
set global storage_engine=myisam;
set session storage_engine=myisam;
unknown's avatar
unknown committed
3
drop table if exists t1,t2,t3,t4,t5,t6;
4
drop database if exists mysqltest;
unknown's avatar
unknown committed
5 6 7 8
create table t1 (a int not null primary key auto_increment, message char(20));
create table t2 (a int not null primary key auto_increment, message char(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
unknown's avatar
unknown committed
9
create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2);
unknown's avatar
unknown committed
10
select * from t3;
11 12 13 14 15 16 17
a	b
1	Testing
2	table
3	t1
1	Testing
2	table
3	t2
unknown's avatar
unknown committed
18
select * from t3 order by a desc;
19 20 21 22 23 24 25
a	b
3	t1
3	t2
2	table
2	table
1	Testing
1	Testing
unknown's avatar
unknown committed
26 27 28 29 30 31 32 33 34 35 36 37
drop table t3;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
unknown's avatar
unknown committed
38
create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,test.t2);
unknown's avatar
unknown committed
39
explain select * from t3 where a < 10;
unknown's avatar
unknown committed
40
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
41
1	SIMPLE	t3	range	a	a	4	NULL	18	Using where
unknown's avatar
unknown committed
42
explain select * from t3 where a > 10 and a < 20;
unknown's avatar
unknown committed
43
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
44
1	SIMPLE	t3	range	a	a	4	NULL	17	Using where
unknown's avatar
unknown committed
45
select * from t3 where a = 10;
46 47 48
a	b
10	Testing
10	Testing
unknown's avatar
unknown committed
49
select * from t3 where a < 10;
50 51 52 53 54 55 56 57 58 59 60 61
a	b
1	Testing
1	Testing
2	table
2	table
3	t1
3	t2
4	Testing
4	Testing
5	table
5	table
6	t2
62
6	t1
63 64 65 66 67 68
7	Testing
7	Testing
8	table
8	table
9	t2
9	t2
unknown's avatar
unknown committed
69
select * from t3 where a > 10 and a < 20;
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
a	b
11	table
11	table
12	t1
12	t1
13	Testing
13	Testing
14	table
14	table
15	t2
15	t2
16	Testing
16	Testing
17	table
17	table
18	t2
18	t2
19	Testing
19	Testing
unknown's avatar
unknown committed
89
explain select a from t3 order by a desc limit 10;
unknown's avatar
unknown committed
90
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
91
1	SIMPLE	t3	index	NULL	a	4	NULL	10	Using index
unknown's avatar
unknown committed
92
select a from t3 order by a desc limit 10;
93 94 95 96 97 98 99 100 101 102 103
a
699
698
697
696
695
694
693
692
691
690
unknown's avatar
unknown committed
104
select a from t3 order by a desc limit 300,10;
105 106 107 108 109 110 111 112 113 114 115
a
416
415
415
414
414
413
413
412
412
411
unknown's avatar
unknown committed
116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173
delete from t3 where a=3;
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
4	Testing
4	Testing
5	table
5	table
6	t2
6	t1
7	Testing
7	Testing
8	table
8	table
9	t2
9	t2
delete from t3 where a >= 6 and a <= 8;
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
4	Testing
4	Testing
5	table
5	table
9	t2
9	t2
update t3 set a=3 where a=9;
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
3	t2
3	t2
4	Testing
4	Testing
5	table
5	table
update t3 set a=6 where a=7;
select * from t3 where a < 10;
a	b
1	Testing
1	Testing
2	table
2	table
3	t2
3	t2
4	Testing
4	Testing
5	table
5	table
unknown's avatar
unknown committed
174
show create table t3;
unknown's avatar
unknown committed
175 176
Table	Create Table
t3	CREATE TABLE `t3` (
unknown's avatar
unknown committed
177
  `a` int(11) NOT NULL,
178
  `b` char(20) DEFAULT NULL,
unknown's avatar
unknown committed
179
  KEY `a` (`a`)
unknown's avatar
unknown committed
180 181
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2);
unknown's avatar
unknown committed
182
select * from t4;
183
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
184
alter table t4 add column c int;
185
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
unknown's avatar
unknown committed
186 187
flush tables;
select * from t4;
188
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
189 190
create database mysqltest;
create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
unknown's avatar
unknown committed
191
create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6);
192 193 194
show create table t5;
Table	Create Table
t5	CREATE TABLE `t5` (
unknown's avatar
unknown committed
195
  `a` int(11) NOT NULL,
196
  `b` char(20) DEFAULT NULL,
197
  KEY `a` (`a`)
unknown's avatar
unknown committed
198 199
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`)
alter table t5 engine=myisam;
200 201
drop table t5, mysqltest.t6;
drop database mysqltest;
202
drop table t4,t3,t1,t2;
unknown's avatar
unknown committed
203 204 205
create table t1 (c char(10)) engine=myisam;
create table t2 (c char(10)) engine=myisam;
create table t3 (c char(10)) union=(t1,t2) engine=merge;
unknown's avatar
unknown committed
206 207 208 209 210 211 212
insert into t1 (c) values ('test1');
insert into t1 (c) values ('test1');
insert into t1 (c) values ('test1');
insert into t2 (c) values ('test2');
insert into t2 (c) values ('test2');
insert into t2 (c) values ('test2');
select * from t3;
213 214 215 216 217 218 219
c
test1
test1
test1
test2
test2
test2
unknown's avatar
unknown committed
220
select * from t3;
221 222 223 224 225 226 227
c
test1
test1
test1
test2
test2
test2
unknown's avatar
unknown committed
228 229
delete from t3 where 1=1;
select * from t3;
230
c
unknown's avatar
unknown committed
231
select * from t1;
232
c
unknown's avatar
unknown committed
233 234 235 236
drop table t3,t2,t1;
CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr));
CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr));
CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
unknown's avatar
unknown committed
237
ENGINE=MERGE UNION=(t1,t2);
unknown's avatar
unknown committed
238
SELECT * from t3;
239
incr	othr
unknown's avatar
unknown committed
240 241 242 243 244
INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17);
INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32);
INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37);
INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30);
SELECT * from t3 where incr in (1,2,3,4) order by othr;
245 246 247 248 249
incr	othr
1	10
2	24
4	33
3	53
unknown's avatar
unknown committed
250 251
alter table t3 UNION=(t1);
select count(*) from t3;
unknown's avatar
unknown committed
252 253
count(*)
10
unknown's avatar
unknown committed
254 255
alter table t3 UNION=(t1,t2);
select count(*) from t3;
unknown's avatar
unknown committed
256 257
count(*)
20
unknown's avatar
unknown committed
258
alter table t3 ENGINE=MYISAM;
unknown's avatar
unknown committed
259
select count(*) from t3;
unknown's avatar
unknown committed
260 261
count(*)
20
unknown's avatar
unknown committed
262 263
drop table t3;
CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
unknown's avatar
unknown committed
264
ENGINE=MERGE UNION=(t1,t2);
unknown's avatar
unknown committed
265
show create table t3;
266 267
Table	Create Table
t3	CREATE TABLE `t3` (
unknown's avatar
unknown committed
268 269
  `incr` int(11) NOT NULL,
  `othr` int(11) NOT NULL,
270
  PRIMARY KEY (`incr`)
unknown's avatar
unknown committed
271
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
unknown's avatar
unknown committed
272 273
alter table t3 drop primary key;
show create table t3;
274 275
Table	Create Table
t3	CREATE TABLE `t3` (
unknown's avatar
unknown committed
276 277
  `incr` int(11) NOT NULL,
  `othr` int(11) NOT NULL
unknown's avatar
unknown committed
278
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
unknown's avatar
unknown committed
279
drop table t3,t2,t1;
unknown's avatar
unknown committed
280
create table t1 (a int not null, key(a)) engine=merge;
unknown's avatar
unknown committed
281
select * from t1;
unknown's avatar
unknown committed
282
ERROR HY000: Got error 124 from storage engine
unknown's avatar
unknown committed
283 284 285
drop table t1;
create table t1 (a int not null, b int not null, key(a,b));
create table t2 (a int not null, b int not null, key(a,b));
unknown's avatar
unknown committed
286
create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2);
unknown's avatar
unknown committed
287 288 289 290
insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6);
insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6);
flush tables;
select * from t3 where a=1 order by b limit 2;
291 292 293
a	b
1	1
1	2
unknown's avatar
unknown committed
294
drop table t3,t1,t2;
unknown's avatar
unknown committed
295 296
create table t1 (a int not null, b int not null auto_increment, primary key(a,b));
create table t2 (a int not null, b int not null auto_increment, primary key(a,b));
unknown's avatar
unknown committed
297
create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO;
unknown's avatar
unknown committed
298 299 300
create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO;
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;
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;
unknown's avatar
unknown committed
301 302 303
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
unknown's avatar
unknown committed
304 305
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
unknown's avatar
unknown committed
306
  KEY `a` (`a`,`b`)
unknown's avatar
unknown committed
307
) ENGINE=MyISAM DEFAULT CHARSET=latin1
unknown's avatar
unknown committed
308 309 310
show create table t4;
Table	Create Table
t4	CREATE TABLE `t4` (
unknown's avatar
unknown committed
311 312
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
unknown's avatar
unknown committed
313
  KEY `a` (`a`,`b`)
unknown's avatar
unknown committed
314
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`)
unknown's avatar
unknown committed
315 316 317
show create table t5;
Table	Create Table
t5	CREATE TABLE `t5` (
unknown's avatar
unknown committed
318
  `a` int(11) NOT NULL,
319
  `b` int(11) NOT NULL AUTO_INCREMENT,
320
  PRIMARY KEY (`a`,`b`)
unknown's avatar
unknown committed
321
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`)
unknown's avatar
unknown committed
322 323 324
show create table t6;
Table	Create Table
t6	CREATE TABLE `t6` (
unknown's avatar
unknown committed
325
  `a` int(11) NOT NULL,
326
  `b` int(11) NOT NULL AUTO_INCREMENT,
327
  PRIMARY KEY (`a`,`b`)
unknown's avatar
unknown committed
328
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
unknown's avatar
unknown committed
329 330
insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
unknown's avatar
unknown committed
331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389
select * from t3 order by b,a limit 3;
a	b
select * from t4 order by b,a limit 3;
a	b
1	1
2	1
1	2
select * from t5 order by b,a limit 3,3;
a	b
2	2
1	3
2	3
select * from t6 order by b,a limit 6,3;
a	b
1	4
2	4
insert into t5 values (5,1),(5,2);
insert into t6 values (6,1),(6,2);
select * from t1 order by a,b;
a	b
1	1
1	2
1	3
1	4
5	1
5	2
select * from t2 order by a,b;
a	b
2	1
2	2
2	3
2	4
6	1
6	2
select * from t4 order by a,b;
a	b
1	1
1	2
1	3
1	4
2	1
2	2
2	3
2	4
5	1
5	2
6	1
6	2
insert into t3 values (3,1),(3,2),(3,3),(3,4);
select * from t3 order by a,b;
a	b
3	1
3	2
3	3
3	4
alter table t4 UNION=(t1,t2,t3);
show create table t4;
Table	Create Table
t4	CREATE TABLE `t4` (
unknown's avatar
unknown committed
390 391
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
unknown's avatar
unknown committed
392
  KEY `a` (`a`,`b`)
unknown's avatar
unknown committed
393
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`,`t2`,`t3`)
unknown's avatar
unknown committed
394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415
select * from t4 order by a,b;
a	b
1	1
1	2
1	3
1	4
2	1
2	2
2	3
2	4
3	1
3	2
3	3
3	4
5	1
5	2
6	1
6	2
alter table t4 INSERT_METHOD=FIRST;
show create table t4;
Table	Create Table
t4	CREATE TABLE `t4` (
unknown's avatar
unknown committed
416 417
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
unknown's avatar
unknown committed
418
  KEY `a` (`a`,`b`)
unknown's avatar
unknown committed
419
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`)
unknown's avatar
unknown committed
420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480
insert into t4 values (4,1),(4,2);
select * from t1 order by a,b;
a	b
1	1
1	2
1	3
1	4
4	1
4	2
5	1
5	2
select * from t2 order by a,b;
a	b
2	1
2	2
2	3
2	4
6	1
6	2
select * from t3 order by a,b;
a	b
3	1
3	2
3	3
3	4
select * from t4 order by a,b;
a	b
1	1
1	2
1	3
1	4
2	1
2	2
2	3
2	4
3	1
3	2
3	3
3	4
4	1
4	2
5	1
5	2
6	1
6	2
select * from t5 order by a,b;
a	b
1	1
1	2
1	3
1	4
2	1
2	2
2	3
2	4
4	1
4	2
5	1
5	2
6	1
6	2
unknown's avatar
unknown committed
481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547
select 1;
1
1
insert into t5 values (1,NULL),(5,NULL);
insert into t6 values (2,NULL),(6,NULL);
select * from t1 order by a,b;
a	b
1	1
1	2
1	3
1	4
1	5
4	1
4	2
5	1
5	2
5	3
select * from t2 order by a,b;
a	b
2	1
2	2
2	3
2	4
2	5
6	1
6	2
6	3
select * from t5 order by a,b;
a	b
1	1
1	2
1	3
1	4
1	5
2	1
2	2
2	3
2	4
2	5
4	1
4	2
5	1
5	2
5	3
6	1
6	2
6	3
select * from t6 order by a,b;
a	b
1	1
1	2
1	3
1	4
1	5
2	1
2	2
2	3
2	4
2	5
4	1
4	2
5	1
5	2
5	3
6	1
6	2
6	3
548 549 550 551
insert into t1 values (99,NULL);
select * from t4 where a+0 > 90;
a	b
99	1
552
insert t5 values (1,1);
553
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
554
insert t6 values (2,1);
555
ERROR 23000: Duplicate entry '2-1' for key 'PRIMARY'
556 557 558 559 560 561 562 563 564 565 566 567 568 569
insert t5 values (1,1) on duplicate key update b=b+10;
insert t6 values (2,1) on duplicate key update b=b+20;
select * from t5 where a < 3;
a	b
1	2
1	3
1	4
1	5
1	11
2	2
2	3
2	4
2	5
2	21
570
drop table t6, t5, t4, t3, t2, t1;
unknown's avatar
unknown committed
571
CREATE TABLE t1 (  a int(11) NOT NULL default '0',  b int(11) NOT NULL default '0',  PRIMARY KEY  (a,b)) ENGINE=MyISAM;
572
INSERT INTO t1 VALUES (1,1), (2,1);
unknown's avatar
unknown committed
573
CREATE TABLE t2 (  a int(11) NOT NULL default '0',  b int(11) NOT NULL default '0',  PRIMARY KEY  (a,b)) ENGINE=MyISAM;
574
INSERT INTO t2 VALUES (1,2), (2,2);
unknown's avatar
unknown committed
575
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);
576
select max(b) from t3 where a = 2;
577
max(b)
578
2
579 580 581
select max(b) from t1 where a = 2;
max(b)
1
582
drop table t3,t1,t2;
unknown's avatar
unknown committed
583 584 585 586
create table t1 (a int not null);
create table t2 (a int not null);
insert into t1 values (1);
insert into t2 values (2);
unknown's avatar
unknown committed
587
create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
unknown's avatar
unknown committed
588
select * from t3;
589
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
unknown's avatar
unknown committed
590 591 592 593
create temporary table t4 (a int not null);
create temporary table t5 (a int not null);
insert into t4 values (1);
insert into t5 values (2);
unknown's avatar
unknown committed
594
create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5);
unknown's avatar
unknown committed
595 596 597 598
select * from t6;
a
1
2
599
drop table t6, t3, t1, t2, t4, t5;
600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647
create temporary table t1 (a int not null);
create temporary table t2 (a int not null);
insert into t1 values (1);
insert into t2 values (2);
create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
select * from t3;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
drop table t3, t2, t1;
create table t1 (a int not null);
create temporary table t2 (a int not null);
insert into t1 values (1);
insert into t2 values (2);
create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
select * from t3;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
drop table t3;
create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
select * from t3;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
drop table t3, t2, t1;
# CREATE...SELECT is not implemented for MERGE tables.
CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL);
CREATE TEMPORARY TABLE t2 (c1 INT NOT NULL);
CREATE TABLE t3 (c1 INT NOT NULL);
INSERT INTO t3 VALUES (3), (33);
LOCK TABLES t3 READ;
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
INSERT_METHOD=LAST SELECT * FROM t3;
ERROR HY000: 'test.t4' is not BASE TABLE
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
UNLOCK TABLES;
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
INSERT_METHOD=LAST;
INSERT INTO t4 SELECT * FROM t3;
# Alter temporary MERGE table.
ALTER TABLE t4 UNION=(t1);
LOCK TABLES t4 WRITE;
# Alter temporary MERGE table under LOCk tables.
ALTER TABLE t4 UNION=(t1,t2);
UNLOCK TABLES;
# MERGE table and function.
CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);
SELECT * FROM t4 WHERE c1 < f1();
c1
3
DROP FUNCTION f1;
DROP TABLE t4, t3, t2, t1;
648 649 650 651 652 653
CREATE TABLE t1 (
fileset_id tinyint(3) unsigned NOT NULL default '0',
file_code varchar(32) NOT NULL default '',
fileset_root_id tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY  (fileset_id,file_code),
KEY files (fileset_id,fileset_root_id)
unknown's avatar
unknown committed
654
) ENGINE=MyISAM;
655 656 657 658 659 660 661 662 663
INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1),
(2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1),
(2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1);
CREATE TABLE t2 (
fileset_id tinyint(3) unsigned NOT NULL default '0',
file_code varchar(32) NOT NULL default '',
fileset_root_id tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY  (fileset_id,file_code),
KEY files (fileset_id,fileset_root_id)
unknown's avatar
unknown committed
664
) ENGINE=MRG_MyISAM UNION=(t1);
665 666
EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
unknown's avatar
unknown committed
667
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
668
1	SIMPLE	t2	range	PRIMARY	PRIMARY	35	NULL	5	Using where
669 670
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
unknown's avatar
unknown committed
671
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
672
1	SIMPLE	t2	range	PRIMARY,files	PRIMARY	35	NULL	5	Using where
673 674
EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
unknown's avatar
unknown committed
675
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
676
1	SIMPLE	t1	range	PRIMARY,files	PRIMARY	35	NULL	5	Using where
677 678
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
AND file_code = '0000000115' LIMIT 1;
unknown's avatar
unknown committed
679
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
680
1	SIMPLE	t2	const	PRIMARY,files	PRIMARY	35	const,const	1	
681
DROP TABLE t2, t1;
682 683
create table t1 (x int, y int, index xy(x, y));
create table t2 (x int, y int, index xy(x, y));
unknown's avatar
unknown committed
684
create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2);
685 686 687 688 689 690 691 692
insert into t1 values(1, 2);
insert into t2 values(1, 3);
select * from t3 where x = 1 and y < 5 order by y;
x	y
1	2
1	3
select * from t3 where x = 1 and y < 5 order by y desc;
x	y
693 694
1	3
1	2
695
drop table t1,t2,t3;
696 697 698 699 700
create table t1 (a int);
create table t2 (a int);
insert into t1 values (0);
insert into t2 values (1);
create table t3 engine=merge union=(t1, t2) select * from t1;
701
ERROR HY000: 'test.t3' is not BASE TABLE
702
create table t3 engine=merge union=(t1, t2) select * from t2;
703
ERROR HY000: 'test.t3' is not BASE TABLE
unknown's avatar
unknown committed
704
create table t3 engine=merge union=(t1, t2) select (select max(a) from t2);
705
ERROR HY000: 'test.t3' is not BASE TABLE
706
drop table t1, t2;
707
create table t1 (
708
a double(14,4),
709 710 711 712
b varchar(10),
index (a,b)
) engine=merge union=(t2,t3);
create table t2 (
713
a double(14,4),
714 715 716 717
b varchar(10),
index (a,b)
) engine=myisam;
create table t3 (
718
a double(14,4),
719 720 721 722
b varchar(10),
index (a,b)
) engine=myisam;
insert into t2 values ( null, '');
723
insert into t2 values ( 9999999999.999, '');
724 725 726
insert into t3 select * from t2;
select min(a), max(a) from t1;
min(a)	max(a)
727
9999999999.9990	9999999999.9990
728 729 730
flush tables;
select min(a), max(a) from t1;
min(a)	max(a)
731
9999999999.9990	9999999999.9990
732
drop table t1, t2, t3;
unknown's avatar
unknown committed
733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756
create table t1 (a int,b int,c int, index (a,b,c));
create table t2 (a int,b int,c int, index (a,b,c));
create table t3 (a int,b int,c int, index (a,b,c))
engine=merge union=(t1 ,t2);
insert into t1 (a,b,c) values (1,1,0),(1,2,0);
insert into t2 (a,b,c) values (1,1,1),(1,2,1);
explain select a,b,c from t3 force index (a) where a=1 order by a,b,c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ref	a	a	5	const	2	Using where; Using index
select a,b,c from t3 force index (a) where a=1 order by a,b,c;
a	b	c
1	1	0
1	1	1
1	2	0
1	2	1
explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ref	a	a	5	const	2	Using where; Using index
select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
a	b	c
1	2	1
1	2	0
1	1	1
1	1	0
757 758 759 760 761
show index from t3;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t3	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE	
t3	1	a	2	b	A	NULL	NULL	NULL	YES	BTREE	
t3	1	a	3	c	A	NULL	NULL	NULL	YES	BTREE	
unknown's avatar
unknown committed
762
drop table t1, t2, t3;
763 764 765 766 767 768 769 770 771 772
CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) )
ENGINE=MyISAM;
CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) )
ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST;
INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2;
INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3;
SELECT b FROM t2;
b
3
DROP TABLE t1, t2;
773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821
create table t1(a int);
create table t2(a int);
insert into t1 values (1);
insert into t2 values (2);
create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
select * from t3;
a
1
2
insert t2 select * from t2;
select * from t2;
a
2
2
insert t3 select * from t1;
select * from t3;
a
1
1
2
2
insert t1 select * from t3;
select * from t1;
a
1
1
1
1
2
2
select * from t2;
a
2
2
select * from t3;
a
1
1
1
1
2
2
2
2
check table t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
test.t2	check	status	OK
drop table t1, t2, t3;
822 823 824 825
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(2),(1);
CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
SELECT * FROM t2 WHERE a=2;
826
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
827
DROP TABLE t1, t2;
unknown's avatar
unknown committed
828 829 830
CREATE TABLE t1(a INT) ENGINE=MEMORY;
CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1);
SELECT * FROM t2;
831
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
unknown's avatar
unknown committed
832 833 834
DROP TABLE t1, t2;
CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3);
SELECT * FROM t2;
835
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
unknown's avatar
unknown committed
836
DROP TABLE t2;
837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856
CREATE TABLE t1(a INT, b TEXT);
CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, tm1;
CREATE TABLE t1(a SMALLINT, b SMALLINT);
CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, tm1;
CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(a, b));
CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, tm1;
CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(b));
CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, tm1;
857 858 859 860 861 862 863 864 865 866 867 868 869
CREATE TABLE t1(c1 VARCHAR(1));
CREATE TABLE m1 LIKE t1;
ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
SELECT * FROM m1;
c1
DROP TABLE t1, m1;
CREATE TABLE t1(c1 VARCHAR(4), c2 TINYINT, c3 TINYINT, c4 TINYINT,
c5 TINYINT, c6 TINYINT, c7 TINYINT, c8 TINYINT, c9 TINYINT);
CREATE TABLE m1 LIKE t1;
ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
SELECT * FROM m1;
c1	c2	c3	c4	c5	c6	c7	c8	c9
DROP TABLE t1, m1;
870 871 872 873 874 875 876 877 878 879 880
CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci,
b INT, INDEX(a,b));
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
ALTER TABLE t3 ENGINE=MERGE UNION=(t1,t2);
INSERT INTO t1 VALUES ('ss',1);
INSERT INTO t2 VALUES ('ss',2),(0xDF,2);
SELECT COUNT(*) FROM t3 WHERE a=0xDF AND b=2;
COUNT(*)
2
DROP TABLE t1,t2,t3;
unknown's avatar
unknown committed
881 882 883 884 885 886
create table t1 (b bit(1));
create table t2 (b bit(1));
create table tm (b bit(1)) engine = merge union = (t1,t2);
select * from tm;
b
drop table tm, t1, t2;
887 888 889 890 891 892 893 894 895 896 897
create table t1 (a int) insert_method = last engine = merge;
insert into t1 values (1);
ERROR HY000: Table 't1' is read only
create table t2 (a int) engine = myisam;
alter table t1 union (t2);
insert into t1 values (1);
alter table t1 insert_method = no;
insert into t1 values (1);
ERROR HY000: Table 't1' is read only
drop table t2;
drop table t1;
898 899
CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2);
SELECT * FROM tm1;
900
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
901 902
CHECK TABLE tm1;
Table	Op	Msg_type	Msg_text
903
test.tm1	check	Error	Table 'test.t1' doesn't exist
904
test.tm1	check	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
905 906 907
test.tm1	check	error	Corrupt
CREATE TABLE t1(a INT);
SELECT * FROM tm1;
908
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
909 910
CHECK TABLE tm1;
Table	Op	Msg_type	Msg_text
911
test.tm1	check	Error	Table 'test.t2' doesn't exist
912
test.tm1	check	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
913 914 915 916 917 918
test.tm1	check	error	Corrupt
CREATE TABLE t2(a BLOB);
SELECT * FROM tm1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
CHECK TABLE tm1;
Table	Op	Msg_type	Msg_text
919
test.tm1	check	Error	Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
920 921 922 923 924 925 926 927 928
test.tm1	check	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
test.tm1	check	error	Corrupt
ALTER TABLE t2 MODIFY a INT;
SELECT * FROM tm1;
a
CHECK TABLE tm1;
Table	Op	Msg_type	Msg_text
test.tm1	check	status	OK
DROP TABLE tm1, t1, t2;
929 930 931 932 933
CREATE TABLE t1(c1 INT);
CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
ERROR HY000: You can't specify target table 't1' for update in FROM clause
DROP TABLE t1, t2;
934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970
CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM;
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t2;
CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE
UNION(t1);
SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
id	ref	ref
4	4	5
4	4	5
4	4	5
4	4	5
SELECT * FROM t3;
id	ref
1	3
2	1
3	2
4	5
4	4
1	3
2	1
3	2
4	5
4	4
DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
SELECT * FROM t3;
id	ref
1	3
2	1
3	2
4	5
1	3
2	1
3	2
4	5
DROP TABLE t1, t2, t3;
971 972 973 974 975
CREATE TABLE t1(a INT);
CREATE TABLE m1(a INT) ENGINE=MERGE;
SHOW CREATE TABLE m1;
Table	Create Table
m1	CREATE TABLE `m1` (
unknown's avatar
unknown committed
976
  `a` int(11) DEFAULT NULL
977 978 979 980 981 982
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
DROP TABLE m1;
CREATE TABLE m1(a INT) ENGINE=MERGE UNION=();
SHOW CREATE TABLE m1;
Table	Create Table
m1	CREATE TABLE `m1` (
unknown's avatar
unknown committed
983
  `a` int(11) DEFAULT NULL
984 985 986 987 988 989
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
ALTER TABLE m1 UNION=(t1);
ALTER TABLE m1 UNION=();
SHOW CREATE TABLE m1;
Table	Create Table
m1	CREATE TABLE `m1` (
unknown's avatar
unknown committed
990
  `a` int(11) DEFAULT NULL
991 992
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1, m1;
993 994 995 996 997 998 999 1000 1001 1002 1003
CREATE TABLE t1(a INT);
CREATE TABLE t2(a VARCHAR(10));
CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2);
CREATE TABLE m2(a INT) ENGINE=MERGE UNION=(t1);
SELECT * FROM t1;
a
SELECT * FROM m1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
SELECT * FROM m2;
a
DROP TABLE t1, t2, m1, m2;
1004
End of 5.0 tests
1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168
create table t1 (c1 int, index(c1));
create table t2 (c1 int, index(c1)) engine=merge union=(t1);
insert into t1 values (1);
flush tables;
select * from t2;
c1
1
flush tables;
truncate table t1;
insert into t1 values (1);
flush tables;
select * from t2;
c1
1
truncate table t1;
insert into t1 values (1);
drop table t1,t2;
#
# Extra tests for TRUNCATE.
#
# Truncate MERGE table.
CREATE TABLE t1 (c1 INT, INDEX(c1));
CREATE TABLE t2 (c1 INT, INDEX(c1));
CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t3;
c1
1
2
TRUNCATE TABLE t3;
SELECT * FROM t3;
c1
#
# Truncate child table.
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
TRUNCATE TABLE t1;
SELECT * FROM t3;
c1
2
#
# Truncate MERGE table under locked tables.
LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
INSERT INTO t1 VALUES (1);
TRUNCATE TABLE t3;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3;
c1
1
2
#
# Truncate child table under locked tables.
TRUNCATE TABLE t1;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3;
c1
1
2
UNLOCK TABLES;
DROP TABLE t1, t2, t3;
#
# Truncate temporary MERGE table.
CREATE TEMPORARY TABLE t1 (c1 INT, INDEX(c1));
CREATE TEMPORARY TABLE t2 (c1 INT, INDEX(c1));
CREATE TEMPORARY TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t3;
c1
1
2
TRUNCATE TABLE t3;
SELECT * FROM t3;
c1
#
# Truncate temporary child table.
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
TRUNCATE TABLE t1;
SELECT * FROM t3;
c1
2
#
# Truncate temporary MERGE table under locked tables.
INSERT INTO t1 VALUES (1);
CREATE TABLE t4 (c1 INT, INDEX(c1));
LOCK TABLE t4 WRITE;
TRUNCATE TABLE t3;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3;
c1
1
2
#
# Truncate temporary child table under locked tables.
TRUNCATE TABLE t1;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3;
c1
1
2
UNLOCK TABLES;
DROP TABLE t1, t2, t3, t4;
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
REPAIR TABLE t1;
INSERT INTO t2 VALUES (1);
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
DROP TABLE t1, t2;
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
LOCK TABLE t1 WRITE;
INSERT INTO t2 VALUES (1);
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
UNLOCK TABLES;
DROP TABLE t1, t2;
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
LOCK TABLE t1 WRITE;
INSERT INTO t1 VALUES (1);
FLUSH TABLES;
FLUSH TABLES;
SELECT * FROM t1;
c1
UNLOCK TABLES;
DROP TABLE t1;
#
# Extra tests for Bug#26379 - Combination of FLUSH TABLE and
#                             REPAIR TABLE corrupts a MERGE table
#
# CREATE ... SELECT is disabled for MERGE tables.
#
CREATE TABLE t1(c1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
CREATE TABLE t3 ENGINE=MRG_MYISAM INSERT_METHOD=LAST SELECT * FROM t2;
ERROR HY000: Table 't3' is read only
SHOW CREATE TABLE t3;
ERROR 42S02: Table 'test.t3' doesn't exist
CREATE TABLE t3 ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST
SELECT * FROM t2;
ERROR HY000: 'test.t3' is not BASE TABLE
SHOW CREATE TABLE t3;
ERROR 42S02: Table 'test.t3' doesn't exist
DROP TABLE t1, t2;
#
# CREATE ... LIKE
#
# 1. Create like.
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (3);
CREATE TABLE t4 LIKE t3;
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` int(11) DEFAULT NULL
unknown's avatar
unknown committed
1169
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185
INSERT INTO t4 VALUES (4);
ERROR HY000: Table 't4' is read only
DROP TABLE t4;
#
# 1. Create like with locked tables.
LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE;
CREATE TABLE t4 LIKE t3;
SHOW CREATE TABLE t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
INSERT INTO t4 VALUES (4);
ERROR HY000: Table 't4' was not locked with LOCK TABLES
UNLOCK TABLES;
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` int(11) DEFAULT NULL
unknown's avatar
unknown committed
1186
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216
INSERT INTO t4 VALUES (4);
ERROR HY000: Table 't4' is read only
DROP TABLE t4;
#
# Rename child.
#
# 1. Normal rename of non-MERGE table.
CREATE TABLE t4 (c1 INT);
INSERT INTO t4 VALUES (4);
SELECT * FROM t4 ORDER BY c1;
c1
4
RENAME TABLE t4 TO t5;
SELECT * FROM t5 ORDER BY c1;
c1
4
RENAME TABLE t5 TO t4;
SELECT * FROM t4 ORDER BY c1;
c1
4
DROP TABLE t4;
#
# 2. Normal rename.
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
RENAME TABLE t2 TO t5;
SELECT * FROM t3 ORDER BY c1;
1217
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250
RENAME TABLE t5 TO t2;
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
#
# 3. Normal rename with locked tables.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
RENAME TABLE t2 TO t5;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
RENAME TABLE t5 TO t2;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
#
# 4. Alter table rename.
ALTER TABLE t2 RENAME TO t5;
SELECT * FROM t3 ORDER BY c1;
1251
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334
ALTER TABLE t5 RENAME TO t2;
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
#
# 5. Alter table rename with locked tables.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
ALTER TABLE t2 RENAME TO t5;
SELECT * FROM t3 ORDER BY c1;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
ALTER TABLE t5 RENAME TO t2;
ERROR HY000: Table 't5' was not locked with LOCK TABLES
UNLOCK TABLES;
ALTER TABLE t5 RENAME TO t2;
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
#
# Rename parent.
#
# 1. Normal rename with locked tables.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
RENAME TABLE t3 TO t5;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
RENAME TABLE t5 TO t3;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
#
# 5. Alter table rename with locked tables.
ALTER TABLE t3 RENAME TO t5;
SELECT * FROM t5 ORDER BY c1;
ERROR HY000: Table 't5' was not locked with LOCK TABLES
ALTER TABLE t5 RENAME TO t3;
ERROR HY000: Table 't5' was not locked with LOCK TABLES
UNLOCK TABLES;
ALTER TABLE t5 RENAME TO t3;
SELECT * FROM t3 ORDER BY c1;
c1
1
2
3
DROP TABLE t1, t2, t3;
#
# Drop locked tables.
#
# 1. Drop parent.
CREATE TABLE t1 (c1 INT, INDEX(c1));
CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
INSERT_METHOD=LAST;
LOCK TABLES t1 WRITE, t2 WRITE;
INSERT INTO t1 VALUES (1);
DROP TABLE t2;
SELECT * FROM t2;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
SELECT * FROM t1;
c1
1
UNLOCK TABLES;
# 2. Drop child.
CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
INSERT_METHOD=LAST;
LOCK TABLES t1 WRITE, t2 WRITE;
INSERT INTO t1 VALUES (1);
DROP TABLE t1;
SELECT * FROM t2;
1335
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
UNLOCK TABLES;
DROP TABLE t2;
#
# ALTER TABLE. Change child list.
#
CREATE TABLE t1 (c1 INT, INDEX(c1));
CREATE TABLE t2 (c1 INT, INDEX(c1));
CREATE TABLE t3 (c1 INT, INDEX(c1));
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (3);
CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t3,t2)
INSERT_METHOD=LAST;
# Shrink child list.
ALTER TABLE t4 UNION=(t3);
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` int(11) DEFAULT NULL,
  KEY `c1` (`c1`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`)
SELECT * FROM t4 ORDER BY c1;
c1
3
# Extend child list.
ALTER TABLE t4 UNION=(t3,t2);
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` int(11) DEFAULT NULL,
  KEY `c1` (`c1`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`,`t2`)
SELECT * FROM t4 ORDER BY c1;
c1
2
3
#
# ALTER TABLE under LOCK TABLES. Change child list.
#
LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE;
# Shrink child list.
ALTER TABLE t4 UNION=(t3);
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
# Extend child list within locked tables.
ALTER TABLE t4 UNION=(t3,t2);
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
# Extend child list beyond locked tables.
ALTER TABLE t4 UNION=(t3,t2,t1);
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` int(11) DEFAULT NULL,
  KEY `c1` (`c1`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`,`t2`)
SELECT * FROM t4 ORDER BY c1;
c1
2
3
UNLOCK TABLES;
DROP TABLE t4;
#
# ALTER TABLE under LOCK TABLES. Grave change, table re-creation.
#
CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
INSERT_METHOD=LAST;
# Lock parent first and then children.
LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
# Lock children first and then parent.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
# Lock parent between children.
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
DROP TABLE t1, t2, t3, t4;
#
# ALTER TABLE under LOCK TABLES. Simple change, no re-creation.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TABLE t3 (c1 INT);
CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (3);
# Lock parent first and then children.
LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
# Lock children first and then parent.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
# Lock parent between children.
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
#
# FLUSH TABLE under LOCK TABLES.
#
# Lock parent first and then children.
LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
FLUSH TABLE t4;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
FLUSH TABLE t2;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
FLUSH TABLES;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
# Lock children first and then parent.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
FLUSH TABLE t4;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
FLUSH TABLE t2;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
FLUSH TABLES;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
# Lock parent between children.
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
FLUSH TABLE t4;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
FLUSH TABLE t2;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
FLUSH TABLES;
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
UNLOCK TABLES;
#
# Triggers
#
# Trigger on parent
DELETE FROM t4 WHERE c1 = 4;
CREATE TRIGGER t4_ai AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
SET @a=0;
INSERT INTO t4 VALUES (4);
SELECT @a;
@a
1
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
DROP TRIGGER t4_ai;
# Trigger on parent under LOCK TABLES
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
CREATE TRIGGER t4_ai AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
SET @a=0;
INSERT INTO t4 VALUES (4);
SELECT @a;
@a
1
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
DROP TRIGGER t4_ai;
UNLOCK TABLES;
#
# Trigger on child
DELETE FROM t4 WHERE c1 = 4;
CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
SET @a=0;
INSERT INTO t4 VALUES (4);
SELECT @a;
@a
0
INSERT INTO t3 VALUES (33);
SELECT @a;
@a
1
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
33
DROP TRIGGER t3_ai;
# Trigger on child under LOCK TABLES
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
SET @a=0;
INSERT INTO t4 VALUES (4);
SELECT @a;
@a
0
INSERT INTO t3 VALUES (33);
SELECT @a;
@a
1
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
33
33
DELETE FROM t4 WHERE c1 = 33;
DROP TRIGGER t3_ai;
#
# Trigger with table use on child
DELETE FROM t4 WHERE c1 = 4;
CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
INSERT INTO t4 VALUES (4);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
INSERT INTO t3 VALUES (33);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
22
33
DELETE FROM t4 WHERE c1 = 22;
DELETE FROM t4 WHERE c1 = 33;
DROP TRIGGER t3_ai;
# Trigger with table use on child under LOCK TABLES
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
INSERT INTO t4 VALUES (4);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
INSERT INTO t3 VALUES (33);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
22
33
DROP TRIGGER t3_ai;
DELETE FROM t4 WHERE c1 = 22;
DELETE FROM t4 WHERE c1 = 33;
UNLOCK TABLES;
#
# Repair
#
REPAIR TABLE t4;
Table	Op	Msg_type	Msg_text
test.t4	repair	note	The storage engine for the table doesn't support repair
REPAIR TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	repair	status	OK
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
REPAIR TABLE t4;
Table	Op	Msg_type	Msg_text
test.t4	repair	note	The storage engine for the table doesn't support repair
REPAIR TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	repair	status	OK
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
UNLOCK TABLES;
#
# Optimize
#
OPTIMIZE TABLE t4;
Table	Op	Msg_type	Msg_text
test.t4	optimize	note	The storage engine for the table doesn't support optimize
OPTIMIZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	optimize	status	OK
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
OPTIMIZE TABLE t4;
Table	Op	Msg_type	Msg_text
test.t4	optimize	note	The storage engine for the table doesn't support optimize
OPTIMIZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	optimize	status	Table is already up to date
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
UNLOCK TABLES;
#
# Checksum
#
CHECKSUM TABLE t4;
Table	Checksum
test.t4	46622073
CHECKSUM TABLE t2;
Table	Checksum
test.t2	3700403066
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
CHECKSUM TABLE t4;
Table	Checksum
test.t4	46622073
CHECKSUM TABLE t2;
Table	Checksum
test.t2	3700403066
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
UNLOCK TABLES;
#
# Insert delayed
#
INSERT DELAYED INTO t4 VALUES(44);
DELETE FROM t4 WHERE c1 = 44;
INSERT DELAYED INTO t3 VALUES(33);
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
33
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
INSERT DELAYED INTO t4 VALUES(444);
Got one of the listed errors
INSERT DELAYED INTO t3 VALUES(333);
Got one of the listed errors
SELECT * FROM t4 ORDER BY c1;
c1
1
2
3
4
4
33
UNLOCK TABLES;
DROP TABLE t1, t2, t3, t4;
#
# Recursive inclusion of merge tables in their union clauses.
#
CREATE TABLE t1 (c1 INT, INDEX(c1));
CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
INSERT_METHOD=LAST;
CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t2,t1)
INSERT_METHOD=LAST;
ALTER TABLE t2 UNION=(t3,t1);
SELECT * FROM t2;
ERROR HY000: Table 't3' is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE= MyISAM;
CREATE TABLE t3 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1, t2);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t3;
c1
1
2
TRUNCATE TABLE t1;
SELECT * FROM t3;
c1
2
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
SET @rnd_max= 2147483647;
set @@read_buffer_size=2*1024*1024;
CREATE TABLE t2 SELECT * FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
CREATE TABLE t3 (id INTEGER, grp TINYINT, id_rev INTEGER)
ENGINE= MRG_MYISAM UNION= (t1, t2);
SELECT COUNT(*) FROM t1;
COUNT(*)
130
SELECT COUNT(*) FROM t2;
COUNT(*)
80
SELECT COUNT(*) FROM t3;
COUNT(*)
210
SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2
WHERE a1.id = a2.id GROUP BY a2.grp;
TRUNCATE TABLE t1;
SELECT COUNT(*) FROM t1;
COUNT(*)
0
SELECT COUNT(*) FROM t2;
COUNT(*)
80
SELECT COUNT(*) FROM t3;
COUNT(*)
80
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
INSERT INTO t2 VALUES (1);
SELECT * FROM t2;
c1
1
LOCK TABLES t2 WRITE, t1 WRITE;
FLUSH TABLES;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
UNLOCK TABLES;
CHECK TABLE t1 EXTENDED;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
LOCK TABLES t2 WRITE, t1 WRITE;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
UNLOCK TABLES;
CHECK TABLE t1 EXTENDED;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1, t2;
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
LOCK TABLES t1 WRITE, m1 WRITE;
FLUSH TABLE t1;
UNLOCK TABLES;
DROP TABLE m1, t1;
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
LOCK TABLES m1 WRITE, t1 WRITE;
FLUSH TABLE t1;
UNLOCK TABLES;
DROP TABLE m1, t1;
CREATE TABLE t1 (c1 INT, c2 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT, c2 INT) ENGINE= MyISAM;
CREATE TABLE t3 (c1 INT, c2 INT) ENGINE= MRG_MYISAM UNION(t1, t2);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t2 VALUES (2, 2);
SELECT * FROM t3;
c1	c2
1	1
2	2
ALTER TABLE t1 ENGINE= MEMORY;
INSERT INTO t1 VALUES (0, 0);
SELECT * FROM t3;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (c1 INT, KEY(c1));
CREATE TABLE t2 (c1 INT, KEY(c1)) ENGINE=MRG_MYISAM UNION=(t1)
INSERT_METHOD=FIRST;
LOCK TABLE t1 WRITE, t2 WRITE;
FLUSH TABLES t2, t1;
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	Table is already up to date
FLUSH TABLES t1;
UNLOCK TABLES;
FLUSH TABLES;
INSERT INTO t1 VALUES (1);
LOCK TABLE t1 WRITE, t2 WRITE;
FLUSH TABLES t2, t1;
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
FLUSH TABLES t1;
UNLOCK TABLES;
DROP TABLE t1, t2;
CREATE TABLE t1 (ID INT) ENGINE=MYISAM;
CREATE TABLE m1 (ID INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
INSERT INTO t1 VALUES ();
INSERT INTO m1 VALUES ();
LOCK TABLE t1 WRITE, m1 WRITE;
FLUSH TABLES m1, t1;
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
FLUSH TABLES m1, t1;
UNLOCK TABLES;
DROP TABLE t1, m1;
1974 1975 1976 1977 1978 1979
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_SCHEMA = 'test' and TABLE_NAME='tm1';
TABLE_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
NULL	test	tm1	BASE TABLE	NULL	NULL	NULL	#	#	#	#	#	#	#	#	#	#	NULL	#	#	Unable to open underlying table which is differently defined or of non-MyISAM ty
DROP TABLE tm1;
1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994
CREATE TABLE t1(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
CREATE TABLE t2(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
CREATE TABLE t3(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
CREATE TABLE t4(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2))
ENGINE=MRG_MYISAM UNION=(t1, t2, t3);
INSERT INTO t1 VALUES (1,1), (1,2),(1,3), (1,4);
INSERT INTO t2 VALUES (2,1), (2,2),(2,3), (2,4);
INSERT INTO t3 VALUES (3,1), (3,2),(3,3), (3,4);
EXPLAIN SELECT COUNT(*) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
EXPLAIN SELECT COUNT(*) FROM t4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
DROP TABLE t1, t2, t3, t4;
1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
CREATE TABLE t1(a INT, KEY(a));
INSERT INTO t1 VALUES(0),(1),(2),(3),(4);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
CREATE TABLE m1(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
CARDINALITY
5
SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
CARDINALITY
5
SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
CARDINALITY
5
SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
CARDINALITY
5
DROP TABLE t1, m1;
2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057
#
# Bug #40675 MySQL 5.1 crash with index merge algorithm and Merge tables
#
# create MYISAM table t1 and insert values into it
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1);
# create MYISAM table t2 and insert values into it
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
INSERT INTO t2(a,b) VALUES
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(1,2);
# Create the merge table t3
CREATE TABLE t3(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b))
ENGINE=MERGE UNION=(t2) INSERT_METHOD=FIRST;
# Lock tables t1 and t3 for write
LOCK TABLES t1 WRITE, t3 WRITE;
# Insert values into the merge table t3
INSERT INTO t3(a,b) VALUES(1,2);
# select from the join of t2 and t3 (The merge table)
SELECT t3.a FROM t1,t3 WHERE t3.b=2 AND t3.a=1;
a
1
1
# Unlock the tables
UNLOCK TABLES;
# drop the created tables
DROP TABLE t1, t2, t3;
2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069
# insert duplicate value in child table while merge table doesn't have key
create table t1 (
col1 int(10),
primary key (col1)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE m1 (
col1 int(10) NOT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1);
insert into m1 (col1) values (1);
insert into m1 (col1) values (1);
ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
drop table m1, t1;
2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115
#
# Bug#45800 crash when replacing into a merge table and there is a duplicate
#
# Replace duplicate value in child table when merge table doesn't have key
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
INSERT INTO m1  VALUES (666);
SELECT * FROM m1;
c1
666
# insert the duplicate value into the merge table
REPLACE INTO m1 VALUES (666);
SELECT * FROM m1;
c1
666
DROP TABLE m1, t1;
# Insert... on duplicate key update (with duplicate values in the table)
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
INSERT INTO m1  VALUES (666);
SELECT * FROM m1;
c1
666
# insert the duplicate value into the merge table
INSERT INTO m1 VALUES (666) ON DUPLICATE KEY UPDATE c1=c1+1;
SELECT * FROM m1;
c1
667
DROP TABLE m1, t1;
# Insert duplicate value on MERGE table, where, MERGE has a key but MyISAM has more keys
CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1), UNIQUE (c2));
CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c1)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
INSERT INTO m1 VALUES (1,2);
# insert the duplicate value into the merge table
INSERT INTO m1 VALUES (3,2);
ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
DROP TABLE m1,t1;
# Try to define MERGE and MyISAM with keys on different columns
CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1));
CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c2)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
# Try accessing the merge table for inserts (error occurs)
INSERT INTO m1 VALUES (1,2);
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
INSERT INTO m1 VALUES (1,4);
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP TABLE m1,t1;
2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127
CREATE TABLE t1 (
col1 INT(10)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
CREATE VIEW v1 as SELECT * FROM t1;
CREATE TABLE m1 (
col1 INT(10)
)ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(v1);
#Select should detect that the child table is a view and fail.
SELECT * FROM m1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
DROP VIEW v1;
DROP TABLE m1, t1;
2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141
#
# Bug #45796: invalid memory reads and writes when altering merge and 
#             base tables
#
CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1);
ALTER TABLE m1 ADD INDEX idx_c1(c1);
SELECT * FROM m1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
ALTER TABLE t1 ADD INDEX idx_c1(c1);
SELECT * FROM m1;
c1
DROP TABLE m1;
DROP TABLE t1;
2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161
#
# Bug45781 infinite hang/crash in "opening tables" after handler tries to
#          open merge table
#
DROP TABLE IF EXISTS m1,t1;
CREATE TABLE t1(a int)engine=myisam;
CREATE TABLE t2(a int)engine=myisam;
CREATE TABLE t3(a int)engine=myisam;
CREATE TABLE t4(a int)engine=myisam;
CREATE TABLE t5(a int)engine=myisam;
CREATE TABLE t6(a int)engine=myisam;
CREATE TABLE t7(a int)engine=myisam;
CREATE TABLE m1(a int)engine=merge union=(t1,t2,t3,t4,t5,t6,t7);
SELECT 1 FROM m1;
1
HANDLER m1 OPEN;
ERROR HY000: Table storage engine for 'm1' doesn't have this option
DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7;
SELECT 1 FROM m1;
ERROR 42S02: Table 'test.m1' doesn't exist
2162
End of 5.1 tests