2
SET @@session.storage_engine = 'MyISAM';
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=MyISAM DEFAULT CHARSET=latin1
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));
78
t1 CREATE TABLE `t1` (
79
`f_date` date DEFAULT NULL,
80
`f_varchar` varchar(30) DEFAULT NULL
81
) ENGINE=MyISAM DEFAULT CHARSET=latin1
82
/*!50100 PARTITION BY HASH (YEAR(f_date)) */
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));
98
t1 CREATE TABLE `t1` (
99
`f_date` date DEFAULT NULL,
100
`f_varchar` varchar(30) DEFAULT NULL
101
) ENGINE=MyISAM DEFAULT CHARSET=latin1
102
/*!50100 PARTITION BY HASH (DAYOFYEAR(f_date)) */
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;
116
t1 CREATE TABLE `t1` (
117
`f_date` date DEFAULT NULL,
118
`f_varchar` varchar(30) DEFAULT NULL
119
) ENGINE=MyISAM DEFAULT CHARSET=latin1
120
/*!50100 PARTITION BY HASH (YEAR(f_date)) */
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;
140
t1 CREATE TABLE `t1` (
141
`f_date` date DEFAULT NULL,
142
`f_varchar` varchar(30) DEFAULT NULL
143
) ENGINE=MyISAM DEFAULT CHARSET=latin1
144
/*!50100 PARTITION BY HASH (YEAR(f_date))
145
(PARTITION p0 ENGINE = MyISAM,
146
PARTITION part1 ENGINE = MyISAM,
147
PARTITION part7 ENGINE = MyISAM) */
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 20 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;
169
t1 CREATE TABLE `t1` (
170
`f_date` date DEFAULT NULL,
171
`f_varchar` varchar(30) DEFAULT NULL
172
) ENGINE=MyISAM DEFAULT CHARSET=latin1
173
/*!50100 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) */
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 20 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;
198
t1 CREATE TABLE `t1` (
199
`f_date` date DEFAULT NULL,
200
`f_varchar` varchar(30) DEFAULT NULL
201
) ENGINE=MyISAM DEFAULT CHARSET=latin1
202
/*!50100 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) */
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 20 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 MySQL 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;
251
t1 CREATE TABLE `t1` (
252
`f_date` date DEFAULT NULL,
253
`f_varchar` varchar(30) DEFAULT NULL
254
) ENGINE=MyISAM DEFAULT CHARSET=latin1
255
/*!50100 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) */
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 20 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;
288
t1 CREATE TABLE `t1` (
289
`f_date` date DEFAULT NULL,
290
`f_varchar` varchar(30) DEFAULT NULL
291
) ENGINE=MyISAM DEFAULT CHARSET=latin1
292
/*!50100 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) */
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 20 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;
322
t1 CREATE TABLE `t1` (
323
`f_date` date DEFAULT NULL,
324
`f_varchar` varchar(30) DEFAULT NULL
325
) ENGINE=MyISAM DEFAULT CHARSET=latin1
326
/*!50100 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) */
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
ALTER TABLE t1 COALESCE PARTITION 1;
351
SHOW CREATE TABLE t1;
353
t1 CREATE TABLE `t1` (
354
`f_date` date DEFAULT NULL,
355
`f_varchar` varchar(30) DEFAULT NULL
356
) ENGINE=MyISAM DEFAULT CHARSET=latin1
357
/*!50100 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) */
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 20 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;
381
t1 CREATE TABLE `t1` (
382
`f_date` date DEFAULT NULL,
383
`f_varchar` varchar(30) DEFAULT NULL
384
) ENGINE=MyISAM DEFAULT CHARSET=latin1
385
/*!50100 PARTITION BY HASH (YEAR(f_date))
386
(PARTITION p0 ENGINE = MyISAM,
387
PARTITION part1 ENGINE = MyISAM,
388
PARTITION part7 ENGINE = MyISAM) */
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 20 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;
406
t1 CREATE TABLE `t1` (
407
`f_date` date DEFAULT NULL,
408
`f_varchar` varchar(30) DEFAULT NULL
409
) ENGINE=MyISAM DEFAULT CHARSET=latin1
410
/*!50100 PARTITION BY HASH (YEAR(f_date))
411
(PARTITION p0 ENGINE = MyISAM,
412
PARTITION part1 ENGINE = MyISAM) */
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 20 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;
428
t1 CREATE TABLE `t1` (
429
`f_date` date DEFAULT NULL,
430
`f_varchar` varchar(30) DEFAULT NULL
431
) ENGINE=MyISAM DEFAULT CHARSET=latin1
432
/*!50100 PARTITION BY HASH (YEAR(f_date))
433
(PARTITION p0 ENGINE = MyISAM) */
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;
451
t1 CREATE TABLE `t1` (
452
`f_date` date DEFAULT NULL,
453
`f_varchar` varchar(30) DEFAULT NULL
454
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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;
468
#========================================================================
469
# 2. Partition management commands on KEY partitioned table
470
#========================================================================
471
DROP TABLE IF EXISTS t1;
477
f_charbig VARCHAR(1000)
479
INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
480
SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
481
SHOW CREATE TABLE t1;
483
t1 CREATE TABLE `t1` (
484
`f_int1` int(11) DEFAULT NULL,
485
`f_int2` int(11) DEFAULT NULL,
486
`f_char1` char(20) DEFAULT NULL,
487
`f_char2` char(20) DEFAULT NULL,
488
`f_charbig` varchar(1000) DEFAULT NULL
489
) ENGINE=MyISAM DEFAULT CHARSET=latin1
493
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
494
id select_type table partitions type possible_keys key key_len ref rows Extra
495
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
496
# check read single success: 1
497
# check read all success: 1
498
# check read row by row success: 1
499
#------------------------------------------------------------------------
500
# 2.1 Increase number of PARTITIONS
501
# Some negative testcases are omitted (already checked with HASH).
502
#------------------------------------------------------------------------
503
# 2.1.1 Assign KEY partitioning
504
ALTER TABLE t1 PARTITION BY KEY(f_int1);
505
SHOW CREATE TABLE t1;
507
t1 CREATE TABLE `t1` (
508
`f_int1` int(11) DEFAULT NULL,
509
`f_int2` int(11) DEFAULT NULL,
510
`f_char1` char(20) DEFAULT NULL,
511
`f_char2` char(20) DEFAULT NULL,
512
`f_charbig` varchar(1000) DEFAULT NULL
513
) ENGINE=MyISAM DEFAULT CHARSET=latin1
514
/*!50100 PARTITION BY KEY (f_int1) */
519
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
520
id select_type table partitions type possible_keys key key_len ref rows Extra
521
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
522
# check read single success: 1
523
# check read all success: 1
524
# check read row by row success: 1
525
# 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
526
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
527
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
528
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
529
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
530
# 2.1.3 Add two named partitions + test
531
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
532
SHOW CREATE TABLE t1;
534
t1 CREATE TABLE `t1` (
535
`f_int1` int(11) DEFAULT NULL,
536
`f_int2` int(11) DEFAULT NULL,
537
`f_char1` char(20) DEFAULT NULL,
538
`f_char2` char(20) DEFAULT NULL,
539
`f_charbig` varchar(1000) DEFAULT NULL
540
) ENGINE=MyISAM DEFAULT CHARSET=latin1
541
/*!50100 PARTITION BY KEY (f_int1)
542
(PARTITION p0 ENGINE = MyISAM,
543
PARTITION part1 ENGINE = MyISAM,
544
PARTITION part7 ENGINE = MyISAM) */
553
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
554
id select_type table partitions type possible_keys key key_len ref rows Extra
555
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
556
# check read single success: 1
557
# check read all success: 1
558
# check read row by row success: 1
559
# 2.1.4 Add one named partition + test
560
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
561
SHOW CREATE TABLE t1;
563
t1 CREATE TABLE `t1` (
564
`f_int1` int(11) DEFAULT NULL,
565
`f_int2` int(11) DEFAULT NULL,
566
`f_char1` char(20) DEFAULT NULL,
567
`f_char2` char(20) DEFAULT NULL,
568
`f_charbig` varchar(1000) DEFAULT NULL
569
) ENGINE=MyISAM DEFAULT CHARSET=latin1
570
/*!50100 PARTITION BY KEY (f_int1)
571
(PARTITION p0 ENGINE = MyISAM,
572
PARTITION part1 ENGINE = MyISAM,
573
PARTITION part7 ENGINE = MyISAM,
574
PARTITION part2 ENGINE = MyISAM) */
585
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
586
id select_type table partitions type possible_keys key key_len ref rows Extra
587
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
588
# check read single success: 1
589
# check read all success: 1
590
# check read row by row success: 1
591
# 2.1.5 Add four not named partitions + test
592
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
593
SHOW CREATE TABLE t1;
595
t1 CREATE TABLE `t1` (
596
`f_int1` int(11) DEFAULT NULL,
597
`f_int2` int(11) DEFAULT NULL,
598
`f_char1` char(20) DEFAULT NULL,
599
`f_char2` char(20) DEFAULT NULL,
600
`f_charbig` varchar(1000) DEFAULT NULL
601
) ENGINE=MyISAM DEFAULT CHARSET=latin1
602
/*!50100 PARTITION BY KEY (f_int1)
603
(PARTITION p0 ENGINE = MyISAM,
604
PARTITION part1 ENGINE = MyISAM,
605
PARTITION part7 ENGINE = MyISAM,
606
PARTITION part2 ENGINE = MyISAM,
607
PARTITION p4 ENGINE = MyISAM,
608
PARTITION p5 ENGINE = MyISAM,
609
PARTITION p6 ENGINE = MyISAM,
610
PARTITION p7 ENGINE = MyISAM) */
629
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
630
id select_type table partitions type possible_keys key key_len ref rows Extra
631
1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 20 Using where
632
# check read single success: 1
633
# check read all success: 1
634
# check read row by row success: 1
635
#------------------------------------------------------------------------
636
# 2.2 Decrease number of PARTITIONS
637
# Some negative testcases are omitted (already checked with HASH).
638
#------------------------------------------------------------------------
639
# 2.2.1 DROP PARTITION is not supported for KEY --> must fail
640
ALTER TABLE t1 DROP PARTITION part1;
641
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
642
# 2.2.4 COALESCE one partition + test loop
643
ALTER TABLE t1 COALESCE PARTITION 1;
644
SHOW CREATE TABLE t1;
646
t1 CREATE TABLE `t1` (
647
`f_int1` int(11) DEFAULT NULL,
648
`f_int2` int(11) DEFAULT NULL,
649
`f_char1` char(20) DEFAULT NULL,
650
`f_char2` char(20) DEFAULT NULL,
651
`f_charbig` varchar(1000) DEFAULT NULL
652
) ENGINE=MyISAM DEFAULT CHARSET=latin1
653
/*!50100 PARTITION BY KEY (f_int1)
654
(PARTITION p0 ENGINE = MyISAM,
655
PARTITION part1 ENGINE = MyISAM,
656
PARTITION part7 ENGINE = MyISAM,
657
PARTITION part2 ENGINE = MyISAM,
658
PARTITION p4 ENGINE = MyISAM,
659
PARTITION p5 ENGINE = MyISAM,
660
PARTITION p6 ENGINE = MyISAM) */
677
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
678
id select_type table partitions type possible_keys key key_len ref rows Extra
679
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
680
# check read single success: 1
681
# check read all success: 1
682
# check read row by row success: 1
683
ALTER TABLE t1 COALESCE PARTITION 1;
684
SHOW CREATE TABLE t1;
686
t1 CREATE TABLE `t1` (
687
`f_int1` int(11) DEFAULT NULL,
688
`f_int2` int(11) DEFAULT NULL,
689
`f_char1` char(20) DEFAULT NULL,
690
`f_char2` char(20) DEFAULT NULL,
691
`f_charbig` varchar(1000) DEFAULT NULL
692
) ENGINE=MyISAM DEFAULT CHARSET=latin1
693
/*!50100 PARTITION BY KEY (f_int1)
694
(PARTITION p0 ENGINE = MyISAM,
695
PARTITION part1 ENGINE = MyISAM,
696
PARTITION part7 ENGINE = MyISAM,
697
PARTITION part2 ENGINE = MyISAM,
698
PARTITION p4 ENGINE = MyISAM,
699
PARTITION p5 ENGINE = MyISAM) */
714
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
715
id select_type table partitions type possible_keys key key_len ref rows Extra
716
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
717
# check read single success: 1
718
# check read all success: 1
719
# check read row by row success: 1
720
ALTER TABLE t1 COALESCE PARTITION 1;
721
SHOW CREATE TABLE t1;
723
t1 CREATE TABLE `t1` (
724
`f_int1` int(11) DEFAULT NULL,
725
`f_int2` int(11) DEFAULT NULL,
726
`f_char1` char(20) DEFAULT NULL,
727
`f_char2` char(20) DEFAULT NULL,
728
`f_charbig` varchar(1000) DEFAULT NULL
729
) ENGINE=MyISAM DEFAULT CHARSET=latin1
730
/*!50100 PARTITION BY KEY (f_int1)
731
(PARTITION p0 ENGINE = MyISAM,
732
PARTITION part1 ENGINE = MyISAM,
733
PARTITION part7 ENGINE = MyISAM,
734
PARTITION part2 ENGINE = MyISAM,
735
PARTITION p4 ENGINE = MyISAM) */
748
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
749
id select_type table partitions type possible_keys key key_len ref rows Extra
750
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 20 Using where
751
# check read single success: 1
752
# check read all success: 1
753
# check read row by row success: 1
754
ALTER TABLE t1 COALESCE PARTITION 1;
755
SHOW CREATE TABLE t1;
757
t1 CREATE TABLE `t1` (
758
`f_int1` int(11) DEFAULT NULL,
759
`f_int2` int(11) DEFAULT NULL,
760
`f_char1` char(20) DEFAULT NULL,
761
`f_char2` char(20) DEFAULT NULL,
762
`f_charbig` varchar(1000) DEFAULT NULL
763
) ENGINE=MyISAM DEFAULT CHARSET=latin1
764
/*!50100 PARTITION BY KEY (f_int1)
765
(PARTITION p0 ENGINE = MyISAM,
766
PARTITION part1 ENGINE = MyISAM,
767
PARTITION part7 ENGINE = MyISAM,
768
PARTITION part2 ENGINE = MyISAM) */
779
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
780
id select_type table partitions type possible_keys key key_len ref rows Extra
781
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
782
# check read single success: 1
783
# check read all success: 1
784
# check read row by row success: 1
785
ALTER TABLE t1 COALESCE PARTITION 1;
786
SHOW CREATE TABLE t1;
788
t1 CREATE TABLE `t1` (
789
`f_int1` int(11) DEFAULT NULL,
790
`f_int2` int(11) DEFAULT NULL,
791
`f_char1` char(20) DEFAULT NULL,
792
`f_char2` char(20) DEFAULT NULL,
793
`f_charbig` varchar(1000) DEFAULT NULL
794
) ENGINE=MyISAM DEFAULT CHARSET=latin1
795
/*!50100 PARTITION BY KEY (f_int1)
796
(PARTITION p0 ENGINE = MyISAM,
797
PARTITION part1 ENGINE = MyISAM,
798
PARTITION part7 ENGINE = MyISAM) */
807
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
808
id select_type table partitions type possible_keys key key_len ref rows Extra
809
1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
810
# check read single success: 1
811
# check read all success: 1
812
# check read row by row success: 1
813
ALTER TABLE t1 COALESCE PARTITION 1;
814
SHOW CREATE TABLE t1;
816
t1 CREATE TABLE `t1` (
817
`f_int1` int(11) DEFAULT NULL,
818
`f_int2` int(11) DEFAULT NULL,
819
`f_char1` char(20) DEFAULT NULL,
820
`f_char2` char(20) DEFAULT NULL,
821
`f_charbig` varchar(1000) DEFAULT NULL
822
) ENGINE=MyISAM DEFAULT CHARSET=latin1
823
/*!50100 PARTITION BY KEY (f_int1)
824
(PARTITION p0 ENGINE = MyISAM,
825
PARTITION part1 ENGINE = MyISAM) */
832
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
833
id select_type table partitions type possible_keys key key_len ref rows Extra
834
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
835
# check read single success: 1
836
# check read all success: 1
837
# check read row by row success: 1
838
ALTER TABLE t1 COALESCE PARTITION 1;
839
SHOW CREATE TABLE t1;
841
t1 CREATE TABLE `t1` (
842
`f_int1` int(11) DEFAULT NULL,
843
`f_int2` int(11) DEFAULT NULL,
844
`f_char1` char(20) DEFAULT NULL,
845
`f_char2` char(20) DEFAULT NULL,
846
`f_charbig` varchar(1000) DEFAULT NULL
847
) ENGINE=MyISAM DEFAULT CHARSET=latin1
848
/*!50100 PARTITION BY KEY (f_int1)
849
(PARTITION p0 ENGINE = MyISAM) */
854
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
855
id select_type table partitions type possible_keys key key_len ref rows Extra
856
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
857
# check read single success: 1
858
# check read all success: 1
859
# check read row by row success: 1
860
# 2.2.5 COALESCE of last partition --> must fail
861
ALTER TABLE t1 COALESCE PARTITION 1;
862
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
863
# 2.2.6 Remove partitioning
864
ALTER TABLE t1 REMOVE PARTITIONING;
865
SHOW CREATE TABLE t1;
867
t1 CREATE TABLE `t1` (
868
`f_int1` int(11) DEFAULT NULL,
869
`f_int2` int(11) DEFAULT NULL,
870
`f_char1` char(20) DEFAULT NULL,
871
`f_char2` char(20) DEFAULT NULL,
872
`f_charbig` varchar(1000) DEFAULT NULL
873
) ENGINE=MyISAM DEFAULT CHARSET=latin1
877
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
878
id select_type table partitions type possible_keys key key_len ref rows Extra
879
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
880
# check read single success: 1
881
# check read all success: 1
882
# check read row by row success: 1
883
# 2.2.7 Remove partitioning from not partitioned table --> ????
884
ALTER TABLE t1 REMOVE PARTITIONING;
886
DROP VIEW IF EXISTS v1;
887
DROP TABLE IF EXISTS t1;
888
DROP TABLE IF EXISTS t0_aux;
889
DROP TABLE IF EXISTS t0_definition;
890
DROP TABLE IF EXISTS t0_template;