1
CREATE TABLE t1 (c1 TINYINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
2
PARTITION p0 VALUES LESS THAN (10),
3
PARTITION p1 VALUES LESS THAN (20),
4
PARTITION p2 VALUES LESS THAN (30),
5
PARTITION p3 VALUES LESS THAN MAXVALUE);
6
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
7
SELECT * FROM t1 ORDER BY c1;
17
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
18
PARTITION s0 VALUES LESS THAN (3),
19
PARTITION s1 VALUES LESS THAN (7));
20
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
21
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
22
PARTITION s0 VALUES LESS THAN (3),
23
PARTITION s1 VALUES LESS THAN (11));
24
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
25
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
26
PARTITION s0 VALUES LESS THAN (3),
27
PARTITION s1 VALUES LESS THAN (10));
28
SELECT * FROM t1 ORDER BY c1;
43
t1 CREATE TABLE `t1` (
44
`c1` tinyint(4) NOT NULL,
45
`c2` char(5) DEFAULT NULL
46
) ENGINE=ENGINE DEFAULT CHARSET=latin1
47
/*!50100 PARTITION BY RANGE ( c1)
48
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
49
PARTITION s1 VALUES LESS THAN (10) ENGINE = ENGINE,
50
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
51
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
52
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
53
ALTER TABLE t1 DROP PARTITION s2;
54
ERROR HY000: Error in list of partitions to DROP
55
ALTER TABLE t1 DROP PARTITION s1;
56
SELECT * FROM t1 ORDER BY c1;
70
t1 CREATE TABLE `t1` (
71
`c1` tinyint(4) NOT NULL,
72
`c2` char(5) DEFAULT NULL
73
) ENGINE=ENGINE DEFAULT CHARSET=latin1
74
/*!50100 PARTITION BY RANGE ( c1)
75
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
76
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
77
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
78
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
82
CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
83
PARTITION p0 VALUES LESS THAN (10),
84
PARTITION p1 VALUES LESS THAN (20),
85
PARTITION p2 VALUES LESS THAN (30),
86
PARTITION p3 VALUES LESS THAN MAXVALUE);
87
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
88
SELECT * FROM t1 ORDER BY c1;
98
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
99
PARTITION s0 VALUES LESS THAN (3),
100
PARTITION s1 VALUES LESS THAN (7));
101
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
102
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
103
PARTITION s0 VALUES LESS THAN (3),
104
PARTITION s1 VALUES LESS THAN (11));
105
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
106
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
107
PARTITION s0 VALUES LESS THAN (3),
108
PARTITION s1 VALUES LESS THAN (10));
109
SELECT * FROM t1 ORDER BY c1;
122
SHOW CREATE TABLE t1;
124
t1 CREATE TABLE `t1` (
125
`c1` smallint(6) NOT NULL,
126
`c2` char(5) DEFAULT NULL
127
) ENGINE=ENGINE DEFAULT CHARSET=latin1
128
/*!50100 PARTITION BY RANGE ( c1)
129
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
130
PARTITION s1 VALUES LESS THAN (10) ENGINE = ENGINE,
131
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
132
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
133
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
134
ALTER TABLE t1 DROP PARTITION s2;
135
ERROR HY000: Error in list of partitions to DROP
136
ALTER TABLE t1 DROP PARTITION s1;
137
SELECT * FROM t1 ORDER BY c1;
149
SHOW CREATE TABLE t1;
151
t1 CREATE TABLE `t1` (
152
`c1` smallint(6) NOT NULL,
153
`c2` char(5) DEFAULT NULL
154
) ENGINE=ENGINE DEFAULT CHARSET=latin1
155
/*!50100 PARTITION BY RANGE ( c1)
156
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
157
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
158
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
159
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
163
CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
164
PARTITION p0 VALUES LESS THAN (10),
165
PARTITION p1 VALUES LESS THAN (20),
166
PARTITION p2 VALUES LESS THAN (30),
167
PARTITION p3 VALUES LESS THAN MAXVALUE);
168
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
169
SELECT * FROM t1 ORDER BY c1;
179
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
180
PARTITION s0 VALUES LESS THAN (3),
181
PARTITION s1 VALUES LESS THAN (7));
182
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
183
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
184
PARTITION s0 VALUES LESS THAN (3),
185
PARTITION s1 VALUES LESS THAN (11));
186
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
187
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
188
PARTITION s0 VALUES LESS THAN (3),
189
PARTITION s1 VALUES LESS THAN (10));
190
SELECT * FROM t1 ORDER BY c1;
203
SHOW CREATE TABLE t1;
205
t1 CREATE TABLE `t1` (
206
`c1` mediumint(9) NOT NULL,
207
`c2` char(5) DEFAULT NULL
208
) ENGINE=ENGINE DEFAULT CHARSET=latin1
209
/*!50100 PARTITION BY RANGE ( c1)
210
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
211
PARTITION s1 VALUES LESS THAN (10) ENGINE = ENGINE,
212
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
213
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
214
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
215
ALTER TABLE t1 DROP PARTITION s2;
216
ERROR HY000: Error in list of partitions to DROP
217
ALTER TABLE t1 DROP PARTITION s1;
218
SELECT * FROM t1 ORDER BY c1;
230
SHOW CREATE TABLE t1;
232
t1 CREATE TABLE `t1` (
233
`c1` mediumint(9) NOT NULL,
234
`c2` char(5) DEFAULT NULL
235
) ENGINE=ENGINE DEFAULT CHARSET=latin1
236
/*!50100 PARTITION BY RANGE ( c1)
237
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
238
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
239
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
240
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
244
CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
245
PARTITION p0 VALUES LESS THAN (10),
246
PARTITION p1 VALUES LESS THAN (20),
247
PARTITION p2 VALUES LESS THAN (30),
248
PARTITION p3 VALUES LESS THAN MAXVALUE);
249
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
250
SELECT * FROM t1 ORDER BY c1;
260
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
261
PARTITION s0 VALUES LESS THAN (3),
262
PARTITION s1 VALUES LESS THAN (7));
263
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
264
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
265
PARTITION s0 VALUES LESS THAN (3),
266
PARTITION s1 VALUES LESS THAN (11));
267
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
268
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
269
PARTITION s0 VALUES LESS THAN (3),
270
PARTITION s1 VALUES LESS THAN (10));
271
SELECT * FROM t1 ORDER BY c1;
284
SHOW CREATE TABLE t1;
286
t1 CREATE TABLE `t1` (
287
`c1` int(11) NOT NULL,
288
`c2` char(5) DEFAULT NULL
289
) ENGINE=ENGINE DEFAULT CHARSET=latin1
290
/*!50100 PARTITION BY RANGE ( c1)
291
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
292
PARTITION s1 VALUES LESS THAN (10) ENGINE = ENGINE,
293
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
294
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
295
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
296
ALTER TABLE t1 DROP PARTITION s2;
297
ERROR HY000: Error in list of partitions to DROP
298
ALTER TABLE t1 DROP PARTITION s1;
299
SELECT * FROM t1 ORDER BY c1;
311
SHOW CREATE TABLE t1;
313
t1 CREATE TABLE `t1` (
314
`c1` int(11) NOT NULL,
315
`c2` char(5) DEFAULT NULL
316
) ENGINE=ENGINE DEFAULT CHARSET=latin1
317
/*!50100 PARTITION BY RANGE ( c1)
318
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
319
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
320
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
321
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
325
CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
326
PARTITION p0 VALUES LESS THAN (10),
327
PARTITION p1 VALUES LESS THAN (20),
328
PARTITION p2 VALUES LESS THAN (30),
329
PARTITION p3 VALUES LESS THAN MAXVALUE);
330
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
331
SELECT * FROM t1 ORDER BY c1;
341
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
342
PARTITION s0 VALUES LESS THAN (3),
343
PARTITION s1 VALUES LESS THAN (7));
344
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
345
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
346
PARTITION s0 VALUES LESS THAN (3),
347
PARTITION s1 VALUES LESS THAN (11));
348
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
349
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
350
PARTITION s0 VALUES LESS THAN (3),
351
PARTITION s1 VALUES LESS THAN (10));
352
SELECT * FROM t1 ORDER BY c1;
365
SHOW CREATE TABLE t1;
367
t1 CREATE TABLE `t1` (
368
`c1` int(11) NOT NULL,
369
`c2` char(5) DEFAULT NULL
370
) ENGINE=ENGINE DEFAULT CHARSET=latin1
371
/*!50100 PARTITION BY RANGE ( c1)
372
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
373
PARTITION s1 VALUES LESS THAN (10) ENGINE = ENGINE,
374
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
375
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
376
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
377
ALTER TABLE t1 DROP PARTITION s2;
378
ERROR HY000: Error in list of partitions to DROP
379
ALTER TABLE t1 DROP PARTITION s1;
380
SELECT * FROM t1 ORDER BY c1;
392
SHOW CREATE TABLE t1;
394
t1 CREATE TABLE `t1` (
395
`c1` int(11) NOT NULL,
396
`c2` char(5) DEFAULT NULL
397
) ENGINE=ENGINE DEFAULT CHARSET=latin1
398
/*!50100 PARTITION BY RANGE ( c1)
399
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
400
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
401
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
402
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
406
CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
407
PARTITION p0 VALUES LESS THAN (10),
408
PARTITION p1 VALUES LESS THAN (20),
409
PARTITION p2 VALUES LESS THAN (30),
410
PARTITION p3 VALUES LESS THAN MAXVALUE);
411
INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
412
SELECT * FROM t1 ORDER BY c1;
422
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
423
PARTITION s0 VALUES LESS THAN (3),
424
PARTITION s1 VALUES LESS THAN (7));
425
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
426
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
427
PARTITION s0 VALUES LESS THAN (3),
428
PARTITION s1 VALUES LESS THAN (11));
429
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
430
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
431
PARTITION s0 VALUES LESS THAN (3),
432
PARTITION s1 VALUES LESS THAN (10));
433
SELECT * FROM t1 ORDER BY c1;
446
SHOW CREATE TABLE t1;
448
t1 CREATE TABLE `t1` (
449
`c1` bigint(20) NOT NULL,
450
`c2` char(5) DEFAULT NULL
451
) ENGINE=ENGINE DEFAULT CHARSET=latin1
452
/*!50100 PARTITION BY RANGE ( c1)
453
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
454
PARTITION s1 VALUES LESS THAN (10) ENGINE = ENGINE,
455
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
456
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
457
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */
458
ALTER TABLE t1 DROP PARTITION s2;
459
ERROR HY000: Error in list of partitions to DROP
460
ALTER TABLE t1 DROP PARTITION s1;
461
SELECT * FROM t1 ORDER BY c1;
473
SHOW CREATE TABLE t1;
475
t1 CREATE TABLE `t1` (
476
`c1` bigint(20) NOT NULL,
477
`c2` char(5) DEFAULT NULL
478
) ENGINE=ENGINE DEFAULT CHARSET=latin1
479
/*!50100 PARTITION BY RANGE ( c1)
480
(PARTITION s0 VALUES LESS THAN (3) ENGINE = ENGINE,
481
PARTITION p1 VALUES LESS THAN (20) ENGINE = ENGINE,
482
PARTITION p2 VALUES LESS THAN (30) ENGINE = ENGINE,
483
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = ENGINE) */