2
SET @@session.default_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
61
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
62
id select_type table partitions type possible_keys key key_len ref rows Extra
63
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
64
# check read single success: 1
65
# check read all success: 1
66
# check read row by row success: 1
67
#------------------------------------------------------------------------
68
# 1.1 Increase number of PARTITIONS
69
#------------------------------------------------------------------------
70
# 1.1.1 ADD PARTITION to not partitioned table --> must fail
71
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
72
ERROR HY000: Partition management on a not partitioned table is not possible
73
# 1.1.2 Assign HASH partitioning
74
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
77
t1 CREATE TABLE `t1` (
78
`f_date` date DEFAULT NULL,
79
`f_varchar` varchar(30) DEFAULT NULL
80
) ENGINE=InnoDB DEFAULT CHARSET=latin1
81
/*!50100 PARTITION BY HASH (YEAR(f_date)) */
85
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
86
id select_type table partitions type possible_keys key key_len ref rows Extra
87
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
88
# check read single success: 1
89
# check read all success: 1
90
# check read row by row success: 1
91
# 1.1.3 Assign other HASH partitioning to already partitioned table
92
# + test and switch back + test
93
ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
96
t1 CREATE TABLE `t1` (
97
`f_date` date DEFAULT NULL,
98
`f_varchar` varchar(30) DEFAULT NULL
99
) ENGINE=InnoDB DEFAULT CHARSET=latin1
100
/*!50100 PARTITION BY HASH (DAYOFYEAR(f_date)) */
104
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
105
id select_type table partitions type possible_keys key key_len ref rows Extra
106
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
107
# check read single success: 1
108
# check read all success: 1
109
# check read row by row success: 1
110
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
111
SHOW CREATE TABLE t1;
113
t1 CREATE TABLE `t1` (
114
`f_date` date DEFAULT NULL,
115
`f_varchar` varchar(30) DEFAULT NULL
116
) ENGINE=InnoDB DEFAULT CHARSET=latin1
117
/*!50100 PARTITION BY HASH (YEAR(f_date)) */
121
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
122
id select_type table partitions type possible_keys key key_len ref rows Extra
123
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
124
# check read single success: 1
125
# check read all success: 1
126
# check read row by row success: 1
127
# 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
128
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
129
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
130
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
131
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
132
# 1.1.5 Add two named partitions + test
133
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
134
SHOW CREATE TABLE t1;
136
t1 CREATE TABLE `t1` (
137
`f_date` date DEFAULT NULL,
138
`f_varchar` varchar(30) DEFAULT NULL
139
) ENGINE=InnoDB DEFAULT CHARSET=latin1
140
/*!50100 PARTITION BY HASH (YEAR(f_date))
141
(PARTITION p0 ENGINE = InnoDB,
142
PARTITION part1 ENGINE = InnoDB,
143
PARTITION part7 ENGINE = InnoDB) */
149
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
150
id select_type table partitions type possible_keys key key_len ref rows Extra
151
1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
152
# check read single success: 1
153
# check read all success: 1
154
# check read row by row success: 1
155
# 1.1.6 Add two named partitions, name clash --> must fail
156
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
157
ERROR HY000: Duplicate partition name part1
158
# 1.1.7 Add one named partition + test
159
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
160
SHOW CREATE TABLE t1;
162
t1 CREATE TABLE `t1` (
163
`f_date` date DEFAULT NULL,
164
`f_varchar` varchar(30) DEFAULT NULL
165
) ENGINE=InnoDB DEFAULT CHARSET=latin1
166
/*!50100 PARTITION BY HASH (YEAR(f_date))
167
(PARTITION p0 ENGINE = InnoDB,
168
PARTITION part1 ENGINE = InnoDB,
169
PARTITION part7 ENGINE = InnoDB,
170
PARTITION part2 ENGINE = InnoDB) */
177
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
178
id select_type table partitions type possible_keys key key_len ref rows Extra
179
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
180
# check read single success: 1
181
# check read all success: 1
182
# check read row by row success: 1
183
# 1.1.8 Add four not named partitions + test
184
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
185
SHOW CREATE TABLE t1;
187
t1 CREATE TABLE `t1` (
188
`f_date` date DEFAULT NULL,
189
`f_varchar` varchar(30) DEFAULT NULL
190
) ENGINE=InnoDB DEFAULT CHARSET=latin1
191
/*!50100 PARTITION BY HASH (YEAR(f_date))
192
(PARTITION p0 ENGINE = InnoDB,
193
PARTITION part1 ENGINE = InnoDB,
194
PARTITION part7 ENGINE = InnoDB,
195
PARTITION part2 ENGINE = InnoDB,
196
PARTITION p4 ENGINE = InnoDB,
197
PARTITION p5 ENGINE = InnoDB,
198
PARTITION p6 ENGINE = InnoDB,
199
PARTITION p7 ENGINE = InnoDB) */
210
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
211
id select_type table partitions type possible_keys key key_len ref rows Extra
212
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
213
# check read single success: 1
214
# check read all success: 1
215
# check read row by row success: 1
216
#------------------------------------------------------------------------
217
# 1.2 Decrease number of PARTITIONS
218
#------------------------------------------------------------------------
219
# 1.2.1 DROP PARTITION is not supported for HASH --> must fail
220
ALTER TABLE t1 DROP PARTITION part1;
221
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
222
# 1.2.2 COALESCE PARTITION partitionname is not supported
223
ALTER TABLE t1 COALESCE PARTITION part1;
224
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
225
# 1.2.3 Decrease by 0 is non sense --> must fail
226
ALTER TABLE t1 COALESCE PARTITION 0;
227
ERROR HY000: At least one partition must be coalesced
228
# 1.2.4 COALESCE one partition + test loop
229
ALTER TABLE t1 COALESCE PARTITION 1;
230
SHOW CREATE TABLE t1;
232
t1 CREATE TABLE `t1` (
233
`f_date` date DEFAULT NULL,
234
`f_varchar` varchar(30) DEFAULT NULL
235
) ENGINE=InnoDB DEFAULT CHARSET=latin1
236
/*!50100 PARTITION BY HASH (YEAR(f_date))
237
(PARTITION p0 ENGINE = InnoDB,
238
PARTITION part1 ENGINE = InnoDB,
239
PARTITION part7 ENGINE = InnoDB,
240
PARTITION part2 ENGINE = InnoDB,
241
PARTITION p4 ENGINE = InnoDB,
242
PARTITION p5 ENGINE = InnoDB,
243
PARTITION p6 ENGINE = InnoDB) */
253
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
254
id select_type table partitions type possible_keys key key_len ref rows Extra
255
1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
256
# check read single success: 1
257
# check read all success: 1
258
# check read row by row success: 1
259
ALTER TABLE t1 COALESCE PARTITION 1;
260
SHOW CREATE TABLE t1;
262
t1 CREATE TABLE `t1` (
263
`f_date` date DEFAULT NULL,
264
`f_varchar` varchar(30) DEFAULT NULL
265
) ENGINE=InnoDB DEFAULT CHARSET=latin1
266
/*!50100 PARTITION BY HASH (YEAR(f_date))
267
(PARTITION p0 ENGINE = InnoDB,
268
PARTITION part1 ENGINE = InnoDB,
269
PARTITION part7 ENGINE = InnoDB,
270
PARTITION part2 ENGINE = InnoDB,
271
PARTITION p4 ENGINE = InnoDB,
272
PARTITION p5 ENGINE = InnoDB) */
281
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
282
id select_type table partitions type possible_keys key key_len ref rows Extra
283
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
284
# check read single success: 1
285
# check read all success: 1
286
# check read row by row success: 1
287
ALTER TABLE t1 COALESCE PARTITION 1;
288
SHOW CREATE TABLE t1;
290
t1 CREATE TABLE `t1` (
291
`f_date` date DEFAULT NULL,
292
`f_varchar` varchar(30) DEFAULT NULL
293
) ENGINE=InnoDB DEFAULT CHARSET=latin1
294
/*!50100 PARTITION BY HASH (YEAR(f_date))
295
(PARTITION p0 ENGINE = InnoDB,
296
PARTITION part1 ENGINE = InnoDB,
297
PARTITION part7 ENGINE = InnoDB,
298
PARTITION part2 ENGINE = InnoDB,
299
PARTITION p4 ENGINE = InnoDB) */
307
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
308
id select_type table partitions type possible_keys key key_len ref rows Extra
309
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
310
# check read single success: 1
311
# check read all success: 1
312
# check read row by row success: 1
313
ALTER TABLE t1 COALESCE PARTITION 1;
314
SHOW CREATE TABLE t1;
316
t1 CREATE TABLE `t1` (
317
`f_date` date DEFAULT NULL,
318
`f_varchar` varchar(30) DEFAULT NULL
319
) ENGINE=InnoDB DEFAULT CHARSET=latin1
320
/*!50100 PARTITION BY HASH (YEAR(f_date))
321
(PARTITION p0 ENGINE = InnoDB,
322
PARTITION part1 ENGINE = InnoDB,
323
PARTITION part7 ENGINE = InnoDB,
324
PARTITION part2 ENGINE = InnoDB) */
331
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
332
id select_type table partitions type possible_keys key key_len ref rows Extra
333
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
334
# check read single success: 1
335
# check read all success: 1
336
# check read row by row success: 1
337
ALTER TABLE t1 COALESCE PARTITION 1;
338
SHOW CREATE TABLE t1;
340
t1 CREATE TABLE `t1` (
341
`f_date` date DEFAULT NULL,
342
`f_varchar` varchar(30) DEFAULT NULL
343
) ENGINE=InnoDB DEFAULT CHARSET=latin1
344
/*!50100 PARTITION BY HASH (YEAR(f_date))
345
(PARTITION p0 ENGINE = InnoDB,
346
PARTITION part1 ENGINE = InnoDB,
347
PARTITION part7 ENGINE = InnoDB) */
353
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
354
id select_type table partitions type possible_keys key key_len ref rows Extra
355
1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
356
# check read single success: 1
357
# check read all success: 1
358
# check read row by row success: 1
359
ALTER TABLE t1 COALESCE PARTITION 1;
360
SHOW CREATE TABLE t1;
362
t1 CREATE TABLE `t1` (
363
`f_date` date DEFAULT NULL,
364
`f_varchar` varchar(30) DEFAULT NULL
365
) ENGINE=InnoDB DEFAULT CHARSET=latin1
366
/*!50100 PARTITION BY HASH (YEAR(f_date))
367
(PARTITION p0 ENGINE = InnoDB,
368
PARTITION part1 ENGINE = InnoDB) */
373
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
374
id select_type table partitions type possible_keys key key_len ref rows Extra
375
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
376
# check read single success: 1
377
# check read all success: 1
378
# check read row by row success: 1
379
ALTER TABLE t1 COALESCE PARTITION 1;
380
SHOW CREATE TABLE t1;
382
t1 CREATE TABLE `t1` (
383
`f_date` date DEFAULT NULL,
384
`f_varchar` varchar(30) DEFAULT NULL
385
) ENGINE=InnoDB DEFAULT CHARSET=latin1
386
/*!50100 PARTITION BY HASH (YEAR(f_date))
387
(PARTITION p0 ENGINE = InnoDB) */
391
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
392
id select_type table partitions type possible_keys key key_len ref rows Extra
393
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
394
# check read single success: 1
395
# check read all success: 1
396
# check read row by row success: 1
397
# 1.2.5 COALESCE of last partition --> must fail
398
ALTER TABLE t1 COALESCE PARTITION 1;
399
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
400
# 1.2.6 Remove partitioning
401
ALTER TABLE t1 REMOVE PARTITIONING;
402
SHOW CREATE TABLE t1;
404
t1 CREATE TABLE `t1` (
405
`f_date` date DEFAULT NULL,
406
`f_varchar` varchar(30) DEFAULT NULL
407
) ENGINE=InnoDB DEFAULT CHARSET=latin1
410
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
411
id select_type table partitions type possible_keys key key_len ref rows Extra
412
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
413
# check read single success: 1
414
# check read all success: 1
415
# check read row by row success: 1
416
# 1.2.7 Remove partitioning from not partitioned table --> ????
417
ALTER TABLE t1 REMOVE PARTITIONING;
418
ERROR HY000: Partition management on a not partitioned table is not possible
420
# Attention: There are unused files.
421
# Either the DROP TABLE or a preceding ALTER TABLE
422
# <alter partitioning> worked incomplete.
425
--- not determined ---
427
#========================================================================
428
# 2. Partition management commands on KEY partitioned table
429
#========================================================================
430
DROP TABLE IF EXISTS t1;
436
f_charbig VARCHAR(1000)
438
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
439
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
440
SHOW CREATE TABLE t1;
442
t1 CREATE TABLE `t1` (
443
`f_int1` int(11) DEFAULT NULL,
444
`f_int2` int(11) DEFAULT NULL,
445
`f_char1` char(20) DEFAULT NULL,
446
`f_char2` char(20) DEFAULT NULL,
447
`f_charbig` varchar(1000) DEFAULT NULL
448
) ENGINE=InnoDB DEFAULT CHARSET=latin1
451
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
452
id select_type table partitions type possible_keys key key_len ref rows Extra
453
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
454
# check read single success: 1
455
# check read all success: 1
456
# check read row by row success: 1
457
#------------------------------------------------------------------------
458
# 2.1 Increase number of PARTITIONS
459
# Some negative testcases are omitted (already checked with HASH).
460
#------------------------------------------------------------------------
461
# 2.1.1 Assign KEY partitioning
462
ALTER TABLE t1 PARTITION BY KEY(f_int1);
463
SHOW CREATE TABLE t1;
465
t1 CREATE TABLE `t1` (
466
`f_int1` int(11) DEFAULT NULL,
467
`f_int2` int(11) DEFAULT NULL,
468
`f_char1` char(20) DEFAULT NULL,
469
`f_char2` char(20) DEFAULT NULL,
470
`f_charbig` varchar(1000) DEFAULT NULL
471
) ENGINE=InnoDB DEFAULT CHARSET=latin1
472
/*!50100 PARTITION BY KEY (f_int1) */
476
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
477
id select_type table partitions type possible_keys key key_len ref rows Extra
478
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
479
# check read single success: 1
480
# check read all success: 1
481
# check read row by row success: 1
482
# 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
483
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
484
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
485
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
486
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
487
# 2.1.3 Add two named partitions + test
488
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
489
SHOW CREATE TABLE t1;
491
t1 CREATE TABLE `t1` (
492
`f_int1` int(11) DEFAULT NULL,
493
`f_int2` int(11) DEFAULT NULL,
494
`f_char1` char(20) DEFAULT NULL,
495
`f_char2` char(20) DEFAULT NULL,
496
`f_charbig` varchar(1000) DEFAULT NULL
497
) ENGINE=InnoDB DEFAULT CHARSET=latin1
498
/*!50100 PARTITION BY KEY (f_int1)
499
(PARTITION p0 ENGINE = InnoDB,
500
PARTITION part1 ENGINE = InnoDB,
501
PARTITION part7 ENGINE = InnoDB) */
507
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
508
id select_type table partitions type possible_keys key key_len ref rows Extra
509
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
510
# check read single success: 1
511
# check read all success: 1
512
# check read row by row success: 1
513
# 2.1.4 Add one named partition + test
514
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
515
SHOW CREATE TABLE t1;
517
t1 CREATE TABLE `t1` (
518
`f_int1` int(11) DEFAULT NULL,
519
`f_int2` int(11) DEFAULT NULL,
520
`f_char1` char(20) DEFAULT NULL,
521
`f_char2` char(20) DEFAULT NULL,
522
`f_charbig` varchar(1000) DEFAULT NULL
523
) ENGINE=InnoDB DEFAULT CHARSET=latin1
524
/*!50100 PARTITION BY KEY (f_int1)
525
(PARTITION p0 ENGINE = InnoDB,
526
PARTITION part1 ENGINE = InnoDB,
527
PARTITION part7 ENGINE = InnoDB,
528
PARTITION part2 ENGINE = InnoDB) */
535
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
536
id select_type table partitions type possible_keys key key_len ref rows Extra
537
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
538
# check read single success: 1
539
# check read all success: 1
540
# check read row by row success: 1
541
# 2.1.5 Add four not named partitions + test
542
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
543
SHOW CREATE TABLE t1;
545
t1 CREATE TABLE `t1` (
546
`f_int1` int(11) DEFAULT NULL,
547
`f_int2` int(11) DEFAULT NULL,
548
`f_char1` char(20) DEFAULT NULL,
549
`f_char2` char(20) DEFAULT NULL,
550
`f_charbig` varchar(1000) DEFAULT NULL
551
) ENGINE=InnoDB DEFAULT CHARSET=latin1
552
/*!50100 PARTITION BY KEY (f_int1)
553
(PARTITION p0 ENGINE = InnoDB,
554
PARTITION part1 ENGINE = InnoDB,
555
PARTITION part7 ENGINE = InnoDB,
556
PARTITION part2 ENGINE = InnoDB,
557
PARTITION p4 ENGINE = InnoDB,
558
PARTITION p5 ENGINE = InnoDB,
559
PARTITION p6 ENGINE = InnoDB,
560
PARTITION p7 ENGINE = InnoDB) */
571
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
572
id select_type table partitions type possible_keys key key_len ref rows Extra
573
1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
574
# check read single success: 1
575
# check read all success: 1
576
# check read row by row success: 1
577
#------------------------------------------------------------------------
578
# 2.2 Decrease number of PARTITIONS
579
# Some negative testcases are omitted (already checked with HASH).
580
#------------------------------------------------------------------------
581
# 2.2.1 DROP PARTITION is not supported for KEY --> must fail
582
ALTER TABLE t1 DROP PARTITION part1;
583
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
584
# 2.2.4 COALESCE one partition + test loop
585
ALTER TABLE t1 COALESCE PARTITION 1;
586
SHOW CREATE TABLE t1;
588
t1 CREATE TABLE `t1` (
589
`f_int1` int(11) DEFAULT NULL,
590
`f_int2` int(11) DEFAULT NULL,
591
`f_char1` char(20) DEFAULT NULL,
592
`f_char2` char(20) DEFAULT NULL,
593
`f_charbig` varchar(1000) DEFAULT NULL
594
) ENGINE=InnoDB DEFAULT CHARSET=latin1
595
/*!50100 PARTITION BY KEY (f_int1)
596
(PARTITION p0 ENGINE = InnoDB,
597
PARTITION part1 ENGINE = InnoDB,
598
PARTITION part7 ENGINE = InnoDB,
599
PARTITION part2 ENGINE = InnoDB,
600
PARTITION p4 ENGINE = InnoDB,
601
PARTITION p5 ENGINE = InnoDB,
602
PARTITION p6 ENGINE = InnoDB) */
612
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
613
id select_type table partitions type possible_keys key key_len ref rows Extra
614
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
615
# check read single success: 1
616
# check read all success: 1
617
# check read row by row success: 1
618
ALTER TABLE t1 COALESCE PARTITION 1;
619
SHOW CREATE TABLE t1;
621
t1 CREATE TABLE `t1` (
622
`f_int1` int(11) DEFAULT NULL,
623
`f_int2` int(11) DEFAULT NULL,
624
`f_char1` char(20) DEFAULT NULL,
625
`f_char2` char(20) DEFAULT NULL,
626
`f_charbig` varchar(1000) DEFAULT NULL
627
) ENGINE=InnoDB DEFAULT CHARSET=latin1
628
/*!50100 PARTITION BY KEY (f_int1)
629
(PARTITION p0 ENGINE = InnoDB,
630
PARTITION part1 ENGINE = InnoDB,
631
PARTITION part7 ENGINE = InnoDB,
632
PARTITION part2 ENGINE = InnoDB,
633
PARTITION p4 ENGINE = InnoDB,
634
PARTITION p5 ENGINE = InnoDB) */
643
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
644
id select_type table partitions type possible_keys key key_len ref rows Extra
645
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 3 Using where
646
# check read single success: 1
647
# check read all success: 1
648
# check read row by row success: 1
649
ALTER TABLE t1 COALESCE PARTITION 1;
650
SHOW CREATE TABLE t1;
652
t1 CREATE TABLE `t1` (
653
`f_int1` int(11) DEFAULT NULL,
654
`f_int2` int(11) DEFAULT NULL,
655
`f_char1` char(20) DEFAULT NULL,
656
`f_char2` char(20) DEFAULT NULL,
657
`f_charbig` varchar(1000) DEFAULT NULL
658
) ENGINE=InnoDB DEFAULT CHARSET=latin1
659
/*!50100 PARTITION BY KEY (f_int1)
660
(PARTITION p0 ENGINE = InnoDB,
661
PARTITION part1 ENGINE = InnoDB,
662
PARTITION part7 ENGINE = InnoDB,
663
PARTITION part2 ENGINE = InnoDB,
664
PARTITION p4 ENGINE = InnoDB) */
672
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
673
id select_type table partitions type possible_keys key key_len ref rows Extra
674
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 10 Using where
675
# check read single success: 1
676
# check read all success: 1
677
# check read row by row success: 1
678
ALTER TABLE t1 COALESCE PARTITION 1;
679
SHOW CREATE TABLE t1;
681
t1 CREATE TABLE `t1` (
682
`f_int1` int(11) DEFAULT NULL,
683
`f_int2` int(11) DEFAULT NULL,
684
`f_char1` char(20) DEFAULT NULL,
685
`f_char2` char(20) DEFAULT NULL,
686
`f_charbig` varchar(1000) DEFAULT NULL
687
) ENGINE=InnoDB DEFAULT CHARSET=latin1
688
/*!50100 PARTITION BY KEY (f_int1)
689
(PARTITION p0 ENGINE = InnoDB,
690
PARTITION part1 ENGINE = InnoDB,
691
PARTITION part7 ENGINE = InnoDB,
692
PARTITION part2 ENGINE = InnoDB) */
699
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
700
id select_type table partitions type possible_keys key key_len ref rows Extra
701
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
702
# check read single success: 1
703
# check read all success: 1
704
# check read row by row success: 1
705
ALTER TABLE t1 COALESCE PARTITION 1;
706
SHOW CREATE TABLE t1;
708
t1 CREATE TABLE `t1` (
709
`f_int1` int(11) DEFAULT NULL,
710
`f_int2` int(11) DEFAULT NULL,
711
`f_char1` char(20) DEFAULT NULL,
712
`f_char2` char(20) DEFAULT NULL,
713
`f_charbig` varchar(1000) DEFAULT NULL
714
) ENGINE=InnoDB DEFAULT CHARSET=latin1
715
/*!50100 PARTITION BY KEY (f_int1)
716
(PARTITION p0 ENGINE = InnoDB,
717
PARTITION part1 ENGINE = InnoDB,
718
PARTITION part7 ENGINE = InnoDB) */
724
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
725
id select_type table partitions type possible_keys key key_len ref rows Extra
726
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
727
# check read single success: 1
728
# check read all success: 1
729
# check read row by row success: 1
730
ALTER TABLE t1 COALESCE PARTITION 1;
731
SHOW CREATE TABLE t1;
733
t1 CREATE TABLE `t1` (
734
`f_int1` int(11) DEFAULT NULL,
735
`f_int2` int(11) DEFAULT NULL,
736
`f_char1` char(20) DEFAULT NULL,
737
`f_char2` char(20) DEFAULT NULL,
738
`f_charbig` varchar(1000) DEFAULT NULL
739
) ENGINE=InnoDB DEFAULT CHARSET=latin1
740
/*!50100 PARTITION BY KEY (f_int1)
741
(PARTITION p0 ENGINE = InnoDB,
742
PARTITION part1 ENGINE = InnoDB) */
747
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
748
id select_type table partitions type possible_keys key key_len ref rows Extra
749
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
750
# check read single success: 1
751
# check read all success: 1
752
# check read row by row success: 1
753
ALTER TABLE t1 COALESCE PARTITION 1;
754
SHOW CREATE TABLE t1;
756
t1 CREATE TABLE `t1` (
757
`f_int1` int(11) DEFAULT NULL,
758
`f_int2` int(11) DEFAULT NULL,
759
`f_char1` char(20) DEFAULT NULL,
760
`f_char2` char(20) DEFAULT NULL,
761
`f_charbig` varchar(1000) DEFAULT NULL
762
) ENGINE=InnoDB DEFAULT CHARSET=latin1
763
/*!50100 PARTITION BY KEY (f_int1)
764
(PARTITION p0 ENGINE = InnoDB) */
768
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
769
id select_type table partitions type possible_keys key key_len ref rows Extra
770
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
771
# check read single success: 1
772
# check read all success: 1
773
# check read row by row success: 1
774
# 2.2.5 COALESCE of last partition --> must fail
775
ALTER TABLE t1 COALESCE PARTITION 1;
776
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
777
# 2.2.6 Remove partitioning
778
ALTER TABLE t1 REMOVE PARTITIONING;
779
SHOW CREATE TABLE t1;
781
t1 CREATE TABLE `t1` (
782
`f_int1` int(11) DEFAULT NULL,
783
`f_int2` int(11) DEFAULT NULL,
784
`f_char1` char(20) DEFAULT NULL,
785
`f_char2` char(20) DEFAULT NULL,
786
`f_charbig` varchar(1000) DEFAULT NULL
787
) ENGINE=InnoDB DEFAULT CHARSET=latin1
790
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
791
id select_type table partitions type possible_keys key key_len ref rows Extra
792
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
793
# check read single success: 1
794
# check read all success: 1
795
# check read row by row success: 1
796
# 2.2.7 Remove partitioning from not partitioned table --> ????
797
ALTER TABLE t1 REMOVE PARTITIONING;
798
ERROR HY000: Partition management on a not partitioned table is not possible
800
# Attention: There are unused files.
801
# Either the DROP TABLE or a preceding ALTER TABLE
802
# <alter partitioning> worked incomplete.
805
--- not determined ---
806
DROP VIEW IF EXISTS v1;
807
DROP TABLE IF EXISTS t1;
808
DROP TABLE IF EXISTS t0_aux;
809
DROP TABLE IF EXISTS t0_definition;
810
DROP TABLE IF EXISTS t0_template;