Debian Codegraph @

mysql-test/suite/parts/r/partition_reorg_merge.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 p1,p2 INTO (
18 PARTITION m0 VALUES LESS THAN (29));
19 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
20 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
21 PARTITION m0 VALUES LESS THAN (31));
22 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
23 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
24 PARTITION m0 VALUES LESS THAN (30));
25 SELECT * FROM t1 ORDER BY c1;
26 c1 c2
27 0 abc
28 5 abc
29 10 abc
30 15 abc
31 20 abc
32 25 abc
33 30 abc
34 35 abc
35 SHOW TABLES;
36 Tables_in_test
37 t1
38 SHOW CREATE TABLE t1;
39 Table Create Table
40 t1 CREATE TABLE `t1` (
41 `c1` tinyint NOT NULL,
42 `c2` char(5) DEFAULT NULL
43 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
44 /*!50100 PARTITION BY RANGE (`c1`)
45 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
46 PARTITION m0 VALUES LESS THAN (30) ENGINE = InnoDB,
47 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
48 ALTER TABLE t1 DROP PARTITION m1;
49 ERROR HY000: Error in list of partitions to DROP
50 ALTER TABLE t1 DROP PARTITION m0;
51 SELECT * FROM t1 ORDER BY c1;
52 c1 c2
53 0 abc
54 5 abc
55 30 abc
56 35 abc
57 SHOW TABLES;
58 Tables_in_test
59 t1
60 SHOW CREATE TABLE t1;
61 Table Create Table
62 t1 CREATE TABLE `t1` (
63 `c1` tinyint NOT NULL,
64 `c2` char(5) DEFAULT NULL
65 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
66 /*!50100 PARTITION BY RANGE (`c1`)
67 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
68 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
69 DROP TABLE t1;
70 SHOW TABLES;
71 Tables_in_test
72 CREATE TABLE t1 (c1 SMALLINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
73 PARTITION p0 VALUES LESS THAN (10),
74 PARTITION p1 VALUES LESS THAN (20),
75 PARTITION p2 VALUES LESS THAN (30),
76 PARTITION p3 VALUES LESS THAN MAXVALUE);
77 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
78 SELECT * FROM t1 ORDER BY c1;
79 c1 c2
80 0 abc
81 5 abc
82 10 abc
83 15 abc
84 20 abc
85 25 abc
86 30 abc
87 35 abc
88 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
89 PARTITION m0 VALUES LESS THAN (29));
90 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
91 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
92 PARTITION m0 VALUES LESS THAN (31));
93 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
94 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
95 PARTITION m0 VALUES LESS THAN (30));
96 SELECT * FROM t1 ORDER BY c1;
97 c1 c2
98 0 abc
99 5 abc
100 10 abc
101 15 abc
102 20 abc
103 25 abc
104 30 abc
105 35 abc
106 SHOW TABLES;
107 Tables_in_test
108 t1
109 SHOW CREATE TABLE t1;
110 Table Create Table
111 t1 CREATE TABLE `t1` (
112 `c1` smallint NOT NULL,
113 `c2` char(5) DEFAULT NULL
114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
115 /*!50100 PARTITION BY RANGE (`c1`)
116 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
117 PARTITION m0 VALUES LESS THAN (30) ENGINE = InnoDB,
118 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
119 ALTER TABLE t1 DROP PARTITION m1;
120 ERROR HY000: Error in list of partitions to DROP
121 ALTER TABLE t1 DROP PARTITION m0;
122 SELECT * FROM t1 ORDER BY c1;
123 c1 c2
124 0 abc
125 5 abc
126 30 abc
127 35 abc
128 SHOW TABLES;
129 Tables_in_test
130 t1
131 SHOW CREATE TABLE t1;
132 Table Create Table
133 t1 CREATE TABLE `t1` (
134 `c1` smallint NOT NULL,
135 `c2` char(5) DEFAULT NULL
136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
137 /*!50100 PARTITION BY RANGE (`c1`)
138 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
139 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
140 DROP TABLE t1;
141 SHOW TABLES;
142 Tables_in_test
143 CREATE TABLE t1 (c1 MEDIUMINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
144 PARTITION p0 VALUES LESS THAN (10),
145 PARTITION p1 VALUES LESS THAN (20),
146 PARTITION p2 VALUES LESS THAN (30),
147 PARTITION p3 VALUES LESS THAN MAXVALUE);
148 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
149 SELECT * FROM t1 ORDER BY c1;
150 c1 c2
151 0 abc
152 5 abc
153 10 abc
154 15 abc
155 20 abc
156 25 abc
157 30 abc
158 35 abc
159 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
160 PARTITION m0 VALUES LESS THAN (29));
161 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
162 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
163 PARTITION m0 VALUES LESS THAN (31));
164 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
165 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
166 PARTITION m0 VALUES LESS THAN (30));
167 SELECT * FROM t1 ORDER BY c1;
168 c1 c2
169 0 abc
170 5 abc
171 10 abc
172 15 abc
173 20 abc
174 25 abc
175 30 abc
176 35 abc
177 SHOW TABLES;
178 Tables_in_test
179 t1
180 SHOW CREATE TABLE t1;
181 Table Create Table
182 t1 CREATE TABLE `t1` (
183 `c1` mediumint NOT NULL,
184 `c2` char(5) DEFAULT NULL
185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
186 /*!50100 PARTITION BY RANGE (`c1`)
187 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
188 PARTITION m0 VALUES LESS THAN (30) ENGINE = InnoDB,
189 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
190 ALTER TABLE t1 DROP PARTITION m1;
191 ERROR HY000: Error in list of partitions to DROP
192 ALTER TABLE t1 DROP PARTITION m0;
193 SELECT * FROM t1 ORDER BY c1;
194 c1 c2
195 0 abc
196 5 abc
197 30 abc
198 35 abc
199 SHOW TABLES;
200 Tables_in_test
201 t1
202 SHOW CREATE TABLE t1;
203 Table Create Table
204 t1 CREATE TABLE `t1` (
205 `c1` mediumint NOT NULL,
206 `c2` char(5) DEFAULT NULL
207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
208 /*!50100 PARTITION BY RANGE (`c1`)
209 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
210 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
211 DROP TABLE t1;
212 SHOW TABLES;
213 Tables_in_test
214 CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
215 PARTITION p0 VALUES LESS THAN (10),
216 PARTITION p1 VALUES LESS THAN (20),
217 PARTITION p2 VALUES LESS THAN (30),
218 PARTITION p3 VALUES LESS THAN MAXVALUE);
219 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
220 SELECT * FROM t1 ORDER BY c1;
221 c1 c2
222 0 abc
223 5 abc
224 10 abc
225 15 abc
226 20 abc
227 25 abc
228 30 abc
229 35 abc
230 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
231 PARTITION m0 VALUES LESS THAN (29));
232 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
233 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
234 PARTITION m0 VALUES LESS THAN (31));
235 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
236 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
237 PARTITION m0 VALUES LESS THAN (30));
238 SELECT * FROM t1 ORDER BY c1;
239 c1 c2
240 0 abc
241 5 abc
242 10 abc
243 15 abc
244 20 abc
245 25 abc
246 30 abc
247 35 abc
248 SHOW TABLES;
249 Tables_in_test
250 t1
251 SHOW CREATE TABLE t1;
252 Table Create Table
253 t1 CREATE TABLE `t1` (
254 `c1` int NOT NULL,
255 `c2` char(5) DEFAULT NULL
256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
257 /*!50100 PARTITION BY RANGE (`c1`)
258 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
259 PARTITION m0 VALUES LESS THAN (30) ENGINE = InnoDB,
260 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
261 ALTER TABLE t1 DROP PARTITION m1;
262 ERROR HY000: Error in list of partitions to DROP
263 ALTER TABLE t1 DROP PARTITION m0;
264 SELECT * FROM t1 ORDER BY c1;
265 c1 c2
266 0 abc
267 5 abc
268 30 abc
269 35 abc
270 SHOW TABLES;
271 Tables_in_test
272 t1
273 SHOW CREATE TABLE t1;
274 Table Create Table
275 t1 CREATE TABLE `t1` (
276 `c1` int NOT NULL,
277 `c2` char(5) DEFAULT NULL
278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
279 /*!50100 PARTITION BY RANGE (`c1`)
280 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
281 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
282 DROP TABLE t1;
283 SHOW TABLES;
284 Tables_in_test
285 CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
286 PARTITION p0 VALUES LESS THAN (10),
287 PARTITION p1 VALUES LESS THAN (20),
288 PARTITION p2 VALUES LESS THAN (30),
289 PARTITION p3 VALUES LESS THAN MAXVALUE);
290 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
291 SELECT * FROM t1 ORDER BY c1;
292 c1 c2
293 0 abc
294 5 abc
295 10 abc
296 15 abc
297 20 abc
298 25 abc
299 30 abc
300 35 abc
301 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
302 PARTITION m0 VALUES LESS THAN (29));
303 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
304 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
305 PARTITION m0 VALUES LESS THAN (31));
306 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
307 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
308 PARTITION m0 VALUES LESS THAN (30));
309 SELECT * FROM t1 ORDER BY c1;
310 c1 c2
311 0 abc
312 5 abc
313 10 abc
314 15 abc
315 20 abc
316 25 abc
317 30 abc
318 35 abc
319 SHOW TABLES;
320 Tables_in_test
321 t1
322 SHOW CREATE TABLE t1;
323 Table Create Table
324 t1 CREATE TABLE `t1` (
325 `c1` int NOT NULL,
326 `c2` char(5) DEFAULT NULL
327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
328 /*!50100 PARTITION BY RANGE (`c1`)
329 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
330 PARTITION m0 VALUES LESS THAN (30) ENGINE = InnoDB,
331 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
332 ALTER TABLE t1 DROP PARTITION m1;
333 ERROR HY000: Error in list of partitions to DROP
334 ALTER TABLE t1 DROP PARTITION m0;
335 SELECT * FROM t1 ORDER BY c1;
336 c1 c2
337 0 abc
338 5 abc
339 30 abc
340 35 abc
341 SHOW TABLES;
342 Tables_in_test
343 t1
344 SHOW CREATE TABLE t1;
345 Table Create Table
346 t1 CREATE TABLE `t1` (
347 `c1` int NOT NULL,
348 `c2` char(5) DEFAULT NULL
349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
350 /*!50100 PARTITION BY RANGE (`c1`)
351 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
352 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
353 DROP TABLE t1;
354 SHOW TABLES;
355 Tables_in_test
356 CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 CHAR(5)) PARTITION BY RANGE ( c1 ) (
357 PARTITION p0 VALUES LESS THAN (10),
358 PARTITION p1 VALUES LESS THAN (20),
359 PARTITION p2 VALUES LESS THAN (30),
360 PARTITION p3 VALUES LESS THAN MAXVALUE);
361 INSERT INTO t1 VALUES(0,'abc'),(5,'abc'),(10,'abc'),(15,'abc'),(20,'abc'),(25,'abc'),(30,'abc'),(35,'abc');
362 SELECT * FROM t1 ORDER BY c1;
363 c1 c2
364 0 abc
365 5 abc
366 10 abc
367 15 abc
368 20 abc
369 25 abc
370 30 abc
371 35 abc
372 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
373 PARTITION m0 VALUES LESS THAN (29));
374 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
375 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
376 PARTITION m0 VALUES LESS THAN (31));
377 ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
378 ALTER TABLE t1 REORGANIZE PARTITION p1,p2 INTO (
379 PARTITION m0 VALUES LESS THAN (30));
380 SELECT * FROM t1 ORDER BY c1;
381 c1 c2
382 0 abc
383 5 abc
384 10 abc
385 15 abc
386 20 abc
387 25 abc
388 30 abc
389 35 abc
390 SHOW TABLES;
391 Tables_in_test
392 t1
393 SHOW CREATE TABLE t1;
394 Table Create Table
395 t1 CREATE TABLE `t1` (
396 `c1` bigint NOT NULL,
397 `c2` char(5) DEFAULT NULL
398 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
399 /*!50100 PARTITION BY RANGE (`c1`)
400 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
401 PARTITION m0 VALUES LESS THAN (30) ENGINE = InnoDB,
402 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
403 ALTER TABLE t1 DROP PARTITION m1;
404 ERROR HY000: Error in list of partitions to DROP
405 ALTER TABLE t1 DROP PARTITION m0;
406 SELECT * FROM t1 ORDER BY c1;
407 c1 c2
408 0 abc
409 5 abc
410 30 abc
411 35 abc
412 SHOW TABLES;
413 Tables_in_test
414 t1
415 SHOW CREATE TABLE t1;
416 Table Create Table
417 t1 CREATE TABLE `t1` (
418 `c1` bigint NOT NULL,
419 `c2` char(5) DEFAULT NULL
420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
421 /*!50100 PARTITION BY RANGE (`c1`)
422 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
423 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
424 DROP TABLE t1;
425 SHOW TABLES;
426 Tables_in_test