2 # Simple test for the erroneos create statements using the
3 # partition storage engine
5 -- source include/have_partition.inc
8 drop table if exists t1;
12 # Try faulty DROP PARTITION and COALESCE PARTITION
14 CREATE TABLE t1 (a int, b int)
15 PARTITION BY RANGE (a)
16 (PARTITION x0 VALUES LESS THAN (2),
17 PARTITION x1 VALUES LESS THAN (4),
18 PARTITION x2 VALUES LESS THAN (6),
19 PARTITION x3 VALUES LESS THAN (8),
20 PARTITION x4 VALUES LESS THAN (10),
21 PARTITION x5 VALUES LESS THAN (12),
22 PARTITION x6 VALUES LESS THAN (14),
23 PARTITION x7 VALUES LESS THAN (16),
24 PARTITION x8 VALUES LESS THAN (18),
25 PARTITION x9 VALUES LESS THAN (20));
27 --error ER_REORG_OUTSIDE_RANGE
28 ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
29 (PARTITION x01 VALUES LESS THAN (2),
30 PARTITION x11 VALUES LESS THAN (5));
32 --error ER_DROP_PARTITION_NON_EXISTENT
33 ALTER TABLE t1 DROP PARTITION x0, x1, x2, x3, x3;
35 --error ER_DROP_PARTITION_NON_EXISTENT
36 ALTER TABLE t1 DROP PARTITION x0, x1, x2, x10;
38 --error ER_DROP_PARTITION_NON_EXISTENT
39 ALTER TABLE t1 DROP PARTITION x10, x1, x2, x1;
41 --error ER_DROP_PARTITION_NON_EXISTENT
42 ALTER TABLE t1 DROP PARTITION x10, x1, x2, x3;
44 --error ER_REORG_PARTITION_NOT_EXIST
45 ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10 INTO
46 (PARTITION x11 VALUES LESS THAN (22));
48 --error ER_SAME_NAME_PARTITION
49 ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO
50 (PARTITION x3 VALUES LESS THAN (6));
52 --error ER_CONSECUTIVE_REORG_PARTITIONS
53 ALTER TABLE t1 REORGANIZE PARTITION x0, x2 INTO
54 (PARTITION x11 VALUES LESS THAN (2));
56 --error ER_DROP_PARTITION_NON_EXISTENT
57 ALTER TABLE t1 REORGANIZE PARTITION x0, x1, x1 INTO
58 (PARTITION x11 VALUES LESS THAN (4));
60 --error ER_REORG_OUTSIDE_RANGE
61 ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
62 (PARTITION x01 VALUES LESS THAN (5));
64 --error ER_REORG_OUTSIDE_RANGE
65 ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
66 (PARTITION x01 VALUES LESS THAN (4),
67 PARTITION x11 VALUES LESS THAN (2));
69 --error ER_RANGE_NOT_INCREASING_ERROR
70 ALTER TABLE t1 REORGANIZE PARTITION x0,x1 INTO
71 (PARTITION x01 VALUES LESS THAN (6),
72 PARTITION x11 VALUES LESS THAN (4));
76 CREATE TABLE t1 (a int)
80 --error ER_SAME_NAME_PARTITION
81 ALTER TABLE t1 ADD PARTITION (PARTITION p1);
85 --error ER_SAME_NAME_PARTITION
86 CREATE TABLE t1 (a int)
88 (PARTITION x0, PARTITION x1, PARTITION x2, PARTITION x3, PARTITION x3);
90 CREATE TABLE t1 (a int)
91 PARTITION BY RANGE (a)
92 SUBPARTITION BY KEY (a)
94 (PARTITION x0 VALUES LESS THAN (4),
95 PARTITION x1 VALUES LESS THAN (8));
97 --error ER_RANGE_NOT_INCREASING_ERROR
98 ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (5)
99 (SUBPARTITION sp0, SUBPARTITION sp1));
101 --error ER_ADD_PARTITION_SUBPART_ERROR
102 ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES LESS THAN (12)
103 (SUBPARTITION sp0, SUBPARTITION sp1, SUBPARTITION sp2));
107 CREATE TABLE t1 (a int)
108 PARTITION BY LIST (a)
109 (PARTITION x0 VALUES IN (1,2,3),
110 PARTITION x1 VALUES IN (4,5,6));
112 --error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
113 ALTER TABLE t1 ADD PARTITION (PARTITION x2 VALUES IN (3,4));
117 CREATE TABLE t1 (a int);
119 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
120 ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
122 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
123 ALTER TABLE t1 DROP PARTITION x1;
125 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
126 ALTER TABLE t1 COALESCE PARTITION 1;
128 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
129 ALTER TABLE t1 ANALYZE PARTITION p1;
131 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
132 ALTER TABLE t1 CHECK PARTITION p1;
134 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
135 ALTER TABLE t1 OPTIMIZE PARTITION p1;
137 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
138 ALTER TABLE t1 REPAIR PARTITION p1;
142 CREATE TABLE t1 (a int)
144 (PARTITION x0, PARTITION x1);
146 --error ER_ADD_PARTITION_NO_NEW_PARTITION
147 ALTER TABLE t1 ADD PARTITION PARTITIONS 0;
149 --error ER_TOO_MANY_PARTITIONS_ERROR
150 ALTER TABLE t1 ADD PARTITION PARTITIONS 1024;
152 --error ER_ONLY_ON_RANGE_LIST_PARTITION
153 ALTER TABLE t1 DROP PARTITION x0;
155 ALTER TABLE t1 COALESCE PARTITION 1;
157 --error ER_DROP_LAST_PARTITION
158 ALTER TABLE t1 COALESCE PARTITION 1;
162 CREATE TABLE t1 (a int)
163 PARTITION BY RANGE (a)
164 (PARTITION x0 VALUES LESS THAN (4),
165 PARTITION x1 VALUES LESS THAN (8));
167 --error ER_PARTITIONS_MUST_BE_DEFINED_ERROR
168 ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
170 --error ER_DROP_PARTITION_NON_EXISTENT
171 ALTER TABLE t1 DROP PARTITION x2;
173 --error ER_COALESCE_ONLY_ON_HASH_PARTITION
174 ALTER TABLE t1 COALESCE PARTITION 1;
176 ALTER TABLE t1 DROP PARTITION x1;
178 --error ER_DROP_LAST_PARTITION
179 ALTER TABLE t1 DROP PARTITION x0;
184 # Bug# 16534 - Trying to add multiple partitions crashes server
186 CREATE TABLE t1 ( id INT NOT NULL,
187 fname VARCHAR(50) NOT NULL,
188 lname VARCHAR(50) NOT NULL,
189 hired DATE NOT NULL )
190 PARTITION BY RANGE(YEAR(hired)) (
191 PARTITION p1 VALUES LESS THAN (1991),
192 PARTITION p2 VALUES LESS THAN (1996),
193 PARTITION p3 VALUES LESS THAN (2001),
194 PARTITION p4 VALUES LESS THAN (2005));
195 ALTER TABLE t1 ADD PARTITION (
196 PARTITION p5 VALUES LESS THAN (2010),
197 PARTITION p6 VALUES LESS THAN MAXVALUE);
201 #BUG 15523 ALTER TABLE ... PARTITION BY does not work
203 CREATE TABLE t1 (a INT);
204 SHOW CREATE TABLE t1;
205 ALTER TABLE t1 PARTITION BY KEY(a) PARTITIONS 2;
206 SHOW CREATE TABLE t1;
210 #BUG 15820 create table with 1 partition, doing ALTER TABLE ADD PARTITION fails
212 CREATE TABLE t1 (a INT) PARTITION BY HASH(a);
213 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
217 #BUG 15408: Partitions: subpartition names are not unique
219 --error ER_SAME_NAME_PARTITION
220 CREATE TABLE t1 (s1 int, s2 int)
221 PARTITION BY LIST (s1)
222 SUBPARTITION BY KEY (s2) (
223 PARTITION p1 VALUES IN (0) (SUBPARTITION p1b),
224 PARTITION p2 VALUES IN (2) (SUBPARTITION p1b)