Debian Codegraph @

mysql-test/suite/merge/merge.result

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 set @save_default_storage_engine=@@global.default_storage_engine;
3 set global default_storage_engine=myisam;
4 set session default_storage_engine=myisam;
5 create table t1 (a int not null primary key auto_increment, message char(20));
6 create table t2 (a int not null primary key auto_increment, message char(20));
7 INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
8 INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
9 create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2);
10 select * from t3;
11 a b
12 1 Testing
13 2 table
14 3 t1
15 1 Testing
16 2 table
17 3 t2
18 select * from t3 order by a desc;
19 a b
20 3 t1
21 3 t2
22 2 table
23 2 table
24 1 Testing
25 1 Testing
26 drop table t3;
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 insert into t2 select NULL,message from t1;
37 insert into t1 select NULL,message from t2;
38 create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,test.t2);
39 explain select * from t3 where a < 10;
40 id select_type table type possible_keys key key_len ref rows Extra
41 1 SIMPLE t3 range a a 4 NULL 17 Using where
42 explain select * from t3 where a > 10 and a < 20;
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
45 select * from t3 where a = 10;
46 a b
47 10 Testing
48 10 Testing
49 select * from t3 where a < 10;
50 a b
51 1 Testing
52 1 Testing
53 2 table
54 2 table
55 3 t1
56 3 t2
57 4 Testing
58 4 Testing
59 5 table
60 5 table
61 6 t2
62 6 t1
63 7 Testing
64 7 Testing
65 8 table
66 8 table
67 9 t2
68 9 t2
69 select * from t3 where a > 10 and a < 20;
70 a b
71 11 table
72 11 table
73 12 t1
74 12 t1
75 13 Testing
76 13 Testing
77 14 table
78 14 table
79 15 t2
80 15 t2
81 16 Testing
82 16 Testing
83 17 table
84 17 table
85 18 t2
86 18 t2
87 19 Testing
88 19 Testing
89 explain select a from t3 order by a desc limit 10;
90 id select_type table type possible_keys key key_len ref rows Extra
91 1 SIMPLE t3 index NULL a 4 NULL 10 Using index
92 select a from t3 order by a desc limit 10;
93 a
94 699
95 698
96 697
97 696
98 695
99 694
100 693
101 692
102 691
103 690
104 select a from t3 order by a desc limit 300,10;
105 a
106 416
107 415
108 415
109 414
110 414
111 413
112 413
113 412
114 412
115 411
116 delete from t3 where a=3;
117 select * from t3 where a < 10;
118 a b
119 1 Testing
120 1 Testing
121 2 table
122 2 table
123 4 Testing
124 4 Testing
125 5 table
126 5 table
127 6 t2
128 6 t1
129 7 Testing
130 7 Testing
131 8 table
132 8 table
133 9 t2
134 9 t2
135 delete from t3 where a >= 6 and a <= 8;
136 select * from t3 where a < 10;
137 a b
138 1 Testing
139 1 Testing
140 2 table
141 2 table
142 4 Testing
143 4 Testing
144 5 table
145 5 table
146 9 t2
147 9 t2
148 update t3 set a=3 where a=9;
149 select * from t3 where a < 10;
150 a b
151 1 Testing
152 1 Testing
153 2 table
154 2 table
155 3 t2
156 3 t2
157 4 Testing
158 4 Testing
159 5 table
160 5 table
161 update t3 set a=6 where a=7;
162 select * from t3 where a < 10;
163 a b
164 1 Testing
165 1 Testing
166 2 table
167 2 table
168 3 t2
169 3 t2
170 4 Testing
171 4 Testing
172 5 table
173 5 table
174 show create table t3;
175 Table Create Table
176 t3 CREATE TABLE `t3` (
177 `a` int(11) NOT NULL,
178 `b` char(20) DEFAULT NULL,
179 KEY `a` (`a`)
180 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`)
181 create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2);
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
186 flush tables;
187 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 create database mysqltest character set latin1;
190 create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
191 create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6);
192 show create table t5;
193 Table Create Table
194 t5 CREATE TABLE `t5` (
195 `a` int(11) NOT NULL,
196 `b` char(20) DEFAULT NULL,
197 KEY `a` (`a`)
198 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`mysqltest`.`t6`)
199 alter table t5 engine=myisam;
200 drop table t5, mysqltest.t6;
201 drop database mysqltest;
202 drop table t4,t3,t1,t2;
203 create table t1 (c char(10)) engine=myisam;
204 create table t2 (c char(10)) engine=myisam;
205 create table t3 (c char(10)) union=(t1,t2) engine=merge;
206 insert into t1 (c) values ('test1');
207 insert into t1 (c) values ('test1');
208 insert into t1 (c) values ('test1');
209 insert into t2 (c) values ('test2');
210 insert into t2 (c) values ('test2');
211 insert into t2 (c) values ('test2');
212 select * from t3;
213 c
214 test1
215 test1
216 test1
217 test2
218 test2
219 test2
220 select * from t3;
221 c
222 test1
223 test1
224 test1
225 test2
226 test2
227 test2
228 delete from t3 where 1=1;
229 select * from t3;
230 c
231 select * from t1;
232 c
233 drop table t3,t2,t1;
234 CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr));
235 CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr));
236 CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
237 ENGINE=MERGE UNION=(t1,t2);
238 SELECT * from t3;
239 incr othr
240 INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17);
241 INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32);
242 INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37);
243 INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30);
244 SELECT * from t3 where incr in (1,2,3,4) order by othr;
245 incr othr
246 1 10
247 2 24
248 4 33
249 3 53
250 alter table t3 UNION=(t1);
251 select count(*) from t3;
252 count(*)
253 10
254 alter table t3 UNION=(t1,t2);
255 select count(*) from t3;
256 count(*)
257 20
258 alter table t3 ENGINE=MYISAM;
259 select count(*) from t3;
260 count(*)
261 20
262 drop table t3;
263 CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
264 ENGINE=MERGE UNION=(t1,t2);
265 show create table t3;
266 Table Create Table
267 t3 CREATE TABLE `t3` (
268 `incr` int(11) NOT NULL,
269 `othr` int(11) NOT NULL,
270 PRIMARY KEY (`incr`)
271 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`)
272 alter table t3 drop primary key;
273 show create table t3;
274 Table Create Table
275 t3 CREATE TABLE `t3` (
276 `incr` int(11) NOT NULL,
277 `othr` int(11) NOT NULL
278 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`)
279 drop table t3,t2,t1;
280 create table t1 (a int not null, key(a)) engine=merge;
281 select * from t1;
282 a
283 drop table t1;
284 create table t1 (a int not null, b int not null, key(a,b));
285 create table t2 (a int not null, b int not null, key(a,b));
286 create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2);
287 insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6);
288 insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6);
289 flush tables;
290 select * from t3 where a=1 order by b limit 2;
291 a b
292 1 1
293 1 2
294 drop table t3,t1,t2;
295 create table t1 (a int not null, b int not null auto_increment, primary key(a,b));
296 create table t2 (a int not null, b int not null auto_increment, primary key(a,b));
297 create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO;
298 create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO;
299 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;
300 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;
301 show create table t3;
302 Table Create Table
303 t3 CREATE TABLE `t3` (
304 `a` int(11) NOT NULL,
305 `b` int(11) NOT NULL,
306 KEY `a` (`a`,`b`)
307 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
308 show create table t4;
309 Table Create Table
310 t4 CREATE TABLE `t4` (
311 `a` int(11) NOT NULL,
312 `b` int(11) NOT NULL,
313 KEY `a` (`a`,`b`)
314 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`)
315 show create table t5;
316 Table Create Table
317 t5 CREATE TABLE `t5` (
318 `a` int(11) NOT NULL,
319 `b` int(11) NOT NULL AUTO_INCREMENT,
320 PRIMARY KEY (`a`,`b`)
321 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=FIRST UNION=(`t1`,`t2`)
322 show create table t6;
323 Table Create Table
324 t6 CREATE TABLE `t6` (
325 `a` int(11) NOT NULL,
326 `b` int(11) NOT NULL AUTO_INCREMENT,
327 PRIMARY KEY (`a`,`b`)
328 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
329 insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
330 insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
331 select * from t3 order by b,a limit 3;
332 a b
333 select * from t4 order by b,a limit 3;
334 a b
335 1 1
336 2 1
337 1 2
338 select * from t5 order by b,a limit 3,3;
339 a b
340 2 2
341 1 3
342 2 3
343 select * from t6 order by b,a limit 6,3;
344 a b
345 1 4
346 2 4
347 insert into t5 values (5,1),(5,2);
348 insert into t6 values (6,1),(6,2);
349 select * from t1 order by a,b;
350 a b
351 1 1
352 1 2
353 1 3
354 1 4
355 5 1
356 5 2
357 select * from t2 order by a,b;
358 a b
359 2 1
360 2 2
361 2 3
362 2 4
363 6 1
364 6 2
365 select * from t4 order by a,b;
366 a b
367 1 1
368 1 2
369 1 3
370 1 4
371 2 1
372 2 2
373 2 3
374 2 4
375 5 1
376 5 2
377 6 1
378 6 2
379 insert into t3 values (3,1),(3,2),(3,3),(3,4);
380 select * from t3 order by a,b;
381 a b
382 3 1
383 3 2
384 3 3
385 3 4
386 alter table t4 UNION=(t1,t2,t3);
387 show create table t4;
388 Table Create Table
389 t4 CREATE TABLE `t4` (
390 `a` int(11) NOT NULL,
391 `b` int(11) NOT NULL,
392 KEY `a` (`a`,`b`)
393 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`,`t3`)
394 select * from t4 order by a,b;
395 a b
396 1 1
397 1 2
398 1 3
399 1 4
400 2 1
401 2 2
402 2 3
403 2 4
404 3 1
405 3 2
406 3 3
407 3 4
408 5 1
409 5 2
410 6 1
411 6 2
412 alter table t4 INSERT_METHOD=FIRST;
413 show create table t4;
414 Table Create Table
415 t4 CREATE TABLE `t4` (
416 `a` int(11) NOT NULL,
417 `b` int(11) NOT NULL,
418 KEY `a` (`a`,`b`)
419 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`)
420 insert into t4 values (4,1),(4,2);
421 select * from t1 order by a,b;
422 a b
423 1 1
424 1 2
425 1 3
426 1 4
427 4 1
428 4 2
429 5 1
430 5 2
431 select * from t2 order by a,b;
432 a b
433 2 1
434 2 2
435 2 3
436 2 4
437 6 1
438 6 2
439 select * from t3 order by a,b;
440 a b
441 3 1
442 3 2
443 3 3
444 3 4
445 select * from t4 order by a,b;
446 a b
447 1 1
448 1 2
449 1 3
450 1 4
451 2 1
452 2 2
453 2 3
454 2 4
455 3 1
456 3 2
457 3 3
458 3 4
459 4 1
460 4 2
461 5 1
462 5 2
463 6 1
464 6 2
465 select * from t5 order by a,b;
466 a b
467 1 1
468 1 2
469 1 3
470 1 4
471 2 1
472 2 2
473 2 3
474 2 4
475 4 1
476 4 2
477 5 1
478 5 2
479 6 1
480 6 2
481 select 1;
482 1
483 1
484 insert into t5 values (1,NULL),(5,NULL);
485 insert into t6 values (2,NULL),(6,NULL);
486 select * from t1 order by a,b;
487 a b
488 1 1
489 1 2
490 1 3
491 1 4
492 1 5
493 4 1
494 4 2
495 5 1
496 5 2
497 5 3
498 select * from t2 order by a,b;
499 a b
500 2 1
501 2 2
502 2 3
503 2 4
504 2 5
505 6 1
506 6 2
507 6 3
508 select * from t5 order by a,b;
509 a b
510 1 1
511 1 2
512 1 3
513 1 4
514 1 5
515 2 1
516 2 2
517 2 3
518 2 4
519 2 5
520 4 1
521 4 2
522 5 1
523 5 2
524 5 3
525 6 1
526 6 2
527 6 3
528 select * from t6 order by a,b;
529 a b
530 1 1
531 1 2
532 1 3
533 1 4
534 1 5
535 2 1
536 2 2
537 2 3
538 2 4
539 2 5
540 4 1
541 4 2
542 5 1
543 5 2
544 5 3
545 6 1
546 6 2
547 6 3
548 insert into t1 values (99,NULL);
549 select * from t4 where a+0 > 90;
550 a b
551 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 insert t5 values (1,1) on duplicate key update b=b+10;
557 insert t6 values (2,1) on duplicate key update b=b+20;
558 select * from t5 where a < 3;
559 a b
560 1 2
561 1 3
562 1 4
563 1 5
564 1 11
565 2 2
566 2 3
567 2 4
568 2 5
569 2 21
570 drop table t6, t5, t4, t3, t2, t1;
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);
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);
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 select max(b) from t1 where a = 2;
580 max(b)
581 1
582 drop table t3,t1,t2;
583 create table t1 (a int not null);
584 create table t2 (a int not null);
585 insert into t1 values (1);
586 insert into t2 values (2);
587 create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
588 select * from t3;
589 a
590 1
591 2
592 create temporary table t4 (a int not null);
593 create temporary table t5 (a int not null);
594 insert into t4 values (1);
595 insert into t5 values (2);
596 create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5);
597 select * from t6;
598 a
599 1
600 2
601 drop table t6, t3, t1, t2, t4, t5;
602 create temporary table t1 (a int not null);
603 create temporary table t2 (a int not null);
604 insert into t1 values (1);
605 insert into t2 values (2);
606 create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
607 select * from t3;
608 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
609 drop table t3, t2, t1;
610 create table t1 (a int not null);
611 create temporary table t2 (a int not null);
612 insert into t1 values (1);
613 insert into t2 values (2);
614 create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
615 select * from t3;
616 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
617 drop table t3;
618 create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
619 select * from t3;
620 a
621 1
622 2
623 drop table t3, t2, t1;
624 # CREATE...SELECT is not implemented for MERGE tables.
625 CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL);
626 CREATE TEMPORARY TABLE t2 (c1 INT NOT NULL);
627 CREATE TABLE t3 (c1 INT NOT NULL);
628 INSERT INTO t3 VALUES (3), (33);
629 LOCK TABLES t3 READ;
630 CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
631 INSERT_METHOD=LAST SELECT * FROM t3;
632 ERROR HY000: 'test.t4' is not of type 'BASE TABLE'
633 SELECT * FROM t4;
634 ERROR HY000: Table 't4' was not locked with LOCK TABLES
635 UNLOCK TABLES;
636 CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
637 INSERT_METHOD=LAST;
638 INSERT INTO t4 SELECT * FROM t3;
639 # Alter temporary MERGE table.
640 ALTER TABLE t4 UNION=(t1);
641 LOCK TABLES t4 WRITE;
642 # Alter temporary MERGE table under LOCk tables.
643 ALTER TABLE t4 UNION=(t1,t2);
644 UNLOCK TABLES;
645 # MERGE table and function.
646 CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);
647 SELECT * FROM t4 WHERE c1 < f1();
648 c1
649 3
650 DROP FUNCTION f1;
651 DROP TABLE t4, t3, t2, t1;
652 CREATE TABLE t1 (
653 fileset_id tinyint(3) unsigned NOT NULL default '0',
654 file_code varchar(32) NOT NULL default '',
655 fileset_root_id tinyint(3) unsigned NOT NULL default '0',
656 PRIMARY KEY (fileset_id,file_code),
657 KEY files (fileset_id,fileset_root_id)
658 ) ENGINE=MyISAM;
659 INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1),
660 (2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1),
661 (2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1);
662 CREATE TABLE t2 (
663 fileset_id tinyint(3) unsigned NOT NULL default '0',
664 file_code varchar(32) NOT NULL default '',
665 fileset_root_id tinyint(3) unsigned NOT NULL default '0',
666 PRIMARY KEY (fileset_id,file_code),
667 KEY files (fileset_id,fileset_root_id)
668 ) ENGINE=MRG_MyISAM UNION=(t1);
669 EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2
670 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
671 id select_type table type possible_keys key key_len ref rows Extra
672 1 SIMPLE t2 range PRIMARY PRIMARY 35 NULL 6 Using where
673 EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
674 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
675 id select_type table type possible_keys key key_len ref rows Extra
676 1 SIMPLE t2 range PRIMARY,files PRIMARY 35 NULL 6 Using where
677 EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2
678 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
679 id select_type table type possible_keys key key_len ref rows Extra
680 1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 6 Using index condition
681 EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
682 AND file_code = '0000000115' LIMIT 1;
683 id select_type table type possible_keys key key_len ref rows Extra
684 1 SIMPLE t2 const PRIMARY,files PRIMARY 35 const,const 1
685 DROP TABLE t2, t1;
686 create table t1 (x int, y int, index xy(x, y));
687 create table t2 (x int, y int, index xy(x, y));
688 create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2);
689 insert into t1 values(1, 2);
690 insert into t2 values(1, 3);
691 select * from t3 where x = 1 and y < 5 order by y;
692 x y
693 1 2
694 1 3
695 select * from t3 where x = 1 and y < 5 order by y desc;
696 x y
697 1 3
698 1 2
699 drop table t1,t2,t3;
700 create table t1 (a int);
701 create table t2 (a int);
702 insert into t1 values (0);
703 insert into t2 values (1);
704 create table t3 engine=merge union=(t1, t2) select * from t1;
705 ERROR HY000: 'test.t3' is not of type 'BASE TABLE'
706 create table t3 engine=merge union=(t1, t2) select * from t2;
707 ERROR HY000: 'test.t3' is not of type 'BASE TABLE'
708 create table t3 engine=merge union=(t1, t2) select (select max(a) from t2);
709 ERROR HY000: 'test.t3' is not of type 'BASE TABLE'
710 drop table t1, t2;
711 create table t1 (
712 a double(14,4),
713 b varchar(10),
714 index (a,b)
715 ) engine=merge union=(t2,t3);
716 create table t2 (
717 a double(14,4),
718 b varchar(10),
719 index (a,b)
720 ) engine=myisam;
721 create table t3 (
722 a double(14,4),
723 b varchar(10),
724 index (a,b)
725 ) engine=myisam;
726 insert into t2 values ( null, '');
727 insert into t2 values ( 9999999999.999, '');
728 insert into t3 select * from t2;
729 select min(a), max(a) from t1;
730 min(a) max(a)
731 9999999999.9990 9999999999.9990
732 flush tables;
733 select min(a), max(a) from t1;
734 min(a) max(a)
735 9999999999.9990 9999999999.9990
736 drop table t1, t2, t3;
737 create table t1 (a int,b int,c int, index (a,b,c));
738 create table t2 (a int,b int,c int, index (a,b,c));
739 create table t3 (a int,b int,c int, index (a,b,c))
740 engine=merge union=(t1 ,t2);
741 insert into t1 (a,b,c) values (1,1,0),(1,2,0);
742 insert into t2 (a,b,c) values (1,1,1),(1,2,1);
743 explain select a,b,c from t3 force index (a) where a=1 order by a,b,c;
744 id select_type table type possible_keys key key_len ref rows Extra
745 1 SIMPLE t3 ref a a 5 const 4 Using where; Using index
746 select a,b,c from t3 force index (a) where a=1 order by a,b,c;
747 a b c
748 1 1 0
749 1 1 1
750 1 2 0
751 1 2 1
752 explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
753 id select_type table type possible_keys key key_len ref rows Extra
754 1 SIMPLE t3 ref a a 5 const 4 Using where; Using index
755 select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
756 a b c
757 1 2 1
758 1 2 0
759 1 1 1
760 1 1 0
761 show index from t3;
762 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
763 t3 1 a 1 a A NULL NULL NULL YES BTREE NO
764 t3 1 a 2 b A NULL NULL NULL YES BTREE NO
765 t3 1 a 3 c A NULL NULL NULL YES BTREE NO
766 drop table t1, t2, t3;
767 CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) )
768 ENGINE=MyISAM;
769 CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) )
770 ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST;
771 INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2;
772 INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3;
773 SELECT b FROM t2;
774 b
775 3
776 DROP TABLE t1, t2;
777 create table t1(a int);
778 create table t2(a int);
779 insert into t1 values (1);
780 insert into t2 values (2);
781 create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
782 select * from t3;
783 a
784 1
785 2
786 insert t2 select * from t2;
787 select * from t2;
788 a
789 2
790 2
791 insert t3 select * from t1;
792 select * from t3;
793 a
794 1
795 1
796 2
797 2
798 insert t1 select * from t3;
799 select * from t1;
800 a
801 1
802 1
803 1
804 1
805 2
806 2
807 select * from t2;
808 a
809 2
810 2
811 select * from t3;
812 a
813 1
814 1
815 1
816 1
817 2
818 2
819 2
820 2
821 check table t1, t2;
822 Table Op Msg_type Msg_text
823 test.t1 check status OK
824 test.t2 check status OK
825 drop table t1, t2, t3;
826 CREATE TABLE t1(a INT);
827 INSERT INTO t1 VALUES(2),(1);
828 CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
829 SELECT * FROM t2 WHERE a=2;
830 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
831 DROP TABLE t1, t2;
832 CREATE TABLE t1(a INT) ENGINE=MEMORY;
833 CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1);
834 SELECT * FROM t2;
835 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
836 DROP TABLE t1, t2;
837 CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3);
838 SELECT * FROM t2;
839 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
840 DROP TABLE t2;
841 CREATE TABLE t1(a INT, b TEXT);
842 CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1);
843 SELECT * FROM tm1;
844 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
845 DROP TABLE t1, tm1;
846 CREATE TABLE t1(a SMALLINT, b SMALLINT);
847 CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1);
848 SELECT * FROM tm1;
849 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
850 DROP TABLE t1, tm1;
851 CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(a, b));
852 CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
853 SELECT * FROM tm1;
854 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
855 DROP TABLE t1, tm1;
856 CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(b));
857 CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
858 SELECT * FROM tm1;
859 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
860 DROP TABLE t1, tm1;
861 CREATE TABLE t1(c1 VARCHAR(1));
862 CREATE TABLE m1 LIKE t1;
863 ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
864 SELECT * FROM m1;
865 c1
866 DROP TABLE t1, m1;
867 CREATE TABLE t1(c1 VARCHAR(4), c2 TINYINT, c3 TINYINT, c4 TINYINT,
868 c5 TINYINT, c6 TINYINT, c7 TINYINT, c8 TINYINT, c9 TINYINT);
869 CREATE TABLE m1 LIKE t1;
870 ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
871 SELECT * FROM m1;
872 c1 c2 c3 c4 c5 c6 c7 c8 c9
873 DROP TABLE t1, m1;
874 CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci,
875 b INT, INDEX(a,b));
876 CREATE TABLE t2 LIKE t1;
877 CREATE TABLE t3 LIKE t1;
878 ALTER TABLE t3 ENGINE=MERGE UNION=(t1,t2);
879 INSERT INTO t1 VALUES ('ss',1);
880 INSERT INTO t2 VALUES ('ss',2),(0xDF,2);
881 SELECT COUNT(*) FROM t3 WHERE a=0xDF AND b=2;
882 COUNT(*)
883 2
884 DROP TABLE t1,t2,t3;
885 # End of 4.1 tests
886 create table t1 (b bit(1));
887 create table t2 (b bit(1));
888 create table tm (b bit(1)) engine = merge union = (t1,t2);
889 select * from tm;
890 b
891 drop table tm, t1, t2;
892 create table t1 (a int) insert_method = last engine = merge;
893 insert into t1 values (1);
894 ERROR HY000: Table 't1' is read only
895 create table t2 (a int) engine = myisam;
896 alter table t1 union (t2);
897 insert into t1 values (1);
898 alter table t1 insert_method = no;
899 insert into t1 values (1);
900 ERROR HY000: Table 't1' is read only
901 drop table t2;
902 drop table t1;
903 CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2);
904 SELECT * FROM tm1;
905 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
906 CHECK TABLE tm1;
907 Table Op Msg_type Msg_text
908 test.tm1 check Error Table 'test.t1' is differently defined or of non-MyISAM type or doesn't exist
909 test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
910 test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
911 test.tm1 check error Corrupt
912 CREATE TABLE t1(a INT);
913 SELECT * FROM tm1;
914 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
915 CHECK TABLE tm1;
916 Table Op Msg_type Msg_text
917 test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
918 test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
919 test.tm1 check error Corrupt
920 CREATE TABLE t2(a BLOB);
921 SELECT * FROM tm1;
922 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
923 CHECK TABLE tm1;
924 Table Op Msg_type Msg_text
925 test.tm1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
926 test.tm1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
927 test.tm1 check error Corrupt
928 ALTER TABLE t2 MODIFY a INT;
929 SELECT * FROM tm1;
930 a
931 CHECK TABLE tm1;
932 Table Op Msg_type Msg_text
933 test.tm1 check status OK
934 DROP TABLE tm1, t1, t2;
935 CREATE TABLE t1(c1 INT);
936 CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
937 CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
938 Warnings:
939 Note 1050 Table 't1' already exists
940 DROP TABLE t1, t2;
941 CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM;
942 CREATE TABLE t2 LIKE t1;
943 INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
944 INSERT INTO t1 SELECT * FROM t2;
945 INSERT INTO t1 SELECT * FROM t2;
946 CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE
947 UNION(t1);
948 SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
949 id ref ref
950 4 4 5
951 4 4 5
952 4 4 5
953 4 4 5
954 SELECT * FROM t3;
955 id ref
956 1 3
957 2 1
958 3 2
959 4 5
960 4 4
961 1 3
962 2 1
963 3 2
964 4 5
965 4 4
966 DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
967 SELECT * FROM t3;
968 id ref
969 1 3
970 2 1
971 3 2
972 4 5
973 1 3
974 2 1
975 3 2
976 4 5
977 DROP TABLE t1, t2, t3;
978 CREATE TABLE t1(a INT);
979 CREATE TABLE m1(a INT) ENGINE=MERGE;
980 SHOW CREATE TABLE m1;
981 Table Create Table
982 m1 CREATE TABLE `m1` (
983 `a` int(11) DEFAULT NULL
984 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
985 DROP TABLE m1;
986 CREATE TABLE m1(a INT) ENGINE=MERGE UNION=();
987 SHOW CREATE TABLE m1;
988 Table Create Table
989 m1 CREATE TABLE `m1` (
990 `a` int(11) DEFAULT NULL
991 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
992 ALTER TABLE m1 UNION=(t1);
993 ALTER TABLE m1 UNION=();
994 SHOW CREATE TABLE m1;
995 Table Create Table
996 m1 CREATE TABLE `m1` (
997 `a` int(11) DEFAULT NULL
998 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
999 DROP TABLE t1, m1;
1000 CREATE TABLE t1(a INT);
1001 CREATE TABLE t2(a VARCHAR(10));
1002 CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2);
1003 CREATE TABLE m2(a INT) ENGINE=MERGE UNION=(t1);
1004 SELECT * FROM t1;
1005 a
1006 SELECT * FROM m1;
1007 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
1008 SELECT * FROM m2;
1009 a
1010 DROP TABLE t1, t2, m1, m2;
1011 # End of 5.0 tests
1012 create table t1 (c1 int, index(c1));
1013 create table t2 (c1 int, index(c1)) engine=merge union=(t1);
1014 insert into t1 values (1);
1015 flush tables;
1016 select * from t2;
1017 c1
1018 1
1019 flush tables;
1020 truncate table t1;
1021 insert into t1 values (1);
1022 flush tables;
1023 select * from t2;
1024 c1
1025 1
1026 truncate table t1;
1027 insert into t1 values (1);
1028 drop table t1,t2;
1029 #
1030 # Extra tests for TRUNCATE.
1031 #
1032 # Truncate MERGE table.
1033 CREATE TABLE t1 (c1 INT, INDEX(c1));
1034 CREATE TABLE t2 (c1 INT, INDEX(c1));
1035 CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
1036 INSERT INTO t1 VALUES (1);
1037 INSERT INTO t2 VALUES (2);
1038 SELECT * FROM t3;
1039 c1
1040 1
1041 2
1042 TRUNCATE TABLE t3;
1043 SELECT * FROM t3;
1044 c1
1045 #
1046 # Truncate child table.
1047 INSERT INTO t1 VALUES (1);
1048 INSERT INTO t2 VALUES (2);
1049 TRUNCATE TABLE t1;
1050 SELECT * FROM t3;
1051 c1
1052 2
1053 #
1054 # Truncate MERGE table under locked tables.
1055 LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
1056 INSERT INTO t1 VALUES (1);
1057 TRUNCATE TABLE t3;
1058 SELECT * FROM t3;
1059 c1
1060 UNLOCK TABLES;
1061 SELECT * FROM t1;
1062 c1
1063 SELECT * FROM t2;
1064 c1
1065 #
1066 # Truncate child table under locked tables.
1067 LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
1068 INSERT INTO t1 VALUES (1);
1069 INSERT INTO t2 VALUES (2);
1070 TRUNCATE TABLE t1;
1071 SELECT * FROM t3;
1072 c1
1073 2
1074 UNLOCK TABLES;
1075 DROP TABLE t1, t2, t3;
1076 #
1077 # Truncate temporary MERGE table.
1078 CREATE TEMPORARY TABLE t1 (c1 INT, INDEX(c1));
1079 CREATE TEMPORARY TABLE t2 (c1 INT, INDEX(c1));
1080 CREATE TEMPORARY TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
1081 INSERT INTO t1 VALUES (1);
1082 INSERT INTO t2 VALUES (2);
1083 SELECT * FROM t3;
1084 c1
1085 1
1086 2
1087 TRUNCATE TABLE t3;
1088 SELECT * FROM t3;
1089 c1
1090 #
1091 # Truncate temporary child table.
1092 INSERT INTO t1 VALUES (1);
1093 INSERT INTO t2 VALUES (2);
1094 TRUNCATE TABLE t1;
1095 SELECT * FROM t3;
1096 c1
1097 2
1098 #
1099 # Truncate temporary MERGE table under locked tables.
1100 INSERT INTO t1 VALUES (1);
1101 CREATE TABLE t4 (c1 INT, INDEX(c1));
1102 LOCK TABLE t4 WRITE;
1103 TRUNCATE TABLE t3;
1104 SELECT * FROM t3;
1105 c1
1106 SELECT * FROM t1;
1107 c1
1108 SELECT * FROM t2;
1109 c1
1110 #
1111 # Truncate temporary child table under locked tables.
1112 INSERT INTO t1 VALUES (1);
1113 INSERT INTO t2 VALUES (2);
1114 TRUNCATE TABLE t1;
1115 SELECT * FROM t3;
1116 c1
1117 2
1118 SELECT * FROM t1;
1119 c1
1120 SELECT * FROM t2;
1121 c1
1122 2
1123 UNLOCK TABLES;
1124 DROP TABLE t1, t2, t3, t4;
1125 connect con1,localhost,root,,;
1126 connect con2,localhost,root,,;
1127 connection default;
1128 CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
1129 CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
1130 REPAIR TABLE t1;
1131 connection con1;
1132 INSERT INTO t2 VALUES (1);
1133 connection default;
1134 Table Op Msg_type Msg_text
1135 test.t1 repair status OK
1136 DROP TABLE t1, t2;
1137 CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
1138 CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
1139 LOCK TABLE t1 WRITE;
1140 connection con1;
1141 INSERT INTO t2 VALUES (1);
1142 connection default;
1143 REPAIR TABLE t1;
1144 Table Op Msg_type Msg_text
1145 test.t1 repair status OK
1146 UNLOCK TABLES;
1147 connection con1;
1148 connection default;
1149 DROP TABLE t1, t2;
1150 CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
1151 LOCK TABLE t1 WRITE;
1152 connection con1;
1153 INSERT INTO t1 VALUES (1);
1154 connection default;
1155 FLUSH TABLES;
1156 FLUSH TABLES;
1157 SELECT * FROM t1;
1158 c1
1159 UNLOCK TABLES;
1160 connection con1;
1161 connection default;
1162 DROP TABLE t1;
1163 disconnect con1;
1164 disconnect con2;
1165 #
1166 # Extra tests for Bug#26379 - Combination of FLUSH TABLE and
1167 # REPAIR TABLE corrupts a MERGE table
1168 #
1169 # CREATE ... SELECT is disabled for MERGE tables.
1170 #
1171 CREATE TABLE t1(c1 INT);
1172 INSERT INTO t1 VALUES (1);
1173 CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
1174 CREATE TABLE t3 ENGINE=MRG_MYISAM INSERT_METHOD=LAST SELECT * FROM t2;
1175 ERROR HY000: Table 't3' is read only
1176 SHOW CREATE TABLE t3;
1177 ERROR 42S02: Table 'test.t3' doesn't exist
1178 CREATE TABLE t3 ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST
1179 SELECT * FROM t2;
1180 ERROR HY000: 'test.t3' is not of type 'BASE TABLE'
1181 SHOW CREATE TABLE t3;
1182 ERROR 42S02: Table 'test.t3' doesn't exist
1183 DROP TABLE t1, t2;
1184 #
1185 # Bug#37371 "CREATE TABLE LIKE merge loses UNION parameter"
1186 # Demonstrate that this is no longer the case.
1187 #
1188 # 1. Create like.
1189 CREATE TABLE t1 (c1 INT);
1190 CREATE TABLE t2 (c1 INT);
1191 CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
1192 INSERT_METHOD=LAST;
1193 INSERT INTO t1 VALUES (1);
1194 INSERT INTO t2 VALUES (2);
1195 INSERT INTO t3 VALUES (3);
1196 CREATE TABLE t4 LIKE t3;
1197 SHOW CREATE TABLE t4;
1198 Table Create Table
1199 t4 CREATE TABLE `t4` (
1200 `c1` int(11) DEFAULT NULL
1201 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
1202 INSERT INTO t4 VALUES (4);
1203 DROP TABLE t4;
1204 #
1205 # 1. Create like with locked tables.
1206 LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE;
1207 CREATE TABLE t4 LIKE t3;
1208 ERROR HY000: Table 't4' was not locked with LOCK TABLES
1209 SHOW CREATE TABLE t4;
1210 ERROR HY000: Table 't4' was not locked with LOCK TABLES
1211 INSERT INTO t4 VALUES (4);
1212 ERROR HY000: Table 't4' was not locked with LOCK TABLES
1213 # Temporary tables can be created in spite of LOCK TABLES.
1214 # If the temporary MERGE table uses the locked children only,
1215 # it can even be used.
1216 CREATE TEMPORARY TABLE t4 LIKE t3;
1217 SHOW CREATE TABLE t4;
1218 Table Create Table
1219 t4 CREATE TEMPORARY TABLE `t4` (
1220 `c1` int(11) DEFAULT NULL
1221 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
1222 INSERT INTO t4 VALUES (4);
1223 UNLOCK TABLES;
1224 INSERT INTO t4 VALUES (4);
1225 DROP TABLE t4;
1226 #
1227 # Rename child.
1228 #
1229 # 1. Normal rename of non-MERGE table.
1230 CREATE TABLE t4 (c1 INT);
1231 INSERT INTO t4 VALUES (4);
1232 SELECT * FROM t4 ORDER BY c1;
1233 c1
1234 4
1235 RENAME TABLE t4 TO t5;
1236 SELECT * FROM t5 ORDER BY c1;
1237 c1
1238 4
1239 RENAME TABLE t5 TO t4;
1240 SELECT * FROM t4 ORDER BY c1;
1241 c1
1242 4
1243 DROP TABLE t4;
1244 #
1245 # 2. Normal rename.
1246 SELECT * FROM t3 ORDER BY c1;
1247 c1
1248 1
1249 2
1250 3
1251 4
1252 4
1253 4
1254 RENAME TABLE t2 TO t5;
1255 SELECT * FROM t3 ORDER BY c1;
1256 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
1257 RENAME TABLE t5 TO t2;
1258 SELECT * FROM t3 ORDER BY c1;
1259 c1
1260 1
1261 2
1262 3
1263 4
1264 4
1265 4
1266 #
1267 # 3. Normal rename with locked tables.
1268 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
1269 SELECT * FROM t3 ORDER BY c1;
1270 c1
1271 1
1272 2
1273 3
1274 4
1275 4
1276 4
1277 RENAME TABLE t2 TO t5;
1278 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
1279 SELECT * FROM t3 ORDER BY c1;
1280 c1
1281 1
1282 2
1283 3
1284 4
1285 4
1286 4
1287 RENAME TABLE t5 TO t2;
1288 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
1289 SELECT * FROM t3 ORDER BY c1;
1290 c1
1291 1
1292 2
1293 3
1294 4
1295 4
1296 4
1297 UNLOCK TABLES;
1298 #
1299 # 4. Alter table rename.
1300 ALTER TABLE t2 RENAME TO t5;
1301 SELECT * FROM t3 ORDER BY c1;
1302 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
1303 ALTER TABLE t5 RENAME TO t2;
1304 SELECT * FROM t3 ORDER BY c1;
1305 c1
1306 1
1307 2
1308 3
1309 4
1310 4
1311 4
1312 #
1313 # 5. Alter table rename with locked tables.
1314 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
1315 ALTER TABLE t2 RENAME TO t5;
1316 SELECT * FROM t3 ORDER BY c1;
1317 ERROR HY000: Table 't2' was not locked with LOCK TABLES
1318 ALTER TABLE t5 RENAME TO t2;
1319 ERROR HY000: Table 't5' was not locked with LOCK TABLES
1320 UNLOCK TABLES;
1321 ALTER TABLE t5 RENAME TO t2;
1322 SELECT * FROM t3 ORDER BY c1;
1323 c1
1324 1
1325 2
1326 3
1327 4
1328 4
1329 4
1330 #
1331 # Rename parent.
1332 #
1333 # 1. Normal rename with locked tables.
1334 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
1335 SELECT * FROM t3 ORDER BY c1;
1336 c1
1337 1
1338 2
1339 3
1340 4
1341 4
1342 4
1343 RENAME TABLE t3 TO t5;
1344 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
1345 SELECT * FROM t3 ORDER BY c1;
1346 c1
1347 1
1348 2
1349 3
1350 4
1351 4
1352 4
1353 RENAME TABLE t5 TO t3;
1354 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
1355 SELECT * FROM t3 ORDER BY c1;
1356 c1
1357 1
1358 2
1359 3
1360 4
1361 4
1362 4
1363 #
1364 # 5. Alter table rename with locked tables.
1365 ALTER TABLE t3 RENAME TO t5;
1366 SELECT * FROM t5 ORDER BY c1;
1367 ERROR HY000: Table 't5' was not locked with LOCK TABLES
1368 ALTER TABLE t5 RENAME TO t3;
1369 ERROR HY000: Table 't5' was not locked with LOCK TABLES
1370 UNLOCK TABLES;
1371 ALTER TABLE t5 RENAME TO t3;
1372 SELECT * FROM t3 ORDER BY c1;
1373 c1
1374 1
1375 2
1376 3
1377 4
1378 4
1379 4
1380 DROP TABLE t1, t2, t3;
1381 #
1382 # Drop locked tables.
1383 #
1384 # 1. Drop parent.
1385 CREATE TABLE t1 (c1 INT, INDEX(c1));
1386 CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
1387 INSERT_METHOD=LAST;
1388 LOCK TABLES t1 WRITE, t2 WRITE;
1389 INSERT INTO t1 VALUES (1);
1390 DROP TABLE t2;
1391 SELECT * FROM t2;
1392 ERROR HY000: Table 't2' was not locked with LOCK TABLES
1393 SELECT * FROM t1;
1394 c1
1395 1
1396 UNLOCK TABLES;
1397 # 2. Drop child.
1398 CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
1399 INSERT_METHOD=LAST;
1400 LOCK TABLES t1 WRITE, t2 WRITE;
1401 INSERT INTO t1 VALUES (1);
1402 DROP TABLE t1;
1403 SELECT * FROM t2;
1404 ERROR HY000: Table 't1' was not locked with LOCK TABLES
1405 SELECT * FROM t1;
1406 ERROR HY000: Table 't1' was not locked with LOCK TABLES
1407 UNLOCK TABLES;
1408 DROP TABLE t2;
1409 #
1410 # ALTER TABLE. Change child list.
1411 #
1412 CREATE TABLE t1 (c1 INT, INDEX(c1));
1413 CREATE TABLE t2 (c1 INT, INDEX(c1));
1414 CREATE TABLE t3 (c1 INT, INDEX(c1));
1415 INSERT INTO t1 VALUES (1);
1416 INSERT INTO t2 VALUES (2);
1417 INSERT INTO t3 VALUES (3);
1418 CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t3,t2)
1419 INSERT_METHOD=LAST;
1420 # Shrink child list.
1421 ALTER TABLE t4 UNION=(t3);
1422 SHOW CREATE TABLE t4;
1423 Table Create Table
1424 t4 CREATE TABLE `t4` (
1425 `c1` int(11) DEFAULT NULL,
1426 KEY `c1` (`c1`)
1427 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t3`)
1428 SELECT * FROM t4 ORDER BY c1;
1429 c1
1430 3
1431 # Extend child list.
1432 ALTER TABLE t4 UNION=(t3,t2);
1433 SHOW CREATE TABLE t4;
1434 Table Create Table
1435 t4 CREATE TABLE `t4` (
1436 `c1` int(11) DEFAULT NULL,
1437 KEY `c1` (`c1`)
1438 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t3`,`t2`)
1439 SELECT * FROM t4 ORDER BY c1;
1440 c1
1441 2
1442 3
1443 #
1444 # ALTER TABLE under LOCK TABLES. Change child list.
1445 #
1446 LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE;
1447 # Shrink child list.
1448 ALTER TABLE t4 UNION=(t3);
1449 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
1450 # Extend child list within locked tables.
1451 ALTER TABLE t4 UNION=(t3,t2);
1452 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
1453 # Extend child list beyond locked tables.
1454 ALTER TABLE t4 UNION=(t3,t2,t1);
1455 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
1456 SHOW CREATE TABLE t4;
1457 Table Create Table
1458 t4 CREATE TABLE `t4` (
1459 `c1` int(11) DEFAULT NULL,
1460 KEY `c1` (`c1`)
1461 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t3`,`t2`)
1462 SELECT * FROM t4 ORDER BY c1;
1463 c1
1464 2
1465 3
1466 UNLOCK TABLES;
1467 DROP TABLE t4;
1468 #
1469 # ALTER TABLE under LOCK TABLES. Grave change, table re-creation.
1470 #
1471 CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
1472 INSERT_METHOD=LAST;
1473 # Lock parent first and then children.
1474 LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
1475 ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1476 SELECT * FROM t4 ORDER BY c1;
1477 c1
1478 1
1479 2
1480 3
1481 ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1482 SELECT * FROM t4 ORDER BY c1;
1483 c1
1484 1
1485 2
1486 3
1487 UNLOCK TABLES;
1488 # Lock children first and then parent.
1489 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
1490 ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1491 SELECT * FROM t4 ORDER BY c1;
1492 c1
1493 1
1494 2
1495 3
1496 ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1497 SELECT * FROM t4 ORDER BY c1;
1498 c1
1499 1
1500 2
1501 3
1502 UNLOCK TABLES;
1503 # Lock parent between children.
1504 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1505 ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1506 SELECT * FROM t4 ORDER BY c1;
1507 c1
1508 1
1509 2
1510 3
1511 ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1512 SELECT * FROM t4 ORDER BY c1;
1513 c1
1514 1
1515 2
1516 3
1517 UNLOCK TABLES;
1518 DROP TABLE t1, t2, t3, t4;
1519 #
1520 # ALTER TABLE under LOCK TABLES. Simple change, no re-creation.
1521 #
1522 CREATE TABLE t1 (c1 INT);
1523 CREATE TABLE t2 (c1 INT);
1524 CREATE TABLE t3 (c1 INT);
1525 CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
1526 INSERT_METHOD=LAST;
1527 INSERT INTO t1 VALUES (1);
1528 INSERT INTO t2 VALUES (2);
1529 INSERT INTO t3 VALUES (3);
1530 # Lock parent first and then children.
1531 LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
1532 ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
1533 SELECT * FROM t4 ORDER BY c1;
1534 c1
1535 1
1536 2
1537 3
1538 ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
1539 SELECT * FROM t4 ORDER BY c1;
1540 c1
1541 1
1542 2
1543 3
1544 UNLOCK TABLES;
1545 # Lock children first and then parent.
1546 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
1547 ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
1548 SELECT * FROM t4 ORDER BY c1;
1549 c1
1550 1
1551 2
1552 3
1553 ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
1554 SELECT * FROM t4 ORDER BY c1;
1555 c1
1556 1
1557 2
1558 3
1559 UNLOCK TABLES;
1560 # Lock parent between children.
1561 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1562 ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
1563 SELECT * FROM t4 ORDER BY c1;
1564 c1
1565 1
1566 2
1567 3
1568 ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
1569 SELECT * FROM t4 ORDER BY c1;
1570 c1
1571 1
1572 2
1573 3
1574 UNLOCK TABLES;
1575 #
1576 # FLUSH TABLE under LOCK TABLES.
1577 #
1578 # Lock parent first and then children.
1579 LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
1580 FLUSH TABLE t4;
1581 SELECT * FROM t4 ORDER BY c1;
1582 c1
1583 1
1584 2
1585 3
1586 FLUSH TABLE t2;
1587 SELECT * FROM t4 ORDER BY c1;
1588 c1
1589 1
1590 2
1591 3
1592 FLUSH TABLES;
1593 SELECT * FROM t4 ORDER BY c1;
1594 c1
1595 1
1596 2
1597 3
1598 UNLOCK TABLES;
1599 # Lock children first and then parent.
1600 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
1601 FLUSH TABLE t4;
1602 SELECT * FROM t4 ORDER BY c1;
1603 c1
1604 1
1605 2
1606 3
1607 FLUSH TABLE t2;
1608 SELECT * FROM t4 ORDER BY c1;
1609 c1
1610 1
1611 2
1612 3
1613 FLUSH TABLES;
1614 SELECT * FROM t4 ORDER BY c1;
1615 c1
1616 1
1617 2
1618 3
1619 UNLOCK TABLES;
1620 # Lock parent between children.
1621 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1622 FLUSH TABLE t4;
1623 SELECT * FROM t4 ORDER BY c1;
1624 c1
1625 1
1626 2
1627 3
1628 FLUSH TABLE t2;
1629 SELECT * FROM t4 ORDER BY c1;
1630 c1
1631 1
1632 2
1633 3
1634 FLUSH TABLES;
1635 SELECT * FROM t4 ORDER BY c1;
1636 c1
1637 1
1638 2
1639 3
1640 UNLOCK TABLES;
1641 #
1642 # Triggers
1643 #
1644 # Trigger on parent
1645 DELETE FROM t4 WHERE c1 = 4;
1646 CREATE TRIGGER t4_ai1 AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
1647 SET @a=0;
1648 INSERT INTO t4 VALUES (4);
1649 SELECT @a;
1650 @a
1651 1
1652 SELECT * FROM t4 ORDER BY c1;
1653 c1
1654 1
1655 2
1656 3
1657 4
1658 DROP TRIGGER t4_ai1;
1659 CHECK TABLE t3;
1660 Table Op Msg_type Msg_text
1661 test.t3 check status OK
1662 # Trigger on parent under LOCK TABLES
1663 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1664 CREATE TRIGGER t4_ai2 AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
1665 SET @a=0;
1666 INSERT INTO t4 VALUES (4);
1667 SELECT @a;
1668 @a
1669 1
1670 SELECT * FROM t4 ORDER BY c1;
1671 c1
1672 1
1673 2
1674 3
1675 4
1676 4
1677 DROP TRIGGER t4_ai2;
1678 UNLOCK TABLES;
1679 CHECK TABLE t3;
1680 Table Op Msg_type Msg_text
1681 test.t3 check status OK
1682 #
1683 # Trigger on child
1684 DELETE FROM t4 WHERE c1 = 4;
1685 CREATE TRIGGER t3_ai3 AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
1686 SET @a=0;
1687 INSERT INTO t4 VALUES (4);
1688 SELECT @a;
1689 @a
1690 0
1691 INSERT INTO t3 VALUES (33);
1692 SELECT @a;
1693 @a
1694 1
1695 SELECT * FROM t4 ORDER BY c1;
1696 c1
1697 1
1698 2
1699 3
1700 4
1701 33
1702 DROP TRIGGER t3_ai3;
1703 CHECK TABLE t3;
1704 Table Op Msg_type Msg_text
1705 test.t3 check status OK
1706 # Trigger on child under LOCK TABLES
1707 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1708 CREATE TRIGGER t3_ai4 AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
1709 SET @a=0;
1710 INSERT INTO t4 VALUES (4);
1711 SELECT @a;
1712 @a
1713 0
1714 INSERT INTO t3 VALUES (33);
1715 SELECT @a;
1716 @a
1717 1
1718 SELECT * FROM t4 ORDER BY c1;
1719 c1
1720 1
1721 2
1722 3
1723 4
1724 4
1725 33
1726 33
1727 DELETE FROM t4 WHERE c1 = 33;
1728 DROP TRIGGER t3_ai4;
1729 UNLOCK TABLES;
1730 CHECK TABLE t3;
1731 Table Op Msg_type Msg_text
1732 test.t3 check status OK
1733 #
1734 # Trigger with table use on child
1735 DELETE FROM t4 WHERE c1 = 4;
1736 CREATE TRIGGER t3_ai5 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
1737 SELECT COUNT(*) FROM t2;
1738 COUNT(*)
1739 1
1740 INSERT INTO t4 VALUES (4);
1741 SELECT * FROM t4 ORDER BY c1;
1742 c1
1743 1
1744 2
1745 3
1746 4
1747 INSERT INTO t3 VALUES (33);
1748 SELECT * FROM t4 ORDER BY c1;
1749 c1
1750 1
1751 2
1752 3
1753 4
1754 22
1755 33
1756 DELETE FROM t4 WHERE c1 = 22;
1757 DELETE FROM t4 WHERE c1 = 33;
1758 DROP TRIGGER t3_ai5;
1759 UNLOCK TABLES;
1760 CHECK TABLE t2,t3;
1761 Table Op Msg_type Msg_text
1762 test.t2 check status OK
1763 test.t3 check status OK
1764 # Trigger with table use on child under LOCK TABLES
1765 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1766 CREATE TRIGGER t3_ai6 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
1767 INSERT INTO t4 VALUES (4);
1768 SELECT * FROM t4 ORDER BY c1;
1769 c1
1770 1
1771 2
1772 3
1773 4
1774 4
1775 INSERT INTO t3 VALUES (33);
1776 SELECT * FROM t4 ORDER BY c1;
1777 c1
1778 1
1779 2
1780 3
1781 4
1782 4
1783 22
1784 33
1785 DROP TRIGGER t3_ai6;
1786 UNLOCK TABLES;
1787 check table t2,t3,t4;
1788 Table Op Msg_type Msg_text
1789 test.t2 check status OK
1790 test.t3 check status OK
1791 test.t4 check status OK
1792 DELETE FROM t4 WHERE c1 = 22;
1793 DELETE FROM t4 WHERE c1 = 33;
1794 # Trigger with table use on child under different LOCK TABLES
1795 DELETE FROM t4 WHERE c1 = 4;
1796 LOCK TABLES t4 WRITE,t3 WRITE, t2 WRITE, t1 WRITE;
1797 CREATE TRIGGER t3_ai7 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
1798 INSERT INTO t4 VALUES (4);
1799 SELECT * FROM t4 ORDER BY c1;
1800 c1
1801 1
1802 2
1803 3
1804 4
1805 INSERT INTO t3 VALUES (33);
1806 SELECT * FROM t4 ORDER BY c1;
1807 c1
1808 1
1809 2
1810 3
1811 4
1812 22
1813 33
1814 DROP TRIGGER t3_ai7;
1815 UNLOCK TABLES;
1816 check table t2,t3,t4;
1817 Table Op Msg_type Msg_text
1818 test.t2 check status OK
1819 test.t3 check status OK
1820 test.t4 check status OK
1821 DELETE FROM t4 WHERE c1 = 22;
1822 DELETE FROM t4 WHERE c1 = 33;
1823 #
1824 # Repair
1825 #
1826 REPAIR TABLE t4;
1827 Table Op Msg_type Msg_text
1828 test.t4 repair status OK
1829 REPAIR TABLE t2;
1830 Table Op Msg_type Msg_text
1831 test.t2 repair status OK
1832 SELECT * FROM t4 ORDER BY c1;
1833 c1
1834 1
1835 2
1836 3
1837 4
1838 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1839 REPAIR TABLE t4;
1840 Table Op Msg_type Msg_text
1841 test.t4 repair status OK
1842 REPAIR TABLE t2;
1843 Table Op Msg_type Msg_text
1844 test.t2 repair status OK
1845 SELECT * FROM t4 ORDER BY c1;
1846 c1
1847 1
1848 2
1849 3
1850 4
1851 UNLOCK TABLES;
1852 #
1853 # Optimize
1854 #
1855 OPTIMIZE TABLE t4;
1856 Table Op Msg_type Msg_text
1857 test.t4 optimize note The storage engine for the table doesn't support optimize
1858 OPTIMIZE TABLE t2;
1859 Table Op Msg_type Msg_text
1860 test.t2 optimize status OK
1861 SELECT * FROM t4 ORDER BY c1;
1862 c1
1863 1
1864 2
1865 3
1866 4
1867 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1868 OPTIMIZE TABLE t4;
1869 Table Op Msg_type Msg_text
1870 test.t4 optimize note The storage engine for the table doesn't support optimize
1871 OPTIMIZE TABLE t2;
1872 Table Op Msg_type Msg_text
1873 test.t2 optimize status Table is already up to date
1874 SELECT * FROM t4 ORDER BY c1;
1875 c1
1876 1
1877 2
1878 3
1879 4
1880 UNLOCK TABLES;
1881 #
1882 # Checksum
1883 #
1884 CHECKSUM TABLE t4;
1885 Table Checksum
1886 test.t4 149057747
1887 CHECKSUM TABLE t2;
1888 Table Checksum
1889 test.t2 3700403066
1890 SELECT * FROM t4 ORDER BY c1;
1891 c1
1892 1
1893 2
1894 3
1895 4
1896 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1897 CHECKSUM TABLE t4;
1898 Table Checksum
1899 test.t4 149057747
1900 CHECKSUM TABLE t2;
1901 Table Checksum
1902 test.t2 3700403066
1903 SELECT * FROM t4 ORDER BY c1;
1904 c1
1905 1
1906 2
1907 3
1908 4
1909 UNLOCK TABLES;
1910 #
1911 # Insert delayed
1912 #
1913 INSERT DELAYED INTO t4 VALUES(44);
1914 DELETE FROM t4 WHERE c1 = 44;
1915 INSERT DELAYED INTO t3 VALUES(33);
1916 SELECT * FROM t4 ORDER BY c1;
1917 c1
1918 1
1919 2
1920 3
1921 4
1922 33
1923 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1924 INSERT DELAYED INTO t4 VALUES(444);
1925 Got one of the listed errors
1926 INSERT DELAYED INTO t3 VALUES(333);
1927 Got one of the listed errors
1928 SELECT * FROM t4 ORDER BY c1;
1929 c1
1930 1
1931 2
1932 3
1933 4
1934 33
1935 UNLOCK TABLES;
1936 DROP TABLE t1, t2, t3, t4;
1937 #
1938 # Recursive inclusion of merge tables in their union clauses.
1939 #
1940 CREATE TABLE t1 (c1 INT, INDEX(c1));
1941 CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
1942 INSERT_METHOD=LAST;
1943 CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t2,t1)
1944 INSERT_METHOD=LAST;
1945 ALTER TABLE t2 UNION=(t3,t1);
1946 SELECT * FROM t2;
1947 ERROR HY000: Table 't3' is differently defined or of non-MyISAM type or doesn't exist
1948 DROP TABLE t1, t2, t3;
1949 CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
1950 CREATE TABLE t2 (c1 INT) ENGINE= MyISAM;
1951 CREATE TABLE t3 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1, t2);
1952 INSERT INTO t1 VALUES (1);
1953 INSERT INTO t2 VALUES (2);
1954 SELECT * FROM t3;
1955 c1
1956 1
1957 2
1958 TRUNCATE TABLE t1;
1959 SELECT * FROM t3;
1960 c1
1961 2
1962 DROP TABLE t1, t2, t3;
1963 CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
1964 SET @rnd_max= 2147483647;
1965 set @@read_buffer_size=2*1024*1024;
1966 CREATE TABLE t2 SELECT * FROM t1;
1967 INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
1968 INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
1969 INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
1970 INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
1971 INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
1972 CREATE TABLE t3 (id INTEGER, grp TINYINT, id_rev INTEGER)
1973 ENGINE= MRG_MYISAM UNION= (t1, t2);
1974 SELECT COUNT(*) FROM t1;
1975 COUNT(*)
1976 130
1977 SELECT COUNT(*) FROM t2;
1978 COUNT(*)
1979 80
1980 SELECT COUNT(*) FROM t3;
1981 COUNT(*)
1982 210
1983 connect con1,localhost,root,,;
1984 SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2
1985 WHERE a1.id = a2.id GROUP BY a2.grp;
1986 connection default;
1987 TRUNCATE TABLE t1;
1988 connection con1;
1989 disconnect con1;
1990 connection default;
1991 SELECT COUNT(*) FROM t1;
1992 COUNT(*)
1993 0
1994 SELECT COUNT(*) FROM t2;
1995 COUNT(*)
1996 80
1997 SELECT COUNT(*) FROM t3;
1998 COUNT(*)
1999 80
2000 DROP TABLE t1, t2, t3;
2001 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2002 CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2003 INSERT INTO t2 VALUES (1);
2004 SELECT * FROM t2;
2005 c1
2006 1
2007 LOCK TABLES t2 WRITE, t1 WRITE;
2008 FLUSH TABLES;
2009 REPAIR TABLE t1;
2010 Table Op Msg_type Msg_text
2011 test.t1 repair status OK
2012 CHECK TABLE t1;
2013 Table Op Msg_type Msg_text
2014 test.t1 check status OK
2015 REPAIR TABLE t1;
2016 Table Op Msg_type Msg_text
2017 test.t1 repair status OK
2018 UNLOCK TABLES;
2019 CHECK TABLE t1 EXTENDED;
2020 Table Op Msg_type Msg_text
2021 test.t1 check status OK
2022 LOCK TABLES t2 WRITE, t1 WRITE;
2023 REPAIR TABLE t1;
2024 Table Op Msg_type Msg_text
2025 test.t1 repair status OK
2026 CHECK TABLE t1;
2027 Table Op Msg_type Msg_text
2028 test.t1 check status OK
2029 REPAIR TABLE t1;
2030 Table Op Msg_type Msg_text
2031 test.t1 repair status OK
2032 UNLOCK TABLES;
2033 CHECK TABLE t1 EXTENDED;
2034 Table Op Msg_type Msg_text
2035 test.t1 check status OK
2036 DROP TABLE t1, t2;
2037 CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
2038 CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
2039 LOCK TABLES t1 WRITE, m1 WRITE;
2040 FLUSH TABLE t1;
2041 UNLOCK TABLES;
2042 DROP TABLE m1, t1;
2043 CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
2044 CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
2045 LOCK TABLES m1 WRITE, t1 WRITE;
2046 FLUSH TABLE t1;
2047 UNLOCK TABLES;
2048 DROP TABLE m1, t1;
2049 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE= MyISAM;
2050 CREATE TABLE t2 (c1 INT, c2 INT) ENGINE= MyISAM;
2051 CREATE TABLE t3 (c1 INT, c2 INT) ENGINE= MRG_MYISAM UNION(t1, t2);
2052 INSERT INTO t1 VALUES (1, 1);
2053 INSERT INTO t2 VALUES (2, 2);
2054 SELECT * FROM t3;
2055 c1 c2
2056 1 1
2057 2 2
2058 ALTER TABLE t1 ENGINE= MEMORY;
2059 INSERT INTO t1 VALUES (0, 0);
2060 SELECT * FROM t3;
2061 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2062 DROP TABLE t1, t2, t3;
2063 CREATE TABLE t1 (c1 INT, KEY(c1));
2064 CREATE TABLE t2 (c1 INT, KEY(c1)) ENGINE=MRG_MYISAM UNION=(t1)
2065 INSERT_METHOD=FIRST;
2066 LOCK TABLE t1 WRITE, t2 WRITE;
2067 FLUSH TABLES t2, t1;
2068 OPTIMIZE TABLE t1;
2069 Table Op Msg_type Msg_text
2070 test.t1 optimize status Table is already up to date
2071 FLUSH TABLES t1;
2072 UNLOCK TABLES;
2073 FLUSH TABLES;
2074 INSERT INTO t1 VALUES (1);
2075 LOCK TABLE t1 WRITE, t2 WRITE;
2076 FLUSH TABLES t2, t1;
2077 OPTIMIZE TABLE t1;
2078 Table Op Msg_type Msg_text
2079 test.t1 optimize status OK
2080 FLUSH TABLES t1;
2081 UNLOCK TABLES;
2082 DROP TABLE t1, t2;
2083 CREATE TABLE t1 (ID INT) ENGINE=MYISAM;
2084 CREATE TABLE m1 (ID INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
2085 INSERT INTO t1 VALUES ();
2086 INSERT INTO m1 VALUES ();
2087 LOCK TABLE t1 WRITE, m1 WRITE;
2088 FLUSH TABLES m1, t1;
2089 OPTIMIZE TABLE t1;
2090 Table Op Msg_type Msg_text
2091 test.t1 optimize status OK
2092 FLUSH TABLES m1, t1;
2093 UNLOCK TABLES;
2094 DROP TABLE t1, m1;
2095 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
2096 SELECT table_schema, table_name, table_type, engine, version, row_format, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='tm1';
2097 table_schema table_name table_type engine version row_format table_comment
2098 test tm1 BASE TABLE NULL NULL NULL Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2099 Warnings:
2100 Warning 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2101 DROP TABLE tm1;
2102 CREATE TABLE t1(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
2103 CREATE TABLE t2(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
2104 CREATE TABLE t3(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
2105 CREATE TABLE t4(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2))
2106 ENGINE=MRG_MYISAM UNION=(t1, t2, t3);
2107 INSERT INTO t1 VALUES (1,1), (1,2),(1,3), (1,4);
2108 INSERT INTO t2 VALUES (2,1), (2,2),(2,3), (2,4);
2109 INSERT INTO t3 VALUES (3,1), (3,2),(3,3), (3,4);
2110 EXPLAIN SELECT COUNT(*) FROM t1;
2111 id select_type table type possible_keys key key_len ref rows Extra
2112 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2113 EXPLAIN SELECT COUNT(*) FROM t4;
2114 id select_type table type possible_keys key key_len ref rows Extra
2115 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2116 DROP TABLE t1, t2, t3, t4;
2117 CREATE TABLE t1(a INT, KEY(a));
2118 INSERT INTO t1 VALUES(0),(1),(2),(3),(4);
2119 ANALYZE TABLE t1;
2120 Table Op Msg_type Msg_text
2121 test.t1 analyze status Engine-independent statistics collected
2122 test.t1 analyze status OK
2123 CREATE TABLE m1(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
2124 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
2125 CARDINALITY
2126 5
2127 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
2128 CARDINALITY
2129 5
2130 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
2131 CARDINALITY
2132 5
2133 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
2134 CARDINALITY
2135 5
2136 DROP TABLE t1, m1;
2137 #
2138 # Bug #40675 MySQL 5.1 crash with index merge algorithm and Merge tables
2139 #
2140 # create MYISAM table t1 and insert values into it
2141 CREATE TABLE t1(a INT);
2142 INSERT INTO t1 VALUES(1);
2143 # create MYISAM table t2 and insert values into it
2144 CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
2145 INSERT INTO t2(a,b) VALUES
2146 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2147 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2148 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2149 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2150 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2151 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2152 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2153 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2154 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2155 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2156 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2157 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2158 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2159 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2160 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2161 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2162 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2163 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
2164 (1,2);
2165 # Create the merge table t3
2166 CREATE TABLE t3(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b))
2167 ENGINE=MERGE UNION=(t2) INSERT_METHOD=FIRST;
2168 # Lock tables t1 and t3 for write
2169 LOCK TABLES t1 WRITE, t3 WRITE;
2170 # Insert values into the merge table t3
2171 INSERT INTO t3(a,b) VALUES(1,2);
2172 # select from the join of t2 and t3 (The merge table)
2173 SELECT t3.a FROM t1,t3 WHERE t3.b=2 AND t3.a=1;
2174 a
2175 1
2176 1
2177 # Unlock the tables
2178 UNLOCK TABLES;
2179 # drop the created tables
2180 DROP TABLE t1, t2, t3;
2181 # insert duplicate value in child table while merge table doesn't have key
2182 create table t1 (
2183 col1 int(10),
2184 primary key (col1)
2185 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2186 CREATE TABLE m1 (
2187 col1 int(10) NOT NULL
2188 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1);
2189 insert into m1 (col1) values (1);
2190 insert into m1 (col1) values (1);
2191 ERROR 23000: Can't write; duplicate key in table 'm1'
2192 drop table m1, t1;
2193 #
2194 # Bug#45800 crash when replacing into a merge table and there is a duplicate
2195 #
2196 # Replace duplicate value in child table when merge table doesn't have key
2197 CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM;
2198 CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
2199 INSERT INTO m1 VALUES (666);
2200 SELECT * FROM m1;
2201 c1
2202 666
2203 # insert the duplicate value into the merge table
2204 REPLACE INTO m1 VALUES (666);
2205 SELECT * FROM m1;
2206 c1
2207 666
2208 DROP TABLE m1, t1;
2209 # Insert... on duplicate key update (with duplicate values in the table)
2210 CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM;
2211 CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
2212 INSERT INTO m1 VALUES (666);
2213 SELECT * FROM m1;
2214 c1
2215 666
2216 # insert the duplicate value into the merge table
2217 INSERT INTO m1 VALUES (666) ON DUPLICATE KEY UPDATE c1=c1+1;
2218 SELECT * FROM m1;
2219 c1
2220 667
2221 DROP TABLE m1, t1;
2222 # Insert duplicate value on MERGE table, where, MERGE has a key but MyISAM has more keys
2223 CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1), UNIQUE (c2));
2224 CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c1)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
2225 INSERT INTO m1 VALUES (1,2);
2226 # insert the duplicate value into the merge table
2227 INSERT INTO m1 VALUES (3,2);
2228 ERROR 23000: Can't write; duplicate key in table 'm1'
2229 DROP TABLE m1,t1;
2230 # Try to define MERGE and MyISAM with keys on different columns
2231 CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1));
2232 CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c2)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
2233 # Try accessing the merge table for inserts (error occurs)
2234 INSERT INTO m1 VALUES (1,2);
2235 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2236 INSERT INTO m1 VALUES (1,4);
2237 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2238 DROP TABLE m1,t1;
2239 CREATE TABLE t1 (
2240 col1 INT(10)
2241 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2242 CREATE VIEW v1 as SELECT * FROM t1;
2243 CREATE TABLE m1 (
2244 col1 INT(10)
2245 )ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(v1);
2246 #Select should detect that the child table is a view and fail.
2247 SELECT * FROM m1;
2248 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2249 DROP VIEW v1;
2250 DROP TABLE m1, t1;
2251 #
2252 # Bug #45796: invalid memory reads and writes when altering merge and
2253 # base tables
2254 #
2255 CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
2256 CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1);
2257 ALTER TABLE m1 ADD INDEX idx_c1(c1);
2258 SELECT * FROM m1;
2259 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2260 ALTER TABLE t1 ADD INDEX idx_c1(c1);
2261 SELECT * FROM m1;
2262 c1
2263 DROP TABLE m1;
2264 DROP TABLE t1;
2265 #
2266 # Bug45781 infinite hang/crash in "opening tables" after handler tries to
2267 # open merge table
2268 #
2269 CREATE TABLE t1(a int)engine=myisam;
2270 CREATE TABLE t2(a int)engine=myisam;
2271 CREATE TABLE t3(a int)engine=myisam;
2272 CREATE TABLE t4(a int)engine=myisam;
2273 CREATE TABLE t5(a int)engine=myisam;
2274 CREATE TABLE t6(a int)engine=myisam;
2275 CREATE TABLE t7(a int)engine=myisam;
2276 CREATE TABLE m1(a int)engine=merge union=(t1,t2,t3,t4,t5,t6,t7);
2277 SELECT 1 FROM m1;
2278 1
2279 HANDLER m1 OPEN;
2280 ERROR HY000: Storage engine MRG_MyISAM of the table `test`.`m1` doesn't have this option
2281 DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7;
2282 SELECT 1 FROM m1;
2283 ERROR 42S02: Table 'test.m1' doesn't exist
2284 #
2285 # Bug #46614: Assertion in show_create_trigger()
2286 #
2287 CREATE TABLE t1(a int);
2288 CREATE TABLE t2(a int);
2289 CREATE TABLE t3(a int) ENGINE = MERGE UNION(t1, t2);
2290 CREATE TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo();
2291 SHOW CREATE TRIGGER tr1;
2292 Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created
2293 tr1 STRICT_TRANS_TABLES,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 #
2294 DROP TRIGGER tr1;
2295 DROP TABLE t1, t2, t3;
2296 #
2297 # BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40)
2298 #
2299 CREATE DATABASE `test/1`;
2300 CREATE TABLE `test/1`.`t/1`(a INT);
2301 CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
2302 SELECT * FROM m1;
2303 a
2304 SHOW CREATE TABLE m1;
2305 Table Create Table
2306 m1 CREATE TABLE `m1` (
2307 `a` int(11) DEFAULT NULL
2308 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`test/1`.`t/1`)
2309 DROP TABLE m1;
2310 CREATE TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
2311 SELECT * FROM `test/1`.m1;
2312 a
2313 SHOW CREATE TABLE `test/1`.m1;
2314 Table Create Table
2315 m1 CREATE TABLE `m1` (
2316 `a` int(11) DEFAULT NULL
2317 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci UNION=(`t/1`)
2318 DROP TABLE `test/1`.m1;
2319 DROP TABLE `test/1`.`t/1`;
2320 CREATE TEMPORARY TABLE `test/1`.`t/1`(a INT);
2321 CREATE TEMPORARY TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
2322 SELECT * FROM m1;
2323 a
2324 SHOW CREATE TABLE m1;
2325 Table Create Table
2326 m1 CREATE TEMPORARY TABLE `m1` (
2327 `a` int(11) DEFAULT NULL
2328 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`test/1`.`t/1`)
2329 DROP TABLE m1;
2330 CREATE TEMPORARY TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
2331 SELECT * FROM `test/1`.m1;
2332 a
2333 SHOW CREATE TABLE `test/1`.m1;
2334 Table Create Table
2335 m1 CREATE TEMPORARY TABLE `m1` (
2336 `a` int(11) DEFAULT NULL
2337 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci UNION=(`t/1`)
2338 DROP TABLE `test/1`.m1;
2339 DROP TABLE `test/1`.`t/1`;
2340 DROP DATABASE `test/1`;
2341 CREATE TABLE `t@1`(a INT);
2342 SELECT * FROM m1;
2343 a
2344 SHOW CREATE TABLE m1;
2345 Table Create Table
2346 m1 CREATE TABLE `m1` (
2347 `a` int(11) DEFAULT NULL
2348 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t@1`)
2349 DROP TABLE `t@1`;
2350 CREATE DATABASE `test@1`;
2351 CREATE TABLE `test@1`.`t@1`(a INT);
2352 FLUSH TABLE m1;
2353 SELECT * FROM m1;
2354 a
2355 SHOW CREATE TABLE m1;
2356 Table Create Table
2357 m1 CREATE TABLE `m1` (
2358 `a` int(11) DEFAULT NULL
2359 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`test@1`.`t@1`)
2360 DROP TABLE m1;
2361 DROP TABLE `test@1`.`t@1`;
2362 DROP DATABASE `test@1`;
2363 #
2364 # Bug#51494c rash with join, explain and 'sounds like' operator
2365 #
2366 CREATE TABLE t1 (a INT) ENGINE=MYISAM;
2367 INSERT INTO t1 VALUES(1);
2368 CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL,
2369 KEY idx0 (d, c)) ENGINE=MERGE;
2370 EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN
2371 t2 WHERE b SOUNDS LIKE e AND d = 1;
2372 id select_type table type possible_keys key key_len ref rows Extra
2373 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2374 DROP TABLE t2, t1;
2375 #
2376 # Bug#46339 - crash on REPAIR TABLE merge table USE_FRM
2377 #
2378 CREATE TABLE t1 (c1 INT) ENGINE=MYISAM;
2379 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST;
2380 LOCK TABLE m1 READ;
2381 REPAIR TABLE m1 USE_FRM;
2382 Table Op Msg_type Msg_text
2383 test.m1 repair Error Table 'm1' was locked with a READ lock and can't be updated
2384 test.m1 repair status Operation failed
2385 UNLOCK TABLES;
2386 REPAIR TABLE m1 USE_FRM;
2387 Table Op Msg_type Msg_text
2388 test.m1 repair note The storage engine for the table doesn't support repair
2389 DROP TABLE m1,t1;
2390 CREATE TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1);
2391 REPAIR TABLE m1 USE_FRM;
2392 Table Op Msg_type Msg_text
2393 test.m1 repair Warning Can't open table
2394 test.m1 repair error Corrupt
2395 CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM;
2396 REPAIR TABLE m1 USE_FRM;
2397 Table Op Msg_type Msg_text
2398 test.m1 repair note The storage engine for the table doesn't support repair
2399 REPAIR TABLE m1;
2400 Table Op Msg_type Msg_text
2401 test.m1 repair status OK
2402 DROP TABLE m1, t1;
2403 CREATE TEMPORARY TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1);
2404 REPAIR TABLE m1 USE_FRM;
2405 Table Op Msg_type Msg_text
2406 test.m1 repair Error Table 'test.m1' doesn't exist
2407 test.m1 repair error Corrupt
2408 CREATE TEMPORARY TABLE t1 (f1 BIGINT) ENGINE=MyISAM;
2409 REPAIR TABLE m1 USE_FRM;
2410 Table Op Msg_type Msg_text
2411 m1 repair error Cannot repair temporary table from .frm file
2412 REPAIR TABLE m1;
2413 Table Op Msg_type Msg_text
2414 test.m1 repair status OK
2415 DROP TABLE m1, t1;
2416 create temporary table t1_temp(i int);
2417 create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last;
2418 alter table tm_temp_temp insert_method=first;
2419 check table tm_temp_temp;
2420 Table Op Msg_type Msg_text
2421 test.tm_temp_temp check status OK
2422 drop temporary table t1_temp, tm_temp_temp;
2423 # End of 5.1 tests
2424 #
2425 # MDEV-4277: Crash inside mi_killed_in_mariadb() with myisammrg
2426 #
2427 create table t1 (a int);
2428 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2429 create table t2 ( i int ) engine=myisam;
2430 insert into t2 values (1),(2);
2431 create table t3 (a int, b int, filler char(100), key(a), key(b)) engine=myisam;
2432 create table t4 like t3;
2433 insert into t3
2434 select A.a+10*B.a+100*C.a,
2435 A.a+10*B.a+100*C.a,
2436 'filler-data-FILLER-DATA-qqq'
2437 from t1 A, t1 B, t1 C where C.a < 5;
2438 insert into t4
2439 select A.a+10*B.a+100*C.a,
2440 A.a+10*B.a+100*C.a,
2441 'filler-data-FILLER-DATA-qqq'
2442 from t1 A, t1 B, t1 C where C.a >= 5;
2443 create table t5 like t3;
2444 alter table t5 engine=merge;
2445 alter table t5 union(t3, t4);
2446 update t5 set b=999, a=999 where b>950;
2447 explain
2448 select * from t2, t5 where t5.a=999 and t5.b=999;
2449 id select_type table type possible_keys key key_len ref rows Extra
2450 1 SIMPLE t2 ALL NULL NULL NULL NULL 2
2451 1 SIMPLE t5 index_merge a,b a,b 5,5 NULL 6 Using intersect(a,b); Using where; Using join buffer (flat, BNL join)
2452 select * from t2, t5 where t5.a=999 and t5.b=999;
2453 i a b filler
2454 1 999 999 filler-data-FILLER-DATA-qqq
2455 2 999 999 filler-data-FILLER-DATA-qqq
2456 1 999 999 filler-data-FILLER-DATA-qqq
2457 2 999 999 filler-data-FILLER-DATA-qqq
2458 1 999 999 filler-data-FILLER-DATA-qqq
2459 2 999 999 filler-data-FILLER-DATA-qqq
2460 1 999 999 filler-data-FILLER-DATA-qqq
2461 2 999 999 filler-data-FILLER-DATA-qqq
2462 1 999 999 filler-data-FILLER-DATA-qqq
2463 2 999 999 filler-data-FILLER-DATA-qqq
2464 1 999 999 filler-data-FILLER-DATA-qqq
2465 2 999 999 filler-data-FILLER-DATA-qqq
2466 1 999 999 filler-data-FILLER-DATA-qqq
2467 2 999 999 filler-data-FILLER-DATA-qqq
2468 1 999 999 filler-data-FILLER-DATA-qqq
2469 2 999 999 filler-data-FILLER-DATA-qqq
2470 1 999 999 filler-data-FILLER-DATA-qqq
2471 2 999 999 filler-data-FILLER-DATA-qqq
2472 1 999 999 filler-data-FILLER-DATA-qqq
2473 2 999 999 filler-data-FILLER-DATA-qqq
2474 1 999 999 filler-data-FILLER-DATA-qqq
2475 2 999 999 filler-data-FILLER-DATA-qqq
2476 1 999 999 filler-data-FILLER-DATA-qqq
2477 2 999 999 filler-data-FILLER-DATA-qqq
2478 1 999 999 filler-data-FILLER-DATA-qqq
2479 2 999 999 filler-data-FILLER-DATA-qqq
2480 1 999 999 filler-data-FILLER-DATA-qqq
2481 2 999 999 filler-data-FILLER-DATA-qqq
2482 1 999 999 filler-data-FILLER-DATA-qqq
2483 2 999 999 filler-data-FILLER-DATA-qqq
2484 1 999 999 filler-data-FILLER-DATA-qqq
2485 2 999 999 filler-data-FILLER-DATA-qqq
2486 1 999 999 filler-data-FILLER-DATA-qqq
2487 2 999 999 filler-data-FILLER-DATA-qqq
2488 1 999 999 filler-data-FILLER-DATA-qqq
2489 2 999 999 filler-data-FILLER-DATA-qqq
2490 1 999 999 filler-data-FILLER-DATA-qqq
2491 2 999 999 filler-data-FILLER-DATA-qqq
2492 1 999 999 filler-data-FILLER-DATA-qqq
2493 2 999 999 filler-data-FILLER-DATA-qqq
2494 1 999 999 filler-data-FILLER-DATA-qqq
2495 2 999 999 filler-data-FILLER-DATA-qqq
2496 1 999 999 filler-data-FILLER-DATA-qqq
2497 2 999 999 filler-data-FILLER-DATA-qqq
2498 1 999 999 filler-data-FILLER-DATA-qqq
2499 2 999 999 filler-data-FILLER-DATA-qqq
2500 1 999 999 filler-data-FILLER-DATA-qqq
2501 2 999 999 filler-data-FILLER-DATA-qqq
2502 1 999 999 filler-data-FILLER-DATA-qqq
2503 2 999 999 filler-data-FILLER-DATA-qqq
2504 1 999 999 filler-data-FILLER-DATA-qqq
2505 2 999 999 filler-data-FILLER-DATA-qqq
2506 1 999 999 filler-data-FILLER-DATA-qqq
2507 2 999 999 filler-data-FILLER-DATA-qqq
2508 1 999 999 filler-data-FILLER-DATA-qqq
2509 2 999 999 filler-data-FILLER-DATA-qqq
2510 1 999 999 filler-data-FILLER-DATA-qqq
2511 2 999 999 filler-data-FILLER-DATA-qqq
2512 1 999 999 filler-data-FILLER-DATA-qqq
2513 2 999 999 filler-data-FILLER-DATA-qqq
2514 1 999 999 filler-data-FILLER-DATA-qqq
2515 2 999 999 filler-data-FILLER-DATA-qqq
2516 1 999 999 filler-data-FILLER-DATA-qqq
2517 2 999 999 filler-data-FILLER-DATA-qqq
2518 1 999 999 filler-data-FILLER-DATA-qqq
2519 2 999 999 filler-data-FILLER-DATA-qqq
2520 1 999 999 filler-data-FILLER-DATA-qqq
2521 2 999 999 filler-data-FILLER-DATA-qqq
2522 1 999 999 filler-data-FILLER-DATA-qqq
2523 2 999 999 filler-data-FILLER-DATA-qqq
2524 1 999 999 filler-data-FILLER-DATA-qqq
2525 2 999 999 filler-data-FILLER-DATA-qqq
2526 1 999 999 filler-data-FILLER-DATA-qqq
2527 2 999 999 filler-data-FILLER-DATA-qqq
2528 1 999 999 filler-data-FILLER-DATA-qqq
2529 2 999 999 filler-data-FILLER-DATA-qqq
2530 1 999 999 filler-data-FILLER-DATA-qqq
2531 2 999 999 filler-data-FILLER-DATA-qqq
2532 1 999 999 filler-data-FILLER-DATA-qqq
2533 2 999 999 filler-data-FILLER-DATA-qqq
2534 1 999 999 filler-data-FILLER-DATA-qqq
2535 2 999 999 filler-data-FILLER-DATA-qqq
2536 1 999 999 filler-data-FILLER-DATA-qqq
2537 2 999 999 filler-data-FILLER-DATA-qqq
2538 1 999 999 filler-data-FILLER-DATA-qqq
2539 2 999 999 filler-data-FILLER-DATA-qqq
2540 1 999 999 filler-data-FILLER-DATA-qqq
2541 2 999 999 filler-data-FILLER-DATA-qqq
2542 1 999 999 filler-data-FILLER-DATA-qqq
2543 2 999 999 filler-data-FILLER-DATA-qqq
2544 1 999 999 filler-data-FILLER-DATA-qqq
2545 2 999 999 filler-data-FILLER-DATA-qqq
2546 1 999 999 filler-data-FILLER-DATA-qqq
2547 2 999 999 filler-data-FILLER-DATA-qqq
2548 1 999 999 filler-data-FILLER-DATA-qqq
2549 2 999 999 filler-data-FILLER-DATA-qqq
2550 1 999 999 filler-data-FILLER-DATA-qqq
2551 2 999 999 filler-data-FILLER-DATA-qqq
2552 drop table t5;
2553 drop table t1,t2,t3,t4;
2554 # End of 5.3 tests
2555 CREATE TABLE t1(a INT, KEY(a)) ENGINE=merge;
2556 SELECT MAX(a) FROM t1;
2557 MAX(a)
2558 NULL
2559 DROP TABLE t1;
2560 #
2561 # An additional test case for Bug#27430 Crash in subquery code
2562 # when in PS and table DDL changed after PREPARE
2563 #
2564 # Test merge table with too many merge children.
2565 #
2566 set @save_table_open_cache=@@global.table_open_cache;
2567 #
2568 # Set @@global.table_open_cache to minimum
2569 #
2570 set @@global.table_open_cache=400;
2571 select @@table_open_cache;
2572 @@table_open_cache
2573 400
2574 set @a=null;
2575 #
2576 # Create 400 merge children
2577 #
2578 set @a=concat("create table t_parent (a int) union(", @a,
2579 ") insert_method=first engine=mrg_myisam");
2580 prepare stmt from @a;
2581 execute stmt;
2582 prepare stmt from "select * from t_parent";
2583 execute stmt;
2584 execute stmt;
2585 execute stmt;
2586 deallocate prepare stmt;
2587 #
2588 # Create merge parent
2589 #
2590 #
2591 # Cleanup
2592 #
2593 drop table t_parent;
2594 set @@global.table_open_cache=@save_table_open_cache;
2595 CREATE DATABASE mysql_test1;
2596 CREATE TABLE t1 ... DATA DIRECTORY=... INDEX DIRECTORY=...
2597 CREATE TABLE mysql_test1.t2 ... DATA DIRECTORY=... INDEX DIRECTORY=...
2598 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,mysql_test1.t2)
2599 INSERT_METHOD=LAST;
2600 INSERT INTO t1 VALUES (1);
2601 INSERT INTO mysql_test1.t2 VALUES (2);
2602 SELECT * FROM m1;
2603 c1
2604 1
2605 2
2606 DROP TABLE t1, mysql_test1.t2, m1;
2607 DROP DATABASE mysql_test1;
2608 CREATE TABLE t1 (c1 INT);
2609 CREATE TABLE t2 (c1 INT);
2610 INSERT INTO t1 (c1) VALUES (1);
2611 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) INSERT_METHOD=FIRST;
2612 CREATE TABLE t3 (c1 INT);
2613 INSERT INTO t3 (c1) VALUES (1);
2614 CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM t3);
2615 CREATE VIEW v1 AS SELECT foo.c1 c1, f1() c2, bar.c1 c3, f1() c4
2616 FROM tm1 foo, tm1 bar, t3;
2617 SELECT * FROM v1;
2618 c1 c2 c3 c4
2619 1 1 1 1
2620 DROP FUNCTION f1;
2621 DROP VIEW v1;
2622 DROP TABLE tm1, t1, t2, t3;
2623 CREATE TEMPORARY TABLE t1 (c1 INT);
2624 CREATE TEMPORARY TABLE t2 (c1 INT);
2625 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
2626 INSERT_METHOD=FIRST;
2627 CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM tm1);
2628 INSERT INTO tm1 (c1) VALUES (1);
2629 SELECT f1() FROM (SELECT 1) AS c1;
2630 f1()
2631 1
2632 DROP FUNCTION f1;
2633 DROP TABLE tm1, t1, t2;
2634 CREATE FUNCTION f1() RETURNS INT
2635 BEGIN
2636 CREATE TEMPORARY TABLE t1 (c1 INT);
2637 CREATE TEMPORARY TABLE t2 (c1 INT);
2638 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2);
2639 INSERT INTO t1 (c1) VALUES (1);
2640 RETURN (SELECT MAX(c1) FROM tm1);
2641 END|
2642 SELECT f1() FROM (SELECT 1 UNION SELECT 1) c1;
2643 f1()
2644 1
2645 DROP FUNCTION f1;
2646 DROP TABLE tm1, t1, t2;
2647 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2648 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2649 INSERT INTO tm1 VALUES (1);
2650 SELECT * FROM tm1;
2651 c1
2652 1
2653 DROP TABLE tm1, t1;
2654 CREATE FUNCTION f1() RETURNS INT
2655 BEGIN
2656 INSERT INTO tm1 VALUES (1);
2657 RETURN (SELECT MAX(c1) FROM tm1);
2658 END|
2659 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2660 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2661 SELECT f1();
2662 f1()
2663 1
2664 DROP FUNCTION f1;
2665 DROP TABLE tm1, t1;
2666 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2667 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2668 LOCK TABLE tm1 WRITE;
2669 INSERT INTO tm1 VALUES (1);
2670 SELECT * FROM tm1;
2671 c1
2672 1
2673 UNLOCK TABLES;
2674 DROP TABLE tm1, t1;
2675 CREATE FUNCTION f1() RETURNS INT
2676 BEGIN
2677 INSERT INTO tm1 VALUES (1);
2678 RETURN (SELECT MAX(c1) FROM tm1);
2679 END|
2680 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2681 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2682 LOCK TABLE tm1 WRITE;
2683 SELECT f1();
2684 f1()
2685 1
2686 UNLOCK TABLES;
2687 DROP FUNCTION f1;
2688 DROP TABLE tm1, t1;
2689 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2690 CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
2691 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2692 CREATE TRIGGER t2_ai AFTER INSERT ON t2
2693 FOR EACH ROW INSERT INTO tm1 VALUES(11);
2694 LOCK TABLE t2 WRITE;
2695 INSERT INTO t2 VALUES (2);
2696 SELECT * FROM tm1;
2697 c1
2698 11
2699 SELECT * FROM t2;
2700 c1
2701 2
2702 UNLOCK TABLES;
2703 DROP TRIGGER t2_ai;
2704 DROP TABLE tm1, t1, t2;
2705 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2706 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2707 INSERT_METHOD=LAST;
2708 INSERT INTO tm1 VALUES (1);
2709 SELECT * FROM tm1;
2710 c1
2711 1
2712 DROP TABLE tm1, t1;
2713 CREATE FUNCTION f1() RETURNS INT
2714 BEGIN
2715 INSERT INTO tm1 VALUES (1);
2716 RETURN (SELECT MAX(c1) FROM tm1);
2717 END|
2718 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2719 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2720 INSERT_METHOD=LAST;
2721 SELECT f1();
2722 f1()
2723 1
2724 DROP FUNCTION f1;
2725 DROP TABLE tm1, t1;
2726 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2727 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2728 INSERT_METHOD=LAST;
2729 CREATE TABLE t9 (c1 INT) ENGINE=MyISAM;
2730 LOCK TABLE t9 WRITE;
2731 INSERT INTO tm1 VALUES (1);
2732 SELECT * FROM tm1;
2733 c1
2734 1
2735 UNLOCK TABLES;
2736 DROP TABLE tm1, t1, t9;
2737 CREATE FUNCTION f1() RETURNS INT
2738 BEGIN
2739 INSERT INTO tm1 VALUES (1);
2740 RETURN (SELECT MAX(c1) FROM tm1);
2741 END|
2742 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2743 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2744 INSERT_METHOD=LAST;
2745 CREATE TABLE t9 (c1 INT) ENGINE=MyISAM;
2746 LOCK TABLE t9 WRITE;
2747 SELECT f1();
2748 f1()
2749 1
2750 UNLOCK TABLES;
2751 DROP FUNCTION f1;
2752 DROP TABLE tm1, t1, t9;
2753 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2754 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2755 INSERT_METHOD=LAST;
2756 CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
2757 CREATE TRIGGER t2_ai AFTER INSERT ON t2
2758 FOR EACH ROW INSERT INTO tm1 VALUES(11);
2759 LOCK TABLE t2 WRITE;
2760 INSERT INTO t2 VALUES (2);
2761 SELECT * FROM tm1;
2762 c1
2763 11
2764 SELECT * FROM t2;
2765 c1
2766 2
2767 UNLOCK TABLES;
2768 DROP TRIGGER t2_ai;
2769 DROP TABLE tm1, t1, t2;
2770 #
2771 # Don't allow an update of a MERGE child in a trigger
2772 # if the table's already being modified by the main
2773 # statement.
2774 #
2775 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2776 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2777 INSERT_METHOD=LAST;
2778 CREATE TRIGGER tm1_ai AFTER INSERT ON tm1
2779 FOR EACH ROW INSERT INTO t1 VALUES(11);
2780 LOCK TABLE tm1 WRITE, t1 WRITE;
2781 INSERT INTO tm1 VALUES (1);
2782 ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
2783 SELECT * FROM tm1;
2784 c1
2785 1
2786 UNLOCK TABLES;
2787 LOCK TABLE t1 WRITE, tm1 WRITE;
2788 INSERT INTO tm1 VALUES (1);
2789 ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
2790 SELECT * FROM tm1;
2791 c1
2792 1
2793 1
2794 UNLOCK TABLES;
2795 DROP TRIGGER tm1_ai;
2796 DROP TABLE tm1, t1;
2797 #
2798 # Don't select MERGE child when trying to get a prelocked table.
2799 #
2800 # Due to a limitation demonstrated by the previous test
2801 # we can no longer use a write-locked prelocked table.
2802 # The test is kept for historical purposes.
2803 #
2804 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2805 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2806 INSERT_METHOD=LAST;
2807 CREATE TRIGGER tm1_ai AFTER INSERT ON tm1
2808 FOR EACH ROW SELECT max(c1) FROM t1 INTO @var;
2809 LOCK TABLE tm1 WRITE, t1 WRITE;
2810 INSERT INTO tm1 VALUES (1);
2811 SELECT * FROM tm1;
2812 c1
2813 1
2814 UNLOCK TABLES;
2815 LOCK TABLE t1 WRITE, tm1 WRITE;
2816 INSERT INTO tm1 VALUES (1);
2817 SELECT * FROM tm1;
2818 c1
2819 1
2820 1
2821 UNLOCK TABLES;
2822 DROP TRIGGER tm1_ai;
2823 DROP TABLE tm1, t1;
2824 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2825 CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
2826 CREATE TABLE t3 (c1 INT) ENGINE=MyISAM;
2827 CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
2828 CREATE TABLE t5 (c1 INT) ENGINE=MyISAM;
2829 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3,t4,t5)
2830 INSERT_METHOD=LAST;
2831 CREATE TRIGGER t2_au AFTER UPDATE ON t2
2832 FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var;
2833 CREATE FUNCTION f1() RETURNS INT
2834 RETURN (SELECT MAX(c1) FROM t4);
2835 LOCK TABLE tm1 WRITE, t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE, t5 WRITE;
2836 INSERT INTO t1 VALUES(1);
2837 INSERT INTO t2 VALUES(2);
2838 INSERT INTO t3 VALUES(3);
2839 INSERT INTO t4 VALUES(4);
2840 INSERT INTO t5 VALUES(5);
2841 connect con1,localhost,root,,;
2842 UPDATE t2, tm1 SET t2.c1=f1();
2843 connection default;
2844 FLUSH TABLES;
2845 FLUSH TABLES;
2846 UNLOCK TABLES;
2847 connection con1;
2848 disconnect con1;
2849 connection default;
2850 SELECT * FROM tm1;
2851 c1
2852 1
2853 4
2854 3
2855 4
2856 5
2857 DROP TRIGGER t2_au;
2858 DROP FUNCTION f1;
2859 DROP TABLE tm1, t1, t2, t3, t4, t5;
2860 #
2861 # Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
2862 #
2863 CREATE TEMPORARY TABLE t1 (c1 INT);
2864 ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists,t1);
2865 OPTIMIZE TABLE t1;
2866 Table Op Msg_type Msg_text
2867 test.t1 optimize Error Table 'test.t_not_exists' doesn't exist
2868 test.t1 optimize Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2869 test.t1 optimize error Corrupt
2870 DROP TABLE t1;
2871 #
2872 # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
2873 # More tests with TEMPORARY MERGE table and permanent children.
2874 # First without locked tables.
2875 #
2876 #
2877 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
2878 CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
2879 CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
2880 INSERT_METHOD=LAST;
2881 SHOW CREATE TABLE t1;
2882 Table Create Table
2883 t1 CREATE TABLE `t1` (
2884 `c1` int(11) DEFAULT NULL,
2885 `c2` int(11) DEFAULT NULL
2886 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
2887 SHOW CREATE TABLE m1;
2888 Table Create Table
2889 m1 CREATE TEMPORARY TABLE `m1` (
2890 `c1` int(11) DEFAULT NULL,
2891 `c2` int(11) DEFAULT NULL
2892 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
2893 SELECT * FROM m1;
2894 c1 c2
2895 INSERT INTO t1 VALUES (111, 121);
2896 INSERT INTO m1 VALUES (211, 221);
2897 SELECT * FROM m1;
2898 c1 c2
2899 111 121
2900 211 221
2901 SELECT * FROM t1;
2902 c1 c2
2903 111 121
2904 SELECT * FROM t2;
2905 c1 c2
2906 211 221
2907 #
2908 ALTER TABLE m1 RENAME m2;
2909 SHOW CREATE TABLE m2;
2910 Table Create Table
2911 m2 CREATE TEMPORARY TABLE `m2` (
2912 `c1` int(11) DEFAULT NULL,
2913 `c2` int(11) DEFAULT NULL
2914 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
2915 SELECT * FROM m2;
2916 c1 c2
2917 111 121
2918 211 221
2919 #
2920 CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
2921 INSERT_METHOD=LAST;
2922 ALTER TABLE m2 RENAME m1;
2923 ERROR 42S01: Table 'm1' already exists
2924 DROP TABLE m1;
2925 ALTER TABLE m2 RENAME m1;
2926 SHOW CREATE TABLE m1;
2927 Table Create Table
2928 m1 CREATE TEMPORARY TABLE `m1` (
2929 `c1` int(11) DEFAULT NULL,
2930 `c2` int(11) DEFAULT NULL
2931 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
2932 SELECT * FROM m1;
2933 c1 c2
2934 111 121
2935 211 221
2936 #
2937 ALTER TABLE m1 ADD COLUMN c3 INT;
2938 INSERT INTO m1 VALUES (212, 222, 232);
2939 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2940 SELECT * FROM m1;
2941 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2942 ALTER TABLE t1 ADD COLUMN c3 INT;
2943 ALTER TABLE t2 ADD COLUMN c3 INT;
2944 INSERT INTO m1 VALUES (212, 222, 232);
2945 SELECT * FROM m1;
2946 c1 c2 c3
2947 111 121 NULL
2948 211 221 NULL
2949 212 222 232
2950 #
2951 ALTER TABLE m1 DROP COLUMN c3;
2952 INSERT INTO m1 VALUES (213, 223);
2953 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2954 SELECT * FROM m1;
2955 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
2956 ALTER TABLE t1 DROP COLUMN c3;
2957 ALTER TABLE t2 DROP COLUMN c3;
2958 INSERT INTO m1 VALUES (213, 223);
2959 SELECT * FROM m1;
2960 c1 c2
2961 111 121
2962 211 221
2963 212 222
2964 213 223
2965 #
2966 CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
2967 ALTER TABLE m1 UNION=(t1,t2,t3);
2968 INSERT INTO m1 VALUES (311, 321);
2969 SELECT * FROM m1;
2970 c1 c2
2971 111 121
2972 211 221
2973 212 222
2974 213 223
2975 311 321
2976 SELECT * FROM t1;
2977 c1 c2
2978 111 121
2979 SELECT * FROM t2;
2980 c1 c2
2981 211 221
2982 212 222
2983 213 223
2984 SELECT * FROM t3;
2985 c1 c2
2986 311 321
2987 #
2988 CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
2989 ALTER TABLE m1 UNION=(t1,t2,t3,t4);
2990 INSERT INTO m1 VALUES (411, 421);
2991 SELECT * FROM m1;
2992 c1 c2
2993 111 121
2994 211 221
2995 212 222
2996 213 223
2997 311 321
2998 411 421
2999 SELECT * FROM t1;
3000 c1 c2
3001 111 121
3002 SELECT * FROM t2;
3003 c1 c2
3004 211 221
3005 212 222
3006 213 223
3007 SELECT * FROM t3;
3008 c1 c2
3009 311 321
3010 SELECT * FROM t4;
3011 c1 c2
3012 411 421
3013 #
3014 ALTER TABLE m1 ENGINE=MyISAM;
3015 SHOW CREATE TABLE m1;
3016 Table Create Table
3017 m1 CREATE TEMPORARY TABLE `m1` (
3018 `c1` int(11) DEFAULT NULL,
3019 `c2` int(11) DEFAULT NULL
3020 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
3021 INSERT INTO m1 VALUES (511, 521);
3022 SELECT * FROM m1;
3023 c1 c2
3024 111 121
3025 211 221
3026 212 222
3027 213 223
3028 311 321
3029 411 421
3030 511 521
3031 #
3032 ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
3033 INSERT_METHOD=LAST;
3034 SELECT * FROM m1;
3035 c1 c2
3036 111 121
3037 211 221
3038 212 222
3039 213 223
3040 SELECT * FROM t1;
3041 c1 c2
3042 111 121
3043 SELECT * FROM t2;
3044 c1 c2
3045 211 221
3046 212 222
3047 213 223
3048 #
3049 CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
3050 INSERT INTO t1 VALUES (611, 621);
3051 SELECT * FROM m1;
3052 c1 c2
3053 611 621
3054 211 221
3055 212 222
3056 213 223
3057 DROP TABLE t1;
3058 SELECT * FROM m1;
3059 c1 c2
3060 111 121
3061 211 221
3062 212 222
3063 213 223
3064 #
3065 #
3066 SHOW CREATE TABLE m1;
3067 Table Create Table
3068 m1 CREATE TEMPORARY TABLE `m1` (
3069 `c1` int(11) DEFAULT NULL,
3070 `c2` int(11) DEFAULT NULL
3071 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3072 #
3073 CREATE TABLE m2 SELECT * FROM m1;
3074 SHOW CREATE TABLE m2;
3075 Table Create Table
3076 m2 CREATE TABLE `m2` (
3077 `c1` int(11) DEFAULT NULL,
3078 `c2` int(11) DEFAULT NULL
3079 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
3080 SELECT * FROM m2;
3081 c1 c2
3082 111 121
3083 211 221
3084 212 222
3085 213 223
3086 DROP TABLE m2;
3087 #
3088 CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
3089 SHOW CREATE TABLE m2;
3090 Table Create Table
3091 m2 CREATE TEMPORARY TABLE `m2` (
3092 `c1` int(11) DEFAULT NULL,
3093 `c2` int(11) DEFAULT NULL
3094 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
3095 SELECT * FROM m2;
3096 c1 c2
3097 111 121
3098 211 221
3099 212 222
3100 213 223
3101 DROP TABLE m2;
3102 #
3103 CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
3104 INSERT_METHOD=LAST;
3105 SELECT * FROM m2;
3106 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3107 DROP TABLE m2;
3108 #
3109 CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
3110 INSERT_METHOD=LAST SELECT * FROM m1;
3111 ERROR HY000: 'test.m2' is not of type 'BASE TABLE'
3112 #
3113 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
3114 INSERT_METHOD=LAST SELECT * FROM m1;
3115 ERROR HY000: 'test.m2' is not of type 'BASE TABLE'
3116 #
3117 CREATE TABLE m2 LIKE m1;
3118 SHOW CREATE TABLE m2;
3119 Table Create Table
3120 m2 CREATE TABLE `m2` (
3121 `c1` int(11) DEFAULT NULL,
3122 `c2` int(11) DEFAULT NULL
3123 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3124 SELECT * FROM m2;
3125 c1 c2
3126 111 121
3127 211 221
3128 212 222
3129 213 223
3130 INSERT INTO m2 SELECT * FROM m1;
3131 SELECT * FROM m2;
3132 c1 c2
3133 111 121
3134 211 221
3135 212 222
3136 213 223
3137 111 121
3138 211 221
3139 212 222
3140 213 223
3141 DROP TABLE m2;
3142 #
3143 CREATE TEMPORARY TABLE m2 LIKE m1;
3144 SHOW CREATE TABLE m2;
3145 Table Create Table
3146 m2 CREATE TEMPORARY TABLE `m2` (
3147 `c1` int(11) DEFAULT NULL,
3148 `c2` int(11) DEFAULT NULL
3149 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3150 SELECT * FROM m2;
3151 c1 c2
3152 111 121
3153 211 221
3154 212 222
3155 213 223
3156 111 121
3157 211 221
3158 212 222
3159 213 223
3160 INSERT INTO m2 SELECT * FROM m1;
3161 SELECT * FROM m2;
3162 c1 c2
3163 111 121
3164 211 221
3165 212 222
3166 213 223
3167 111 121
3168 211 221
3169 212 222
3170 213 223
3171 111 121
3172 211 221
3173 212 222
3174 213 223
3175 111 121
3176 211 221
3177 212 222
3178 213 223
3179 DROP TABLE m2;
3180 #
3181 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
3182 INSERT_METHOD=LAST;
3183 INSERT INTO m2 SELECT * FROM m1;
3184 SELECT * FROM m2;
3185 c1 c2
3186 311 321
3187 411 421
3188 111 121
3189 211 221
3190 212 222
3191 213 223
3192 111 121
3193 211 221
3194 212 222
3195 213 223
3196 111 121
3197 211 221
3198 212 222
3199 213 223
3200 111 121
3201 211 221
3202 212 222
3203 213 223
3204 #
3205 #
3206 LOCK TABLE m1 WRITE, m2 WRITE;
3207 SELECT * FROM m1,m2 WHERE m1.c1=m2.c1;
3208 c1 c2 c1 c2
3209 111 121 111 121
3210 111 121 111 121
3211 111 121 111 121
3212 111 121 111 121
3213 211 221 211 221
3214 211 221 211 221
3215 211 221 211 221
3216 211 221 211 221
3217 212 222 212 222
3218 212 222 212 222
3219 212 222 212 222
3220 212 222 212 222
3221 213 223 213 223
3222 213 223 213 223
3223 213 223 213 223
3224 213 223 213 223
3225 111 121 111 121
3226 111 121 111 121
3227 111 121 111 121
3228 111 121 111 121
3229 211 221 211 221
3230 211 221 211 221
3231 211 221 211 221
3232 211 221 211 221
3233 212 222 212 222
3234 212 222 212 222
3235 212 222 212 222
3236 212 222 212 222
3237 213 223 213 223
3238 213 223 213 223
3239 213 223 213 223
3240 213 223 213 223
3241 111 121 111 121
3242 111 121 111 121
3243 111 121 111 121
3244 111 121 111 121
3245 211 221 211 221
3246 211 221 211 221
3247 211 221 211 221
3248 211 221 211 221
3249 212 222 212 222
3250 212 222 212 222
3251 212 222 212 222
3252 212 222 212 222
3253 213 223 213 223
3254 213 223 213 223
3255 213 223 213 223
3256 213 223 213 223
3257 111 121 111 121
3258 111 121 111 121
3259 111 121 111 121
3260 111 121 111 121
3261 211 221 211 221
3262 211 221 211 221
3263 211 221 211 221
3264 211 221 211 221
3265 212 222 212 222
3266 212 222 212 222
3267 212 222 212 222
3268 212 222 212 222
3269 213 223 213 223
3270 213 223 213 223
3271 213 223 213 223
3272 213 223 213 223
3273 UNLOCK TABLES;
3274 DROP TABLE t1, t2, t3, t4, m1, m2;
3275 #
3276 # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
3277 # More tests with TEMPORARY MERGE table and permanent children.
3278 # (continued) Now the same with locked table.
3279 #
3280 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
3281 CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
3282 CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
3283 INSERT_METHOD=LAST;
3284 SHOW CREATE TABLE t1;
3285 Table Create Table
3286 t1 CREATE TABLE `t1` (
3287 `c1` int(11) DEFAULT NULL,
3288 `c2` int(11) DEFAULT NULL
3289 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
3290 SHOW CREATE TABLE m1;
3291 Table Create Table
3292 m1 CREATE TEMPORARY TABLE `m1` (
3293 `c1` int(11) DEFAULT NULL,
3294 `c2` int(11) DEFAULT NULL
3295 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3296 SELECT * FROM m1;
3297 c1 c2
3298 INSERT INTO t1 VALUES (111, 121);
3299 INSERT INTO m1 VALUES (211, 221);
3300 SELECT * FROM m1;
3301 c1 c2
3302 111 121
3303 211 221
3304 SELECT * FROM t1;
3305 c1 c2
3306 111 121
3307 SELECT * FROM t2;
3308 c1 c2
3309 211 221
3310 #
3311 LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE;
3312 #
3313 ALTER TABLE m1 RENAME m2;
3314 SHOW CREATE TABLE m2;
3315 Table Create Table
3316 m2 CREATE TEMPORARY TABLE `m2` (
3317 `c1` int(11) DEFAULT NULL,
3318 `c2` int(11) DEFAULT NULL
3319 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3320 SELECT * FROM m2;
3321 c1 c2
3322 111 121
3323 211 221
3324 #
3325 CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
3326 INSERT_METHOD=LAST;
3327 ALTER TABLE m2 RENAME m1;
3328 ERROR 42S01: Table 'm1' already exists
3329 DROP TABLE m1;
3330 ALTER TABLE m2 RENAME m1;
3331 SHOW CREATE TABLE m1;
3332 Table Create Table
3333 m1 CREATE TEMPORARY TABLE `m1` (
3334 `c1` int(11) DEFAULT NULL,
3335 `c2` int(11) DEFAULT NULL
3336 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3337 SELECT * FROM m1;
3338 c1 c2
3339 111 121
3340 211 221
3341 #
3342 ALTER TABLE m1 ADD COLUMN c3 INT;
3343 INSERT INTO m1 VALUES (212, 222, 232);
3344 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3345 SELECT * FROM m1;
3346 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3347 ALTER TABLE t1 ADD COLUMN c3 INT;
3348 ALTER TABLE t2 ADD COLUMN c3 INT;
3349 INSERT INTO m1 VALUES (212, 222, 232);
3350 SELECT * FROM m1;
3351 c1 c2 c3
3352 111 121 NULL
3353 211 221 NULL
3354 212 222 232
3355 #
3356 ALTER TABLE m1 DROP COLUMN c3;
3357 INSERT INTO m1 VALUES (213, 223);
3358 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3359 SELECT * FROM m1;
3360 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3361 ALTER TABLE t1 DROP COLUMN c3;
3362 ALTER TABLE t2 DROP COLUMN c3;
3363 INSERT INTO m1 VALUES (213, 223);
3364 SELECT * FROM m1;
3365 c1 c2
3366 111 121
3367 211 221
3368 212 222
3369 213 223
3370 #
3371 UNLOCK TABLES;
3372 CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
3373 ALTER TABLE m1 UNION=(t1,t2,t3);
3374 LOCK TABLE m1 WRITE;
3375 INSERT INTO m1 VALUES (311, 321);
3376 SELECT * FROM m1;
3377 c1 c2
3378 111 121
3379 211 221
3380 212 222
3381 213 223
3382 311 321
3383 SELECT * FROM t1;
3384 c1 c2
3385 111 121
3386 SELECT * FROM t2;
3387 c1 c2
3388 211 221
3389 212 222
3390 213 223
3391 SELECT * FROM t3;
3392 c1 c2
3393 311 321
3394 #
3395 CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
3396 ALTER TABLE m1 UNION=(t1,t2,t3,t4);
3397 INSERT INTO m1 VALUES (411, 421);
3398 SELECT * FROM m1;
3399 c1 c2
3400 111 121
3401 211 221
3402 212 222
3403 213 223
3404 311 321
3405 411 421
3406 SELECT * FROM t1;
3407 c1 c2
3408 111 121
3409 SELECT * FROM t2;
3410 c1 c2
3411 211 221
3412 212 222
3413 213 223
3414 SELECT * FROM t3;
3415 c1 c2
3416 311 321
3417 SELECT * FROM t4;
3418 c1 c2
3419 411 421
3420 #
3421 ALTER TABLE m1 ENGINE=MyISAM;
3422 SHOW CREATE TABLE m1;
3423 Table Create Table
3424 m1 CREATE TEMPORARY TABLE `m1` (
3425 `c1` int(11) DEFAULT NULL,
3426 `c2` int(11) DEFAULT NULL
3427 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
3428 INSERT INTO m1 VALUES (511, 521);
3429 SELECT * FROM m1;
3430 c1 c2
3431 111 121
3432 211 221
3433 212 222
3434 213 223
3435 311 321
3436 411 421
3437 511 521
3438 #
3439 ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
3440 INSERT_METHOD=LAST;
3441 SELECT * FROM m1;
3442 c1 c2
3443 111 121
3444 211 221
3445 212 222
3446 213 223
3447 SELECT * FROM t1;
3448 c1 c2
3449 111 121
3450 SELECT * FROM t2;
3451 c1 c2
3452 211 221
3453 212 222
3454 213 223
3455 #
3456 CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
3457 INSERT INTO t1 VALUES (611, 621);
3458 SELECT * FROM m1;
3459 c1 c2
3460 611 621
3461 211 221
3462 212 222
3463 213 223
3464 DROP TABLE t1;
3465 SELECT * FROM m1;
3466 c1 c2
3467 111 121
3468 211 221
3469 212 222
3470 213 223
3471 #
3472 #
3473 SHOW CREATE TABLE m1;
3474 Table Create Table
3475 m1 CREATE TEMPORARY TABLE `m1` (
3476 `c1` int(11) DEFAULT NULL,
3477 `c2` int(11) DEFAULT NULL
3478 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3479 CREATE TABLE m2 SELECT * FROM m1;
3480 ERROR HY000: Table 'm2' was not locked with LOCK TABLES
3481 #
3482 CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
3483 SHOW CREATE TABLE m2;
3484 Table Create Table
3485 m2 CREATE TEMPORARY TABLE `m2` (
3486 `c1` int(11) DEFAULT NULL,
3487 `c2` int(11) DEFAULT NULL
3488 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
3489 SELECT * FROM m2;
3490 c1 c2
3491 111 121
3492 211 221
3493 212 222
3494 213 223
3495 DROP TABLE m2;
3496 #
3497 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
3498 INSERT_METHOD=LAST;
3499 SELECT * FROM m2;
3500 c1 c2
3501 311 321
3502 411 421
3503 LOCK TABLE m1 WRITE, m2 WRITE;
3504 UNLOCK TABLES;
3505 DROP TABLE m2;
3506 LOCK TABLE m1 WRITE;
3507 #
3508 # ER_TABLE_NOT_LOCKED is returned in ps-protocol
3509 CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
3510 INSERT_METHOD=LAST SELECT * FROM m1;
3511 Got one of the listed errors
3512 #
3513 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
3514 INSERT_METHOD=LAST SELECT * FROM m1;
3515 ERROR HY000: 'test.m2' is not of type 'BASE TABLE'
3516 #
3517 CREATE TEMPORARY TABLE m2 LIKE m1;
3518 SHOW CREATE TABLE m2;
3519 Table Create Table
3520 m2 CREATE TEMPORARY TABLE `m2` (
3521 `c1` int(11) DEFAULT NULL,
3522 `c2` int(11) DEFAULT NULL
3523 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3524 LOCK TABLE m1 WRITE, m2 WRITE;
3525 SHOW CREATE TABLE m2;
3526 Table Create Table
3527 m2 CREATE TEMPORARY TABLE `m2` (
3528 `c1` int(11) DEFAULT NULL,
3529 `c2` int(11) DEFAULT NULL
3530 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`)
3531 SELECT * FROM m2;
3532 c1 c2
3533 111 121
3534 211 221
3535 212 222
3536 213 223
3537 INSERT INTO m2 SELECT * FROM m1;
3538 SELECT * FROM m2;
3539 c1 c2
3540 111 121
3541 211 221
3542 212 222
3543 213 223
3544 111 121
3545 211 221
3546 212 222
3547 213 223
3548 DROP TABLE m2;
3549 #
3550 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
3551 INSERT_METHOD=LAST;
3552 LOCK TABLE m1 WRITE, m2 WRITE;
3553 INSERT INTO m2 SELECT * FROM m1;
3554 SELECT * FROM m2;
3555 c1 c2
3556 311 321
3557 411 421
3558 111 121
3559 211 221
3560 212 222
3561 213 223
3562 111 121
3563 211 221
3564 212 222
3565 213 223
3566 #
3567 UNLOCK TABLES;
3568 DROP TABLE t1, t2, t3, t4, m1, m2;
3569 #
3570 # Bug47098 assert in MDL_context::destroy on HANDLER
3571 # <damaged merge table> OPEN
3572 #
3573 # Test that merge tables are closed correctly when opened using
3574 # HANDLER ... OPEN.
3575 # The general case.
3576 connect con1,localhost,root,,;
3577 CREATE TABLE t1 (c1 int);
3578 CREATE TABLE t2 (c1 int);
3579 CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2);
3580 START TRANSACTION;
3581 HANDLER t3 OPEN;
3582 ERROR HY000: Storage engine MRG_MyISAM of the table `test`.`t3` doesn't have this option
3583 DROP TABLE t1, t2, t3;
3584 connection default;
3585 # all mdl_tickets must have been released.
3586 disconnect con1;
3587 # The bug-specific case.
3588 connect con1,localhost,root,,;
3589 CREATE TABLE t1 (c1 int);
3590 CREATE TABLE t2 (c1 int);
3591 CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2);
3592 DROP TABLE t2;
3593 START TRANSACTION;
3594 HANDLER t3 OPEN;
3595 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3596 DROP TABLE t1, t3;
3597 connection default;
3598 # all mdl_tickets must have been released.
3599 disconnect con1;
3600 #
3601 # A test case for Bug#47648 main.merge fails sporadically
3602 #
3603 # Make sure we correctly maintain lex->query_tables_last_own.
3604 #
3605 create table t1 (c1 int not null);
3606 create table t2 (c1 int not null);
3607 create table t3 (c1 int not null);
3608 create function f1 () returns int return (select max(c1) from t3);
3609 create table t4 (c1 int not null) engine=merge union=(t1,t2) insert_method=last ;
3610 select * from t4 where c1 < f1();
3611 c1
3612 prepare stmt from "select * from t4 where c1 < f1()";
3613 execute stmt;
3614 c1
3615 execute stmt;
3616 c1
3617 execute stmt;
3618 c1
3619 drop function f1;
3620 execute stmt;
3621 ERROR 42000: FUNCTION test.f1 does not exist
3622 execute stmt;
3623 ERROR 42000: FUNCTION test.f1 does not exist
3624 drop table t4, t3, t2, t1;
3625 #
3626 # Bug#51240 ALTER TABLE of a locked MERGE table fails
3627 #
3628 CREATE TABLE t1 (c1 INT);
3629 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
3630 LOCK TABLE m1 WRITE;
3631 ALTER TABLE m1 ADD INDEX (c1);
3632 UNLOCK TABLES;
3633 DROP TABLE m1, t1;
3634 #
3635 # Locking the merge table won't implicitly lock children.
3636 #
3637 CREATE TABLE t1 (c1 INT);
3638 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
3639 LOCK TABLE m1 WRITE;
3640 ALTER TABLE t1 ADD INDEX (c1);
3641 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
3642 LOCK TABLE m1 WRITE, t1 WRITE;
3643 ALTER TABLE t1 ADD INDEX (c1);
3644 UNLOCK TABLES;
3645 DROP TABLE m1, t1;
3646 #
3647 # Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter"
3648 #
3649 create table t1 (i int) engine=myisam;
3650 create table m1 (i int) engine=mrg_myisam union=(t1) insert_method=first;
3651 create table m2 like m1;
3652 # Table definitions should match
3653 show create table m1;
3654 Table Create Table
3655 m1 CREATE TABLE `m1` (
3656 `i` int(11) DEFAULT NULL
3657 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=FIRST UNION=(`t1`)
3658 show create table m2;
3659 Table Create Table
3660 m2 CREATE TABLE `m2` (
3661 `i` int(11) DEFAULT NULL
3662 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=FIRST UNION=(`t1`)
3663 drop tables m1, m2, t1;
3664 #
3665 # Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()"
3666 # Check that unique_table() works correctly for merge tables.
3667 #
3668 create table t1 (a int);
3669 create table t2 (a int);
3670 create table t3 (b int);
3671 create view v1 as select * from t3,t1;
3672 create table m1 (a int) engine=merge union (t1, t2) insert_method=last;
3673 create table m2 (a int) engine=merge union (t1, t2) insert_method=first;
3674 create temporary table tmp (b int);
3675 insert into tmp (b) values (1);
3676 insert into t1 (a) values (1);
3677 insert into t3 (b) values (1);
3678 insert into m1 (a) values ((select max(a) from m1));
3679 insert into m1 (a) values ((select max(a) from m2));
3680 insert into m1 (a) values ((select max(a) from t1));
3681 insert into m1 (a) values ((select max(a) from t2));
3682 insert into m1 (a) values ((select max(a) from t3, m1));
3683 insert into m1 (a) values ((select max(a) from t3, m2));
3684 insert into m1 (a) values ((select max(a) from t3, t1));
3685 insert into m1 (a) values ((select max(a) from t3, t2));
3686 insert into m1 (a) values ((select max(a) from tmp, m1));
3687 insert into m1 (a) values ((select max(a) from tmp, m2));
3688 insert into m1 (a) values ((select max(a) from tmp, t1));
3689 insert into m1 (a) values ((select max(a) from tmp, t2));
3690 insert into m1 (a) values ((select max(a) from v1));
3691 insert into m1 (a) values ((select max(a) from tmp, v1));
3692 select count(*) from m1;
3693 count(*)
3694 15
3695 drop view v1;
3696 drop temporary table tmp;
3697 drop table t1, t2, t3, m1, m2;
3698 #
3699 # Bug#56494 Segfault in upgrade_shared_lock_to_exclusive() for
3700 # REPAIR of merge table
3701 #
3702 CREATE TABLE t1(a INT);
3703 ALTER TABLE t1 engine= MERGE UNION (t_not_exists);
3704 ANALYZE TABLE t1;
3705 Table Op Msg_type Msg_text
3706 test.t1 analyze Error Table 'test.t_not_exists' doesn't exist
3707 test.t1 analyze Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3708 test.t1 analyze error Corrupt
3709 CHECK TABLE t1;
3710 Table Op Msg_type Msg_text
3711 test.t1 check Error Table 'test.t_not_exists' is differently defined or of non-MyISAM type or doesn't exist
3712 test.t1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3713 test.t1 check error Corrupt
3714 CHECKSUM TABLE t1;
3715 Table Checksum
3716 test.t1 NULL
3717 Warnings:
3718 Error 1146 Table 'test.t_not_exists' doesn't exist
3719 Error 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3720 OPTIMIZE TABLE t1;
3721 Table Op Msg_type Msg_text
3722 test.t1 optimize Error Table 'test.t_not_exists' doesn't exist
3723 test.t1 optimize Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3724 test.t1 optimize error Corrupt
3725 REPAIR TABLE t1;
3726 Table Op Msg_type Msg_text
3727 test.t1 repair Error Table 'test.t_not_exists' is differently defined or of non-MyISAM type or doesn't exist
3728 test.t1 repair Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3729 test.t1 repair error Corrupt
3730 REPAIR TABLE t1 USE_FRM;
3731 Table Op Msg_type Msg_text
3732 test.t1 repair Warning Can't open table
3733 test.t1 repair error Corrupt
3734 DROP TABLE t1;
3735 CREATE TABLE t1(a INT);
3736 CREATE TABLE t2(a INT) engine= MERGE UNION (t1);
3737 REPAIR TABLE t2 USE_FRM;
3738 Table Op Msg_type Msg_text
3739 test.t2 repair note The storage engine for the table doesn't support repair
3740 DROP TABLE t1, t2;
3741 #
3742 # Bug#57002 Assert in upgrade_shared_lock_to_exclusive()
3743 # for ALTER TABLE + MERGE tables
3744 #
3745 CREATE TABLE t1(a INT) engine=myisam;
3746 CREATE TABLE m1(a INT) engine=merge UNION(t1);
3747 LOCK TABLES t1 READ, m1 WRITE;
3748 ALTER TABLE t1 engine=myisam;
3749 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
3750 UNLOCK TABLES;
3751 DROP TABLE m1, t1;
3752 #
3753 # Test for bug #11754210 - "45777: CHECK TABLE DOESN'T SHOW ALL
3754 # PROBLEMS FOR MERGE TABLE COMPLIANCE IN 5.1"
3755 #
3756 create table t1(id int) engine=myisam;
3757 create view t3 as select 1 as id;
3758 create table t4(id int) engine=memory;
3759 create table m1(id int) engine=merge union=(t1,t2,t3,t4);
3760 select * from m1;
3761 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3762 # The below CHECK and REPAIR TABLE statements should
3763 # report all problems with underlying tables:
3764 # - absence of 't2',
3765 # - missing base table for 't3',
3766 # - wrong engine of 't4'.
3767 check table m1;
3768 Table Op Msg_type Msg_text
3769 test.m1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
3770 test.m1 check Error Table 'test.t3' is differently defined or of non-MyISAM type or doesn't exist
3771 test.m1 check Error Table 'test.t4' is differently defined or of non-MyISAM type or doesn't exist
3772 test.m1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3773 test.m1 check error Corrupt
3774 repair table m1;
3775 Table Op Msg_type Msg_text
3776 test.m1 repair Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
3777 test.m1 repair Error Table 'test.t3' is differently defined or of non-MyISAM type or doesn't exist
3778 test.m1 repair Error Table 'test.t4' is differently defined or of non-MyISAM type or doesn't exist
3779 test.m1 repair Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3780 test.m1 repair error Corrupt
3781 # Clean-up.
3782 drop tables m1, t1, t4;
3783 drop view t3;
3784 #
3785 # MDEV-10424 Assertion `ticket == __null' failed in MDL_request::set_type
3786 #
3787 CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
3788 CREATE TABLE tmerge (f1 INT) ENGINE=MERGE UNION=(t1);
3789 PREPARE stmt FROM "ANALYZE TABLE tmerge, t1";
3790 EXECUTE stmt;
3791 Table Op Msg_type Msg_text
3792 test.tmerge analyze status Engine-independent statistics collected
3793 test.tmerge analyze note The storage engine for the table doesn't support analyze
3794 test.t1 analyze status Engine-independent statistics collected
3795 test.t1 analyze status Table is already up to date
3796 EXECUTE stmt;
3797 Table Op Msg_type Msg_text
3798 test.tmerge analyze status Engine-independent statistics collected
3799 test.tmerge analyze note The storage engine for the table doesn't support analyze
3800 test.t1 analyze status Engine-independent statistics collected
3801 test.t1 analyze status Table is already up to date
3802 DEALLOCATE PREPARE stmt;
3803 DROP TABLE t1, tmerge;
3804 # End of 5.5 tests
3805 #
3806 # Additional coverage for refactoring which is made as part
3807 # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege
3808 # to allow temp table operations".
3809 #
3810 # Check that prelocking works correctly for various variants of
3811 # merge tables.
3812 #
3813 create table t1 (j int);
3814 insert into t1 values (1);
3815 create function f1() returns int return (select count(*) from m1);
3816 create temporary table t2 (a int) engine=myisam;
3817 insert into t2 values (1);
3818 create temporary table m1 (a int) engine=merge union=(t2);
3819 select f1() from t1;
3820 f1()
3821 1
3822 drop tables t2, m1;
3823 create table t2 (a int) engine=myisam;
3824 insert into t2 values (1);
3825 create table m1 (a int) engine=merge union=(t2);
3826 select f1() from t1;
3827 f1()
3828 1
3829 drop table m1;
3830 create temporary table m1 (a int) engine=merge union=(t2);
3831 select f1() from t1;
3832 f1()
3833 1
3834 drop tables t1, t2, m1;
3835 drop function f1;
3836 #
3837 # Check that REPAIR/CHECK and CHECKSUM statements work correctly
3838 # for various variants of merge tables.
3839 create table t1 (a int) engine=myisam;
3840 insert into t1 values (1);
3841 create table m1 (a int) engine=merge union=(t1);
3842 check table m1;
3843 Table Op Msg_type Msg_text
3844 test.m1 check status OK
3845 repair table m1;
3846 Table Op Msg_type Msg_text
3847 test.m1 repair status OK
3848 checksum table m1;
3849 Table Checksum
3850 test.m1 3459908756
3851 drop tables t1, m1;
3852 create temporary table t1 (a int) engine=myisam;
3853 insert into t1 values (1);
3854 create temporary table m1 (a int) engine=merge union=(t1);
3855 check table m1;
3856 Table Op Msg_type Msg_text
3857 test.m1 check status OK
3858 repair table m1;
3859 Table Op Msg_type Msg_text
3860 test.m1 repair status OK
3861 checksum table m1;
3862 Table Checksum
3863 test.m1 3459908756
3864 drop tables t1, m1;
3865 create table t1 (a int) engine=myisam;
3866 insert into t1 values (1);
3867 create temporary table m1 (a int) engine=merge union=(t1);
3868 check table m1;
3869 Table Op Msg_type Msg_text
3870 test.m1 check status OK
3871 repair table m1;
3872 Table Op Msg_type Msg_text
3873 test.m1 repair status OK
3874 checksum table m1;
3875 Table Checksum
3876 test.m1 3459908756
3877 drop tables t1, m1;
3878 CREATE TABLE t1(a INT);
3879 CREATE TABLE m1(a INT) ENGINE = MERGE UNION (q1, q2);
3880 CREATE TRIGGER trg1 BEFORE DELETE ON t1
3881 FOR EACH ROW
3882 INSERT INTO m1 VALUES (1);
3883 DELETE FROM t1;
3884 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3885 DROP TRIGGER trg1;
3886 DROP TABLE t1;
3887 DROP TABLE m1;
3888 set global default_storage_engine=@save_default_storage_engine;
3889 #
3890 # MDEV-31083 ASAN use-after-poison in myrg_attach_children
3891 #
3892 CREATE TABLE t1 (f TEXT, FULLTEXT (f)) ENGINE=MyISAM;
3893 INSERT INTO t1 VALUES ('foo'),('bar');
3894 CREATE TABLE mrg (f TEXT) ENGINE=MERGE, UNION(t1);
3895 SELECT * FROM mrg;
3896 f
3897 foo
3898 bar
3899 DROP TABLE mrg, t1;
3900 # End of 10.0 tests
3901 #
3902 # MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results
3903 #
3904 create table t (a int, key(a desc)) engine=myisam;
3905 create table tm (a int, key(a)) engine=merge union(t);
3906 select * from tm;
3907 ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
3908 drop table tm, t;
3909 #
3910 # MDEV-27586 Auto-increment does not work with DESC on MERGE table
3911 #
3912 create table t (a int not null, primary key(a desc)) engine=myisam;
3913 create table tm (a int not null auto_increment, primary key(a desc)) engine=merge union=(t) insert_method=first;
3914 insert into tm () values ();
3915 insert into tm () values ();
3916 insert into tm () values ();
3917 select * from tm;
3918 a
3919 3
3920 2
3921 1
3922 drop table tm, t;
3923 # End of 10.8 tests
3924 #
3925 # MDEV-35816 ASAN use-after-poison in st_select_lex::print
3926 #
3927 CREATE TABLE t1 (a INT);
3928 INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5);
3929 SET SESSION optimizer_trace = 'enabled=on';
3930 PREPARE stmt FROM 'SELECT STRAIGHT_JOIN * FROM t1 WHERE a IN (WITH cte AS (SELECT a FROM t1) SELECT * FROM cte)';
3931 EXECUTE stmt;
3932 a
3933 1
3934 2
3935 3
3936 4
3937 5
3938 EXECUTE stmt;
3939 a
3940 1
3941 2
3942 3
3943 4
3944 5
3945 PREPARE nested FROM 'SELECT STRAIGHT_JOIN * FROM t1 WHERE a IN (WITH cte AS (WITH cte2 AS (SELECT a FROM t1) SELECT * from cte2) SELECT * FROM cte)';
3946 EXECUTE nested;
3947 a
3948 1
3949 2
3950 3
3951 4
3952 5
3953 EXECUTE nested;
3954 a
3955 1
3956 2
3957 3
3958 4
3959 5
3960 DROP TABLE t1;
3961 #
3962 # MDEV-39494 UBSAN failure
3963 #
3964 CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
3965 CREATE TABLE t2 (a INT, KEY(a)) ENGINE=MyISAM;
3966 CREATE TABLE tm (a INT, KEY(a)) ENGINE=MRG_MyISAM UNION=(t1,t2);
3967 SELECT DISTINCT a FROM tm WHERE a > 50;
3968 a
3969 DROP TABLE tm, t1, t2;
3970 #
3971 # MDEV-38474 Double free or corruption, ASAN heap-use-after-free in st_join_table::cleanup
3972 #
3973 # Test case 1, fails on 10.11+
3974 CREATE TABLE t1 (a INT);
3975 CREATE TABLE t2 (b INT);
3976 CREATE TABLE t3 (c INT);
3977 # Inserts are optional, fails with and without data
3978 INSERT INTO t1 VALUES (1),(2);
3979 INSERT INTO t2 VALUES (3),(4);
3980 INSERT INTO t3 VALUES (5),(6);
3981 EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE a IN ((SELECT c FROM t3 WHERE FALSE HAVING c < 0)));
3982 id select_type table type possible_keys key key_len ref rows Extra
3983 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3984 3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
3985 DROP TABLE t1, t2, t3;
3986 # Test case 2, fails on 11.4 but not on 10.11
3987 CREATE TABLE t1 (a INT);
3988 CREATE TABLE t2 (b INT);
3989 CREATE TABLE t3 (c INT);
3990 CREATE TABLE t4 (d INT PRIMARY KEY);
3991 SET SQL_SAFE_UPDATES=1;
3992 UPDATE t1 STRAIGHT_JOIN t2 SET a = 89 WHERE 9 IN (SELECT c FROM t3 WHERE c IN (SELECT MAX(d) FROM t4));
3993 ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
3994 DROP TABLE t1, t2, t3, t4;
3995 #
3996 # End of 10.11 tests
3997 #
3998 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;