Debian Codegraph @

mysql-test/suite/parts/r/partition_alter3_myisam.result

raw
1 SET @max_row = 20;
2 SET @@session.default_storage_engine = 'MyISAM';
3
4 #------------------------------------------------------------------------
5 # 0. Setting of auxiliary variables + Creation of an auxiliary tables
6 # needed in many testcases
7 #------------------------------------------------------------------------
8 SELECT @max_row DIV 2 INTO @max_row_div2;
9 SELECT @max_row DIV 3 INTO @max_row_div3;
10 SELECT @max_row DIV 4 INTO @max_row_div4;
11 SET @max_int_4 = 2147483647;
12 DROP TABLE IF EXISTS t0_template;
13 CREATE TABLE t0_template (
14 f_int1 INTEGER DEFAULT 0,
15 f_int2 INTEGER DEFAULT 0,
16 f_char1 CHAR(20),
17 f_char2 CHAR(20),
18 f_charbig VARCHAR(1000) ,
19 PRIMARY KEY(f_int1))
20 ENGINE = MEMORY;
21 # Logging of <max_row> INSERTs into t0_template suppressed
22 DROP TABLE IF EXISTS t0_definition;
23 CREATE TABLE t0_definition (
24 state CHAR(3),
25 create_command VARBINARY(5000),
26 file_list VARBINARY(10000),
27 PRIMARY KEY (state)
28 ) ENGINE = MEMORY;
29 DROP TABLE IF EXISTS t0_aux;
30 CREATE TABLE t0_aux ( f_int1 INTEGER DEFAULT 0,
31 f_int2 INTEGER DEFAULT 0,
32 f_char1 CHAR(20),
33 f_char2 CHAR(20),
34 f_charbig VARCHAR(1000) )
35 ENGINE = MEMORY;
36 SET AUTOCOMMIT= 1;
37 SET @@session.sql_mode= '';
38 # End of basic preparations needed for all tests
39 #-----------------------------------------------
40
41 #========================================================================
42 # 1. Partition management commands on HASH partitioned table
43 # column in partitioning function is of type DATE
44 #========================================================================
45 DROP TABLE IF EXISTS t1;
46 CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30));
47 INSERT INTO t1 (f_date, f_varchar)
48 SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR)
49 FROM t0_template
50 WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
51 SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
52 INTO @exp_row_count;
53 SHOW CREATE TABLE t1;
54 Table Create Table
55 t1 CREATE TABLE `t1` (
56 `f_date` date DEFAULT NULL,
57 `f_varchar` varchar(30) DEFAULT NULL
58 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
59 t1.MYD
60 t1.MYI
61 t1.frm
62 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
63 id select_type table partitions type possible_keys key key_len ref rows Extra
64 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
65 # check read single success: 1
66 # check read all success: 1
67 # check read row by row success: 1
68 #------------------------------------------------------------------------
69 # 1.1 Increase number of PARTITIONS
70 #------------------------------------------------------------------------
71 # 1.1.1 ADD PARTITION to not partitioned table --> must fail
72 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
73 ERROR HY000: Partition management on a not partitioned table is not possible
74 # 1.1.2 Assign HASH partitioning
75 ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
76 SHOW CREATE TABLE t1;
77 Table Create Table
78 t1 CREATE TABLE `t1` (
79 `f_date` date DEFAULT NULL,
80 `f_varchar` varchar(30) DEFAULT NULL
81 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
82 PARTITION BY HASH (year(`f_date`))
83 t1#P#p0.MYD
84 t1#P#p0.MYI
85 t1.frm
86 t1.par
87 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
88 id select_type table partitions type possible_keys key key_len ref rows Extra
89 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
90 # check read single success: 1
91 # check read all success: 1
92 # check read row by row success: 1
93 # 1.1.3 Assign other HASH partitioning to already partitioned table
94 # + test and switch back + test
95 ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
96 SHOW CREATE TABLE t1;
97 Table Create Table
98 t1 CREATE TABLE `t1` (
99 `f_date` date DEFAULT NULL,
100 `f_varchar` varchar(30) DEFAULT NULL
101 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
102 PARTITION BY HASH (dayofyear(`f_date`))
103 t1#P#p0.MYD
104 t1#P#p0.MYI
105 t1.frm
106 t1.par
107 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
108 id select_type table partitions type possible_keys key key_len ref rows Extra
109 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
110 # check read single success: 1
111 # check read all success: 1
112 # check read row by row success: 1
113 ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
114 SHOW CREATE TABLE t1;
115 Table Create Table
116 t1 CREATE TABLE `t1` (
117 `f_date` date DEFAULT NULL,
118 `f_varchar` varchar(30) DEFAULT NULL
119 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
120 PARTITION BY HASH (year(`f_date`))
121 t1#P#p0.MYD
122 t1#P#p0.MYI
123 t1.frm
124 t1.par
125 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
126 id select_type table partitions type possible_keys key key_len ref rows Extra
127 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
128 # check read single success: 1
129 # check read all success: 1
130 # check read row by row success: 1
131 # 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
132 ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
133 ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
134 ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
135 ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
136 # 1.1.5 Add two named partitions + test
137 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
138 SHOW CREATE TABLE t1;
139 Table Create Table
140 t1 CREATE TABLE `t1` (
141 `f_date` date DEFAULT NULL,
142 `f_varchar` varchar(30) DEFAULT NULL
143 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
144 PARTITION BY HASH (year(`f_date`))
145 (PARTITION `p0` ENGINE = MyISAM,
146 PARTITION `part1` ENGINE = MyISAM,
147 PARTITION `part7` ENGINE = MyISAM)
148 t1#P#p0.MYD
149 t1#P#p0.MYI
150 t1#P#part1.MYD
151 t1#P#part1.MYI
152 t1#P#part7.MYD
153 t1#P#part7.MYI
154 t1.frm
155 t1.par
156 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
157 id select_type table partitions type possible_keys key key_len ref rows Extra
158 1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
159 # check read single success: 1
160 # check read all success: 1
161 # check read row by row success: 1
162 # 1.1.6 Add two named partitions, name clash --> must fail
163 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
164 ERROR HY000: Duplicate partition name part1
165 # 1.1.7 Add one named partition + test
166 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
167 SHOW CREATE TABLE t1;
168 Table Create Table
169 t1 CREATE TABLE `t1` (
170 `f_date` date DEFAULT NULL,
171 `f_varchar` varchar(30) DEFAULT NULL
172 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
173 PARTITION BY HASH (year(`f_date`))
174 (PARTITION `p0` ENGINE = MyISAM,
175 PARTITION `part1` ENGINE = MyISAM,
176 PARTITION `part7` ENGINE = MyISAM,
177 PARTITION `part2` ENGINE = MyISAM)
178 t1#P#p0.MYD
179 t1#P#p0.MYI
180 t1#P#part1.MYD
181 t1#P#part1.MYI
182 t1#P#part2.MYD
183 t1#P#part2.MYI
184 t1#P#part7.MYD
185 t1#P#part7.MYI
186 t1.frm
187 t1.par
188 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
189 id select_type table partitions type possible_keys key key_len ref rows Extra
190 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
191 # check read single success: 1
192 # check read all success: 1
193 # check read row by row success: 1
194 # 1.1.8 Add four not named partitions + test
195 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
196 SHOW CREATE TABLE t1;
197 Table Create Table
198 t1 CREATE TABLE `t1` (
199 `f_date` date DEFAULT NULL,
200 `f_varchar` varchar(30) DEFAULT NULL
201 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
202 PARTITION BY HASH (year(`f_date`))
203 (PARTITION `p0` ENGINE = MyISAM,
204 PARTITION `part1` ENGINE = MyISAM,
205 PARTITION `part7` ENGINE = MyISAM,
206 PARTITION `part2` ENGINE = MyISAM,
207 PARTITION `p4` ENGINE = MyISAM,
208 PARTITION `p5` ENGINE = MyISAM,
209 PARTITION `p6` ENGINE = MyISAM,
210 PARTITION `p7` ENGINE = MyISAM)
211 t1#P#p0.MYD
212 t1#P#p0.MYI
213 t1#P#p4.MYD
214 t1#P#p4.MYI
215 t1#P#p5.MYD
216 t1#P#p5.MYI
217 t1#P#p6.MYD
218 t1#P#p6.MYI
219 t1#P#p7.MYD
220 t1#P#p7.MYI
221 t1#P#part1.MYD
222 t1#P#part1.MYI
223 t1#P#part2.MYD
224 t1#P#part2.MYI
225 t1#P#part7.MYD
226 t1#P#part7.MYI
227 t1.frm
228 t1.par
229 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
230 id select_type table partitions type possible_keys key key_len ref rows Extra
231 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
232 # check read single success: 1
233 # check read all success: 1
234 # check read row by row success: 1
235 #------------------------------------------------------------------------
236 # 1.2 Decrease number of PARTITIONS
237 #------------------------------------------------------------------------
238 # 1.2.1 DROP PARTITION is not supported for HASH --> must fail
239 ALTER TABLE t1 DROP PARTITION part1;
240 ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
241 # 1.2.2 COALESCE PARTITION partitionname is not supported
242 ALTER TABLE t1 COALESCE PARTITION part1;
243 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'part1' at line 1
244 # 1.2.3 Decrease by 0 is non sense --> must fail
245 ALTER TABLE t1 COALESCE PARTITION 0;
246 ERROR HY000: At least one partition must be coalesced
247 # 1.2.4 COALESCE one partition + test loop
248 ALTER TABLE t1 COALESCE PARTITION 1;
249 SHOW CREATE TABLE t1;
250 Table Create Table
251 t1 CREATE TABLE `t1` (
252 `f_date` date DEFAULT NULL,
253 `f_varchar` varchar(30) DEFAULT NULL
254 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
255 PARTITION BY HASH (year(`f_date`))
256 (PARTITION `p0` ENGINE = MyISAM,
257 PARTITION `part1` ENGINE = MyISAM,
258 PARTITION `part7` ENGINE = MyISAM,
259 PARTITION `part2` ENGINE = MyISAM,
260 PARTITION `p4` ENGINE = MyISAM,
261 PARTITION `p5` ENGINE = MyISAM,
262 PARTITION `p6` ENGINE = MyISAM)
263 t1#P#p0.MYD
264 t1#P#p0.MYI
265 t1#P#p4.MYD
266 t1#P#p4.MYI
267 t1#P#p5.MYD
268 t1#P#p5.MYI
269 t1#P#p6.MYD
270 t1#P#p6.MYI
271 t1#P#part1.MYD
272 t1#P#part1.MYI
273 t1#P#part2.MYD
274 t1#P#part2.MYI
275 t1#P#part7.MYD
276 t1#P#part7.MYI
277 t1.frm
278 t1.par
279 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
280 id select_type table partitions type possible_keys key key_len ref rows Extra
281 1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
282 # check read single success: 1
283 # check read all success: 1
284 # check read row by row success: 1
285 ALTER TABLE t1 COALESCE PARTITION 1;
286 SHOW CREATE TABLE t1;
287 Table Create Table
288 t1 CREATE TABLE `t1` (
289 `f_date` date DEFAULT NULL,
290 `f_varchar` varchar(30) DEFAULT NULL
291 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
292 PARTITION BY HASH (year(`f_date`))
293 (PARTITION `p0` ENGINE = MyISAM,
294 PARTITION `part1` ENGINE = MyISAM,
295 PARTITION `part7` ENGINE = MyISAM,
296 PARTITION `part2` ENGINE = MyISAM,
297 PARTITION `p4` ENGINE = MyISAM,
298 PARTITION `p5` ENGINE = MyISAM)
299 t1#P#p0.MYD
300 t1#P#p0.MYI
301 t1#P#p4.MYD
302 t1#P#p4.MYI
303 t1#P#p5.MYD
304 t1#P#p5.MYI
305 t1#P#part1.MYD
306 t1#P#part1.MYI
307 t1#P#part2.MYD
308 t1#P#part2.MYI
309 t1#P#part7.MYD
310 t1#P#part7.MYI
311 t1.frm
312 t1.par
313 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
314 id select_type table partitions type possible_keys key key_len ref rows Extra
315 1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
316 # check read single success: 1
317 # check read all success: 1
318 # check read row by row success: 1
319 ALTER TABLE t1 COALESCE PARTITION 1;
320 SHOW CREATE TABLE t1;
321 Table Create Table
322 t1 CREATE TABLE `t1` (
323 `f_date` date DEFAULT NULL,
324 `f_varchar` varchar(30) DEFAULT NULL
325 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
326 PARTITION BY HASH (year(`f_date`))
327 (PARTITION `p0` ENGINE = MyISAM,
328 PARTITION `part1` ENGINE = MyISAM,
329 PARTITION `part7` ENGINE = MyISAM,
330 PARTITION `part2` ENGINE = MyISAM,
331 PARTITION `p4` ENGINE = MyISAM)
332 t1#P#p0.MYD
333 t1#P#p0.MYI
334 t1#P#p4.MYD
335 t1#P#p4.MYI
336 t1#P#part1.MYD
337 t1#P#part1.MYI
338 t1#P#part2.MYD
339 t1#P#part2.MYI
340 t1#P#part7.MYD
341 t1#P#part7.MYI
342 t1.frm
343 t1.par
344 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
345 id select_type table partitions type possible_keys key key_len ref rows Extra
346 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
347 # check read single success: 1
348 # check read all success: 1
349 # check read row by row success: 1
350 ALTER TABLE t1 COALESCE PARTITION 1;
351 SHOW CREATE TABLE t1;
352 Table Create Table
353 t1 CREATE TABLE `t1` (
354 `f_date` date DEFAULT NULL,
355 `f_varchar` varchar(30) DEFAULT NULL
356 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
357 PARTITION BY HASH (year(`f_date`))
358 (PARTITION `p0` ENGINE = MyISAM,
359 PARTITION `part1` ENGINE = MyISAM,
360 PARTITION `part7` ENGINE = MyISAM,
361 PARTITION `part2` ENGINE = MyISAM)
362 t1#P#p0.MYD
363 t1#P#p0.MYI
364 t1#P#part1.MYD
365 t1#P#part1.MYI
366 t1#P#part2.MYD
367 t1#P#part2.MYI
368 t1#P#part7.MYD
369 t1#P#part7.MYI
370 t1.frm
371 t1.par
372 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
373 id select_type table partitions type possible_keys key key_len ref rows Extra
374 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
375 # check read single success: 1
376 # check read all success: 1
377 # check read row by row success: 1
378 ALTER TABLE t1 COALESCE PARTITION 1;
379 SHOW CREATE TABLE t1;
380 Table Create Table
381 t1 CREATE TABLE `t1` (
382 `f_date` date DEFAULT NULL,
383 `f_varchar` varchar(30) DEFAULT NULL
384 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
385 PARTITION BY HASH (year(`f_date`))
386 (PARTITION `p0` ENGINE = MyISAM,
387 PARTITION `part1` ENGINE = MyISAM,
388 PARTITION `part7` ENGINE = MyISAM)
389 t1#P#p0.MYD
390 t1#P#p0.MYI
391 t1#P#part1.MYD
392 t1#P#part1.MYI
393 t1#P#part7.MYD
394 t1#P#part7.MYI
395 t1.frm
396 t1.par
397 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
398 id select_type table partitions type possible_keys key key_len ref rows Extra
399 1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
400 # check read single success: 1
401 # check read all success: 1
402 # check read row by row success: 1
403 ALTER TABLE t1 COALESCE PARTITION 1;
404 SHOW CREATE TABLE t1;
405 Table Create Table
406 t1 CREATE TABLE `t1` (
407 `f_date` date DEFAULT NULL,
408 `f_varchar` varchar(30) DEFAULT NULL
409 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
410 PARTITION BY HASH (year(`f_date`))
411 (PARTITION `p0` ENGINE = MyISAM,
412 PARTITION `part1` ENGINE = MyISAM)
413 t1#P#p0.MYD
414 t1#P#p0.MYI
415 t1#P#part1.MYD
416 t1#P#part1.MYI
417 t1.frm
418 t1.par
419 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
420 id select_type table partitions type possible_keys key key_len ref rows Extra
421 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
422 # check read single success: 1
423 # check read all success: 1
424 # check read row by row success: 1
425 ALTER TABLE t1 COALESCE PARTITION 1;
426 SHOW CREATE TABLE t1;
427 Table Create Table
428 t1 CREATE TABLE `t1` (
429 `f_date` date DEFAULT NULL,
430 `f_varchar` varchar(30) DEFAULT NULL
431 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
432 PARTITION BY HASH (year(`f_date`))
433 (PARTITION `p0` ENGINE = MyISAM)
434 t1#P#p0.MYD
435 t1#P#p0.MYI
436 t1.frm
437 t1.par
438 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
439 id select_type table partitions type possible_keys key key_len ref rows Extra
440 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
441 # check read single success: 1
442 # check read all success: 1
443 # check read row by row success: 1
444 # 1.2.5 COALESCE of last partition --> must fail
445 ALTER TABLE t1 COALESCE PARTITION 1;
446 ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
447 # 1.2.6 Remove partitioning
448 ALTER TABLE t1 REMOVE PARTITIONING;
449 SHOW CREATE TABLE t1;
450 Table Create Table
451 t1 CREATE TABLE `t1` (
452 `f_date` date DEFAULT NULL,
453 `f_varchar` varchar(30) DEFAULT NULL
454 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
455 t1.MYD
456 t1.MYI
457 t1.frm
458 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
459 id select_type table partitions type possible_keys key key_len ref rows Extra
460 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
461 # check read single success: 1
462 # check read all success: 1
463 # check read row by row success: 1
464 # 1.2.7 Remove partitioning from not partitioned table --> ????
465 ALTER TABLE t1 REMOVE PARTITIONING;
466 ERROR HY000: Partition management on a not partitioned table is not possible
467 DROP TABLE t1;
468
469 #========================================================================
470 # 2. Partition management commands on KEY partitioned table
471 #========================================================================
472 DROP TABLE IF EXISTS t1;
473 CREATE TABLE t1 (
474 f_int1 INTEGER DEFAULT 0,
475 f_int2 INTEGER DEFAULT 0,
476 f_char1 CHAR(20),
477 f_char2 CHAR(20),
478 f_charbig VARCHAR(1000)
479 );
480 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
481 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
482 SHOW CREATE TABLE t1;
483 Table Create Table
484 t1 CREATE TABLE `t1` (
485 `f_int1` int(11) DEFAULT 0,
486 `f_int2` int(11) DEFAULT 0,
487 `f_char1` char(20) DEFAULT NULL,
488 `f_char2` char(20) DEFAULT NULL,
489 `f_charbig` varchar(1000) DEFAULT NULL
490 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
491 t1.MYD
492 t1.MYI
493 t1.frm
494 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
495 id select_type table partitions type possible_keys key key_len ref rows Extra
496 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
497 # check read single success: 1
498 # check read all success: 1
499 # check read row by row success: 1
500 #------------------------------------------------------------------------
501 # 2.1 Increase number of PARTITIONS
502 # Some negative testcases are omitted (already checked with HASH).
503 #------------------------------------------------------------------------
504 # 2.1.1 Assign KEY partitioning
505 ALTER TABLE t1 PARTITION BY KEY(f_int1);
506 SHOW CREATE TABLE t1;
507 Table Create Table
508 t1 CREATE TABLE `t1` (
509 `f_int1` int(11) DEFAULT 0,
510 `f_int2` int(11) DEFAULT 0,
511 `f_char1` char(20) DEFAULT NULL,
512 `f_char2` char(20) DEFAULT NULL,
513 `f_charbig` varchar(1000) DEFAULT NULL
514 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
515 PARTITION BY KEY (`f_int1`)
516 t1#P#p0.MYD
517 t1#P#p0.MYI
518 t1.frm
519 t1.par
520 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
521 id select_type table partitions type possible_keys key key_len ref rows Extra
522 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
523 # check read single success: 1
524 # check read all success: 1
525 # check read row by row success: 1
526 # 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
527 ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
528 ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
529 ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
530 ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
531 # 2.1.3 Add two named partitions + test
532 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
533 SHOW CREATE TABLE t1;
534 Table Create Table
535 t1 CREATE TABLE `t1` (
536 `f_int1` int(11) DEFAULT 0,
537 `f_int2` int(11) DEFAULT 0,
538 `f_char1` char(20) DEFAULT NULL,
539 `f_char2` char(20) DEFAULT NULL,
540 `f_charbig` varchar(1000) DEFAULT NULL
541 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
542 PARTITION BY KEY (`f_int1`)
543 (PARTITION `p0` ENGINE = MyISAM,
544 PARTITION `part1` ENGINE = MyISAM,
545 PARTITION `part7` ENGINE = MyISAM)
546 t1#P#p0.MYD
547 t1#P#p0.MYI
548 t1#P#part1.MYD
549 t1#P#part1.MYI
550 t1#P#part7.MYD
551 t1#P#part7.MYI
552 t1.frm
553 t1.par
554 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
555 id select_type table partitions type possible_keys key key_len ref rows Extra
556 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
557 # check read single success: 1
558 # check read all success: 1
559 # check read row by row success: 1
560 # 2.1.4 Add one named partition + test
561 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
562 SHOW CREATE TABLE t1;
563 Table Create Table
564 t1 CREATE TABLE `t1` (
565 `f_int1` int(11) DEFAULT 0,
566 `f_int2` int(11) DEFAULT 0,
567 `f_char1` char(20) DEFAULT NULL,
568 `f_char2` char(20) DEFAULT NULL,
569 `f_charbig` varchar(1000) DEFAULT NULL
570 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
571 PARTITION BY KEY (`f_int1`)
572 (PARTITION `p0` ENGINE = MyISAM,
573 PARTITION `part1` ENGINE = MyISAM,
574 PARTITION `part7` ENGINE = MyISAM,
575 PARTITION `part2` ENGINE = MyISAM)
576 t1#P#p0.MYD
577 t1#P#p0.MYI
578 t1#P#part1.MYD
579 t1#P#part1.MYI
580 t1#P#part2.MYD
581 t1#P#part2.MYI
582 t1#P#part7.MYD
583 t1#P#part7.MYI
584 t1.frm
585 t1.par
586 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
587 id select_type table partitions type possible_keys key key_len ref rows Extra
588 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
589 # check read single success: 1
590 # check read all success: 1
591 # check read row by row success: 1
592 # 2.1.5 Add four not named partitions + test
593 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
594 SHOW CREATE TABLE t1;
595 Table Create Table
596 t1 CREATE TABLE `t1` (
597 `f_int1` int(11) DEFAULT 0,
598 `f_int2` int(11) DEFAULT 0,
599 `f_char1` char(20) DEFAULT NULL,
600 `f_char2` char(20) DEFAULT NULL,
601 `f_charbig` varchar(1000) DEFAULT NULL
602 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
603 PARTITION BY KEY (`f_int1`)
604 (PARTITION `p0` ENGINE = MyISAM,
605 PARTITION `part1` ENGINE = MyISAM,
606 PARTITION `part7` ENGINE = MyISAM,
607 PARTITION `part2` ENGINE = MyISAM,
608 PARTITION `p4` ENGINE = MyISAM,
609 PARTITION `p5` ENGINE = MyISAM,
610 PARTITION `p6` ENGINE = MyISAM,
611 PARTITION `p7` ENGINE = MyISAM)
612 t1#P#p0.MYD
613 t1#P#p0.MYI
614 t1#P#p4.MYD
615 t1#P#p4.MYI
616 t1#P#p5.MYD
617 t1#P#p5.MYI
618 t1#P#p6.MYD
619 t1#P#p6.MYI
620 t1#P#p7.MYD
621 t1#P#p7.MYI
622 t1#P#part1.MYD
623 t1#P#part1.MYI
624 t1#P#part2.MYD
625 t1#P#part2.MYI
626 t1#P#part7.MYD
627 t1#P#part7.MYI
628 t1.frm
629 t1.par
630 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
631 id select_type table partitions type possible_keys key key_len ref rows Extra
632 1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
633 # check read single success: 1
634 # check read all success: 1
635 # check read row by row success: 1
636 #------------------------------------------------------------------------
637 # 2.2 Decrease number of PARTITIONS
638 # Some negative testcases are omitted (already checked with HASH).
639 #------------------------------------------------------------------------
640 # 2.2.1 DROP PARTITION is not supported for KEY --> must fail
641 ALTER TABLE t1 DROP PARTITION part1;
642 ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
643 # 2.2.4 COALESCE one partition + test loop
644 ALTER TABLE t1 COALESCE PARTITION 1;
645 SHOW CREATE TABLE t1;
646 Table Create Table
647 t1 CREATE TABLE `t1` (
648 `f_int1` int(11) DEFAULT 0,
649 `f_int2` int(11) DEFAULT 0,
650 `f_char1` char(20) DEFAULT NULL,
651 `f_char2` char(20) DEFAULT NULL,
652 `f_charbig` varchar(1000) DEFAULT NULL
653 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
654 PARTITION BY KEY (`f_int1`)
655 (PARTITION `p0` ENGINE = MyISAM,
656 PARTITION `part1` ENGINE = MyISAM,
657 PARTITION `part7` ENGINE = MyISAM,
658 PARTITION `part2` ENGINE = MyISAM,
659 PARTITION `p4` ENGINE = MyISAM,
660 PARTITION `p5` ENGINE = MyISAM,
661 PARTITION `p6` ENGINE = MyISAM)
662 t1#P#p0.MYD
663 t1#P#p0.MYI
664 t1#P#p4.MYD
665 t1#P#p4.MYI
666 t1#P#p5.MYD
667 t1#P#p5.MYI
668 t1#P#p6.MYD
669 t1#P#p6.MYI
670 t1#P#part1.MYD
671 t1#P#part1.MYI
672 t1#P#part2.MYD
673 t1#P#part2.MYI
674 t1#P#part7.MYD
675 t1#P#part7.MYI
676 t1.frm
677 t1.par
678 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
679 id select_type table partitions type possible_keys key key_len ref rows Extra
680 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
681 # check read single success: 1
682 # check read all success: 1
683 # check read row by row success: 1
684 ALTER TABLE t1 COALESCE PARTITION 1;
685 SHOW CREATE TABLE t1;
686 Table Create Table
687 t1 CREATE TABLE `t1` (
688 `f_int1` int(11) DEFAULT 0,
689 `f_int2` int(11) DEFAULT 0,
690 `f_char1` char(20) DEFAULT NULL,
691 `f_char2` char(20) DEFAULT NULL,
692 `f_charbig` varchar(1000) DEFAULT NULL
693 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
694 PARTITION BY KEY (`f_int1`)
695 (PARTITION `p0` ENGINE = MyISAM,
696 PARTITION `part1` ENGINE = MyISAM,
697 PARTITION `part7` ENGINE = MyISAM,
698 PARTITION `part2` ENGINE = MyISAM,
699 PARTITION `p4` ENGINE = MyISAM,
700 PARTITION `p5` ENGINE = MyISAM)
701 t1#P#p0.MYD
702 t1#P#p0.MYI
703 t1#P#p4.MYD
704 t1#P#p4.MYI
705 t1#P#p5.MYD
706 t1#P#p5.MYI
707 t1#P#part1.MYD
708 t1#P#part1.MYI
709 t1#P#part2.MYD
710 t1#P#part2.MYI
711 t1#P#part7.MYD
712 t1#P#part7.MYI
713 t1.frm
714 t1.par
715 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
716 id select_type table partitions type possible_keys key key_len ref rows Extra
717 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 3 Using where
718 # check read single success: 1
719 # check read all success: 1
720 # check read row by row success: 1
721 ALTER TABLE t1 COALESCE PARTITION 1;
722 SHOW CREATE TABLE t1;
723 Table Create Table
724 t1 CREATE TABLE `t1` (
725 `f_int1` int(11) DEFAULT 0,
726 `f_int2` int(11) DEFAULT 0,
727 `f_char1` char(20) DEFAULT NULL,
728 `f_char2` char(20) DEFAULT NULL,
729 `f_charbig` varchar(1000) DEFAULT NULL
730 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
731 PARTITION BY KEY (`f_int1`)
732 (PARTITION `p0` ENGINE = MyISAM,
733 PARTITION `part1` ENGINE = MyISAM,
734 PARTITION `part7` ENGINE = MyISAM,
735 PARTITION `part2` ENGINE = MyISAM,
736 PARTITION `p4` ENGINE = MyISAM)
737 t1#P#p0.MYD
738 t1#P#p0.MYI
739 t1#P#p4.MYD
740 t1#P#p4.MYI
741 t1#P#part1.MYD
742 t1#P#part1.MYI
743 t1#P#part2.MYD
744 t1#P#part2.MYI
745 t1#P#part7.MYD
746 t1#P#part7.MYI
747 t1.frm
748 t1.par
749 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
750 id select_type table partitions type possible_keys key key_len ref rows Extra
751 1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 10 Using where
752 # check read single success: 1
753 # check read all success: 1
754 # check read row by row success: 1
755 ALTER TABLE t1 COALESCE PARTITION 1;
756 SHOW CREATE TABLE t1;
757 Table Create Table
758 t1 CREATE TABLE `t1` (
759 `f_int1` int(11) DEFAULT 0,
760 `f_int2` int(11) DEFAULT 0,
761 `f_char1` char(20) DEFAULT NULL,
762 `f_char2` char(20) DEFAULT NULL,
763 `f_charbig` varchar(1000) DEFAULT NULL
764 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
765 PARTITION BY KEY (`f_int1`)
766 (PARTITION `p0` ENGINE = MyISAM,
767 PARTITION `part1` ENGINE = MyISAM,
768 PARTITION `part7` ENGINE = MyISAM,
769 PARTITION `part2` ENGINE = MyISAM)
770 t1#P#p0.MYD
771 t1#P#p0.MYI
772 t1#P#part1.MYD
773 t1#P#part1.MYI
774 t1#P#part2.MYD
775 t1#P#part2.MYI
776 t1#P#part7.MYD
777 t1#P#part7.MYI
778 t1.frm
779 t1.par
780 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
781 id select_type table partitions type possible_keys key key_len ref rows Extra
782 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
783 # check read single success: 1
784 # check read all success: 1
785 # check read row by row success: 1
786 ALTER TABLE t1 COALESCE PARTITION 1;
787 SHOW CREATE TABLE t1;
788 Table Create Table
789 t1 CREATE TABLE `t1` (
790 `f_int1` int(11) DEFAULT 0,
791 `f_int2` int(11) DEFAULT 0,
792 `f_char1` char(20) DEFAULT NULL,
793 `f_char2` char(20) DEFAULT NULL,
794 `f_charbig` varchar(1000) DEFAULT NULL
795 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
796 PARTITION BY KEY (`f_int1`)
797 (PARTITION `p0` ENGINE = MyISAM,
798 PARTITION `part1` ENGINE = MyISAM,
799 PARTITION `part7` ENGINE = MyISAM)
800 t1#P#p0.MYD
801 t1#P#p0.MYI
802 t1#P#part1.MYD
803 t1#P#part1.MYI
804 t1#P#part7.MYD
805 t1#P#part7.MYI
806 t1.frm
807 t1.par
808 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
809 id select_type table partitions type possible_keys key key_len ref rows Extra
810 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
811 # check read single success: 1
812 # check read all success: 1
813 # check read row by row success: 1
814 ALTER TABLE t1 COALESCE PARTITION 1;
815 SHOW CREATE TABLE t1;
816 Table Create Table
817 t1 CREATE TABLE `t1` (
818 `f_int1` int(11) DEFAULT 0,
819 `f_int2` int(11) DEFAULT 0,
820 `f_char1` char(20) DEFAULT NULL,
821 `f_char2` char(20) DEFAULT NULL,
822 `f_charbig` varchar(1000) DEFAULT NULL
823 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
824 PARTITION BY KEY (`f_int1`)
825 (PARTITION `p0` ENGINE = MyISAM,
826 PARTITION `part1` ENGINE = MyISAM)
827 t1#P#p0.MYD
828 t1#P#p0.MYI
829 t1#P#part1.MYD
830 t1#P#part1.MYI
831 t1.frm
832 t1.par
833 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
834 id select_type table partitions type possible_keys key key_len ref rows Extra
835 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
836 # check read single success: 1
837 # check read all success: 1
838 # check read row by row success: 1
839 ALTER TABLE t1 COALESCE PARTITION 1;
840 SHOW CREATE TABLE t1;
841 Table Create Table
842 t1 CREATE TABLE `t1` (
843 `f_int1` int(11) DEFAULT 0,
844 `f_int2` int(11) DEFAULT 0,
845 `f_char1` char(20) DEFAULT NULL,
846 `f_char2` char(20) DEFAULT NULL,
847 `f_charbig` varchar(1000) DEFAULT NULL
848 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
849 PARTITION BY KEY (`f_int1`)
850 (PARTITION `p0` ENGINE = MyISAM)
851 t1#P#p0.MYD
852 t1#P#p0.MYI
853 t1.frm
854 t1.par
855 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
856 id select_type table partitions type possible_keys key key_len ref rows Extra
857 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
858 # check read single success: 1
859 # check read all success: 1
860 # check read row by row success: 1
861 # 2.2.5 COALESCE of last partition --> must fail
862 ALTER TABLE t1 COALESCE PARTITION 1;
863 ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
864 # 2.2.6 Remove partitioning
865 ALTER TABLE t1 REMOVE PARTITIONING;
866 SHOW CREATE TABLE t1;
867 Table Create Table
868 t1 CREATE TABLE `t1` (
869 `f_int1` int(11) DEFAULT 0,
870 `f_int2` int(11) DEFAULT 0,
871 `f_char1` char(20) DEFAULT NULL,
872 `f_char2` char(20) DEFAULT NULL,
873 `f_charbig` varchar(1000) DEFAULT NULL
874 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
875 t1.MYD
876 t1.MYI
877 t1.frm
878 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
879 id select_type table partitions type possible_keys key key_len ref rows Extra
880 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
881 # check read single success: 1
882 # check read all success: 1
883 # check read row by row success: 1
884 # 2.2.7 Remove partitioning from not partitioned table --> ????
885 ALTER TABLE t1 REMOVE PARTITIONING;
886 ERROR HY000: Partition management on a not partitioned table is not possible
887 DROP TABLE t1;
888 DROP VIEW IF EXISTS v1;
889 DROP TABLE IF EXISTS t1;
890 DROP TABLE IF EXISTS t0_aux;
891 DROP TABLE IF EXISTS t0_definition;
892 DROP TABLE IF EXISTS t0_template;