1drop table if exists t1; 2CREATE TABLE t1 (a int, b int) 3PARTITION BY RANGE (a) 4(PARTITION x0 VALUES LESS THAN (2), 5PARTITION x1 VALUES LESS THAN (4), 6PARTITION x2 VALUES LESS THAN (6), 7PARTITION x3 VALUES LESS THAN (8), 8PARTITION x4 VALUES LESS THAN (10), 9PARTITION x5 VALUES LESS THAN (12), 10PARTITION x6 VALUES LESS THAN (14), 11PARTITION x7 VALUES LESS THAN (16), 12PARTITION x8 VALUES LESS THAN (18), 13PARTITION x9 VALUES LESS THAN (20)); 14ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO 15(PARTITION x01 VALUES LESS THAN (2), 16PARTITION x11 VALUES LESS THAN (5)); 17ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range 18ALTER TABLE t1 DROP PARTITION x0, x1, x2, x3, x3; 19ERROR HY000: Error in list of partitions to DROP 20ALTER TABLE t1 DROP PARTITION x0, x1, x2, x10; 21ERROR HY000: Error in list of partitions to DROP 22ALTER TABLE t1 DROP PARTITION x10, x1, x2, x1; 23ERROR HY000: Error in list of partitions to DROP 24ALTER TABLE t1 DROP PARTITION x10, x1, x2, x3; 25ERROR HY000: Error in list of partitions to DROP 26ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10 INTO 27(PARTITION x11 VALUES LESS THAN (22)); 28ERROR HY000: More partitions to reorganize than there are partitions 29ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO 30(PARTITION x3 VALUES LESS THAN (6)); 31ERROR HY000: Duplicate partition name x3 32ALTER TABLE t1 REORGANIZE PARTITION x0, x2 INTO 33(PARTITION x11 VALUES LESS THAN (2)); 34ERROR HY000: When reorganizing a set of partitions they must be in consecutive order 35ALTER TABLE t1 REORGANIZE PARTITION x0, x1, x1 INTO 36(PARTITION x11 VALUES LESS THAN (4)); 37ERROR HY000: Error in list of partitions to REORGANIZE 38ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO 39(PARTITION x01 VALUES LESS THAN (5)); 40ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range 41ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO 42(PARTITION x01 VALUES LESS THAN (4), 43PARTITION x11 VALUES LESS THAN (2)); 44ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition 45ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO 46(PARTITION x01 VALUES LESS THAN (6), 47PARTITION x11 VALUES LESS THAN (4)); 48ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition 49DROP TABLE t1; 50CREATE TABLE t1 (a int) 51PARTITION BY KEY (a) 52PARTITIONS 2; 53ALTER TABLE t1 ADD PARTITION (PARTITION p1); 54ERROR HY000: Duplicate partition name p1 55DROP TABLE t1; 56CREATE TABLE t1 (a int) 57PARTITION BY KEY (a) 58(PARTITION x0, PARTITION x1, PARTITION x2, PARTITION x3, PARTITION x3); 59ERROR HY000: Duplicate partition name x3 60CREATE TABLE t1 (a int) 61PARTITION BY RANGE (a) 62SUBPARTITION BY KEY (a) 63SUBPARTITIONS 2 64(PARTITION x0 VALUES LESS THAN (4), 65PARTITION x1 VALUES LESS THAN (8)); 66ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (5) 67(SUBPARTITION sp0, SUBPARTITION sp1)); 68ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition 69ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (12) 70(SUBPARTITION sp0, SUBPARTITION sp1, SUBPARTITION sp2)); 71ERROR HY000: Trying to Add partition(s) with wrong number of subpartitions 72DROP TABLE t1; 73CREATE TABLE t1 (a int) 74PARTITION BY LIST (a) 75(PARTITION x0 VALUES IN (1,2,3), 76PARTITION x1 VALUES IN (4,5,6)); 77ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES IN (3,4)); 78ERROR HY000: Multiple definition of same constant in list partitioning 79DROP TABLE t1; 80CREATE TABLE t1 (a int); 81ALTER TABLE t1 ADD PARTITION PARTITIONS 1; 82ERROR HY000: Partition management on a not partitioned table is not possible 83ALTER TABLE t1 DROP PARTITION x1; 84ERROR HY000: Partition management on a not partitioned table is not possible 85ALTER TABLE t1 COALESCE PARTITION 1; 86ERROR HY000: Partition management on a not partitioned table is not possible 87ALTER TABLE t1 ANALYZE PARTITION p1; 88ERROR HY000: Partition management on a not partitioned table is not possible 89ALTER TABLE t1 CHECK PARTITION p1; 90ERROR HY000: Partition management on a not partitioned table is not possible 91ALTER TABLE t1 OPTIMIZE PARTITION p1; 92ERROR HY000: Partition management on a not partitioned table is not possible 93ALTER TABLE t1 REPAIR PARTITION p1; 94ERROR HY000: Partition management on a not partitioned table is not possible 95DROP TABLE t1; 96CREATE TABLE t1 (a int) 97PARTITION BY KEY (a) 98(PARTITION x0, PARTITION x1); 99ALTER TABLE t1 ADD PARTITION PARTITIONS 0; 100ERROR HY000: At least one partition must be added 101ALTER TABLE t1 ADD PARTITION PARTITIONS 8192; 102ERROR HY000: Too many partitions (including subpartitions) were defined 103ALTER TABLE t1 DROP PARTITION x0; 104ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions 105ALTER TABLE t1 COALESCE PARTITION 1; 106ALTER TABLE t1 COALESCE PARTITION 1; 107ERROR HY000: Cannot remove all partitions, use DROP TABLE instead 108DROP TABLE t1; 109CREATE TABLE t1 (a int) 110PARTITION BY RANGE (a) 111(PARTITION x0 VALUES LESS THAN (4), 112PARTITION x1 VALUES LESS THAN (8)); 113ALTER TABLE t1 ADD PARTITION PARTITIONS 1; 114ERROR HY000: For RANGE partitions each partition must be defined 115ALTER TABLE t1 DROP PARTITION x2; 116ERROR HY000: Error in list of partitions to DROP 117ALTER TABLE t1 COALESCE PARTITION 1; 118ERROR HY000: COALESCE PARTITION can only be used on HASH/KEY partitions 119ALTER TABLE t1 DROP PARTITION x1; 120ALTER TABLE t1 DROP PARTITION x0; 121ERROR HY000: Cannot remove all partitions, use DROP TABLE instead 122DROP TABLE t1; 123CREATE TABLE t1 ( id INT NOT NULL, 124fname VARCHAR(50) NOT NULL, 125lname VARCHAR(50) NOT NULL, 126hired DATE NOT NULL ) 127PARTITION BY RANGE(YEAR(hired)) ( 128PARTITION p1 VALUES LESS THAN (1991), 129PARTITION p2 VALUES LESS THAN (1996), 130PARTITION p3 VALUES LESS THAN (2001), 131PARTITION p4 VALUES LESS THAN (2005)); 132ALTER TABLE t1 ADD PARTITION ( 133PARTITION p5 VALUES LESS THAN (2010), 134PARTITION p6 VALUES LESS THAN MAXVALUE); 135DROP TABLE t1; 136CREATE TABLE t1 (a INT); 137SHOW CREATE TABLE t1; 138Table Create Table 139t1 CREATE TABLE `t1` ( 140 `a` int(11) DEFAULT NULL 141) ENGINE=MyISAM DEFAULT CHARSET=latin1 142ALTER TABLE t1 PARTITION BY KEY(a) PARTITIONS 2; 143SHOW CREATE TABLE t1; 144Table Create Table 145t1 CREATE TABLE `t1` ( 146 `a` int(11) DEFAULT NULL 147) ENGINE=MyISAM DEFAULT CHARSET=latin1 148 PARTITION BY KEY (`a`) 149PARTITIONS 2 150DROP TABLE t1; 151CREATE TABLE t1 (a INT) PARTITION BY HASH(a); 152ALTER TABLE t1 ADD PARTITION PARTITIONS 4; 153DROP TABLE t1; 154CREATE TABLE t1 (s1 int, s2 int) 155PARTITION BY LIST (s1) 156SUBPARTITION BY KEY (s2) ( 157PARTITION p1 VALUES IN (0) (SUBPARTITION p1b), 158PARTITION p2 VALUES IN (2) (SUBPARTITION p1b) 159); 160ERROR HY000: Duplicate partition name p1b 161