Debian Codegraph @

mysql-test/suite/merge/merge.test

raw
1 #
2 # Test of MERGE TABLES
3 #
4
5 --source include/test_db_charset_latin1.inc
6
7 # Save the initial number of concurrent sessions.
8 --source include/count_sessions.inc
9
10 # MERGE tables require MyISAM tables
11 set @save_default_storage_engine=@@global.default_storage_engine;
12 set global default_storage_engine=myisam;
13 set session default_storage_engine=myisam;
14
15 # This test assumes a stable character set
16 --source include/default_charset.inc
17
18 let $MYSQLD_DATADIR= `select @@datadir`;
19 create table t1 (a int not null primary key auto_increment, message char(20));
20 create table t2 (a int not null primary key auto_increment, message char(20));
21 INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
22 INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
23 create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2);
24 select * from t3;
25 select * from t3 order by a desc;
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 explain select * from t3 where a > 10 and a < 20;
41 select * from t3 where a = 10;
42 select * from t3 where a < 10;
43 select * from t3 where a > 10 and a < 20;
44 explain select a from t3 order by a desc limit 10;
45 select a from t3 order by a desc limit 10;
46 select a from t3 order by a desc limit 300,10;
47 delete from t3 where a=3;
48 select * from t3 where a < 10;
49 delete from t3 where a >= 6 and a <= 8;
50 select * from t3 where a < 10;
51 update t3 set a=3 where a=9;
52 select * from t3 where a < 10;
53 update t3 set a=6 where a=7;
54 select * from t3 where a < 10;
55 show create table t3;
56
57 # The following should give errors
58 create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2);
59 --error 1168
60 select * from t4;
61 --error 1168
62 alter table t4 add column c int;
63 flush tables;
64 --error 1168
65 select * from t4;
66
67 #
68 # Test tables in different databases
69 #
70 create database mysqltest character set latin1;
71 create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
72 create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6);
73 show create table t5;
74 alter table t5 engine=myisam;
75 drop table t5, mysqltest.t6;
76 drop database mysqltest;
77
78 # Because of windows, it's important that we drop the merge tables first!
79 drop table t4,t3,t1,t2;
80
81 create table t1 (c char(10)) engine=myisam;
82 create table t2 (c char(10)) engine=myisam;
83 create table t3 (c char(10)) union=(t1,t2) engine=merge;
84 insert into t1 (c) values ('test1');
85 insert into t1 (c) values ('test1');
86 insert into t1 (c) values ('test1');
87 insert into t2 (c) values ('test2');
88 insert into t2 (c) values ('test2');
89 insert into t2 (c) values ('test2');
90 select * from t3;
91 select * from t3;
92 delete from t3 where 1=1;
93 select * from t3;
94 select * from t1;
95 drop table t3,t2,t1;
96
97 #
98 # Test 2
99 #
100
101 CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr));
102 CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr));
103 CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
104 ENGINE=MERGE UNION=(t1,t2);
105
106 SELECT * from t3;
107
108 INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17);
109 INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32);
110 INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37);
111 INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30);
112
113 SELECT * from t3 where incr in (1,2,3,4) order by othr;
114 alter table t3 UNION=(t1);
115 select count(*) from t3;
116 alter table t3 UNION=(t1,t2);
117 select count(*) from t3;
118 alter table t3 ENGINE=MYISAM;
119 select count(*) from t3;
120
121 # Test that ALTER TABLE rembers the old UNION
122
123 drop table t3;
124 CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
125 ENGINE=MERGE UNION=(t1,t2);
126 show create table t3;
127 alter table t3 drop primary key;
128 show create table t3;
129
130 drop table t3,t2,t1;
131
132 #
133 # Test table without unions
134 #
135 create table t1 (a int not null, key(a)) engine=merge;
136 select * from t1;
137 drop table t1;
138
139 #
140 # Bug in flush tables combined with MERGE tables
141 #
142
143 create table t1 (a int not null, b int not null, key(a,b));
144 create table t2 (a int not null, b int not null, key(a,b));
145 create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2);
146 insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6);
147 insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6);
148 flush tables;
149 select * from t3 where a=1 order by b limit 2;
150 drop table t3,t1,t2;
151
152 #
153 # [phi] testing INSERT_METHOD stuff
154 #
155
156 # first testing of common stuff with new parameters
157 create table t1 (a int not null, b int not null auto_increment, primary key(a,b));
158 create table t2 (a int not null, b int not null auto_increment, primary key(a,b));
159 create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO;
160 create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO;
161 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;
162 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;
163 show create table t3;
164 show create table t4;
165 show create table t5;
166 show create table t6;
167 insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
168 insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
169 select * from t3 order by b,a limit 3;
170 select * from t4 order by b,a limit 3;
171 select * from t5 order by b,a limit 3,3;
172 select * from t6 order by b,a limit 6,3;
173 # now testing inserts and where the data gets written
174 insert into t5 values (5,1),(5,2);
175 insert into t6 values (6,1),(6,2);
176 select * from t1 order by a,b;
177 select * from t2 order by a,b;
178 select * from t4 order by a,b;
179 # preperation for next test
180 insert into t3 values (3,1),(3,2),(3,3),(3,4);
181 select * from t3 order by a,b;
182 # now testing whether options are kept by alter table
183 alter table t4 UNION=(t1,t2,t3);
184 show create table t4;
185 select * from t4 order by a,b;
186 # testing switching off insert method and inserts again
187 alter table t4 INSERT_METHOD=FIRST;
188 show create table t4;
189 insert into t4 values (4,1),(4,2);
190 select * from t1 order by a,b;
191 select * from t2 order by a,b;
192 select * from t3 order by a,b;
193 select * from t4 order by a,b;
194 select * from t5 order by a,b;
195 # auto_increment
196 select 1;
197 insert into t5 values (1,NULL),(5,NULL);
198 insert into t6 values (2,NULL),(6,NULL);
199 select * from t1 order by a,b;
200 select * from t2 order by a,b;
201 select * from t5 order by a,b;
202 select * from t6 order by a,b;
203 insert into t1 values (99,NULL);
204 select * from t4 where a+0 > 90;
205 # bug#4008 - cannot determine a unique key that caused "dupl. key error"
206 --error ER_DUP_ENTRY
207 insert t5 values (1,1);
208 --error ER_DUP_ENTRY
209 insert t6 values (2,1);
210 insert t5 values (1,1) on duplicate key update b=b+10;
211 insert t6 values (2,1) on duplicate key update b=b+20;
212 select * from t5 where a < 3;
213 drop table t6, t5, t4, t3, t2, t1;
214
215 CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM;
216 INSERT INTO t1 VALUES (1,1), (2,1);
217 CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM;
218 INSERT INTO t2 VALUES (1,2), (2,2);
219 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);
220 select max(b) from t3 where a = 2;
221 select max(b) from t1 where a = 2;
222 drop table t3,t1,t2;
223
224 #
225 # temporary merge tables
226 #
227 create table t1 (a int not null);
228 create table t2 (a int not null);
229 insert into t1 values (1);
230 insert into t2 values (2);
231 create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
232 select * from t3;
233 create temporary table t4 (a int not null);
234 create temporary table t5 (a int not null);
235 insert into t4 values (1);
236 insert into t5 values (2);
237 create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5);
238 select * from t6;
239 drop table t6, t3, t1, t2, t4, t5;
240 #
241 # Bug#19627 - temporary merge table locking
242 # MERGE table and its children must match in temporary type.
243 # Forbid temporary merge on non-temporary children: shown above.
244 # Forbid non-temporary merge on temporary children:
245 create temporary table t1 (a int not null);
246 create temporary table t2 (a int not null);
247 insert into t1 values (1);
248 insert into t2 values (2);
249 create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
250 --error ER_WRONG_MRG_TABLE
251 select * from t3;
252 drop table t3, t2, t1;
253 # Forbid children mismatch in temporary:
254 create table t1 (a int not null);
255 create temporary table t2 (a int not null);
256 insert into t1 values (1);
257 insert into t2 values (2);
258 create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
259 --error ER_WRONG_MRG_TABLE
260 select * from t3;
261 drop table t3;
262 create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
263 select * from t3;
264 drop table t3, t2, t1;
265 --echo # CREATE...SELECT is not implemented for MERGE tables.
266 CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL);
267 CREATE TEMPORARY TABLE t2 (c1 INT NOT NULL);
268 CREATE TABLE t3 (c1 INT NOT NULL);
269 INSERT INTO t3 VALUES (3), (33);
270 LOCK TABLES t3 READ;
271 --error ER_WRONG_OBJECT
272 CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
273 INSERT_METHOD=LAST SELECT * FROM t3;
274 --error ER_TABLE_NOT_LOCKED
275 SELECT * FROM t4;
276 UNLOCK TABLES;
277 CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
278 INSERT_METHOD=LAST;
279 INSERT INTO t4 SELECT * FROM t3;
280 --echo # Alter temporary MERGE table.
281 ALTER TABLE t4 UNION=(t1);
282 LOCK TABLES t4 WRITE;
283 --echo # Alter temporary MERGE table under LOCk tables.
284 ALTER TABLE t4 UNION=(t1,t2);
285 UNLOCK TABLES;
286 --echo # MERGE table and function.
287 CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);
288 SELECT * FROM t4 WHERE c1 < f1();
289 DROP FUNCTION f1;
290 DROP TABLE t4, t3, t2, t1;
291
292 #
293 # testing merge::records_in_range and optimizer
294 #
295
296 CREATE TABLE t1 (
297 fileset_id tinyint(3) unsigned NOT NULL default '0',
298 file_code varchar(32) NOT NULL default '',
299 fileset_root_id tinyint(3) unsigned NOT NULL default '0',
300 PRIMARY KEY (fileset_id,file_code),
301 KEY files (fileset_id,fileset_root_id)
302 ) ENGINE=MyISAM;
303 INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1),
304 (2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1),
305 (2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1);
306 CREATE TABLE t2 (
307 fileset_id tinyint(3) unsigned NOT NULL default '0',
308 file_code varchar(32) NOT NULL default '',
309 fileset_root_id tinyint(3) unsigned NOT NULL default '0',
310 PRIMARY KEY (fileset_id,file_code),
311 KEY files (fileset_id,fileset_root_id)
312 ) ENGINE=MRG_MyISAM UNION=(t1);
313
314 EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2
315 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
316 EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
317 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
318 EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2
319 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
320 EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
321 AND file_code = '0000000115' LIMIT 1;
322 DROP TABLE t2, t1;
323
324 #
325 # Test of ORDER BY DESC on key (Bug #515)
326 #
327
328 create table t1 (x int, y int, index xy(x, y));
329 create table t2 (x int, y int, index xy(x, y));
330 create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2);
331 insert into t1 values(1, 2);
332 insert into t2 values(1, 3);
333 select * from t3 where x = 1 and y < 5 order by y;
334 # Bug is that followng query returns empty set while it must be same as above
335 select * from t3 where x = 1 and y < 5 order by y desc;
336 drop table t1,t2,t3;
337
338 #
339 # Bug#5232: CREATE TABLE ... SELECT
340 #
341
342 create table t1 (a int);
343 create table t2 (a int);
344 insert into t1 values (0);
345 insert into t2 values (1);
346 --error ER_WRONG_OBJECT
347 create table t3 engine=merge union=(t1, t2) select * from t1;
348 --error ER_WRONG_OBJECT
349 create table t3 engine=merge union=(t1, t2) select * from t2;
350 --error ER_WRONG_OBJECT
351 create table t3 engine=merge union=(t1, t2) select (select max(a) from t2);
352 drop table t1, t2;
353
354 #
355 # Bug#9112 - Merge table with composite index producing invalid results with some queries
356 # This test case will fail only without the bugfix and some
357 # non-deterministic circumstances. It depends on properly initialized
358 # "un-initialized" memory. At the time it happens with a standard
359 # non-debug build. But there is no guarantee that this will be always so.
360 #
361 create table t1 (
362 a double(14,4),
363 b varchar(10),
364 index (a,b)
365 ) engine=merge union=(t2,t3);
366
367 create table t2 (
368 a double(14,4),
369 b varchar(10),
370 index (a,b)
371 ) engine=myisam;
372
373 create table t3 (
374 a double(14,4),
375 b varchar(10),
376 index (a,b)
377 ) engine=myisam;
378
379 insert into t2 values ( null, '');
380 insert into t2 values ( 9999999999.999, '');
381 insert into t3 select * from t2;
382 select min(a), max(a) from t1;
383 flush tables;
384 select min(a), max(a) from t1;
385 drop table t1, t2, t3;
386 # BUG#6699 : no sorting on 'ref' retrieval
387 create table t1 (a int,b int,c int, index (a,b,c));
388 create table t2 (a int,b int,c int, index (a,b,c));
389 create table t3 (a int,b int,c int, index (a,b,c))
390 engine=merge union=(t1 ,t2);
391 insert into t1 (a,b,c) values (1,1,0),(1,2,0);
392 insert into t2 (a,b,c) values (1,1,1),(1,2,1);
393
394 explain select a,b,c from t3 force index (a) where a=1 order by a,b,c;
395 select a,b,c from t3 force index (a) where a=1 order by a,b,c;
396
397 # this actually wasn't affected:
398 explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
399 select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
400
401 # BUG#7377 SHOW index on MERGE table crashes debug server
402 show index from t3;
403
404 drop table t1, t2, t3;
405
406 #
407 # Bug#10400 - Improperly-defined MERGE table crashes with INSERT ... ON DUPLICATE KEY UPDATE
408 #
409 CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) )
410 ENGINE=MyISAM;
411 CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) )
412 ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST;
413 INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2;
414 INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3;
415 SELECT b FROM t2;
416 DROP TABLE t1, t2;
417
418
419 #
420 # BUG#5390 - problems with merge tables
421 # Problem #1: INSERT...SELECT
422 #
423 #drop table if exists t1, t2, t3;
424 create table t1(a int);
425 create table t2(a int);
426 insert into t1 values (1);
427 insert into t2 values (2);
428 create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
429 select * from t3;
430 #
431 insert t2 select * from t2;
432 select * from t2;
433 #
434 insert t3 select * from t1;
435 select * from t3;
436 #
437 insert t1 select * from t3;
438 select * from t1;
439 select * from t2;
440 select * from t3;
441 check table t1, t2;
442 drop table t1, t2, t3;
443
444 #
445 # BUG#21617 - crash when selecting from merge table with inconsistent
446 # indexes
447 #
448 CREATE TABLE t1(a INT);
449 INSERT INTO t1 VALUES(2),(1);
450 CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
451 --error 1168
452 SELECT * FROM t2 WHERE a=2;
453 DROP TABLE t1, t2;
454
455 #
456 # BUG#10974 - No error message if merge table based on union of innodb,
457 # memory
458 #
459 CREATE TABLE t1(a INT) ENGINE=MEMORY;
460 CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1);
461 --error 1168
462 SELECT * FROM t2;
463 DROP TABLE t1, t2;
464 CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3);
465 --error ER_WRONG_MRG_TABLE
466 SELECT * FROM t2;
467 DROP TABLE t2;
468
469 #
470 # Underlying table definition conformance tests.
471 #
472 CREATE TABLE t1(a INT, b TEXT);
473 CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1);
474 --error 1168
475 SELECT * FROM tm1;
476 DROP TABLE t1, tm1;
477
478 CREATE TABLE t1(a SMALLINT, b SMALLINT);
479 CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1);
480 --error 1168
481 SELECT * FROM tm1;
482 DROP TABLE t1, tm1;
483
484 CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(a, b));
485 CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
486 --error 1168
487 SELECT * FROM tm1;
488 DROP TABLE t1, tm1;
489
490 CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(b));
491 CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
492 --error 1168
493 SELECT * FROM tm1;
494 DROP TABLE t1, tm1;
495
496
497 # BUG#26881 - Large MERGE tables report incorrect specification when no
498 # differences in tables
499 #
500 CREATE TABLE t1(c1 VARCHAR(1));
501 CREATE TABLE m1 LIKE t1;
502 ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
503 SELECT * FROM m1;
504 DROP TABLE t1, m1;
505
506 CREATE TABLE t1(c1 VARCHAR(4), c2 TINYINT, c3 TINYINT, c4 TINYINT,
507 c5 TINYINT, c6 TINYINT, c7 TINYINT, c8 TINYINT, c9 TINYINT);
508 CREATE TABLE m1 LIKE t1;
509 ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
510 SELECT * FROM m1;
511 DROP TABLE t1, m1;
512
513 #
514 # BUG#24342 - Incorrect results with query over MERGE table
515 #
516 CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci,
517 b INT, INDEX(a,b));
518 CREATE TABLE t2 LIKE t1;
519 CREATE TABLE t3 LIKE t1;
520 ALTER TABLE t3 ENGINE=MERGE UNION=(t1,t2);
521 INSERT INTO t1 VALUES ('ss',1);
522 INSERT INTO t2 VALUES ('ss',2),(0xDF,2);
523 SELECT COUNT(*) FROM t3 WHERE a=0xDF AND b=2;
524 DROP TABLE t1,t2,t3;
525
526 --echo # End of 4.1 tests
527
528 #
529 # BUG#19648 - Merge table does not work with bit types
530 #
531 create table t1 (b bit(1));
532 create table t2 (b bit(1));
533 create table tm (b bit(1)) engine = merge union = (t1,t2);
534 select * from tm;
535 drop table tm, t1, t2;
536
537 #
538 # Bug #17766: The server accepts to create MERGE tables which cannot work
539 #
540 create table t1 (a int) insert_method = last engine = merge;
541 --error ER_OPEN_AS_READONLY
542 insert into t1 values (1);
543 create table t2 (a int) engine = myisam;
544 alter table t1 union (t2);
545 insert into t1 values (1);
546 alter table t1 insert_method = no;
547 --error ER_OPEN_AS_READONLY
548 insert into t1 values (1);
549 drop table t2;
550 drop table t1;
551
552 #
553 # BUG#26976 - Missing table in merge not noted in related error msg + SHOW
554 # CREATE TABLE fails
555 #
556 CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2);
557 --error ER_WRONG_MRG_TABLE
558 SELECT * FROM tm1;
559 CHECK TABLE tm1;
560 CREATE TABLE t1(a INT);
561 --error ER_WRONG_MRG_TABLE
562 SELECT * FROM tm1;
563 CHECK TABLE tm1;
564 CREATE TABLE t2(a BLOB);
565 --error ER_WRONG_MRG_TABLE
566 SELECT * FROM tm1;
567 CHECK TABLE tm1;
568 ALTER TABLE t2 MODIFY a INT;
569 SELECT * FROM tm1;
570 CHECK TABLE tm1;
571 DROP TABLE tm1, t1, t2;
572
573 #
574 # Bug#15522 - create ... select and with merge tables
575 #
576 # This was fixed together with Bug#20662 (Infinite loop in CREATE TABLE
577 # IF NOT EXISTS ... SELECT with locked tables).
578 # The new behavior for MERGE tables is consistent with the
579 # CREATE TABLE SELECT behavior for ordinary tables.
580 #
581 CREATE TABLE t1(c1 INT);
582 CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
583 # After WL#5370, it just generates a warning that the table already exists
584 CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
585 DROP TABLE t1, t2;
586
587 #
588 # Bug #28837: MyISAM storage engine error (134) doing delete with self-join
589 #
590
591 CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM;
592 CREATE TABLE t2 LIKE t1;
593
594 INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
595 INSERT INTO t1 SELECT * FROM t2;
596 INSERT INTO t1 SELECT * FROM t2;
597
598 CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE
599 UNION(t1);
600
601 SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
602 SELECT * FROM t3;
603 DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
604 SELECT * FROM t3;
605
606 DROP TABLE t1, t2, t3;
607
608 #
609 # BUG#28248 - mysqldump results with MERGE ... UNION=() cannot be executed
610 #
611 CREATE TABLE t1(a INT);
612 CREATE TABLE m1(a INT) ENGINE=MERGE;
613 SHOW CREATE TABLE m1;
614 DROP TABLE m1;
615 CREATE TABLE m1(a INT) ENGINE=MERGE UNION=();
616 SHOW CREATE TABLE m1;
617 ALTER TABLE m1 UNION=(t1);
618 ALTER TABLE m1 UNION=();
619 SHOW CREATE TABLE m1;
620 DROP TABLE t1, m1;
621
622 #
623 # BUG#32047 - 'Spurious' errors while opening MERGE tables
624 #
625 CREATE TABLE t1(a INT);
626 CREATE TABLE t2(a VARCHAR(10));
627 CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2);
628 CREATE TABLE m2(a INT) ENGINE=MERGE UNION=(t1);
629 SELECT * FROM t1;
630 --error ER_WRONG_MRG_TABLE
631 SELECT * FROM m1;
632 SELECT * FROM m2;
633 DROP TABLE t1, t2, m1, m2;
634
635 --echo # End of 5.0 tests
636
637
638 #
639 # Bug #8306: TRUNCATE leads to index corruption
640 #
641 create table t1 (c1 int, index(c1));
642 create table t2 (c1 int, index(c1)) engine=merge union=(t1);
643 insert into t1 values (1);
644 # Close all tables.
645 flush tables;
646 # Open t2 and (implicitly) t1.
647 select * from t2;
648 # Truncate after flush works (unless another threads reopens t2 in between).
649 flush tables;
650 truncate table t1;
651 insert into t1 values (1);
652 # Close all tables.
653 flush tables;
654 # Open t2 and (implicitly) t1.
655 select * from t2;
656 # Truncate t1, which was not recognized as open without the bugfix.
657 # After fix for Bug#8306 and before fix for Bug#26379,
658 # it should fail with a table-in-use error message, otherwise succeed.
659 truncate table t1;
660 # The insert used to fail on the crashed table.
661 insert into t1 values (1);
662 drop table t1,t2;
663 --echo #
664 --echo # Extra tests for TRUNCATE.
665 --echo #
666 --echo # Truncate MERGE table.
667 CREATE TABLE t1 (c1 INT, INDEX(c1));
668 CREATE TABLE t2 (c1 INT, INDEX(c1));
669 CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
670 INSERT INTO t1 VALUES (1);
671 INSERT INTO t2 VALUES (2);
672 SELECT * FROM t3;
673 TRUNCATE TABLE t3;
674 SELECT * FROM t3;
675 --echo #
676 --echo # Truncate child table.
677 INSERT INTO t1 VALUES (1);
678 INSERT INTO t2 VALUES (2);
679 TRUNCATE TABLE t1;
680 SELECT * FROM t3;
681 --echo #
682 --echo # Truncate MERGE table under locked tables.
683 LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
684 INSERT INTO t1 VALUES (1);
685 TRUNCATE TABLE t3;
686 SELECT * FROM t3;
687 UNLOCK TABLES;
688 SELECT * FROM t1;
689 SELECT * FROM t2;
690 --echo #
691 --echo # Truncate child table under locked tables.
692 LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
693 INSERT INTO t1 VALUES (1);
694 INSERT INTO t2 VALUES (2);
695 TRUNCATE TABLE t1;
696 SELECT * FROM t3;
697 UNLOCK TABLES;
698 DROP TABLE t1, t2, t3;
699 --echo #
700 --echo # Truncate temporary MERGE table.
701 CREATE TEMPORARY TABLE t1 (c1 INT, INDEX(c1));
702 CREATE TEMPORARY TABLE t2 (c1 INT, INDEX(c1));
703 CREATE TEMPORARY TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
704 INSERT INTO t1 VALUES (1);
705 INSERT INTO t2 VALUES (2);
706 SELECT * FROM t3;
707 TRUNCATE TABLE t3;
708 SELECT * FROM t3;
709 --echo #
710 --echo # Truncate temporary child table.
711 INSERT INTO t1 VALUES (1);
712 INSERT INTO t2 VALUES (2);
713 TRUNCATE TABLE t1;
714 SELECT * FROM t3;
715 --echo #
716 --echo # Truncate temporary MERGE table under locked tables.
717 INSERT INTO t1 VALUES (1);
718 CREATE TABLE t4 (c1 INT, INDEX(c1));
719 LOCK TABLE t4 WRITE;
720 TRUNCATE TABLE t3;
721 SELECT * FROM t3;
722 SELECT * FROM t1;
723 SELECT * FROM t2;
724 --echo #
725 --echo # Truncate temporary child table under locked tables.
726 INSERT INTO t1 VALUES (1);
727 INSERT INTO t2 VALUES (2);
728 TRUNCATE TABLE t1;
729 SELECT * FROM t3;
730 SELECT * FROM t1;
731 SELECT * FROM t2;
732 UNLOCK TABLES;
733 DROP TABLE t1, t2, t3, t4;
734
735 #
736 # Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
737 # Preparation
738 connect (con1,localhost,root,,);
739 connect (con2,localhost,root,,);
740 connection default;
741 #
742 # Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
743 # Problem #1
744 # A thread trying to lock a MERGE table performed busy waiting while
745 # REPAIR TABLE or a similar table administration task was ongoing on one or
746 # more of its MyISAM tables.
747 # To allow for observability it was necessary to enter a multi-second sleep
748 # in mysql_admin_table() after remove_table_from_cache(), which comes after
749 # mysql_abort_lock(). The sleep faked a long running operation. One could
750 # watch a high CPU load during the sleep time.
751 # The problem was that mysql_abort_lock() upgrades the write lock to
752 # TL_WRITE_ONLY. This lock type persisted until the final unlock at the end
753 # of the administration task. The effect of TL_WRITE_ONLY is to reject any
754 # attempt to lock the table. The trying thread must close the table and wait
755 # until it is no longer used. Unfortunately there is no way to detect that
756 # one of the MyISAM tables of a MERGE table is in use. When trying to lock
757 # the MERGE table, all MyISAM tables are locked. If one fails on
758 # TL_WRITE_ONLY, all locks are aborted and wait_for_tables() is entered.
759 # But this doesn't see the MERGE table as used, so it seems appropriate to
760 # retry a lock...
761 #
762 CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
763 CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
764 send REPAIR TABLE t1;
765 connection con1;
766 sleep 1; # let repair run into its sleep
767 INSERT INTO t2 VALUES (1);
768 connection default;
769 reap;
770 DROP TABLE t1, t2;
771 #
772 # Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
773 # Problem #2
774 # A thread trying to lock a MERGE table performed busy waiting until all
775 # threads that did REPAIR TABLE or similar table administration tasks on
776 # one or more of its MyISAM tables in LOCK TABLES segments did
777 # UNLOCK TABLES.
778 # The difference against problem #1 is that the busy waiting took place
779 # *after* the administration task. It was terminated by UNLOCK TABLES only.
780 #
781 # This is the same test case as for
782 # Bug#26867 - LOCK TABLES REPAIR + merge table result in memory/cpu hogging
783 #
784 #
785 CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
786 CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
787 LOCK TABLE t1 WRITE;
788 connection con1;
789 send INSERT INTO t2 VALUES (1);
790 connection default;
791 sleep 1; # Let INSERT go into thr_multi_lock().
792 REPAIR TABLE t1;
793 sleep 2; # con1 performs busy waiting during this sleep.
794 UNLOCK TABLES;
795 connection con1;
796 reap;
797 connection default;
798 DROP TABLE t1, t2;
799 #
800 # Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
801 # Problem #3
802 # Two FLUSH TABLES within a LOCK TABLES segment could invalidate the lock.
803 # This did *not* require a MERGE table.
804 # To increase reproducibility it was necessary to enter a sleep of 2 seconds
805 # at the end of wait_for_tables() after unlock of LOCK_open. In 5.0 and 5.1
806 # the sleep must be inserted in open_and_lock_tables() after open_tables()
807 # instead. wait_for_tables() is not used in this case.
808 # The problem was that FLUSH TABLES releases LOCK_open while having unlocked
809 # and closed all tables. When this happened while a thread was in the loop in
810 # mysql_lock_tables() right after wait_for_tables() and before retrying to
811 # lock, the thread got the lock. (Translate to similar code places in 5.0
812 # and 5.1). And it did not notice that the table needed a refresh. So it
813 # executed its statement on the table.
814 # The first FLUSH TABLES kicked the INSERT out of thr_multi_lock() and let
815 # it wait in wait_for_tables(). (open_table() in 5.0 and 5.1). The second
816 # FLUSH TABLES must happen while the INSERT was on its way from
817 # wait_for_tables() to the next call of thr_multi_lock(). This needed to be
818 # supported by a sleep to make it repeatable.
819 #
820 CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
821 LOCK TABLE t1 WRITE;
822 connection con1;
823 send INSERT INTO t1 VALUES (1);
824 connection default;
825 sleep 1; # Let INSERT go into thr_multi_lock().
826 FLUSH TABLES;
827 sleep 1; # Let INSERT go through wait_for_tables() where it sleeps.
828 FLUSH TABLES;
829 # This should give no result. But it will with sleep(2) at the right place.
830 SELECT * FROM t1;
831 UNLOCK TABLES;
832 connection con1;
833 reap;
834 connection default;
835 DROP TABLE t1;
836 #
837 # Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
838 # Cleanup
839 disconnect con1;
840 disconnect con2;
841 #
842 --echo #
843 --echo # Extra tests for Bug#26379 - Combination of FLUSH TABLE and
844 --echo # REPAIR TABLE corrupts a MERGE table
845 #
846 --echo #
847 --echo # CREATE ... SELECT is disabled for MERGE tables.
848 --echo #
849 CREATE TABLE t1(c1 INT);
850 INSERT INTO t1 VALUES (1);
851 CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
852 --error ER_OPEN_AS_READONLY
853 CREATE TABLE t3 ENGINE=MRG_MYISAM INSERT_METHOD=LAST SELECT * FROM t2;
854 --error ER_NO_SUCH_TABLE
855 SHOW CREATE TABLE t3;
856 --error ER_WRONG_OBJECT
857 CREATE TABLE t3 ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST
858 SELECT * FROM t2;
859 --error ER_NO_SUCH_TABLE
860 SHOW CREATE TABLE t3;
861 DROP TABLE t1, t2;
862 #
863 --echo #
864 --echo # Bug#37371 "CREATE TABLE LIKE merge loses UNION parameter"
865 --echo # Demonstrate that this is no longer the case.
866 --echo #
867 --echo # 1. Create like.
868 CREATE TABLE t1 (c1 INT);
869 CREATE TABLE t2 (c1 INT);
870 CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
871 INSERT_METHOD=LAST;
872 INSERT INTO t1 VALUES (1);
873 INSERT INTO t2 VALUES (2);
874 INSERT INTO t3 VALUES (3);
875 CREATE TABLE t4 LIKE t3;
876 SHOW CREATE TABLE t4;
877 INSERT INTO t4 VALUES (4);
878 DROP TABLE t4;
879 --echo #
880 --echo # 1. Create like with locked tables.
881 LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE;
882 --error ER_TABLE_NOT_LOCKED
883 CREATE TABLE t4 LIKE t3;
884 --error ER_TABLE_NOT_LOCKED
885 SHOW CREATE TABLE t4;
886 --error ER_TABLE_NOT_LOCKED
887 INSERT INTO t4 VALUES (4);
888 --echo # Temporary tables can be created in spite of LOCK TABLES.
889 --echo # If the temporary MERGE table uses the locked children only,
890 --echo # it can even be used.
891 CREATE TEMPORARY TABLE t4 LIKE t3;
892 SHOW CREATE TABLE t4;
893 INSERT INTO t4 VALUES (4);
894 UNLOCK TABLES;
895 INSERT INTO t4 VALUES (4);
896 DROP TABLE t4;
897 #
898 --echo #
899 --echo # Rename child.
900 --echo #
901 --echo # 1. Normal rename of non-MERGE table.
902 CREATE TABLE t4 (c1 INT);
903 INSERT INTO t4 VALUES (4);
904 SELECT * FROM t4 ORDER BY c1;
905 RENAME TABLE t4 TO t5;
906 SELECT * FROM t5 ORDER BY c1;
907 RENAME TABLE t5 TO t4;
908 SELECT * FROM t4 ORDER BY c1;
909 DROP TABLE t4;
910 --echo #
911 --echo # 2. Normal rename.
912 SELECT * FROM t3 ORDER BY c1;
913 RENAME TABLE t2 TO t5;
914 --error ER_WRONG_MRG_TABLE
915 SELECT * FROM t3 ORDER BY c1;
916 RENAME TABLE t5 TO t2;
917 SELECT * FROM t3 ORDER BY c1;
918 --echo #
919 --echo # 3. Normal rename with locked tables.
920 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
921 SELECT * FROM t3 ORDER BY c1;
922 --error ER_LOCK_OR_ACTIVE_TRANSACTION
923 RENAME TABLE t2 TO t5;
924 SELECT * FROM t3 ORDER BY c1;
925 --error ER_LOCK_OR_ACTIVE_TRANSACTION
926 RENAME TABLE t5 TO t2;
927 SELECT * FROM t3 ORDER BY c1;
928 UNLOCK TABLES;
929 --echo #
930 --echo # 4. Alter table rename.
931 ALTER TABLE t2 RENAME TO t5;
932 --error ER_WRONG_MRG_TABLE
933 SELECT * FROM t3 ORDER BY c1;
934 ALTER TABLE t5 RENAME TO t2;
935 SELECT * FROM t3 ORDER BY c1;
936 --echo #
937 --echo # 5. Alter table rename with locked tables.
938 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
939 ALTER TABLE t2 RENAME TO t5;
940 --error ER_TABLE_NOT_LOCKED
941 SELECT * FROM t3 ORDER BY c1;
942 --error ER_TABLE_NOT_LOCKED
943 ALTER TABLE t5 RENAME TO t2;
944 UNLOCK TABLES;
945 ALTER TABLE t5 RENAME TO t2;
946 SELECT * FROM t3 ORDER BY c1;
947 #
948 --echo #
949 --echo # Rename parent.
950 --echo #
951 --echo # 1. Normal rename with locked tables.
952 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
953 SELECT * FROM t3 ORDER BY c1;
954 --error ER_LOCK_OR_ACTIVE_TRANSACTION
955 RENAME TABLE t3 TO t5;
956 SELECT * FROM t3 ORDER BY c1;
957 --error ER_LOCK_OR_ACTIVE_TRANSACTION
958 RENAME TABLE t5 TO t3;
959 SELECT * FROM t3 ORDER BY c1;
960 --echo #
961 --echo # 5. Alter table rename with locked tables.
962 ALTER TABLE t3 RENAME TO t5;
963 --error ER_TABLE_NOT_LOCKED
964 SELECT * FROM t5 ORDER BY c1;
965 --error ER_TABLE_NOT_LOCKED
966 ALTER TABLE t5 RENAME TO t3;
967 UNLOCK TABLES;
968 ALTER TABLE t5 RENAME TO t3;
969 SELECT * FROM t3 ORDER BY c1;
970 DROP TABLE t1, t2, t3;
971 #
972 --echo #
973 --echo # Drop locked tables.
974 --echo #
975 --echo # 1. Drop parent.
976 CREATE TABLE t1 (c1 INT, INDEX(c1));
977 CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
978 INSERT_METHOD=LAST;
979 LOCK TABLES t1 WRITE, t2 WRITE;
980 INSERT INTO t1 VALUES (1);
981 DROP TABLE t2;
982 --error ER_TABLE_NOT_LOCKED
983 SELECT * FROM t2;
984 SELECT * FROM t1;
985 UNLOCK TABLES;
986 --echo # 2. Drop child.
987 CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
988 INSERT_METHOD=LAST;
989 LOCK TABLES t1 WRITE, t2 WRITE;
990 INSERT INTO t1 VALUES (1);
991 DROP TABLE t1;
992 --error ER_TABLE_NOT_LOCKED
993 SELECT * FROM t2;
994 --error ER_TABLE_NOT_LOCKED
995 SELECT * FROM t1;
996 UNLOCK TABLES;
997 DROP TABLE t2;
998 #
999 --echo #
1000 --echo # ALTER TABLE. Change child list.
1001 --echo #
1002 CREATE TABLE t1 (c1 INT, INDEX(c1));
1003 CREATE TABLE t2 (c1 INT, INDEX(c1));
1004 CREATE TABLE t3 (c1 INT, INDEX(c1));
1005 INSERT INTO t1 VALUES (1);
1006 INSERT INTO t2 VALUES (2);
1007 INSERT INTO t3 VALUES (3);
1008 CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t3,t2)
1009 INSERT_METHOD=LAST;
1010 --echo # Shrink child list.
1011 ALTER TABLE t4 UNION=(t3);
1012 SHOW CREATE TABLE t4;
1013 SELECT * FROM t4 ORDER BY c1;
1014 --echo # Extend child list.
1015 ALTER TABLE t4 UNION=(t3,t2);
1016 SHOW CREATE TABLE t4;
1017 SELECT * FROM t4 ORDER BY c1;
1018 #
1019 --echo #
1020 --echo # ALTER TABLE under LOCK TABLES. Change child list.
1021 --echo #
1022 LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE;
1023 --echo # Shrink child list.
1024 --error ER_LOCK_OR_ACTIVE_TRANSACTION
1025 ALTER TABLE t4 UNION=(t3);
1026 --echo # Extend child list within locked tables.
1027 --error ER_LOCK_OR_ACTIVE_TRANSACTION
1028 ALTER TABLE t4 UNION=(t3,t2);
1029 --echo # Extend child list beyond locked tables.
1030 --error ER_LOCK_OR_ACTIVE_TRANSACTION
1031 ALTER TABLE t4 UNION=(t3,t2,t1);
1032 SHOW CREATE TABLE t4;
1033 SELECT * FROM t4 ORDER BY c1;
1034 UNLOCK TABLES;
1035 DROP TABLE t4;
1036 #
1037 --echo #
1038 --echo # ALTER TABLE under LOCK TABLES. Grave change, table re-creation.
1039 --echo #
1040 CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
1041 INSERT_METHOD=LAST;
1042 --echo # Lock parent first and then children.
1043 LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
1044 ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1045 SELECT * FROM t4 ORDER BY c1;
1046 ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1047 SELECT * FROM t4 ORDER BY c1;
1048 UNLOCK TABLES;
1049 --echo # Lock children first and then parent.
1050 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
1051 ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1052 SELECT * FROM t4 ORDER BY c1;
1053 ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1054 SELECT * FROM t4 ORDER BY c1;
1055 UNLOCK TABLES;
1056 --echo # Lock parent between children.
1057 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1058 ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1059 SELECT * FROM t4 ORDER BY c1;
1060 ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
1061 SELECT * FROM t4 ORDER BY c1;
1062 UNLOCK TABLES;
1063 DROP TABLE t1, t2, t3, t4;
1064 #
1065 --echo #
1066 --echo # ALTER TABLE under LOCK TABLES. Simple change, no re-creation.
1067 --echo #
1068 CREATE TABLE t1 (c1 INT);
1069 CREATE TABLE t2 (c1 INT);
1070 CREATE TABLE t3 (c1 INT);
1071 CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
1072 INSERT_METHOD=LAST;
1073 INSERT INTO t1 VALUES (1);
1074 INSERT INTO t2 VALUES (2);
1075 INSERT INTO t3 VALUES (3);
1076 --echo # Lock parent first and then children.
1077 LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
1078 ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
1079 SELECT * FROM t4 ORDER BY c1;
1080 ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
1081 SELECT * FROM t4 ORDER BY c1;
1082 UNLOCK TABLES;
1083 --echo # Lock children first and then parent.
1084 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
1085 ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
1086 SELECT * FROM t4 ORDER BY c1;
1087 ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
1088 SELECT * FROM t4 ORDER BY c1;
1089 UNLOCK TABLES;
1090 --echo # Lock parent between children.
1091 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1092 ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
1093 SELECT * FROM t4 ORDER BY c1;
1094 ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
1095 SELECT * FROM t4 ORDER BY c1;
1096 UNLOCK TABLES;
1097 #
1098 --echo #
1099 --echo # FLUSH TABLE under LOCK TABLES.
1100 --echo #
1101 --echo # Lock parent first and then children.
1102 LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
1103 FLUSH TABLE t4;
1104 SELECT * FROM t4 ORDER BY c1;
1105 FLUSH TABLE t2;
1106 SELECT * FROM t4 ORDER BY c1;
1107 FLUSH TABLES;
1108 SELECT * FROM t4 ORDER BY c1;
1109 UNLOCK TABLES;
1110 --echo # Lock children first and then parent.
1111 LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
1112 FLUSH TABLE t4;
1113 SELECT * FROM t4 ORDER BY c1;
1114 FLUSH TABLE t2;
1115 SELECT * FROM t4 ORDER BY c1;
1116 FLUSH TABLES;
1117 SELECT * FROM t4 ORDER BY c1;
1118 UNLOCK TABLES;
1119 --echo # Lock parent between children.
1120 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1121 FLUSH TABLE t4;
1122 SELECT * FROM t4 ORDER BY c1;
1123 FLUSH TABLE t2;
1124 SELECT * FROM t4 ORDER BY c1;
1125 FLUSH TABLES;
1126 SELECT * FROM t4 ORDER BY c1;
1127 UNLOCK TABLES;
1128 #
1129 --echo #
1130 --echo # Triggers
1131 --echo #
1132 --echo # Trigger on parent
1133 DELETE FROM t4 WHERE c1 = 4;
1134 CREATE TRIGGER t4_ai1 AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
1135 SET @a=0;
1136 INSERT INTO t4 VALUES (4);
1137 SELECT @a;
1138 SELECT * FROM t4 ORDER BY c1;
1139 DROP TRIGGER t4_ai1;
1140 CHECK TABLE t3;
1141 --echo # Trigger on parent under LOCK TABLES
1142 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1143 CREATE TRIGGER t4_ai2 AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
1144 SET @a=0;
1145 INSERT INTO t4 VALUES (4);
1146 SELECT @a;
1147 SELECT * FROM t4 ORDER BY c1;
1148 DROP TRIGGER t4_ai2;
1149 UNLOCK TABLES;
1150 CHECK TABLE t3;
1151 --echo #
1152 --echo # Trigger on child
1153 DELETE FROM t4 WHERE c1 = 4;
1154 CREATE TRIGGER t3_ai3 AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
1155 SET @a=0;
1156 INSERT INTO t4 VALUES (4);
1157 SELECT @a;
1158 INSERT INTO t3 VALUES (33);
1159 SELECT @a;
1160 SELECT * FROM t4 ORDER BY c1;
1161 DROP TRIGGER t3_ai3;
1162 CHECK TABLE t3;
1163 --echo # Trigger on child under LOCK TABLES
1164 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1165 CREATE TRIGGER t3_ai4 AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
1166 SET @a=0;
1167 INSERT INTO t4 VALUES (4);
1168 SELECT @a;
1169 INSERT INTO t3 VALUES (33);
1170 SELECT @a;
1171 SELECT * FROM t4 ORDER BY c1;
1172 DELETE FROM t4 WHERE c1 = 33;
1173 DROP TRIGGER t3_ai4;
1174 UNLOCK TABLES;
1175 CHECK TABLE t3;
1176 --echo #
1177 --echo # Trigger with table use on child
1178 DELETE FROM t4 WHERE c1 = 4;
1179 CREATE TRIGGER t3_ai5 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
1180 SELECT COUNT(*) FROM t2;
1181 INSERT INTO t4 VALUES (4);
1182 SELECT * FROM t4 ORDER BY c1;
1183 INSERT INTO t3 VALUES (33);
1184 SELECT * FROM t4 ORDER BY c1;
1185 DELETE FROM t4 WHERE c1 = 22;
1186 DELETE FROM t4 WHERE c1 = 33;
1187 DROP TRIGGER t3_ai5;
1188 UNLOCK TABLES;
1189 CHECK TABLE t2,t3;
1190 --echo # Trigger with table use on child under LOCK TABLES
1191 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1192 CREATE TRIGGER t3_ai6 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
1193 INSERT INTO t4 VALUES (4);
1194 SELECT * FROM t4 ORDER BY c1;
1195 INSERT INTO t3 VALUES (33);
1196 SELECT * FROM t4 ORDER BY c1;
1197 DROP TRIGGER t3_ai6;
1198 UNLOCK TABLES;
1199 check table t2,t3,t4;
1200 DELETE FROM t4 WHERE c1 = 22;
1201 DELETE FROM t4 WHERE c1 = 33;
1202 --echo # Trigger with table use on child under different LOCK TABLES
1203 DELETE FROM t4 WHERE c1 = 4;
1204 LOCK TABLES t4 WRITE,t3 WRITE, t2 WRITE, t1 WRITE;
1205 CREATE TRIGGER t3_ai7 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
1206 INSERT INTO t4 VALUES (4);
1207 SELECT * FROM t4 ORDER BY c1;
1208 INSERT INTO t3 VALUES (33);
1209 SELECT * FROM t4 ORDER BY c1;
1210 DROP TRIGGER t3_ai7;
1211 UNLOCK TABLES;
1212 check table t2,t3,t4;
1213 DELETE FROM t4 WHERE c1 = 22;
1214 DELETE FROM t4 WHERE c1 = 33;
1215 #
1216 --echo #
1217 --echo # Repair
1218 --echo #
1219 REPAIR TABLE t4;
1220 REPAIR TABLE t2;
1221 SELECT * FROM t4 ORDER BY c1;
1222 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1223 REPAIR TABLE t4;
1224 REPAIR TABLE t2;
1225 SELECT * FROM t4 ORDER BY c1;
1226 UNLOCK TABLES;
1227 #
1228 --echo #
1229 --echo # Optimize
1230 --echo #
1231 OPTIMIZE TABLE t4;
1232 OPTIMIZE TABLE t2;
1233 SELECT * FROM t4 ORDER BY c1;
1234 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1235 OPTIMIZE TABLE t4;
1236 OPTIMIZE TABLE t2;
1237 SELECT * FROM t4 ORDER BY c1;
1238 UNLOCK TABLES;
1239 #
1240 --echo #
1241 --echo # Checksum
1242 --echo #
1243 CHECKSUM TABLE t4;
1244 CHECKSUM TABLE t2;
1245 SELECT * FROM t4 ORDER BY c1;
1246 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1247 CHECKSUM TABLE t4;
1248 CHECKSUM TABLE t2;
1249 SELECT * FROM t4 ORDER BY c1;
1250 UNLOCK TABLES;
1251 #
1252 --echo #
1253 --echo # Insert delayed
1254 --echo #
1255 # See also Bug#26464 - insert delayed + update + merge = corruption
1256 # Succeeds in embedded server - is converted to normal insert
1257 # Fails in normal server, ps-protocol - not supported by engine
1258 # Fails in normal server, normal protocol - not a base table
1259 --error 0, ER_DELAYED_NOT_SUPPORTED, ER_WRONG_OBJECT
1260 INSERT DELAYED INTO t4 VALUES(44);
1261 # Get rid of row in embedded server
1262 DELETE FROM t4 WHERE c1 = 44;
1263 INSERT DELAYED INTO t3 VALUES(33);
1264 let $wait_cmd= SHOW STATUS LIKE 'Not_flushed_delayed_rows';
1265 let $run= query_get_value($wait_cmd, Value, 1);
1266 while ($run)
1267 {
1268 let $run= query_get_value($wait_cmd, Value, 1);
1269 }
1270 SELECT * FROM t4 ORDER BY c1;
1271 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
1272 --error ER_DELAYED_INSERT_TABLE_LOCKED, ER_DELAYED_NOT_SUPPORTED
1273 INSERT DELAYED INTO t4 VALUES(444);
1274 --error ER_DELAYED_INSERT_TABLE_LOCKED, ER_DELAYED_NOT_SUPPORTED
1275 INSERT DELAYED INTO t3 VALUES(333);
1276 SELECT * FROM t4 ORDER BY c1;
1277 UNLOCK TABLES;
1278 DROP TABLE t1, t2, t3, t4;
1279 #
1280 --echo #
1281 --echo # Recursive inclusion of merge tables in their union clauses.
1282 --echo #
1283 CREATE TABLE t1 (c1 INT, INDEX(c1));
1284 CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
1285 INSERT_METHOD=LAST;
1286 CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t2,t1)
1287 INSERT_METHOD=LAST;
1288 ALTER TABLE t2 UNION=(t3,t1);
1289 --error ER_ADMIN_WRONG_MRG_TABLE
1290 SELECT * FROM t2;
1291 DROP TABLE t1, t2, t3;
1292
1293
1294 #
1295 # Bug#25038 - Waiting TRUNCATE
1296 # Truncate failed with error message when table was in use by MERGE.
1297 #
1298 # Show that truncate of child table after use of parent table works.
1299 CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
1300 CREATE TABLE t2 (c1 INT) ENGINE= MyISAM;
1301 CREATE TABLE t3 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1, t2);
1302 INSERT INTO t1 VALUES (1);
1303 INSERT INTO t2 VALUES (2);
1304 SELECT * FROM t3;
1305 TRUNCATE TABLE t1;
1306 SELECT * FROM t3;
1307 DROP TABLE t1, t2, t3;
1308 #
1309 # Show that truncate of child table waits while parent table is used.
1310 # (test partly borrowed from count_distinct3.)
1311 CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
1312 SET @rnd_max= 2147483647;
1313 let $1 = 10;
1314 --disable_query_log
1315 begin;
1316 while ($1)
1317 {
1318 SET @rnd= RAND();
1319 SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
1320 SET @id_rev= @rnd_max - @id;
1321 SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
1322 INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
1323 dec $1;
1324 }
1325 commit;
1326 --enable_query_log
1327 set @@read_buffer_size=2*1024*1024;
1328 CREATE TABLE t2 SELECT * FROM t1;
1329 INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
1330 INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
1331 INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
1332 INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
1333 INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
1334 CREATE TABLE t3 (id INTEGER, grp TINYINT, id_rev INTEGER)
1335 ENGINE= MRG_MYISAM UNION= (t1, t2);
1336 SELECT COUNT(*) FROM t1;
1337 SELECT COUNT(*) FROM t2;
1338 SELECT COUNT(*) FROM t3;
1339 connect (con1,localhost,root,,);
1340 # As t3 contains random numbers, results are different from test to test.
1341 # That's okay, because we test only that select doesn't yield an
1342 # error. Note, that --disable_result_log doesn't suppress error output.
1343 --disable_result_log
1344 send SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2
1345 WHERE a1.id = a2.id GROUP BY a2.grp;
1346 connection default;
1347 sleep 1;
1348 TRUNCATE TABLE t1;
1349 connection con1;
1350 reap;
1351 --enable_result_log
1352 disconnect con1;
1353 connection default;
1354 SELECT COUNT(*) FROM t1;
1355 SELECT COUNT(*) FROM t2;
1356 SELECT COUNT(*) FROM t3;
1357 DROP TABLE t1, t2, t3;
1358
1359 #
1360 # Bug#25700 - merge base tables get corrupted by optimize/analyze/repair table
1361 #
1362 # Using FLUSH TABLES before REPAIR.
1363 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
1364 CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
1365 INSERT INTO t2 VALUES (1);
1366 SELECT * FROM t2;
1367 LOCK TABLES t2 WRITE, t1 WRITE;
1368 FLUSH TABLES;
1369 REPAIR TABLE t1;
1370 CHECK TABLE t1;
1371 REPAIR TABLE t1;
1372 UNLOCK TABLES;
1373 CHECK TABLE t1 EXTENDED;
1374 #
1375 # Not using FLUSH TABLES before REPAIR.
1376 LOCK TABLES t2 WRITE, t1 WRITE;
1377 REPAIR TABLE t1;
1378 CHECK TABLE t1;
1379 REPAIR TABLE t1;
1380 UNLOCK TABLES;
1381 CHECK TABLE t1 EXTENDED;
1382 DROP TABLE t1, t2;
1383
1384 #
1385 # Bug#26377 - Deadlock with MERGE and FLUSH TABLE
1386 #
1387 CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
1388 CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
1389 # Lock t1 first. This did always work.
1390 LOCK TABLES t1 WRITE, m1 WRITE;
1391 FLUSH TABLE t1;
1392 UNLOCK TABLES;
1393 DROP TABLE m1, t1;
1394 #
1395 CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
1396 CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
1397 # Lock m1 first. This did deadlock.
1398 LOCK TABLES m1 WRITE, t1 WRITE;
1399 FLUSH TABLE t1;
1400 UNLOCK TABLES;
1401 DROP TABLE m1, t1;
1402
1403 #
1404 # Bug#27660 - Falcon: merge table possible
1405 #
1406 # Normal MyISAM MERGE operation.
1407 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE= MyISAM;
1408 CREATE TABLE t2 (c1 INT, c2 INT) ENGINE= MyISAM;
1409 CREATE TABLE t3 (c1 INT, c2 INT) ENGINE= MRG_MYISAM UNION(t1, t2);
1410 INSERT INTO t1 VALUES (1, 1);
1411 INSERT INTO t2 VALUES (2, 2);
1412 SELECT * FROM t3;
1413 # Try an unsupported engine.
1414 ALTER TABLE t1 ENGINE= MEMORY;
1415 INSERT INTO t1 VALUES (0, 0);
1416 # Before fixing, this succeeded, but (0, 0) was missing.
1417 --error 1168
1418 SELECT * FROM t3;
1419 DROP TABLE t1, t2, t3;
1420
1421 #
1422 # Bug#30275 - Merge tables: flush tables or unlock tables causes server to crash
1423 #
1424 CREATE TABLE t1 (c1 INT, KEY(c1));
1425 CREATE TABLE t2 (c1 INT, KEY(c1)) ENGINE=MRG_MYISAM UNION=(t1)
1426 INSERT_METHOD=FIRST;
1427 LOCK TABLE t1 WRITE, t2 WRITE;
1428 FLUSH TABLES t2, t1;
1429 OPTIMIZE TABLE t1;
1430 FLUSH TABLES t1;
1431 UNLOCK TABLES;
1432 #
1433 FLUSH TABLES;
1434 INSERT INTO t1 VALUES (1);
1435 LOCK TABLE t1 WRITE, t2 WRITE;
1436 FLUSH TABLES t2, t1;
1437 OPTIMIZE TABLE t1;
1438 FLUSH TABLES t1;
1439 UNLOCK TABLES;
1440 DROP TABLE t1, t2;
1441
1442 #
1443 # Test derived from test program for
1444 # Bug#30273 - merge tables: Can't lock file (errno: 155)
1445 #
1446 CREATE TABLE t1 (ID INT) ENGINE=MYISAM;
1447 CREATE TABLE m1 (ID INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
1448 INSERT INTO t1 VALUES ();
1449 INSERT INTO m1 VALUES ();
1450 LOCK TABLE t1 WRITE, m1 WRITE;
1451 FLUSH TABLES m1, t1;
1452 OPTIMIZE TABLE t1;
1453 FLUSH TABLES m1, t1;
1454 UNLOCK TABLES;
1455 DROP TABLE t1, m1;
1456
1457 #
1458 # Bug#35068 - Assertion fails when reading from i_s.tables
1459 # and there is incorrect merge table
1460 #
1461 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
1462 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';
1463
1464 DROP TABLE tm1;
1465
1466 #
1467 # Bug#36006 - Optimizer does table scan for select count(*)
1468 #
1469 CREATE TABLE t1(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
1470 CREATE TABLE t2(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
1471 CREATE TABLE t3(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
1472 CREATE TABLE t4(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2))
1473 ENGINE=MRG_MYISAM UNION=(t1, t2, t3);
1474 INSERT INTO t1 VALUES (1,1), (1,2),(1,3), (1,4);
1475 INSERT INTO t2 VALUES (2,1), (2,2),(2,3), (2,4);
1476 INSERT INTO t3 VALUES (3,1), (3,2),(3,3), (3,4);
1477 EXPLAIN SELECT COUNT(*) FROM t1;
1478 EXPLAIN SELECT COUNT(*) FROM t4;
1479 DROP TABLE t1, t2, t3, t4;
1480
1481 #
1482 # BUG#39185 - Cardinality for merge tables calculated incorrectly.
1483 #
1484 CREATE TABLE t1(a INT, KEY(a));
1485 INSERT INTO t1 VALUES(0),(1),(2),(3),(4);
1486 ANALYZE TABLE t1;
1487 CREATE TABLE m1(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
1488 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
1489 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
1490 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
1491 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1';
1492 DROP TABLE t1, m1;
1493
1494 --echo #
1495 --echo # Bug #40675 MySQL 5.1 crash with index merge algorithm and Merge tables
1496 --echo #
1497
1498 --echo # create MYISAM table t1 and insert values into it
1499 CREATE TABLE t1(a INT);
1500 INSERT INTO t1 VALUES(1);
1501
1502 --echo # create MYISAM table t2 and insert values into it
1503 CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
1504 INSERT INTO t2(a,b) VALUES
1505 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1506 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1507 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1508 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1509 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1510 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1511 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1512 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1513 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1514 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1515 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1516 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1517 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1518 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1519 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1520 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1521 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1522 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
1523 (1,2);
1524
1525 --echo # Create the merge table t3
1526 CREATE TABLE t3(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b))
1527 ENGINE=MERGE UNION=(t2) INSERT_METHOD=FIRST;
1528
1529 --echo # Lock tables t1 and t3 for write
1530 LOCK TABLES t1 WRITE, t3 WRITE;
1531
1532 --echo # Insert values into the merge table t3
1533 INSERT INTO t3(a,b) VALUES(1,2);
1534
1535 --echo # select from the join of t2 and t3 (The merge table)
1536 SELECT t3.a FROM t1,t3 WHERE t3.b=2 AND t3.a=1;
1537
1538 --echo # Unlock the tables
1539 UNLOCK TABLES;
1540
1541 --echo # drop the created tables
1542 DROP TABLE t1, t2, t3;
1543
1544 #
1545 # Bug #41305 server crashes when inserting duplicate row into a merge table
1546 #
1547 --echo # insert duplicate value in child table while merge table doesn't have key
1548 create table t1 (
1549 col1 int(10),
1550 primary key (col1)
1551 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1552
1553 CREATE TABLE m1 (
1554 col1 int(10) NOT NULL
1555 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1);
1556
1557 insert into m1 (col1) values (1);
1558 --error ER_DUP_KEY
1559 insert into m1 (col1) values (1);
1560
1561 drop table m1, t1;
1562
1563 --echo #
1564 --echo # Bug#45800 crash when replacing into a merge table and there is a duplicate
1565 --echo #
1566
1567 --echo # Replace duplicate value in child table when merge table doesn't have key
1568 CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM;
1569 CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
1570 INSERT INTO m1 VALUES (666);
1571 SELECT * FROM m1;
1572 --echo # insert the duplicate value into the merge table
1573 REPLACE INTO m1 VALUES (666);
1574 SELECT * FROM m1;
1575 DROP TABLE m1, t1;
1576
1577 --echo # Insert... on duplicate key update (with duplicate values in the table)
1578 CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM;
1579 CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
1580 INSERT INTO m1 VALUES (666);
1581 SELECT * FROM m1;
1582 --echo # insert the duplicate value into the merge table
1583 INSERT INTO m1 VALUES (666) ON DUPLICATE KEY UPDATE c1=c1+1;
1584 SELECT * FROM m1;
1585 DROP TABLE m1, t1;
1586
1587 --echo # Insert duplicate value on MERGE table, where, MERGE has a key but MyISAM has more keys
1588 CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1), UNIQUE (c2));
1589 CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c1)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
1590 INSERT INTO m1 VALUES (1,2);
1591 --echo # insert the duplicate value into the merge table
1592 --error ER_DUP_KEY
1593 INSERT INTO m1 VALUES (3,2);
1594 DROP TABLE m1,t1;
1595
1596 --echo # Try to define MERGE and MyISAM with keys on different columns
1597 CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1));
1598 CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c2)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
1599 --echo # Try accessing the merge table for inserts (error occurs)
1600 --error ER_WRONG_MRG_TABLE
1601 INSERT INTO m1 VALUES (1,2);
1602 --error ER_WRONG_MRG_TABLE
1603 INSERT INTO m1 VALUES (1,4);
1604 DROP TABLE m1,t1;
1605
1606 #
1607 #Bug #44040 MySQL allows creating a MERGE table upon VIEWs but crashes
1608 #when using it
1609 #
1610
1611 CREATE TABLE t1 (
1612 col1 INT(10)
1613 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1614
1615 CREATE VIEW v1 as SELECT * FROM t1;
1616 CREATE TABLE m1 (
1617 col1 INT(10)
1618 )ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(v1);
1619
1620 --echo #Select should detect that the child table is a view and fail.
1621 --error ER_WRONG_MRG_TABLE
1622 SELECT * FROM m1;
1623
1624 DROP VIEW v1;
1625 DROP TABLE m1, t1;
1626
1627 --echo #
1628 --echo # Bug #45796: invalid memory reads and writes when altering merge and
1629 --echo # base tables
1630 --echo #
1631
1632 CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
1633 CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1);
1634 ALTER TABLE m1 ADD INDEX idx_c1(c1);
1635 # Open the MERGE table and allocate buffers based on children's definition.
1636 --error ER_WRONG_MRG_TABLE
1637 SELECT * FROM m1;
1638 # Change the child table definition.
1639 ALTER TABLE t1 ADD INDEX idx_c1(c1);
1640 # Check that old buffers are not reused
1641 SELECT * FROM m1;
1642
1643 DROP TABLE m1;
1644 DROP TABLE t1;
1645
1646 --echo #
1647 --echo # Bug45781 infinite hang/crash in "opening tables" after handler tries to
1648 --echo # open merge table
1649 --echo #
1650
1651 CREATE TABLE t1(a int)engine=myisam;
1652 CREATE TABLE t2(a int)engine=myisam;
1653 CREATE TABLE t3(a int)engine=myisam;
1654 CREATE TABLE t4(a int)engine=myisam;
1655 CREATE TABLE t5(a int)engine=myisam;
1656 CREATE TABLE t6(a int)engine=myisam;
1657 CREATE TABLE t7(a int)engine=myisam;
1658 CREATE TABLE m1(a int)engine=merge union=(t1,t2,t3,t4,t5,t6,t7);
1659 SELECT 1 FROM m1;
1660 --error ER_ILLEGAL_HA
1661 HANDLER m1 OPEN;
1662 DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7;
1663 --error ER_NO_SUCH_TABLE
1664 SELECT 1 FROM m1; # Should not hang!
1665
1666 --echo #
1667 --echo # Bug #46614: Assertion in show_create_trigger()
1668 --echo #
1669 CREATE TABLE t1(a int);
1670 CREATE TABLE t2(a int);
1671 CREATE TABLE t3(a int) ENGINE = MERGE UNION(t1, t2);
1672 CREATE TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo();
1673 --replace_column 7 #
1674 SHOW CREATE TRIGGER tr1;
1675 DROP TRIGGER tr1;
1676 DROP TABLE t1, t2, t3;
1677
1678 --echo #
1679 --echo # BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40)
1680 --echo #
1681 CREATE DATABASE `test/1`;
1682
1683 CREATE TABLE `test/1`.`t/1`(a INT);
1684 CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
1685 SELECT * FROM m1;
1686 SHOW CREATE TABLE m1;
1687 DROP TABLE m1;
1688
1689 CREATE TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
1690 SELECT * FROM `test/1`.m1;
1691 SHOW CREATE TABLE `test/1`.m1;
1692 DROP TABLE `test/1`.m1;
1693 DROP TABLE `test/1`.`t/1`;
1694
1695 CREATE TEMPORARY TABLE `test/1`.`t/1`(a INT);
1696 CREATE TEMPORARY TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
1697 SELECT * FROM m1;
1698 SHOW CREATE TABLE m1;
1699 DROP TABLE m1;
1700
1701 CREATE TEMPORARY TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
1702 SELECT * FROM `test/1`.m1;
1703 SHOW CREATE TABLE `test/1`.m1;
1704 DROP TABLE `test/1`.m1;
1705 DROP TABLE `test/1`.`t/1`;
1706
1707 DROP DATABASE `test/1`;
1708
1709 # Test compatibility. Use '@' instead of '/' (was not allowed in 5.0)
1710
1711 CREATE TABLE `t@1`(a INT);
1712 copy_file std_data/bug48265.frm $MYSQLD_DATADIR/test/m1.frm;
1713 write_file $MYSQLD_DATADIR/test/m1.MRG;
1714 t@1
1715 EOF
1716 SELECT * FROM m1;
1717 SHOW CREATE TABLE m1;
1718 DROP TABLE `t@1`;
1719
1720 CREATE DATABASE `test@1`;
1721 CREATE TABLE `test@1`.`t@1`(a INT);
1722 FLUSH TABLE m1;
1723 remove_file $MYSQLD_DATADIR/test/m1.MRG;
1724 write_file $MYSQLD_DATADIR/test/m1.MRG;
1725 ./test@1/t@1
1726 EOF
1727 SELECT * FROM m1;
1728 SHOW CREATE TABLE m1;
1729 DROP TABLE m1;
1730 DROP TABLE `test@1`.`t@1`;
1731 DROP DATABASE `test@1`;
1732
1733 --echo #
1734 --echo # Bug#51494c rash with join, explain and 'sounds like' operator
1735 --echo #
1736
1737 CREATE TABLE t1 (a INT) ENGINE=MYISAM;
1738 INSERT INTO t1 VALUES(1);
1739
1740 CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL,
1741 KEY idx0 (d, c)) ENGINE=MERGE;
1742
1743 EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN
1744 t2 WHERE b SOUNDS LIKE e AND d = 1;
1745
1746 DROP TABLE t2, t1;
1747
1748 --echo #
1749 --echo # Bug#46339 - crash on REPAIR TABLE merge table USE_FRM
1750 --echo #
1751 #
1752 # Test derived from a proposal of Shane Bester.
1753 #
1754 CREATE TABLE t1 (c1 INT) ENGINE=MYISAM;
1755 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST;
1756 #
1757 # REPAIR ... USE_FRM with LOCK TABLES.
1758 #
1759 LOCK TABLE m1 READ;
1760 REPAIR TABLE m1 USE_FRM;
1761 UNLOCK TABLES;
1762 #
1763 # REPAIR ... USE_FRM without LOCK TABLES.
1764 #
1765 # This statement crashed the server (Bug#46339).
1766 #
1767 REPAIR TABLE m1 USE_FRM;
1768 #
1769 DROP TABLE m1,t1;
1770 #
1771 # Test derived from a proposal of Matthias Leich.
1772 #
1773 # Base table is missing.
1774 #
1775 CREATE TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1);
1776 #
1777 # This statement crashed the server (Bug#46339).
1778 #
1779 REPAIR TABLE m1 USE_FRM;
1780 #
1781 # Create base table.
1782 #
1783 CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM;
1784 #
1785 # This statement crashed the server (Bug#46339).
1786 #
1787 REPAIR TABLE m1 USE_FRM;
1788 #
1789 # Normal repair as reference.
1790 #
1791 REPAIR TABLE m1;
1792 #
1793 # Cleanup.
1794 #
1795 DROP TABLE m1, t1;
1796 #
1797 # Same with temporary tables.
1798 #
1799 # Base table is missing.
1800 #
1801 CREATE TEMPORARY TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1);
1802 #
1803 # This statement crashed the server (Bug#46339).
1804 #
1805 REPAIR TABLE m1 USE_FRM;
1806 #
1807 # Create base table.
1808 #
1809 CREATE TEMPORARY TABLE t1 (f1 BIGINT) ENGINE=MyISAM;
1810 #
1811 # This statement crashed the server (Bug#46339).
1812 #
1813 REPAIR TABLE m1 USE_FRM;
1814 #
1815 # Normal repair as reference.
1816 #
1817 REPAIR TABLE m1;
1818 #
1819 # Cleanup.
1820 #
1821 DROP TABLE m1, t1;
1822
1823 #
1824 # MDEV-5266 MySQL:57657 - Temporary MERGE table with temporary underlying is broken by ALTER
1825 #
1826 create temporary table t1_temp(i int);
1827 create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last;
1828 alter table tm_temp_temp insert_method=first;
1829 check table tm_temp_temp;
1830 drop temporary table t1_temp, tm_temp_temp;
1831
1832 --echo # End of 5.1 tests
1833
1834 --echo #
1835 --echo # MDEV-4277: Crash inside mi_killed_in_mariadb() with myisammrg
1836 --echo #
1837 create table t1 (a int);
1838 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1839
1840 create table t2 ( i int ) engine=myisam;
1841 insert into t2 values (1),(2);
1842
1843 create table t3 (a int, b int, filler char(100), key(a), key(b)) engine=myisam;
1844 create table t4 like t3;
1845 insert into t3
1846 select A.a+10*B.a+100*C.a,
1847 A.a+10*B.a+100*C.a,
1848 'filler-data-FILLER-DATA-qqq'
1849 from t1 A, t1 B, t1 C where C.a < 5;
1850 insert into t4
1851 select A.a+10*B.a+100*C.a,
1852 A.a+10*B.a+100*C.a,
1853 'filler-data-FILLER-DATA-qqq'
1854 from t1 A, t1 B, t1 C where C.a >= 5;
1855
1856 create table t5 like t3;
1857 alter table t5 engine=merge;
1858 alter table t5 union(t3, t4);
1859
1860 update t5 set b=999, a=999 where b>950;
1861
1862 explain
1863 select * from t2, t5 where t5.a=999 and t5.b=999;
1864 select * from t2, t5 where t5.a=999 and t5.b=999;
1865
1866 drop table t5;
1867 drop table t1,t2,t3,t4;
1868
1869 --echo # End of 5.3 tests
1870
1871 #
1872 # BUG#35274 - merge table doesn't need any base tables, gives error 124 when
1873 # key accessed
1874 #
1875 CREATE TABLE t1(a INT, KEY(a)) ENGINE=merge;
1876 SELECT MAX(a) FROM t1;
1877 DROP TABLE t1;
1878
1879 --echo #
1880 --echo # An additional test case for Bug#27430 Crash in subquery code
1881 --echo # when in PS and table DDL changed after PREPARE
1882 --echo #
1883 --echo # Test merge table with too many merge children.
1884 --echo #
1885 set @save_table_open_cache=@@global.table_open_cache;
1886 --echo #
1887 --echo # Set @@global.table_open_cache to minimum
1888 --echo #
1889 set @@global.table_open_cache=400;
1890 select @@table_open_cache;
1891 set @a=null;
1892 let $1 = `select @@table_open_cache;`;
1893 --echo #
1894 --echo # Create 400 merge children
1895 --echo #
1896 --disable_query_log
1897 while ($1)
1898 {
1899 eval create table t$1 (a int) engine=myisam;
1900 eval set @a=ifnull(concat(@a, ", ", "t$1"), "t$1");
1901 dec $1;
1902 }
1903 --enable_query_log
1904 set @a=concat("create table t_parent (a int) union(", @a,
1905 ") insert_method=first engine=mrg_myisam");
1906 prepare stmt from @a;
1907 execute stmt;
1908 prepare stmt from "select * from t_parent";
1909 --disable_result_log
1910 --error 0,ER_NEED_REPREPARE
1911 execute stmt;
1912 --error 0,ER_NEED_REPREPARE
1913 execute stmt;
1914 --error 0,ER_NEED_REPREPARE
1915 execute stmt;
1916 deallocate prepare stmt;
1917 --enable_result_log
1918
1919 --echo #
1920 --echo # Create merge parent
1921 --echo #
1922
1923 --echo #
1924 --echo # Cleanup
1925 --echo #
1926 let $1 = `select @@table_open_cache;`;
1927 --disable_query_log
1928 while ($1)
1929 {
1930 eval drop table t$1;
1931 dec $1;
1932 }
1933 --enable_query_log
1934 drop table t_parent;
1935 set @@global.table_open_cache=@save_table_open_cache;
1936
1937 #
1938 # WL#4144 - Lock MERGE engine children
1939 #
1940 # Test DATA/INDEX DIRECTORY
1941 #
1942 CREATE DATABASE mysql_test1;
1943 --disable_query_log
1944 # data/index directory don't work in HAVE_purify builds. Disable
1945 # build-dependent warnings.
1946 --disable_warnings
1947 --echo CREATE TABLE t1 ... DATA DIRECTORY=... INDEX DIRECTORY=...
1948 eval CREATE TABLE t1 (c1 INT)
1949 DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp'
1950 INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp';
1951 --echo CREATE TABLE mysql_test1.t2 ... DATA DIRECTORY=... INDEX DIRECTORY=...
1952 eval CREATE TABLE mysql_test1.t2 (c1 INT)
1953 DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp'
1954 INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp';
1955 --enable_query_log
1956 --enable_warnings
1957 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,mysql_test1.t2)
1958 INSERT_METHOD=LAST;
1959 INSERT INTO t1 VALUES (1);
1960 INSERT INTO mysql_test1.t2 VALUES (2);
1961 SELECT * FROM m1;
1962 #--copy_file $MYSQLTEST_DATADIR/test/m1.MRG /tmp/mysql-test-m1.MRG
1963 DROP TABLE t1, mysql_test1.t2, m1;
1964 DROP DATABASE mysql_test1;
1965 #
1966 # Review detected Crash #1. Detaching main tables while in sub statement.
1967 #
1968 CREATE TABLE t1 (c1 INT);
1969 CREATE TABLE t2 (c1 INT);
1970 INSERT INTO t1 (c1) VALUES (1);
1971 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) INSERT_METHOD=FIRST;
1972 CREATE TABLE t3 (c1 INT);
1973 INSERT INTO t3 (c1) VALUES (1);
1974 CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM t3);
1975 CREATE VIEW v1 AS SELECT foo.c1 c1, f1() c2, bar.c1 c3, f1() c4
1976 FROM tm1 foo, tm1 bar, t3;
1977 SELECT * FROM v1;
1978 DROP FUNCTION f1;
1979 DROP VIEW v1;
1980 DROP TABLE tm1, t1, t2, t3;
1981 #
1982 # Review detected Crash #2. Trying to attach temporary table twice.
1983 #
1984 CREATE TEMPORARY TABLE t1 (c1 INT);
1985 CREATE TEMPORARY TABLE t2 (c1 INT);
1986 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
1987 INSERT_METHOD=FIRST;
1988 CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM tm1);
1989 INSERT INTO tm1 (c1) VALUES (1);
1990 SELECT f1() FROM (SELECT 1) AS c1;
1991 DROP FUNCTION f1;
1992 DROP TABLE tm1, t1, t2;
1993 #
1994 # Review suggested test. DDL in a stored function.
1995 #
1996 DELIMITER |;
1997 CREATE FUNCTION f1() RETURNS INT
1998 BEGIN
1999 CREATE TEMPORARY TABLE t1 (c1 INT);
2000 CREATE TEMPORARY TABLE t2 (c1 INT);
2001 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2);
2002 INSERT INTO t1 (c1) VALUES (1);
2003 RETURN (SELECT MAX(c1) FROM tm1);
2004 END|
2005 DELIMITER ;|
2006 --disable_ps2_protocol
2007 SELECT f1() FROM (SELECT 1 UNION SELECT 1) c1;
2008 --enable_ps2_protocol
2009 DROP FUNCTION f1;
2010 DROP TABLE tm1, t1, t2;
2011 #
2012 # Base table. No LOCK TABLES, no functions/triggers.
2013 #
2014 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2015 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2016 INSERT INTO tm1 VALUES (1);
2017 SELECT * FROM tm1;
2018 DROP TABLE tm1, t1;
2019 #
2020 # Base table. No LOCK TABLES, sub-statement that is run inside a function.
2021 #
2022 DELIMITER |;
2023 CREATE FUNCTION f1() RETURNS INT
2024 BEGIN
2025 INSERT INTO tm1 VALUES (1);
2026 RETURN (SELECT MAX(c1) FROM tm1);
2027 END|
2028 DELIMITER ;|
2029 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2030 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2031 SELECT f1();
2032 DROP FUNCTION f1;
2033 DROP TABLE tm1, t1;
2034 #
2035 # Base table. LOCK TABLES, no functions/triggers.
2036 #
2037 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2038 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2039 LOCK TABLE tm1 WRITE;
2040 INSERT INTO tm1 VALUES (1);
2041 SELECT * FROM tm1;
2042 UNLOCK TABLES;
2043 DROP TABLE tm1, t1;
2044 #
2045 # Base table. LOCK TABLES, sub-statement that is run inside a function.
2046 #
2047 DELIMITER |;
2048 CREATE FUNCTION f1() RETURNS INT
2049 BEGIN
2050 INSERT INTO tm1 VALUES (1);
2051 RETURN (SELECT MAX(c1) FROM tm1);
2052 END|
2053 DELIMITER ;|
2054 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2055 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2056 LOCK TABLE tm1 WRITE;
2057 SELECT f1();
2058 UNLOCK TABLES;
2059 DROP FUNCTION f1;
2060 DROP TABLE tm1, t1;
2061 #
2062 # Base table. LOCK TABLES statement that locks a table that has a trigger
2063 # that inserts into a merge table, so an attempt is made to lock tables
2064 # of a sub-statement.
2065 #
2066 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2067 CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
2068 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
2069 CREATE TRIGGER t2_ai AFTER INSERT ON t2
2070 FOR EACH ROW INSERT INTO tm1 VALUES(11);
2071 LOCK TABLE t2 WRITE;
2072 INSERT INTO t2 VALUES (2);
2073 SELECT * FROM tm1;
2074 SELECT * FROM t2;
2075 UNLOCK TABLES;
2076 DROP TRIGGER t2_ai;
2077 DROP TABLE tm1, t1, t2;
2078 #
2079 # Temporary. No LOCK TABLES, no functions/triggers.
2080 #
2081 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2082 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2083 INSERT_METHOD=LAST;
2084 INSERT INTO tm1 VALUES (1);
2085 SELECT * FROM tm1;
2086 DROP TABLE tm1, t1;
2087 #
2088 # Temporary. No LOCK TABLES, sub-statement that is run inside a function.
2089 #
2090 DELIMITER |;
2091 CREATE FUNCTION f1() RETURNS INT
2092 BEGIN
2093 INSERT INTO tm1 VALUES (1);
2094 RETURN (SELECT MAX(c1) FROM tm1);
2095 END|
2096 DELIMITER ;|
2097 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2098 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2099 INSERT_METHOD=LAST;
2100 SELECT f1();
2101 DROP FUNCTION f1;
2102 DROP TABLE tm1, t1;
2103 #
2104 # Temporary. LOCK TABLES, no functions/triggers.
2105 #
2106 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2107 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2108 INSERT_METHOD=LAST;
2109 CREATE TABLE t9 (c1 INT) ENGINE=MyISAM;
2110 LOCK TABLE t9 WRITE;
2111 INSERT INTO tm1 VALUES (1);
2112 SELECT * FROM tm1;
2113 UNLOCK TABLES;
2114 DROP TABLE tm1, t1, t9;
2115 #
2116 # Temporary. LOCK TABLES, sub-statement that is run inside a function.
2117 #
2118 DELIMITER |;
2119 CREATE FUNCTION f1() RETURNS INT
2120 BEGIN
2121 INSERT INTO tm1 VALUES (1);
2122 RETURN (SELECT MAX(c1) FROM tm1);
2123 END|
2124 DELIMITER ;|
2125 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2126 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2127 INSERT_METHOD=LAST;
2128 CREATE TABLE t9 (c1 INT) ENGINE=MyISAM;
2129 LOCK TABLE t9 WRITE;
2130 SELECT f1();
2131 UNLOCK TABLES;
2132 DROP FUNCTION f1;
2133 DROP TABLE tm1, t1, t9;
2134 #
2135 # Temporary. LOCK TABLES statement that locks a table that has a trigger
2136 # that inserts into a merge table, so an attempt is made to lock tables
2137 # of a sub-statement.
2138 #
2139 CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
2140 CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2141 INSERT_METHOD=LAST;
2142 CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
2143 CREATE TRIGGER t2_ai AFTER INSERT ON t2
2144 FOR EACH ROW INSERT INTO tm1 VALUES(11);
2145 LOCK TABLE t2 WRITE;
2146 INSERT INTO t2 VALUES (2);
2147 SELECT * FROM tm1;
2148 SELECT * FROM t2;
2149 UNLOCK TABLES;
2150 DROP TRIGGER t2_ai;
2151 DROP TABLE tm1, t1, t2;
2152 --echo #
2153 --echo # Don't allow an update of a MERGE child in a trigger
2154 --echo # if the table's already being modified by the main
2155 --echo # statement.
2156 --echo #
2157 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2158 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2159 INSERT_METHOD=LAST;
2160 CREATE TRIGGER tm1_ai AFTER INSERT ON tm1
2161 FOR EACH ROW INSERT INTO t1 VALUES(11);
2162 LOCK TABLE tm1 WRITE, t1 WRITE;
2163 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
2164 INSERT INTO tm1 VALUES (1);
2165 SELECT * FROM tm1;
2166 UNLOCK TABLES;
2167 LOCK TABLE t1 WRITE, tm1 WRITE;
2168 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
2169 INSERT INTO tm1 VALUES (1);
2170 SELECT * FROM tm1;
2171 UNLOCK TABLES;
2172 DROP TRIGGER tm1_ai;
2173 DROP TABLE tm1, t1;
2174
2175 --echo #
2176 --echo # Don't select MERGE child when trying to get a prelocked table.
2177 --echo #
2178 --echo # Due to a limitation demonstrated by the previous test
2179 --echo # we can no longer use a write-locked prelocked table.
2180 --echo # The test is kept for historical purposes.
2181 --echo #
2182 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2183 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
2184 INSERT_METHOD=LAST;
2185 --enable_prepare_warnings
2186 CREATE TRIGGER tm1_ai AFTER INSERT ON tm1
2187 FOR EACH ROW SELECT max(c1) FROM t1 INTO @var;
2188 --disable_prepare_warnings
2189 LOCK TABLE tm1 WRITE, t1 WRITE;
2190 INSERT INTO tm1 VALUES (1);
2191 SELECT * FROM tm1;
2192 UNLOCK TABLES;
2193 LOCK TABLE t1 WRITE, tm1 WRITE;
2194 INSERT INTO tm1 VALUES (1);
2195 SELECT * FROM tm1;
2196 UNLOCK TABLES;
2197 DROP TRIGGER tm1_ai;
2198 DROP TABLE tm1, t1;
2199
2200 # Don't resurrect chopped off prelocked tables.
2201 # The problem is not visible by test results; only by debugging.
2202 #
2203 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
2204 CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
2205 CREATE TABLE t3 (c1 INT) ENGINE=MyISAM;
2206 CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
2207 CREATE TABLE t5 (c1 INT) ENGINE=MyISAM;
2208 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3,t4,t5)
2209 INSERT_METHOD=LAST;
2210 --enable_prepare_warnings
2211 CREATE TRIGGER t2_au AFTER UPDATE ON t2
2212 FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var;
2213 --disable_prepare_warnings
2214 CREATE FUNCTION f1() RETURNS INT
2215 RETURN (SELECT MAX(c1) FROM t4);
2216 LOCK TABLE tm1 WRITE, t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE, t5 WRITE;
2217 INSERT INTO t1 VALUES(1);
2218 INSERT INTO t2 VALUES(2);
2219 INSERT INTO t3 VALUES(3);
2220 INSERT INTO t4 VALUES(4);
2221 INSERT INTO t5 VALUES(5);
2222 connect (con1,localhost,root,,);
2223 send UPDATE t2, tm1 SET t2.c1=f1();
2224 connection default;
2225 # Force reopen in other thread.
2226 #sleep 1;
2227 FLUSH TABLES;
2228 #sleep 1;
2229 FLUSH TABLES;
2230 #sleep 1;
2231 UNLOCK TABLES;
2232 connection con1;
2233 reap;
2234 disconnect con1;
2235 connection default;
2236 SELECT * FROM tm1;
2237 DROP TRIGGER t2_au;
2238 DROP FUNCTION f1;
2239 DROP TABLE tm1, t1, t2, t3, t4, t5;
2240
2241 --echo #
2242 --echo # Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
2243 --echo #
2244 CREATE TEMPORARY TABLE t1 (c1 INT);
2245 ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists,t1);
2246 OPTIMIZE TABLE t1;
2247 DROP TABLE t1;
2248
2249 --echo #
2250 --echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
2251 --echo # More tests with TEMPORARY MERGE table and permanent children.
2252 --echo # First without locked tables.
2253 --echo #
2254 #
2255 --echo #
2256 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
2257 CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
2258 CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
2259 INSERT_METHOD=LAST;
2260 SHOW CREATE TABLE t1;
2261 SHOW CREATE TABLE m1;
2262 SELECT * FROM m1;
2263 INSERT INTO t1 VALUES (111, 121);
2264 INSERT INTO m1 VALUES (211, 221);
2265 SELECT * FROM m1;
2266 SELECT * FROM t1;
2267 SELECT * FROM t2;
2268 #
2269 --echo #
2270 ALTER TABLE m1 RENAME m2;
2271 SHOW CREATE TABLE m2;
2272 SELECT * FROM m2;
2273 #
2274 --echo #
2275 CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
2276 INSERT_METHOD=LAST;
2277 --error ER_TABLE_EXISTS_ERROR
2278 ALTER TABLE m2 RENAME m1;
2279 DROP TABLE m1;
2280 ALTER TABLE m2 RENAME m1;
2281 SHOW CREATE TABLE m1;
2282 SELECT * FROM m1;
2283 #
2284 --echo #
2285 ALTER TABLE m1 ADD COLUMN c3 INT;
2286 --error ER_WRONG_MRG_TABLE
2287 INSERT INTO m1 VALUES (212, 222, 232);
2288 --error ER_WRONG_MRG_TABLE
2289 SELECT * FROM m1;
2290 ALTER TABLE t1 ADD COLUMN c3 INT;
2291 ALTER TABLE t2 ADD COLUMN c3 INT;
2292 INSERT INTO m1 VALUES (212, 222, 232);
2293 SELECT * FROM m1;
2294 #
2295 --echo #
2296 ALTER TABLE m1 DROP COLUMN c3;
2297 --error ER_WRONG_MRG_TABLE
2298 INSERT INTO m1 VALUES (213, 223);
2299 --error ER_WRONG_MRG_TABLE
2300 SELECT * FROM m1;
2301 ALTER TABLE t1 DROP COLUMN c3;
2302 ALTER TABLE t2 DROP COLUMN c3;
2303 INSERT INTO m1 VALUES (213, 223);
2304 SELECT * FROM m1;
2305 #
2306 --echo #
2307 CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
2308 ALTER TABLE m1 UNION=(t1,t2,t3);
2309 INSERT INTO m1 VALUES (311, 321);
2310 SELECT * FROM m1;
2311 SELECT * FROM t1;
2312 SELECT * FROM t2;
2313 SELECT * FROM t3;
2314 #
2315 --echo #
2316 CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
2317 ALTER TABLE m1 UNION=(t1,t2,t3,t4);
2318 INSERT INTO m1 VALUES (411, 421);
2319 SELECT * FROM m1;
2320 SELECT * FROM t1;
2321 SELECT * FROM t2;
2322 SELECT * FROM t3;
2323 SELECT * FROM t4;
2324 #
2325 --echo #
2326 ALTER TABLE m1 ENGINE=MyISAM;
2327 SHOW CREATE TABLE m1;
2328 INSERT INTO m1 VALUES (511, 521);
2329 SELECT * FROM m1;
2330 #
2331 --echo #
2332 ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
2333 INSERT_METHOD=LAST;
2334 SELECT * FROM m1;
2335 SELECT * FROM t1;
2336 SELECT * FROM t2;
2337 #
2338 --echo #
2339 CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
2340 INSERT INTO t1 VALUES (611, 621);
2341 SELECT * FROM m1;
2342 DROP TABLE t1;
2343 SELECT * FROM m1;
2344 #
2345 #
2346 --echo #
2347 --echo #
2348 SHOW CREATE TABLE m1;
2349 #
2350 --echo #
2351 CREATE TABLE m2 SELECT * FROM m1;
2352 SHOW CREATE TABLE m2;
2353 SELECT * FROM m2;
2354 DROP TABLE m2;
2355 #
2356 --echo #
2357 CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
2358 SHOW CREATE TABLE m2;
2359 SELECT * FROM m2;
2360 DROP TABLE m2;
2361 #
2362 --echo #
2363 CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
2364 INSERT_METHOD=LAST;
2365 --error ER_WRONG_MRG_TABLE
2366 SELECT * FROM m2;
2367 DROP TABLE m2;
2368 #
2369 --echo #
2370 --error ER_WRONG_OBJECT
2371 CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
2372 INSERT_METHOD=LAST SELECT * FROM m1;
2373 #
2374 --echo #
2375 --error ER_WRONG_OBJECT
2376 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
2377 INSERT_METHOD=LAST SELECT * FROM m1;
2378 #
2379 --echo #
2380 CREATE TABLE m2 LIKE m1;
2381 SHOW CREATE TABLE m2;
2382 SELECT * FROM m2;
2383 INSERT INTO m2 SELECT * FROM m1;
2384 SELECT * FROM m2;
2385 DROP TABLE m2;
2386 #
2387 --echo #
2388 CREATE TEMPORARY TABLE m2 LIKE m1;
2389 SHOW CREATE TABLE m2;
2390 SELECT * FROM m2;
2391 INSERT INTO m2 SELECT * FROM m1;
2392 SELECT * FROM m2;
2393 DROP TABLE m2;
2394 #
2395 --echo #
2396 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
2397 INSERT_METHOD=LAST;
2398 INSERT INTO m2 SELECT * FROM m1;
2399 SELECT * FROM m2;
2400 #
2401 #
2402 --echo #
2403 --echo #
2404 LOCK TABLE m1 WRITE, m2 WRITE;
2405 SELECT * FROM m1,m2 WHERE m1.c1=m2.c1;
2406 UNLOCK TABLES;
2407 #
2408 DROP TABLE t1, t2, t3, t4, m1, m2;
2409 #
2410 #
2411 #
2412 --echo #
2413 --echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
2414 --echo # More tests with TEMPORARY MERGE table and permanent children.
2415 --echo # (continued) Now the same with locked table.
2416 --echo #
2417 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
2418 CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
2419 CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
2420 INSERT_METHOD=LAST;
2421 SHOW CREATE TABLE t1;
2422 SHOW CREATE TABLE m1;
2423 SELECT * FROM m1;
2424 INSERT INTO t1 VALUES (111, 121);
2425 INSERT INTO m1 VALUES (211, 221);
2426 SELECT * FROM m1;
2427 SELECT * FROM t1;
2428 SELECT * FROM t2;
2429 #
2430 --echo #
2431 LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE;
2432 #
2433 --echo #
2434 ALTER TABLE m1 RENAME m2;
2435 SHOW CREATE TABLE m2;
2436 SELECT * FROM m2;
2437 #
2438 --echo #
2439 CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
2440 INSERT_METHOD=LAST;
2441 --error ER_TABLE_EXISTS_ERROR
2442 ALTER TABLE m2 RENAME m1;
2443 DROP TABLE m1;
2444 ALTER TABLE m2 RENAME m1;
2445 SHOW CREATE TABLE m1;
2446 SELECT * FROM m1;
2447 #
2448 --echo #
2449 ALTER TABLE m1 ADD COLUMN c3 INT;
2450 --error ER_WRONG_MRG_TABLE
2451 INSERT INTO m1 VALUES (212, 222, 232);
2452 --error ER_WRONG_MRG_TABLE
2453 SELECT * FROM m1;
2454 ALTER TABLE t1 ADD COLUMN c3 INT;
2455 ALTER TABLE t2 ADD COLUMN c3 INT;
2456 INSERT INTO m1 VALUES (212, 222, 232);
2457 SELECT * FROM m1;
2458 #
2459 --echo #
2460 ALTER TABLE m1 DROP COLUMN c3;
2461 --error ER_WRONG_MRG_TABLE
2462 INSERT INTO m1 VALUES (213, 223);
2463 --error ER_WRONG_MRG_TABLE
2464 SELECT * FROM m1;
2465 ALTER TABLE t1 DROP COLUMN c3;
2466 ALTER TABLE t2 DROP COLUMN c3;
2467 INSERT INTO m1 VALUES (213, 223);
2468 SELECT * FROM m1;
2469 #
2470 --echo #
2471 UNLOCK TABLES;
2472 CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
2473 ALTER TABLE m1 UNION=(t1,t2,t3);
2474 LOCK TABLE m1 WRITE;
2475 INSERT INTO m1 VALUES (311, 321);
2476 SELECT * FROM m1;
2477 SELECT * FROM t1;
2478 SELECT * FROM t2;
2479 SELECT * FROM t3;
2480 #
2481 --echo #
2482 CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
2483 ALTER TABLE m1 UNION=(t1,t2,t3,t4);
2484 INSERT INTO m1 VALUES (411, 421);
2485 SELECT * FROM m1;
2486 SELECT * FROM t1;
2487 SELECT * FROM t2;
2488 SELECT * FROM t3;
2489 SELECT * FROM t4;
2490 #
2491 --echo #
2492 ALTER TABLE m1 ENGINE=MyISAM;
2493 SHOW CREATE TABLE m1;
2494 INSERT INTO m1 VALUES (511, 521);
2495 SELECT * FROM m1;
2496 #
2497 --echo #
2498 ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
2499 INSERT_METHOD=LAST;
2500 SELECT * FROM m1;
2501 SELECT * FROM t1;
2502 SELECT * FROM t2;
2503 #
2504 --echo #
2505 CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
2506 INSERT INTO t1 VALUES (611, 621);
2507 SELECT * FROM m1;
2508 DROP TABLE t1;
2509 SELECT * FROM m1;
2510 #
2511 #
2512 --echo #
2513 --echo #
2514 SHOW CREATE TABLE m1;
2515 --error ER_TABLE_NOT_LOCKED
2516 CREATE TABLE m2 SELECT * FROM m1;
2517 #
2518 --echo #
2519 CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
2520 SHOW CREATE TABLE m2;
2521 SELECT * FROM m2;
2522 DROP TABLE m2;
2523 #
2524 --echo #
2525 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
2526 INSERT_METHOD=LAST;
2527 SELECT * FROM m2;
2528 LOCK TABLE m1 WRITE, m2 WRITE;
2529 UNLOCK TABLES;
2530 DROP TABLE m2;
2531 LOCK TABLE m1 WRITE;
2532 #
2533 --echo #
2534 --echo # ER_TABLE_NOT_LOCKED is returned in ps-protocol
2535 --error ER_WRONG_OBJECT, ER_TABLE_NOT_LOCKED
2536 CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
2537 INSERT_METHOD=LAST SELECT * FROM m1;
2538 #
2539 --echo #
2540 --error ER_WRONG_OBJECT
2541 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
2542 INSERT_METHOD=LAST SELECT * FROM m1;
2543 #
2544 --echo #
2545 CREATE TEMPORARY TABLE m2 LIKE m1;
2546 SHOW CREATE TABLE m2;
2547 LOCK TABLE m1 WRITE, m2 WRITE;
2548 SHOW CREATE TABLE m2;
2549 SELECT * FROM m2;
2550 INSERT INTO m2 SELECT * FROM m1;
2551 SELECT * FROM m2;
2552 DROP TABLE m2;
2553 #
2554 --echo #
2555 CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
2556 INSERT_METHOD=LAST;
2557 LOCK TABLE m1 WRITE, m2 WRITE;
2558 INSERT INTO m2 SELECT * FROM m1;
2559 SELECT * FROM m2;
2560 #
2561 --echo #
2562 UNLOCK TABLES;
2563 DROP TABLE t1, t2, t3, t4, m1, m2;
2564
2565 --echo #
2566 --echo # Bug47098 assert in MDL_context::destroy on HANDLER
2567 --echo # <damaged merge table> OPEN
2568 --echo #
2569 --echo # Test that merge tables are closed correctly when opened using
2570 --echo # HANDLER ... OPEN.
2571 --echo # The general case.
2572 connect (con1,localhost,root,,);
2573 CREATE TABLE t1 (c1 int);
2574 CREATE TABLE t2 (c1 int);
2575 CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2);
2576 START TRANSACTION;
2577 --error ER_ILLEGAL_HA
2578 HANDLER t3 OPEN;
2579 DROP TABLE t1, t2, t3;
2580 connection default;
2581 --echo # all mdl_tickets must have been released.
2582 disconnect con1;
2583 --echo # The bug-specific case.
2584 connect (con1,localhost,root,,);
2585 CREATE TABLE t1 (c1 int);
2586 CREATE TABLE t2 (c1 int);
2587 CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2);
2588 DROP TABLE t2;
2589 START TRANSACTION;
2590 --error ER_WRONG_MRG_TABLE
2591 HANDLER t3 OPEN;
2592 DROP TABLE t1, t3;
2593 connection default;
2594 --echo # all mdl_tickets must have been released.
2595 disconnect con1;
2596
2597 --echo #
2598 --echo # A test case for Bug#47648 main.merge fails sporadically
2599 --echo #
2600 --echo # Make sure we correctly maintain lex->query_tables_last_own.
2601 --echo #
2602 create table t1 (c1 int not null);
2603 create table t2 (c1 int not null);
2604 create table t3 (c1 int not null);
2605
2606 create function f1 () returns int return (select max(c1) from t3);
2607
2608 create table t4 (c1 int not null) engine=merge union=(t1,t2) insert_method=last ;
2609
2610 select * from t4 where c1 < f1();
2611 prepare stmt from "select * from t4 where c1 < f1()";
2612 execute stmt;
2613 execute stmt;
2614 execute stmt;
2615 drop function f1;
2616 --error ER_SP_DOES_NOT_EXIST
2617 execute stmt;
2618 --error ER_SP_DOES_NOT_EXIST
2619 execute stmt;
2620 drop table t4, t3, t2, t1;
2621
2622
2623 --echo #
2624 --echo # Bug#51240 ALTER TABLE of a locked MERGE table fails
2625 --echo #
2626
2627 CREATE TABLE t1 (c1 INT);
2628 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
2629 LOCK TABLE m1 WRITE;
2630 # This used to cause an error.
2631 ALTER TABLE m1 ADD INDEX (c1);
2632
2633 UNLOCK TABLES;
2634 DROP TABLE m1, t1;
2635
2636 --echo #
2637 --echo # Locking the merge table won't implicitly lock children.
2638 --echo #
2639 CREATE TABLE t1 (c1 INT);
2640 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
2641 LOCK TABLE m1 WRITE;
2642 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
2643 ALTER TABLE t1 ADD INDEX (c1);
2644 LOCK TABLE m1 WRITE, t1 WRITE;
2645 ALTER TABLE t1 ADD INDEX (c1);
2646 UNLOCK TABLES;
2647 DROP TABLE m1, t1;
2648
2649 --echo #
2650 --echo # Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter"
2651 --echo #
2652 create table t1 (i int) engine=myisam;
2653 create table m1 (i int) engine=mrg_myisam union=(t1) insert_method=first;
2654 create table m2 like m1;
2655 --echo # Table definitions should match
2656 show create table m1;
2657 show create table m2;
2658 drop tables m1, m2, t1;
2659
2660 --echo #
2661 --echo # Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()"
2662 --echo # Check that unique_table() works correctly for merge tables.
2663 --echo #
2664 create table t1 (a int);
2665 create table t2 (a int);
2666 create table t3 (b int);
2667 create view v1 as select * from t3,t1;
2668 create table m1 (a int) engine=merge union (t1, t2) insert_method=last;
2669 create table m2 (a int) engine=merge union (t1, t2) insert_method=first;
2670 create temporary table tmp (b int);
2671 insert into tmp (b) values (1);
2672
2673 insert into t1 (a) values (1);
2674 insert into t3 (b) values (1);
2675 insert into m1 (a) values ((select max(a) from m1));
2676 insert into m1 (a) values ((select max(a) from m2));
2677 insert into m1 (a) values ((select max(a) from t1));
2678 insert into m1 (a) values ((select max(a) from t2));
2679
2680 insert into m1 (a) values ((select max(a) from t3, m1));
2681 insert into m1 (a) values ((select max(a) from t3, m2));
2682 insert into m1 (a) values ((select max(a) from t3, t1));
2683 insert into m1 (a) values ((select max(a) from t3, t2));
2684
2685 insert into m1 (a) values ((select max(a) from tmp, m1));
2686 insert into m1 (a) values ((select max(a) from tmp, m2));
2687 insert into m1 (a) values ((select max(a) from tmp, t1));
2688 insert into m1 (a) values ((select max(a) from tmp, t2));
2689
2690 insert into m1 (a) values ((select max(a) from v1));
2691 insert into m1 (a) values ((select max(a) from tmp, v1));
2692 select count(*) from m1;
2693
2694
2695 drop view v1;
2696 drop temporary table tmp;
2697 drop table t1, t2, t3, m1, m2;
2698
2699
2700 --echo #
2701 --echo # Bug#56494 Segfault in upgrade_shared_lock_to_exclusive() for
2702 --echo # REPAIR of merge table
2703 --echo #
2704
2705 CREATE TABLE t1(a INT);
2706 ALTER TABLE t1 engine= MERGE UNION (t_not_exists);
2707 # This caused the segfault
2708 ANALYZE TABLE t1;
2709 CHECK TABLE t1;
2710 CHECKSUM TABLE t1;
2711 OPTIMIZE TABLE t1;
2712 REPAIR TABLE t1;
2713
2714 # This caused an assert
2715 REPAIR TABLE t1 USE_FRM;
2716
2717 DROP TABLE t1;
2718 CREATE TABLE t1(a INT);
2719 CREATE TABLE t2(a INT) engine= MERGE UNION (t1);
2720 # This caused an assert
2721 REPAIR TABLE t2 USE_FRM;
2722
2723 DROP TABLE t1, t2;
2724
2725
2726 --echo #
2727 --echo # Bug#57002 Assert in upgrade_shared_lock_to_exclusive()
2728 --echo # for ALTER TABLE + MERGE tables
2729 --echo #
2730
2731 CREATE TABLE t1(a INT) engine=myisam;
2732 CREATE TABLE m1(a INT) engine=merge UNION(t1);
2733 LOCK TABLES t1 READ, m1 WRITE;
2734
2735 # This caused an assert
2736 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
2737 ALTER TABLE t1 engine=myisam;
2738
2739 UNLOCK TABLES;
2740 DROP TABLE m1, t1;
2741
2742
2743 --echo #
2744 --echo # Test for bug #11754210 - "45777: CHECK TABLE DOESN'T SHOW ALL
2745 --echo # PROBLEMS FOR MERGE TABLE COMPLIANCE IN 5.1"
2746 --echo #
2747 create table t1(id int) engine=myisam;
2748 create view t3 as select 1 as id;
2749 create table t4(id int) engine=memory;
2750 create table m1(id int) engine=merge union=(t1,t2,t3,t4);
2751 --error ER_WRONG_MRG_TABLE
2752 select * from m1;
2753 --echo # The below CHECK and REPAIR TABLE statements should
2754 --echo # report all problems with underlying tables:
2755 --echo # - absence of 't2',
2756 --echo # - missing base table for 't3',
2757 --echo # - wrong engine of 't4'.
2758 check table m1;
2759 repair table m1;
2760 --echo # Clean-up.
2761 drop tables m1, t1, t4;
2762 drop view t3;
2763
2764
2765 --echo #
2766 --echo # MDEV-10424 Assertion `ticket == __null' failed in MDL_request::set_type
2767 --echo #
2768 CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
2769 CREATE TABLE tmerge (f1 INT) ENGINE=MERGE UNION=(t1);
2770 PREPARE stmt FROM "ANALYZE TABLE tmerge, t1";
2771 EXECUTE stmt;
2772 EXECUTE stmt;
2773 DEALLOCATE PREPARE stmt;
2774 DROP TABLE t1, tmerge;
2775
2776 --echo # End of 5.5 tests
2777
2778 --echo #
2779 --echo # Additional coverage for refactoring which is made as part
2780 --echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege
2781 --echo # to allow temp table operations".
2782 --echo #
2783 --echo # Check that prelocking works correctly for various variants of
2784 --echo # merge tables.
2785 --echo #
2786 create table t1 (j int);
2787 insert into t1 values (1);
2788 create function f1() returns int return (select count(*) from m1);
2789 create temporary table t2 (a int) engine=myisam;
2790 insert into t2 values (1);
2791 create temporary table m1 (a int) engine=merge union=(t2);
2792 select f1() from t1;
2793 drop tables t2, m1;
2794 create table t2 (a int) engine=myisam;
2795 insert into t2 values (1);
2796 create table m1 (a int) engine=merge union=(t2);
2797 select f1() from t1;
2798 drop table m1;
2799 create temporary table m1 (a int) engine=merge union=(t2);
2800 select f1() from t1;
2801 drop tables t1, t2, m1;
2802 drop function f1;
2803 --echo #
2804 --echo # Check that REPAIR/CHECK and CHECKSUM statements work correctly
2805 --echo # for various variants of merge tables.
2806 create table t1 (a int) engine=myisam;
2807 insert into t1 values (1);
2808 create table m1 (a int) engine=merge union=(t1);
2809 check table m1;
2810 repair table m1;
2811 checksum table m1;
2812 drop tables t1, m1;
2813 create temporary table t1 (a int) engine=myisam;
2814 insert into t1 values (1);
2815 create temporary table m1 (a int) engine=merge union=(t1);
2816 check table m1;
2817 repair table m1;
2818 checksum table m1;
2819 drop tables t1, m1;
2820 create table t1 (a int) engine=myisam;
2821 insert into t1 values (1);
2822 create temporary table m1 (a int) engine=merge union=(t1);
2823 check table m1;
2824 repair table m1;
2825 checksum table m1;
2826 drop tables t1, m1;
2827
2828 # Check effect of Bug#27480-preliminary patch:
2829 # a merge-table with non-existing children, opened from a prelocked list.
2830
2831 CREATE TABLE t1(a INT);
2832 CREATE TABLE m1(a INT) ENGINE = MERGE UNION (q1, q2);
2833
2834 CREATE TRIGGER trg1 BEFORE DELETE ON t1
2835 FOR EACH ROW
2836 INSERT INTO m1 VALUES (1);
2837
2838 --error ER_WRONG_MRG_TABLE
2839 DELETE FROM t1;
2840
2841 DROP TRIGGER trg1;
2842 DROP TABLE t1;
2843 DROP TABLE m1;
2844
2845 set global default_storage_engine=@save_default_storage_engine;
2846
2847 # Check that all connections opened by test cases in this file are really
2848 # gone so execution of other tests won't be affected by their presence.
2849 --source include/wait_until_count_sessions.inc
2850
2851 --echo #
2852 --echo # MDEV-31083 ASAN use-after-poison in myrg_attach_children
2853 --echo #
2854
2855 CREATE TABLE t1 (f TEXT, FULLTEXT (f)) ENGINE=MyISAM;
2856 INSERT INTO t1 VALUES ('foo'),('bar');
2857 CREATE TABLE mrg (f TEXT) ENGINE=MERGE, UNION(t1);
2858 SELECT * FROM mrg;
2859 DROP TABLE mrg, t1;
2860
2861 --echo # End of 10.0 tests
2862
2863 --echo #
2864 --echo # MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results
2865 --echo #
2866 create table t (a int, key(a desc)) engine=myisam;
2867 create table tm (a int, key(a)) engine=merge union(t);
2868 --error ER_WRONG_MRG_TABLE
2869 select * from tm;
2870 drop table tm, t;
2871
2872 --echo #
2873 --echo # MDEV-27586 Auto-increment does not work with DESC on MERGE table
2874 --echo #
2875 create table t (a int not null, primary key(a desc)) engine=myisam;
2876 create table tm (a int not null auto_increment, primary key(a desc)) engine=merge union=(t) insert_method=first;
2877 insert into tm () values ();
2878 insert into tm () values ();
2879 insert into tm () values ();
2880 select * from tm;
2881 drop table tm, t;
2882
2883 --echo # End of 10.8 tests
2884
2885 --echo #
2886 --echo # MDEV-35816 ASAN use-after-poison in st_select_lex::print
2887 --echo #
2888 CREATE TABLE t1 (a INT);
2889 INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5);
2890 SET SESSION optimizer_trace = 'enabled=on';
2891 PREPARE stmt FROM 'SELECT STRAIGHT_JOIN * FROM t1 WHERE a IN (WITH cte AS (SELECT a FROM t1) SELECT * FROM cte)';
2892 EXECUTE stmt;
2893 EXECUTE stmt;
2894 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)';
2895 EXECUTE nested;
2896 EXECUTE nested;
2897 DROP TABLE t1;
2898
2899 --echo #
2900 --echo # MDEV-39494 UBSAN failure
2901 --echo #
2902 CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
2903 CREATE TABLE t2 (a INT, KEY(a)) ENGINE=MyISAM;
2904 CREATE TABLE tm (a INT, KEY(a)) ENGINE=MRG_MyISAM UNION=(t1,t2);
2905 # Must have zero records to produce UBSAN failure on divide-by-zero.
2906 SELECT DISTINCT a FROM tm WHERE a > 50;
2907 DROP TABLE tm, t1, t2;
2908
2909 --echo #
2910 --echo # MDEV-38474 Double free or corruption, ASAN heap-use-after-free in st_join_table::cleanup
2911 --echo #
2912
2913 --echo # Test case 1, fails on 10.11+
2914 CREATE TABLE t1 (a INT);
2915 CREATE TABLE t2 (b INT);
2916 CREATE TABLE t3 (c INT);
2917
2918 --echo # Inserts are optional, fails with and without data
2919 INSERT INTO t1 VALUES (1),(2);
2920 INSERT INTO t2 VALUES (3),(4);
2921 INSERT INTO t3 VALUES (5),(6);
2922
2923 EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE a IN ((SELECT c FROM t3 WHERE FALSE HAVING c < 0)));
2924
2925 DROP TABLE t1, t2, t3;
2926
2927 --echo # Test case 2, fails on 11.4 but not on 10.11
2928 CREATE TABLE t1 (a INT);
2929 CREATE TABLE t2 (b INT);
2930 CREATE TABLE t3 (c INT);
2931 CREATE TABLE t4 (d INT PRIMARY KEY);
2932
2933 SET SQL_SAFE_UPDATES=1;
2934 --error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
2935 UPDATE t1 STRAIGHT_JOIN t2 SET a = 89 WHERE 9 IN (SELECT c FROM t3 WHERE c IN (SELECT MAX(d) FROM t4));
2936
2937 DROP TABLE t1, t2, t3, t4;
2938
2939 --echo #
2940 --echo # End of 10.11 tests
2941 --echo #
2942
2943 --source include/test_db_charset_restore.inc