2
SET @@session.storage_engine = 'InnoDB';
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 (
18
f_charbig VARCHAR(1000) ,
21
# Logging of <max_row> INSERTs into t0_template suppressed
22
DROP TABLE IF EXISTS t0_definition;
23
CREATE TABLE t0_definition (
25
create_command VARBINARY(5000),
26
file_list VARBINARY(10000),
29
DROP TABLE IF EXISTS t0_aux;
30
CREATE TABLE t0_aux ( f_int1 INTEGER,
34
f_charbig VARCHAR(1000) )
37
SET @@session.sql_mode= '';
38
# End of basic preparations needed for all tests
39
#-----------------------------------------------
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)
50
WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
51
SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
55
t1 CREATE TABLE `t1` (
56
`f_date` date DEFAULT NULL,
57
`f_varchar` varchar(30) DEFAULT NULL
58
) ENGINE=InnoDB DEFAULT CHARSET=latin1
60
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
61
id select_type table partitions type possible_keys key key_len ref rows Extra
62
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
63
# check read single success: 1
64
# check read all success: 1
65
# check read row by row success: 1
66
#------------------------------------------------------------------------
67
# 1.1 Increase number of PARTITIONS
68
#------------------------------------------------------------------------
69
# 1.1.1 ADD PARTITION to not partitioned table --> must fail
70
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
71
ERROR HY000: Partition management on a not partitioned table is not possible
72
# 1.1.2 Assign HASH partitioning
73
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
76
t1 CREATE TABLE `t1` (
77
`f_date` date DEFAULT NULL,
78
`f_varchar` varchar(30) DEFAULT NULL
79
) ENGINE=InnoDB DEFAULT CHARSET=latin1
80
/*!50100 PARTITION BY HASH (YEAR(f_date)) */
83
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
84
id select_type table partitions type possible_keys key key_len ref rows Extra
85
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
86
# check read single success: 1
87
# check read all success: 1
88
# check read row by row success: 1
89
# 1.1.3 Assign other HASH partitioning to already partitioned table
90
# + test and switch back + test
91
ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
94
t1 CREATE TABLE `t1` (
95
`f_date` date DEFAULT NULL,
96
`f_varchar` varchar(30) DEFAULT NULL
97
) ENGINE=InnoDB DEFAULT CHARSET=latin1
98
/*!50100 PARTITION BY HASH (DAYOFYEAR(f_date)) */
101
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
102
id select_type table partitions type possible_keys key key_len ref rows Extra
103
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
104
# check read single success: 1
105
# check read all success: 1
106
# check read row by row success: 1
107
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
108
SHOW CREATE TABLE t1;
110
t1 CREATE TABLE `t1` (
111
`f_date` date DEFAULT NULL,
112
`f_varchar` varchar(30) DEFAULT NULL
113
) ENGINE=InnoDB DEFAULT CHARSET=latin1
114
/*!50100 PARTITION BY HASH (YEAR(f_date)) */
117
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
118
id select_type table partitions type possible_keys key key_len ref rows Extra
119
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
120
# check read single success: 1
121
# check read all success: 1
122
# check read row by row success: 1
123
# 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
124
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
125
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
126
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
127
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
128
# 1.1.5 Add two named partitions + test
129
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
130
SHOW CREATE TABLE t1;
132
t1 CREATE TABLE `t1` (
133
`f_date` date DEFAULT NULL,
134
`f_varchar` varchar(30) DEFAULT NULL
135
) ENGINE=InnoDB DEFAULT CHARSET=latin1
136
/*!50100 PARTITION BY HASH (YEAR(f_date))
137
(PARTITION p0 ENGINE = InnoDB,
138
PARTITION part1 ENGINE = InnoDB,
139
PARTITION part7 ENGINE = InnoDB) */
142
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
143
id select_type table partitions type possible_keys key key_len ref rows Extra
144
1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 20 Using where
145
# check read single success: 1
146
# check read all success: 1
147
# check read row by row success: 1
148
# 1.1.6 Add two named partitions, name clash --> must fail
149
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
150
ERROR HY000: Duplicate partition name part1
151
# 1.1.7 Add one named partition + test
152
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
153
SHOW CREATE TABLE t1;
155
t1 CREATE TABLE `t1` (
156
`f_date` date DEFAULT NULL,
157
`f_varchar` varchar(30) DEFAULT NULL
158
) ENGINE=InnoDB DEFAULT CHARSET=latin1
159
/*!50100 PARTITION BY HASH (YEAR(f_date))
160
(PARTITION p0 ENGINE = InnoDB,
161
PARTITION part1 ENGINE = InnoDB,
162
PARTITION part7 ENGINE = InnoDB,
163
PARTITION part2 ENGINE = InnoDB) */
166
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
167
id select_type table partitions type possible_keys key key_len ref rows Extra
168
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
169
# check read single success: 1
170
# check read all success: 1
171
# check read row by row success: 1
172
# 1.1.8 Add four not named partitions + test
173
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
174
SHOW CREATE TABLE t1;
176
t1 CREATE TABLE `t1` (
177
`f_date` date DEFAULT NULL,
178
`f_varchar` varchar(30) DEFAULT NULL
179
) ENGINE=InnoDB DEFAULT CHARSET=latin1
180
/*!50100 PARTITION BY HASH (YEAR(f_date))
181
(PARTITION p0 ENGINE = InnoDB,
182
PARTITION part1 ENGINE = InnoDB,
183
PARTITION part7 ENGINE = InnoDB,
184
PARTITION part2 ENGINE = InnoDB,
185
PARTITION p4 ENGINE = InnoDB,
186
PARTITION p5 ENGINE = InnoDB,
187
PARTITION p6 ENGINE = InnoDB,
188
PARTITION p7 ENGINE = InnoDB) */
191
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
192
id select_type table partitions type possible_keys key key_len ref rows Extra
193
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
194
# check read single success: 1
195
# check read all success: 1
196
# check read row by row success: 1
197
#------------------------------------------------------------------------
198
# 1.2 Decrease number of PARTITIONS
199
#------------------------------------------------------------------------
200
# 1.2.1 DROP PARTITION is not supported for HASH --> must fail
201
ALTER TABLE t1 DROP PARTITION part1;
202
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
203
# 1.2.2 COALESCE PARTITION partitionname is not supported
204
ALTER TABLE t1 COALESCE PARTITION part1;
205
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
206
# 1.2.3 Decrease by 0 is non sense --> must fail
207
ALTER TABLE t1 COALESCE PARTITION 0;
208
ERROR HY000: At least one partition must be coalesced
209
# 1.2.4 COALESCE one partition + test loop
210
ALTER TABLE t1 COALESCE PARTITION 1;
211
SHOW CREATE TABLE t1;
213
t1 CREATE TABLE `t1` (
214
`f_date` date DEFAULT NULL,
215
`f_varchar` varchar(30) DEFAULT NULL
216
) ENGINE=InnoDB DEFAULT CHARSET=latin1
217
/*!50100 PARTITION BY HASH (YEAR(f_date))
218
(PARTITION p0 ENGINE = InnoDB,
219
PARTITION part1 ENGINE = InnoDB,
220
PARTITION part7 ENGINE = InnoDB,
221
PARTITION part2 ENGINE = InnoDB,
222
PARTITION p4 ENGINE = InnoDB,
223
PARTITION p5 ENGINE = InnoDB,
224
PARTITION p6 ENGINE = InnoDB) */
227
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
228
id select_type table partitions type possible_keys key key_len ref rows Extra
229
1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 20 Using where
230
# check read single success: 1
231
# check read all success: 1
232
# check read row by row success: 1
233
ALTER TABLE t1 COALESCE PARTITION 1;
234
SHOW CREATE TABLE t1;
236
t1 CREATE TABLE `t1` (
237
`f_date` date DEFAULT NULL,
238
`f_varchar` varchar(30) DEFAULT NULL
239
) ENGINE=InnoDB DEFAULT CHARSET=latin1
240
/*!50100 PARTITION BY HASH (YEAR(f_date))
241
(PARTITION p0 ENGINE = InnoDB,
242
PARTITION part1 ENGINE = InnoDB,
243
PARTITION part7 ENGINE = InnoDB,
244
PARTITION part2 ENGINE = InnoDB,
245
PARTITION p4 ENGINE = InnoDB,
246
PARTITION p5 ENGINE = InnoDB) */
249
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
250
id select_type table partitions type possible_keys key key_len ref rows Extra
251
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 20 Using where
252
# check read single success: 1
253
# check read all success: 1
254
# check read row by row success: 1
255
ALTER TABLE t1 COALESCE PARTITION 1;
256
SHOW CREATE TABLE t1;
258
t1 CREATE TABLE `t1` (
259
`f_date` date DEFAULT NULL,
260
`f_varchar` varchar(30) DEFAULT NULL
261
) ENGINE=InnoDB DEFAULT CHARSET=latin1
262
/*!50100 PARTITION BY HASH (YEAR(f_date))
263
(PARTITION p0 ENGINE = InnoDB,
264
PARTITION part1 ENGINE = InnoDB,
265
PARTITION part7 ENGINE = InnoDB,
266
PARTITION part2 ENGINE = InnoDB,
267
PARTITION p4 ENGINE = InnoDB) */
270
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
271
id select_type table partitions type possible_keys key key_len ref rows Extra
272
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
273
# check read single success: 1
274
# check read all success: 1
275
# check read row by row success: 1
276
ALTER TABLE t1 COALESCE PARTITION 1;
277
SHOW CREATE TABLE t1;
279
t1 CREATE TABLE `t1` (
280
`f_date` date DEFAULT NULL,
281
`f_varchar` varchar(30) DEFAULT NULL
282
) ENGINE=InnoDB DEFAULT CHARSET=latin1
283
/*!50100 PARTITION BY HASH (YEAR(f_date))
284
(PARTITION p0 ENGINE = InnoDB,
285
PARTITION part1 ENGINE = InnoDB,
286
PARTITION part7 ENGINE = InnoDB,
287
PARTITION part2 ENGINE = InnoDB) */
290
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
291
id select_type table partitions type possible_keys key key_len ref rows Extra
292
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
293
# check read single success: 1
294
# check read all success: 1
295
# check read row by row success: 1
296
ALTER TABLE t1 COALESCE PARTITION 1;
297
SHOW CREATE TABLE t1;
299
t1 CREATE TABLE `t1` (
300
`f_date` date DEFAULT NULL,
301
`f_varchar` varchar(30) DEFAULT NULL
302
) ENGINE=InnoDB DEFAULT CHARSET=latin1
303
/*!50100 PARTITION BY HASH (YEAR(f_date))
304
(PARTITION p0 ENGINE = InnoDB,
305
PARTITION part1 ENGINE = InnoDB,
306
PARTITION part7 ENGINE = InnoDB) */
309
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
310
id select_type table partitions type possible_keys key key_len ref rows Extra
311
1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 20 Using where
312
# check read single success: 1
313
# check read all success: 1
314
# check read row by row success: 1
315
ALTER TABLE t1 COALESCE PARTITION 1;
316
SHOW CREATE TABLE t1;
318
t1 CREATE TABLE `t1` (
319
`f_date` date DEFAULT NULL,
320
`f_varchar` varchar(30) DEFAULT NULL
321
) ENGINE=InnoDB DEFAULT CHARSET=latin1
322
/*!50100 PARTITION BY HASH (YEAR(f_date))
323
(PARTITION p0 ENGINE = InnoDB,
324
PARTITION part1 ENGINE = InnoDB) */
327
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
328
id select_type table partitions type possible_keys key key_len ref rows Extra
329
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
330
# check read single success: 1
331
# check read all success: 1
332
# check read row by row success: 1
333
ALTER TABLE t1 COALESCE PARTITION 1;
334
SHOW CREATE TABLE t1;
336
t1 CREATE TABLE `t1` (
337
`f_date` date DEFAULT NULL,
338
`f_varchar` varchar(30) DEFAULT NULL
339
) ENGINE=InnoDB DEFAULT CHARSET=latin1
340
/*!50100 PARTITION BY HASH (YEAR(f_date))
341
(PARTITION p0 ENGINE = InnoDB) */
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 20 Using where
347
# check read single success: 1
348
# check read all success: 1
349
# check read row by row success: 1
350
# 1.2.5 COALESCE of last partition --> must fail
351
ALTER TABLE t1 COALESCE PARTITION 1;
352
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
353
# 1.2.6 Remove partitioning
354
ALTER TABLE t1 REMOVE PARTITIONING;
355
SHOW CREATE TABLE t1;
357
t1 CREATE TABLE `t1` (
358
`f_date` date DEFAULT NULL,
359
`f_varchar` varchar(30) DEFAULT NULL
360
) ENGINE=InnoDB DEFAULT CHARSET=latin1
362
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
363
id select_type table partitions type possible_keys key key_len ref rows Extra
364
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
365
# check read single success: 1
366
# check read all success: 1
367
# check read row by row success: 1
368
# 1.2.7 Remove partitioning from not partitioned table --> ????
369
ALTER TABLE t1 REMOVE PARTITIONING;
371
# Attention: There are unused files.
372
# Either the DROP TABLE or a preceding ALTER TABLE
373
# <alter partitioning> worked incomplete.
376
--- not determined ---
378
#========================================================================
379
# 2. Partition management commands on KEY partitioned table
380
#========================================================================
381
DROP TABLE IF EXISTS t1;
387
f_charbig VARCHAR(1000)
389
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
390
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
391
SHOW CREATE TABLE t1;
393
t1 CREATE TABLE `t1` (
394
`f_int1` int(11) DEFAULT NULL,
395
`f_int2` int(11) DEFAULT NULL,
396
`f_char1` char(20) DEFAULT NULL,
397
`f_char2` char(20) DEFAULT NULL,
398
`f_charbig` varchar(1000) DEFAULT NULL
399
) ENGINE=InnoDB DEFAULT CHARSET=latin1
401
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
402
id select_type table partitions type possible_keys key key_len ref rows Extra
403
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
404
# check read single success: 1
405
# check read all success: 1
406
# check read row by row success: 1
407
#------------------------------------------------------------------------
408
# 2.1 Increase number of PARTITIONS
409
# Some negative testcases are omitted (already checked with HASH).
410
#------------------------------------------------------------------------
411
# 2.1.1 Assign KEY partitioning
412
ALTER TABLE t1 PARTITION BY KEY(f_int1);
413
SHOW CREATE TABLE t1;
415
t1 CREATE TABLE `t1` (
416
`f_int1` int(11) DEFAULT NULL,
417
`f_int2` int(11) DEFAULT NULL,
418
`f_char1` char(20) DEFAULT NULL,
419
`f_char2` char(20) DEFAULT NULL,
420
`f_charbig` varchar(1000) DEFAULT NULL
421
) ENGINE=InnoDB DEFAULT CHARSET=latin1
422
/*!50100 PARTITION BY KEY (f_int1) */
425
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
426
id select_type table partitions type possible_keys key key_len ref rows Extra
427
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
428
# check read single success: 1
429
# check read all success: 1
430
# check read row by row success: 1
431
# 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
432
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
433
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
434
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
435
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
436
# 2.1.3 Add two named partitions + test
437
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
438
SHOW CREATE TABLE t1;
440
t1 CREATE TABLE `t1` (
441
`f_int1` int(11) DEFAULT NULL,
442
`f_int2` int(11) DEFAULT NULL,
443
`f_char1` char(20) DEFAULT NULL,
444
`f_char2` char(20) DEFAULT NULL,
445
`f_charbig` varchar(1000) DEFAULT NULL
446
) ENGINE=InnoDB DEFAULT CHARSET=latin1
447
/*!50100 PARTITION BY KEY (f_int1)
448
(PARTITION p0 ENGINE = InnoDB,
449
PARTITION part1 ENGINE = InnoDB,
450
PARTITION part7 ENGINE = InnoDB) */
453
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
454
id select_type table partitions type possible_keys key key_len ref rows Extra
455
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
456
# check read single success: 1
457
# check read all success: 1
458
# check read row by row success: 1
459
# 2.1.4 Add one named partition + test
460
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
461
SHOW CREATE TABLE t1;
463
t1 CREATE TABLE `t1` (
464
`f_int1` int(11) DEFAULT NULL,
465
`f_int2` int(11) DEFAULT NULL,
466
`f_char1` char(20) DEFAULT NULL,
467
`f_char2` char(20) DEFAULT NULL,
468
`f_charbig` varchar(1000) DEFAULT NULL
469
) ENGINE=InnoDB DEFAULT CHARSET=latin1
470
/*!50100 PARTITION BY KEY (f_int1)
471
(PARTITION p0 ENGINE = InnoDB,
472
PARTITION part1 ENGINE = InnoDB,
473
PARTITION part7 ENGINE = InnoDB,
474
PARTITION part2 ENGINE = InnoDB) */
477
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
478
id select_type table partitions type possible_keys key key_len ref rows Extra
479
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
480
# check read single success: 1
481
# check read all success: 1
482
# check read row by row success: 1
483
# 2.1.5 Add four not named partitions + test
484
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
485
SHOW CREATE TABLE t1;
487
t1 CREATE TABLE `t1` (
488
`f_int1` int(11) DEFAULT NULL,
489
`f_int2` int(11) DEFAULT NULL,
490
`f_char1` char(20) DEFAULT NULL,
491
`f_char2` char(20) DEFAULT NULL,
492
`f_charbig` varchar(1000) DEFAULT NULL
493
) ENGINE=InnoDB DEFAULT CHARSET=latin1
494
/*!50100 PARTITION BY KEY (f_int1)
495
(PARTITION p0 ENGINE = InnoDB,
496
PARTITION part1 ENGINE = InnoDB,
497
PARTITION part7 ENGINE = InnoDB,
498
PARTITION part2 ENGINE = InnoDB,
499
PARTITION p4 ENGINE = InnoDB,
500
PARTITION p5 ENGINE = InnoDB,
501
PARTITION p6 ENGINE = InnoDB,
502
PARTITION p7 ENGINE = InnoDB) */
505
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
506
id select_type table partitions type possible_keys key key_len ref rows Extra
507
1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 20 Using where
508
# check read single success: 1
509
# check read all success: 1
510
# check read row by row success: 1
511
#------------------------------------------------------------------------
512
# 2.2 Decrease number of PARTITIONS
513
# Some negative testcases are omitted (already checked with HASH).
514
#------------------------------------------------------------------------
515
# 2.2.1 DROP PARTITION is not supported for KEY --> must fail
516
ALTER TABLE t1 DROP PARTITION part1;
517
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
518
# 2.2.4 COALESCE one partition + test loop
519
ALTER TABLE t1 COALESCE PARTITION 1;
520
SHOW CREATE TABLE t1;
522
t1 CREATE TABLE `t1` (
523
`f_int1` int(11) DEFAULT NULL,
524
`f_int2` int(11) DEFAULT NULL,
525
`f_char1` char(20) DEFAULT NULL,
526
`f_char2` char(20) DEFAULT NULL,
527
`f_charbig` varchar(1000) DEFAULT NULL
528
) ENGINE=InnoDB DEFAULT CHARSET=latin1
529
/*!50100 PARTITION BY KEY (f_int1)
530
(PARTITION p0 ENGINE = InnoDB,
531
PARTITION part1 ENGINE = InnoDB,
532
PARTITION part7 ENGINE = InnoDB,
533
PARTITION part2 ENGINE = InnoDB,
534
PARTITION p4 ENGINE = InnoDB,
535
PARTITION p5 ENGINE = InnoDB,
536
PARTITION p6 ENGINE = InnoDB) */
539
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
540
id select_type table partitions type possible_keys key key_len ref rows Extra
541
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
542
# check read single success: 1
543
# check read all success: 1
544
# check read row by row success: 1
545
ALTER TABLE t1 COALESCE PARTITION 1;
546
SHOW CREATE TABLE t1;
548
t1 CREATE TABLE `t1` (
549
`f_int1` int(11) DEFAULT NULL,
550
`f_int2` int(11) 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=latin1
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) */
564
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
565
id select_type table partitions type possible_keys key key_len ref rows Extra
566
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
567
# check read single success: 1
568
# check read all success: 1
569
# check read row by row success: 1
570
ALTER TABLE t1 COALESCE PARTITION 1;
571
SHOW CREATE TABLE t1;
573
t1 CREATE TABLE `t1` (
574
`f_int1` int(11) DEFAULT NULL,
575
`f_int2` int(11) DEFAULT NULL,
576
`f_char1` char(20) DEFAULT NULL,
577
`f_char2` char(20) DEFAULT NULL,
578
`f_charbig` varchar(1000) DEFAULT NULL
579
) ENGINE=InnoDB DEFAULT CHARSET=latin1
580
/*!50100 PARTITION BY KEY (f_int1)
581
(PARTITION p0 ENGINE = InnoDB,
582
PARTITION part1 ENGINE = InnoDB,
583
PARTITION part7 ENGINE = InnoDB,
584
PARTITION part2 ENGINE = InnoDB,
585
PARTITION p4 ENGINE = InnoDB) */
588
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
589
id select_type table partitions type possible_keys key key_len ref rows Extra
590
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 23 Using where
591
# check read single success: 1
592
# check read all success: 1
593
# check read row by row success: 1
594
ALTER TABLE t1 COALESCE PARTITION 1;
595
SHOW CREATE TABLE t1;
597
t1 CREATE TABLE `t1` (
598
`f_int1` int(11) DEFAULT NULL,
599
`f_int2` int(11) DEFAULT NULL,
600
`f_char1` char(20) DEFAULT NULL,
601
`f_char2` char(20) DEFAULT NULL,
602
`f_charbig` varchar(1000) DEFAULT NULL
603
) ENGINE=InnoDB DEFAULT CHARSET=latin1
604
/*!50100 PARTITION BY KEY (f_int1)
605
(PARTITION p0 ENGINE = InnoDB,
606
PARTITION part1 ENGINE = InnoDB,
607
PARTITION part7 ENGINE = InnoDB,
608
PARTITION part2 ENGINE = InnoDB) */
611
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
612
id select_type table partitions type possible_keys key key_len ref rows Extra
613
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
614
# check read single success: 1
615
# check read all success: 1
616
# check read row by row success: 1
617
ALTER TABLE t1 COALESCE PARTITION 1;
618
SHOW CREATE TABLE t1;
620
t1 CREATE TABLE `t1` (
621
`f_int1` int(11) DEFAULT NULL,
622
`f_int2` int(11) DEFAULT NULL,
623
`f_char1` char(20) DEFAULT NULL,
624
`f_char2` char(20) DEFAULT NULL,
625
`f_charbig` varchar(1000) DEFAULT NULL
626
) ENGINE=InnoDB DEFAULT CHARSET=latin1
627
/*!50100 PARTITION BY KEY (f_int1)
628
(PARTITION p0 ENGINE = InnoDB,
629
PARTITION part1 ENGINE = InnoDB,
630
PARTITION part7 ENGINE = InnoDB) */
633
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
634
id select_type table partitions type possible_keys key key_len ref rows Extra
635
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
636
# check read single success: 1
637
# check read all success: 1
638
# check read row by row success: 1
639
ALTER TABLE t1 COALESCE PARTITION 1;
640
SHOW CREATE TABLE t1;
642
t1 CREATE TABLE `t1` (
643
`f_int1` int(11) DEFAULT NULL,
644
`f_int2` int(11) DEFAULT NULL,
645
`f_char1` char(20) DEFAULT NULL,
646
`f_char2` char(20) DEFAULT NULL,
647
`f_charbig` varchar(1000) DEFAULT NULL
648
) ENGINE=InnoDB DEFAULT CHARSET=latin1
649
/*!50100 PARTITION BY KEY (f_int1)
650
(PARTITION p0 ENGINE = InnoDB,
651
PARTITION part1 ENGINE = InnoDB) */
654
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
655
id select_type table partitions type possible_keys key key_len ref rows Extra
656
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
657
# check read single success: 1
658
# check read all success: 1
659
# check read row by row success: 1
660
ALTER TABLE t1 COALESCE PARTITION 1;
661
SHOW CREATE TABLE t1;
663
t1 CREATE TABLE `t1` (
664
`f_int1` int(11) DEFAULT NULL,
665
`f_int2` int(11) DEFAULT NULL,
666
`f_char1` char(20) DEFAULT NULL,
667
`f_char2` char(20) DEFAULT NULL,
668
`f_charbig` varchar(1000) DEFAULT NULL
669
) ENGINE=InnoDB DEFAULT CHARSET=latin1
670
/*!50100 PARTITION BY KEY (f_int1)
671
(PARTITION p0 ENGINE = InnoDB) */
674
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
675
id select_type table partitions type possible_keys key key_len ref rows Extra
676
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
677
# check read single success: 1
678
# check read all success: 1
679
# check read row by row success: 1
680
# 2.2.5 COALESCE of last partition --> must fail
681
ALTER TABLE t1 COALESCE PARTITION 1;
682
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
683
# 2.2.6 Remove partitioning
684
ALTER TABLE t1 REMOVE PARTITIONING;
685
SHOW CREATE TABLE t1;
687
t1 CREATE TABLE `t1` (
688
`f_int1` int(11) DEFAULT NULL,
689
`f_int2` int(11) DEFAULT NULL,
690
`f_char1` char(20) DEFAULT NULL,
691
`f_char2` char(20) DEFAULT NULL,
692
`f_charbig` varchar(1000) DEFAULT NULL
693
) ENGINE=InnoDB DEFAULT CHARSET=latin1
695
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
696
id select_type table partitions type possible_keys key key_len ref rows Extra
697
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
698
# check read single success: 1
699
# check read all success: 1
700
# check read row by row success: 1
701
# 2.2.7 Remove partitioning from not partitioned table --> ????
702
ALTER TABLE t1 REMOVE PARTITIONING;
704
# Attention: There are unused files.
705
# Either the DROP TABLE or a preceding ALTER TABLE
706
# <alter partitioning> worked incomplete.
709
--- not determined ---
710
DROP VIEW IF EXISTS v1;
711
DROP TABLE IF EXISTS t1;
712
DROP TABLE IF EXISTS t0_aux;
713
DROP TABLE IF EXISTS t0_definition;
714
DROP TABLE IF EXISTS t0_template;