Debian Codegraph @

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

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