Debian Codegraph @

mysql-test/suite/parts/r/partition_reorg_divide.result

raw
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;
8 c1 c2
9 0 abc
10 5 abc
11 10 abc
12 15 abc
13 20 abc
14 25 abc
15 30 abc
16 35 abc
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;
29 c1 c2
30 0 abc
31 5 abc
32 10 abc
33 15 abc
34 20 abc
35 25 abc
36 30 abc
37 35 abc
38 SHOW TABLES;
39 Tables_in_test
40 t1
41 SHOW CREATE TABLE t1;
42 Table Create Table
43 t1 CREATE TABLE `t1` (
44 `c1` tinyint NOT NULL,
45 `c2` char(5) DEFAULT NULL
46 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
47 /*!50100 PARTITION BY RANGE (`c1`)
48 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
49 PARTITION s1 VALUES LESS THAN (10) ENGINE = InnoDB,
50 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
51 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
52 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
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;
57 c1 c2
58 0 abc
59 10 abc
60 15 abc
61 20 abc
62 25 abc
63 30 abc
64 35 abc
65 SHOW TABLES;
66 Tables_in_test
67 t1
68 SHOW CREATE TABLE t1;
69 Table Create Table
70 t1 CREATE TABLE `t1` (
71 `c1` tinyint NOT NULL,
72 `c2` char(5) DEFAULT NULL
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
74 /*!50100 PARTITION BY RANGE (`c1`)
75 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
76 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
77 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
78 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
79 DROP TABLE t1;
80 SHOW TABLES;
81 Tables_in_test
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;
89 c1 c2
90 0 abc
91 5 abc
92 10 abc
93 15 abc
94 20 abc
95 25 abc
96 30 abc
97 35 abc
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;
110 c1 c2
111 0 abc
112 5 abc
113 10 abc
114 15 abc
115 20 abc
116 25 abc
117 30 abc
118 35 abc
119 SHOW TABLES;
120 Tables_in_test
121 t1
122 SHOW CREATE TABLE t1;
123 Table Create Table
124 t1 CREATE TABLE `t1` (
125 `c1` smallint NOT NULL,
126 `c2` char(5) DEFAULT NULL
127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
128 /*!50100 PARTITION BY RANGE (`c1`)
129 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
130 PARTITION s1 VALUES LESS THAN (10) ENGINE = InnoDB,
131 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
132 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
133 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
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;
138 c1 c2
139 0 abc
140 10 abc
141 15 abc
142 20 abc
143 25 abc
144 30 abc
145 35 abc
146 SHOW TABLES;
147 Tables_in_test
148 t1
149 SHOW CREATE TABLE t1;
150 Table Create Table
151 t1 CREATE TABLE `t1` (
152 `c1` smallint NOT NULL,
153 `c2` char(5) DEFAULT NULL
154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
155 /*!50100 PARTITION BY RANGE (`c1`)
156 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
157 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
158 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
159 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
160 DROP TABLE t1;
161 SHOW TABLES;
162 Tables_in_test
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;
170 c1 c2
171 0 abc
172 5 abc
173 10 abc
174 15 abc
175 20 abc
176 25 abc
177 30 abc
178 35 abc
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;
191 c1 c2
192 0 abc
193 5 abc
194 10 abc
195 15 abc
196 20 abc
197 25 abc
198 30 abc
199 35 abc
200 SHOW TABLES;
201 Tables_in_test
202 t1
203 SHOW CREATE TABLE t1;
204 Table Create Table
205 t1 CREATE TABLE `t1` (
206 `c1` mediumint NOT NULL,
207 `c2` char(5) DEFAULT NULL
208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
209 /*!50100 PARTITION BY RANGE (`c1`)
210 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
211 PARTITION s1 VALUES LESS THAN (10) ENGINE = InnoDB,
212 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
213 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
214 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
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;
219 c1 c2
220 0 abc
221 10 abc
222 15 abc
223 20 abc
224 25 abc
225 30 abc
226 35 abc
227 SHOW TABLES;
228 Tables_in_test
229 t1
230 SHOW CREATE TABLE t1;
231 Table Create Table
232 t1 CREATE TABLE `t1` (
233 `c1` mediumint NOT NULL,
234 `c2` char(5) DEFAULT NULL
235 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
236 /*!50100 PARTITION BY RANGE (`c1`)
237 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
238 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
239 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
240 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
241 DROP TABLE t1;
242 SHOW TABLES;
243 Tables_in_test
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;
251 c1 c2
252 0 abc
253 5 abc
254 10 abc
255 15 abc
256 20 abc
257 25 abc
258 30 abc
259 35 abc
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;
272 c1 c2
273 0 abc
274 5 abc
275 10 abc
276 15 abc
277 20 abc
278 25 abc
279 30 abc
280 35 abc
281 SHOW TABLES;
282 Tables_in_test
283 t1
284 SHOW CREATE TABLE t1;
285 Table Create Table
286 t1 CREATE TABLE `t1` (
287 `c1` int NOT NULL,
288 `c2` char(5) DEFAULT NULL
289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
290 /*!50100 PARTITION BY RANGE (`c1`)
291 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
292 PARTITION s1 VALUES LESS THAN (10) ENGINE = InnoDB,
293 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
294 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
295 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
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;
300 c1 c2
301 0 abc
302 10 abc
303 15 abc
304 20 abc
305 25 abc
306 30 abc
307 35 abc
308 SHOW TABLES;
309 Tables_in_test
310 t1
311 SHOW CREATE TABLE t1;
312 Table Create Table
313 t1 CREATE TABLE `t1` (
314 `c1` int NOT NULL,
315 `c2` char(5) DEFAULT NULL
316 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
317 /*!50100 PARTITION BY RANGE (`c1`)
318 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
319 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
320 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
321 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
322 DROP TABLE t1;
323 SHOW TABLES;
324 Tables_in_test
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;
332 c1 c2
333 0 abc
334 5 abc
335 10 abc
336 15 abc
337 20 abc
338 25 abc
339 30 abc
340 35 abc
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;
353 c1 c2
354 0 abc
355 5 abc
356 10 abc
357 15 abc
358 20 abc
359 25 abc
360 30 abc
361 35 abc
362 SHOW TABLES;
363 Tables_in_test
364 t1
365 SHOW CREATE TABLE t1;
366 Table Create Table
367 t1 CREATE TABLE `t1` (
368 `c1` int NOT NULL,
369 `c2` char(5) DEFAULT NULL
370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
371 /*!50100 PARTITION BY RANGE (`c1`)
372 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
373 PARTITION s1 VALUES LESS THAN (10) ENGINE = InnoDB,
374 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
375 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
376 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
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;
381 c1 c2
382 0 abc
383 10 abc
384 15 abc
385 20 abc
386 25 abc
387 30 abc
388 35 abc
389 SHOW TABLES;
390 Tables_in_test
391 t1
392 SHOW CREATE TABLE t1;
393 Table Create Table
394 t1 CREATE TABLE `t1` (
395 `c1` int NOT NULL,
396 `c2` char(5) DEFAULT NULL
397 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
398 /*!50100 PARTITION BY RANGE (`c1`)
399 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
400 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
401 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
402 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
403 DROP TABLE t1;
404 SHOW TABLES;
405 Tables_in_test
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;
413 c1 c2
414 0 abc
415 5 abc
416 10 abc
417 15 abc
418 20 abc
419 25 abc
420 30 abc
421 35 abc
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;
434 c1 c2
435 0 abc
436 5 abc
437 10 abc
438 15 abc
439 20 abc
440 25 abc
441 30 abc
442 35 abc
443 SHOW TABLES;
444 Tables_in_test
445 t1
446 SHOW CREATE TABLE t1;
447 Table Create Table
448 t1 CREATE TABLE `t1` (
449 `c1` bigint NOT NULL,
450 `c2` char(5) DEFAULT NULL
451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
452 /*!50100 PARTITION BY RANGE (`c1`)
453 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
454 PARTITION s1 VALUES LESS THAN (10) ENGINE = InnoDB,
455 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
456 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
457 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
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;
462 c1 c2
463 0 abc
464 10 abc
465 15 abc
466 20 abc
467 25 abc
468 30 abc
469 35 abc
470 SHOW TABLES;
471 Tables_in_test
472 t1
473 SHOW CREATE TABLE t1;
474 Table Create Table
475 t1 CREATE TABLE `t1` (
476 `c1` bigint NOT NULL,
477 `c2` char(5) DEFAULT NULL
478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
479 /*!50100 PARTITION BY RANGE (`c1`)
480 (PARTITION s0 VALUES LESS THAN (3) ENGINE = InnoDB,
481 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
482 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
483 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
484 DROP TABLE t1;
485 SHOW TABLES;
486 Tables_in_test