Debian Codegraph @

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

raw
1 use test;
2 call mtr.add_suppression("row in wrong partition.*from REBUILD/REORGANIZED");
3 DROP TABLE IF EXISTS t_10;
4 DROP TABLE IF EXISTS t_100;
5 DROP TABLE IF EXISTS t_1000;
6 DROP TABLE IF EXISTS tp_r;
7 DROP TABLE IF EXISTS tp_rvar;
8 DROP TABLE IF EXISTS tp_rintvar;
9 DROP TABLE IF EXISTS tp_rdate;
10 DROP TABLE IF EXISTS tp_rintdate;
11 DROP TABLE IF EXISTS tsp_r;
12 DROP TABLE IF EXISTS tsp_rvar;
13 DROP TABLE IF EXISTS tsp_rvar1;
14 DROP TABLE IF EXISTS tp_l;
15 DROP TABLE IF EXISTS tsp_l;
16 DROP TABLE IF EXISTS tsp_lvar;
17 DROP TABLE IF EXISTS tsp_ldate;
18 DROP TABLE IF EXISTS tp_k;
19 DROP TABLE IF EXISTS tp_kvar;
20 DROP TABLE IF EXISTS tp_kdate;
21 DROP TABLE IF EXISTS tp_h;
22 DROP TABLE IF EXISTS tp_hvar;
23 DROP TABLE IF EXISTS tp_hdate;
24 DROP TABLE IF EXISTS tsp_00;
25 DROP TABLE IF EXISTS tsp_01;
26 DROP TABLE IF EXISTS tsp_02;
27 DROP TABLE IF EXISTS tsp_03;
28 DROP TABLE IF EXISTS tsp_04;
29 DROP TABLE IF EXISTS t_empty;
30 DROP TABLE IF EXISTS t_null;
31 CREATE TABLE t_10 (a INT,
32 b VARCHAR(25),
33 c DATE,
34 PRIMARY KEY (a))
35 charset latin1
36 ENGINE = InnoDB;
37 CREATE TABLE t_100 (a INT,
38 b VARCHAR(25),
39 c DATE,
40 PRIMARY KEY (a))
41 charset latin1
42 ENGINE = InnoDB;
43 CREATE TABLE t_1000 (a INT,
44 b VARCHAR(25),
45 c DATE,
46 PRIMARY KEY (a))
47 charset latin1
48 ENGINE = InnoDB;
49 INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01');
50 INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01');
51 INSERT INTO t_100 VALUES (91, "Ninety-one", '2091-01-01'), (93, "Ninety-three", '2093-01-01'), (95, "Ninety-five", '2095-01-01'), (99, "Ninety-nine", '2099-01-01');
52 INSERT INTO t_1000 VALUES (111, "Hundred elven", '2111-01-01'), (113, "Hundred thirdteen", '2113-01-01'), (115, "Hundred fiveteen", '2115-01-01'), (119, "Hundred nineteen", '2119-01-01');
53 INSERT INTO t_1000 VALUES (131, "Hundred thirty-one", '2131-01-01'), (133, "Hundred thirty-three", '2133-01-01'), (135, "Hundred thirty-five", '2135-01-01'), (139, "Hundred thirty-nine", '2139-01-01');
54 INSERT INTO t_1000 VALUES (151, "Hundred fifty-one", '2151-01-01'), (153, "Hundred fifty-three", '2153-01-01'), (155, "Hundred fity-five", '2155-01-01'), (159, "Hundred fifty-nine", '2159-01-01');
55 INSERT INTO t_1000 VALUES (191, "Hundred ninety-one", '2191-01-01'), (193, "Hundred ninety-three", '2193-01-01'), (195, "Hundred ninety-five", '2195-01-01'), (199, "Hundred ninety-nine", '2199-01-01');
56 CREATE TABLE t_empty (a INT,
57 b VARCHAR(25),
58 c DATE,
59 PRIMARY KEY (a))
60 charset latin1
61 ENGINE = InnoDB;
62 CREATE TABLE t_null (a INT,
63 b VARCHAR(25),
64 c DATE,
65 PRIMARY KEY (a))
66 charset latin1
67 ENGINE = InnoDB;
68 CREATE TABLE tsp_01(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
69 charset latin1
70 ENGINE = InnoDB
71 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
72 CREATE TABLE tsp_02(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
73 charset latin1
74 ENGINE = InnoDB
75 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
76 CREATE TABLE tsp_03(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
77 charset latin1
78 ENGINE = InnoDB
79 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
80 CREATE TABLE tsp_04(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
81 charset latin1
82 ENGINE = InnoDB
83 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
84 CREATE TABLE tsp_00(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
85 charset latin1
86 ENGINE = InnoDB
87 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
88 CREATE TABLE tp_r (a INT,
89 b VARCHAR(25),
90 c DATE,
91 PRIMARY KEY (a))
92 charset latin1
93 ENGINE = InnoDB
94 PARTITION BY RANGE (a)
95 (PARTITION p0 VALUES LESS THAN (10) ,
96 PARTITION p1 VALUES LESS THAN (100) ,
97 PARTITION p2 VALUES LESS THAN (1000) );
98 CREATE TABLE tsp_r (a INT,
99 b VARCHAR(25),
100 c DATE,
101 PRIMARY KEY (a))
102 charset latin1
103 ENGINE = InnoDB
104 PARTITION BY RANGE (a)
105 SUBPARTITION BY HASH(a)
106 (PARTITION p0 VALUES LESS THAN (10)
107 (SUBPARTITION sp00,
108 SUBPARTITION sp01,
109 SUBPARTITION sp02,
110 SUBPARTITION sp03,
111 SUBPARTITION sp04),
112 PARTITION p1 VALUES LESS THAN (100)
113 (SUBPARTITION sp10 ,
114 SUBPARTITION sp11 ,
115 SUBPARTITION sp12 ,
116 SUBPARTITION sp13 ,
117 SUBPARTITION sp14 ),
118 PARTITION p2 VALUES LESS THAN (1000)
119 (SUBPARTITION sp20,
120 SUBPARTITION sp21,
121 SUBPARTITION sp22,
122 SUBPARTITION sp23,
123 SUBPARTITION sp24));
124 INSERT INTO tp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
125 INSERT INTO tp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
126 INSERT INTO tp_r VALUES (112, "Hundred twelve", '2112-01-01'), (114, "Hundred fourteen", '2114-01-01'), (116, "Hundred sixteen", '2116-01-01'), (118, "Hundred eightteen", '2118-01-01');
127 INSERT INTO tp_r VALUES (122, "Hundred twenty-two", '2122-01-01'), (124, "Hundred twenty-four", '2124-01-01'), (126, "Hundred twenty-six", '2126-01-01'), (128, "Hundred twenty-eight", '2128-01-01');
128 INSERT INTO tp_r VALUES (162, "Hundred sixty-two", '2162-01-01'), (164, "Hundred sixty-four", '2164-01-01'), (166, "Hundred sixty-six", '2166-01-01'), (168, "Hundred sixty-eight", '2168-01-01');
129 INSERT INTO tp_r VALUES (182, "Hundred eighty-two", '2182-01-01'), (184, "Hundred eighty-four", '2184-01-01'), (186, "Hundred eighty-six", '2186-01-01'), (188, "Hundred eighty-eight", '2188-01-01');
130 INSERT INTO tsp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
131 INSERT INTO tsp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
132 INSERT INTO tsp_r VALUES (112, "Hundred twelve", '2112-01-01'), (114, "Hundred fourteen", '2114-01-01'), (116, "Hundred sixteen", '2116-01-01'), (118, "Hundred eightteen", '2118-01-01');
133 INSERT INTO tsp_r VALUES (122, "Hundred twenty-two", '2122-01-01'), (124, "Hundred twenty-four", '2124-01-01'), (126, "Hundred twenty-six", '2126-01-01'), (128, "Hundred twenty-eight", '2128-01-01');
134 INSERT INTO tsp_r VALUES (162, "Hundred sixty-two", '2162-01-01'), (164, "Hundred sixty-four", '2164-01-01'), (166, "Hundred sixty-six", '2166-01-01'), (168, "Hundred sixty-eight", '2168-01-01');
135 INSERT INTO tsp_r VALUES (182, "Hundred eight-two", '2182-01-01'), (184, "Hundred eighty-four", '2184-01-01'), (186, "Hundred eighty-six", '2186-01-01'), (188, "Hundred eighty-eight", '2188-01-01');
136 CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (b))
137 charset latin1
138 ENGINE = InnoDB
139 PARTITION BY RANGE (b)
140 (PARTITION p0 VALUES LESS THAN ('H') ,
141 PARTITION p1 VALUES LESS THAN ('Q') ,
142 PARTITION p2 VALUES LESS THAN ('Z') ,
143 PARTITION p3 VALUES LESS THAN (MAXVALUE) )
144 AS SELECT a, b FROM tp_r;
145 ERROR HY000: VALUES value for partition 'p0' must have type INT
146 CREATE TABLE tp_rintvar(a INT,b VARCHAR(25),c DATE,
147 PRIMARY KEY (a,b))
148 charset latin1
149 ENGINE = InnoDB
150 PARTITION BY RANGE COLUMNS (a,b)
151 (PARTITION p0 VALUES LESS THAN (10,'HHHHHHHHHHHHHHHHHHHHHHHHH'),
152 PARTITION p1 VALUES LESS THAN (100,'PPPPPPPPPPPPPPPPPPPPPPPPP'),
153 PARTITION p2 VALUES LESS THAN (1000,'WWWWWWWWWWWWWWWWWWWWWWWWW'),
154 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE))
155 AS SELECT a, b, c FROM tp_r;
156 CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE)
157 charset latin1
158 ENGINE = InnoDB
159 PARTITION BY RANGE COLUMNS (b)
160 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'),
161 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'),
162 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'),
163 PARTITION p3 VALUES LESS THAN (MAXVALUE))
164 AS SELECT a, b, c FROM tp_r;
165 CREATE TABLE tp_rintdate(a INT,b VARCHAR(25),c DATE,
166 PRIMARY KEY (a,c))
167 charset latin1
168 ENGINE = InnoDB
169 PARTITION BY RANGE COLUMNS (a,c)
170 (PARTITION p0 VALUES LESS THAN (10,'2010-01-01'),
171 PARTITION p1 VALUES LESS THAN (100,'2200-01-01'),
172 PARTITION p2 VALUES LESS THAN (1000,'2300-01-01'),
173 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE))
174 AS SELECT a, b, c FROM t_10;
175 INSERT tp_rintdate SELECT * FROM t_100;
176 INSERT tp_rintdate SELECT * FROM t_1000;
177 CREATE TABLE tp_rdate(a INT,b VARCHAR(25),c DATE,
178 PRIMARY KEY (c))
179 charset latin1
180 ENGINE = InnoDB
181 PARTITION BY RANGE COLUMNS (c)
182 (PARTITION p0 VALUES LESS THAN ('2010-01-01'),
183 PARTITION p1 VALUES LESS THAN ('2200-01-01'),
184 PARTITION p2 VALUES LESS THAN ('2300-01-01'),
185 PARTITION p3 VALUES LESS THAN (MAXVALUE))
186 AS SELECT a, b, c FROM tp_r;
187 CREATE TABLE tsp_rvar(a INT,b VARCHAR(25),c DATE)
188 charset latin1
189 ENGINE = InnoDB
190 PARTITION BY RANGE COLUMNS (b)
191 SUBPARTITION BY HASH(a)
192 SUBPARTITIONS 5
193 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH')
194 (SUBPARTITION sp00,
195 SUBPARTITION sp01,
196 SUBPARTITION sp02,
197 SUBPARTITION sp03,
198 SUBPARTITION sp04),
199 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP')
200 (SUBPARTITION sp10,
201 SUBPARTITION sp11,
202 SUBPARTITION sp12,
203 SUBPARTITION sp13,
204 SUBPARTITION sp14),
205 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW')
206 (SUBPARTITION sp20,
207 SUBPARTITION sp21,
208 SUBPARTITION sp22,
209 SUBPARTITION sp23,
210 SUBPARTITION sp24))
211 AS SELECT a, b, c FROM tsp_r;
212 CREATE TABLE tsp_rvar1(a INT,b VARCHAR(25),c DATE)
213 charset latin1
214 ENGINE = InnoDB
215 PARTITION BY RANGE COLUMNS (b)
216 SUBPARTITION BY HASH(a)
217 SUBPARTITIONS 5
218 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'),
219 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'),
220 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'))
221 AS SELECT a, b, c FROM tsp_r;
222 CREATE TABLE tp_l (a INT,
223 b VARCHAR(25),
224 c DATE,
225 PRIMARY KEY (a))
226 charset latin1
227 ENGINE = InnoDB
228 PARTITION BY LIST (a)
229 (PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9),
230 PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99),
231 PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119,
232 120,121,122,123,124,125,126,127,128,129,
233 130,131,132,133,134,135,136,137,138,139,
234 150,151,152,153,154,155,156,157,158,159,
235 160,161,162,163,164,165,166,167,168,169,
236 180,181,182,183,184,185,186,187,188,189,
237 190,191,192,193,194,195,196,197,198,199))
238 AS SELECT a, b, c FROM tp_r;
239 CREATE TABLE tsp_l (a INT,
240 b VARCHAR(25),
241 c DATE,
242 PRIMARY KEY (a))
243 charset latin1
244 ENGINE = InnoDB
245 PARTITION BY LIST (a)
246 SUBPARTITION BY HASH(a)
247 (PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9)
248 (SUBPARTITION sp00,
249 SUBPARTITION sp01,
250 SUBPARTITION sp02,
251 SUBPARTITION sp03,
252 SUBPARTITION sp04),
253 PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99)
254 (SUBPARTITION sp10,
255 SUBPARTITION sp11,
256 SUBPARTITION sp12,
257 SUBPARTITION sp13,
258 SUBPARTITION sp14),
259 PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119,
260 120,121,122,123,124,125,126,127,128,129,
261 130,131,132,133,134,135,136,137,138,139,
262 150,151,152,153,154,155,156,157,158,159,
263 160,161,162,163,164,165,166,167,168,169,
264 180,181,182,183,184,185,186,187,188,189,
265 190,191,192,193,194,195,196,197,198,199)
266 (SUBPARTITION sp20,
267 SUBPARTITION sp21,
268 SUBPARTITION sp22,
269 SUBPARTITION sp23,
270 SUBPARTITION sp24))
271 AS SELECT a, b, c FROM tsp_r;
272 CREATE TABLE tp_k (a INT,
273 b VARCHAR(25),
274 c DATE,
275 PRIMARY KEY (a))
276 charset latin1
277 ENGINE = InnoDB
278 PARTITION BY KEY (a)
279 PARTITIONS 3
280 AS SELECT a, b, c FROM tp_r;
281 CREATE TABLE tp_kvar (a INT,
282 b VARCHAR(25),
283 c DATE,
284 PRIMARY KEY (b))
285 charset latin1
286 ENGINE = InnoDB
287 PARTITION BY KEY (b)
288 PARTITIONS 3
289 AS SELECT a, b, c FROM tp_r;
290 CREATE TABLE tp_kdate (a INT,
291 b VARCHAR(25),
292 c DATE,
293 PRIMARY KEY (c))
294 charset latin1
295 ENGINE = InnoDB
296 PARTITION BY KEY (c)
297 PARTITIONS 3
298 AS SELECT a, b, c FROM tp_r;
299 CREATE TABLE tp_h (a INT,
300 b VARCHAR(25),
301 c DATE,
302 PRIMARY KEY (a))
303 charset latin1
304 ENGINE = InnoDB
305 PARTITION BY HASH (a)
306 PARTITIONS 3
307 AS SELECT a, b, c FROM tp_r;
308 ####################################################################
309 ### Hash partitioned tables with/out validation
310 ALTER TABLE tp_h EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
311 ERROR HY000: Found a row that does not match the partition
312 SELECT * FROM tp_h PARTITION (p0);
313 a b c
314 114 Hundred fourteen 2114-01-01
315 12 twelve 2012-01-01
316 126 Hundred twenty-six 2126-01-01
317 162 Hundred sixty-two 2162-01-01
318 168 Hundred sixty-eight 2168-01-01
319 18 Eightteen 2018-01-01
320 186 Hundred eighty-six 2186-01-01
321 6 Six 2006-01-01
322 SELECT * FROM tp_h PARTITION (p1);
323 a b c
324 112 Hundred twelve 2112-01-01
325 118 Hundred eightteen 2118-01-01
326 124 Hundred twenty-four 2124-01-01
327 16 Sixteen 2016-01-01
328 166 Hundred sixty-six 2166-01-01
329 184 Hundred eighty-four 2184-01-01
330 4 Four 2004-01-01
331 SELECT * FROM tp_h PARTITION (p2);
332 a b c
333 116 Hundred sixteen 2116-01-01
334 122 Hundred twenty-two 2122-01-01
335 128 Hundred twenty-eight 2128-01-01
336 14 Fourteen 2014-01-01
337 164 Hundred sixty-four 2164-01-01
338 182 Hundred eighty-two 2182-01-01
339 188 Hundred eighty-eight 2188-01-01
340 2 Two 2002-01-01
341 8 Eight 2008-01-01
342 ALTER TABLE tp_h EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION;
343 ERROR HY000: Found a row that does not match the partition
344 ALTER TABLE tp_h EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION;
345 SELECT * FROM tp_h PARTITION (p0);
346 a b c
347 11 Eleven 2011-01-01
348 13 Thirdteen 2013-01-01
349 15 Fifeteen 2015-01-01
350 19 Nineteen 2019-01-01
351 91 Ninety-one 2091-01-01
352 93 Ninety-three 2093-01-01
353 95 Ninety-five 2095-01-01
354 99 Ninety-nine 2099-01-01
355 SELECT * FROM tp_h PARTITION (p1);
356 a b c
357 112 Hundred twelve 2112-01-01
358 118 Hundred eightteen 2118-01-01
359 124 Hundred twenty-four 2124-01-01
360 16 Sixteen 2016-01-01
361 166 Hundred sixty-six 2166-01-01
362 184 Hundred eighty-four 2184-01-01
363 4 Four 2004-01-01
364 SELECT * FROM tp_h PARTITION (p2);
365 a b c
366 116 Hundred sixteen 2116-01-01
367 122 Hundred twenty-two 2122-01-01
368 128 Hundred twenty-eight 2128-01-01
369 14 Fourteen 2014-01-01
370 164 Hundred sixty-four 2164-01-01
371 182 Hundred eighty-two 2182-01-01
372 188 Hundred eighty-eight 2188-01-01
373 2 Two 2002-01-01
374 8 Eight 2008-01-01
375 ALTER TABLE tp_h COALESCE PARTITION 1;
376 SELECT * FROM tp_h PARTITION (p0);
377 a b c
378 112 Hundred twelve 2112-01-01
379 116 Hundred sixteen 2116-01-01
380 118 Hundred eightteen 2118-01-01
381 122 Hundred twenty-two 2122-01-01
382 124 Hundred twenty-four 2124-01-01
383 128 Hundred twenty-eight 2128-01-01
384 14 Fourteen 2014-01-01
385 16 Sixteen 2016-01-01
386 164 Hundred sixty-four 2164-01-01
387 166 Hundred sixty-six 2166-01-01
388 182 Hundred eighty-two 2182-01-01
389 184 Hundred eighty-four 2184-01-01
390 188 Hundred eighty-eight 2188-01-01
391 2 Two 2002-01-01
392 4 Four 2004-01-01
393 8 Eight 2008-01-01
394 SELECT * FROM tp_h PARTITION (p1);
395 a b c
396 11 Eleven 2011-01-01
397 13 Thirdteen 2013-01-01
398 15 Fifeteen 2015-01-01
399 19 Nineteen 2019-01-01
400 91 Ninety-one 2091-01-01
401 93 Ninety-three 2093-01-01
402 95 Ninety-five 2095-01-01
403 99 Ninety-nine 2099-01-01
404 ALTER TABLE tp_h ADD PARTITION PARTITIONS 4;
405 ALTER TABLE tp_h EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
406 SELECT * FROM tp_h PARTITION (p2);
407 a b c
408 114 Hundred fourteen 2114-01-01
409 12 twelve 2012-01-01
410 126 Hundred twenty-six 2126-01-01
411 162 Hundred sixty-two 2162-01-01
412 168 Hundred sixty-eight 2168-01-01
413 18 Eightteen 2018-01-01
414 186 Hundred eighty-six 2186-01-01
415 6 Six 2006-01-01
416 ALTER TABLE tp_h ANALYZE PARTITION p0;
417 Table Op Msg_type Msg_text
418 test.tp_h analyze status OK
419 ALTER TABLE tp_h OPTIMIZE PARTITION p0;
420 Table Op Msg_type Msg_text
421 test.tp_h optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
422 test.tp_h optimize status OK
423 CREATE TABLE t_11 LIKE t_10;
424 INSERT INTO t_11 SELECT * FROM t_10;
425 INSERT INTO t_11 SELECT * FROM t_100;
426 ALTER TABLE tp_h EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
427 SELECT * FROM tp_h PARTITION (p0);
428 a b c
429 1 One 2001-01-01
430 116 Hundred sixteen 2116-01-01
431 122 Hundred twenty-two 2122-01-01
432 128 Hundred twenty-eight 2128-01-01
433 14 Fourteen 2014-01-01
434 164 Hundred sixty-four 2164-01-01
435 182 Hundred eighty-two 2182-01-01
436 188 Hundred eighty-eight 2188-01-01
437 2 Two 2002-01-01
438 3 Three 2003-01-01
439 5 Five 2005-01-01
440 8 Eight 2008-01-01
441 9 Nine 2009-01-01
442 DELETE FROM tp_h PARTITION (p1);
443 SELECT * FROM tp_h PARTITION (p1);
444 a b c
445 ALTER TABLE tp_h CHECK PARTITION p0 ;
446 Table Op Msg_type Msg_text
447 test.tp_h check error Found a misplaced row in part 0 should be in part 1:
448 a:1
449 test.tp_h check error Partition p0 returned error
450 test.tp_h check error Table upgrade required. Please do "REPAIR TABLE `tp_h`" or dump/reload to fix it!
451 ALTER TABLE tp_h REPAIR PARTITION p0 ;
452 Table Op Msg_type Msg_text
453 test.tp_h repair warning Moved 13 misplaced rows
454 test.tp_h repair status OK
455 SELECT * FROM tp_h PARTITION (p0);
456 a b c
457 SELECT * FROM tp_h PARTITION (p1);
458 a b c
459 1 One 2001-01-01
460 SELECT * FROM tp_h PARTITION (p2);
461 a b c
462 114 Hundred fourteen 2114-01-01
463 116 Hundred sixteen 2116-01-01
464 12 twelve 2012-01-01
465 122 Hundred twenty-two 2122-01-01
466 126 Hundred twenty-six 2126-01-01
467 128 Hundred twenty-eight 2128-01-01
468 14 Fourteen 2014-01-01
469 162 Hundred sixty-two 2162-01-01
470 164 Hundred sixty-four 2164-01-01
471 168 Hundred sixty-eight 2168-01-01
472 18 Eightteen 2018-01-01
473 182 Hundred eighty-two 2182-01-01
474 186 Hundred eighty-six 2186-01-01
475 188 Hundred eighty-eight 2188-01-01
476 2 Two 2002-01-01
477 6 Six 2006-01-01
478 8 Eight 2008-01-01
479 ALTER TABLE tp_h COALESCE PARTITION 2;
480 SELECT * FROM tp_h PARTITION (p0);
481 a b c
482 112 Hundred twelve 2112-01-01
483 116 Hundred sixteen 2116-01-01
484 12 twelve 2012-01-01
485 124 Hundred twenty-four 2124-01-01
486 128 Hundred twenty-eight 2128-01-01
487 16 Sixteen 2016-01-01
488 164 Hundred sixty-four 2164-01-01
489 168 Hundred sixty-eight 2168-01-01
490 184 Hundred eighty-four 2184-01-01
491 188 Hundred eighty-eight 2188-01-01
492 4 Four 2004-01-01
493 8 Eight 2008-01-01
494 SELECT * FROM tp_h PARTITION (p1);
495 a b c
496 1 One 2001-01-01
497 5 Five 2005-01-01
498 9 Nine 2009-01-01
499 93 Ninety-three 2093-01-01
500 SELECT * FROM tp_h PARTITION (p2);
501 a b c
502 114 Hundred fourteen 2114-01-01
503 118 Hundred eightteen 2118-01-01
504 122 Hundred twenty-two 2122-01-01
505 126 Hundred twenty-six 2126-01-01
506 14 Fourteen 2014-01-01
507 162 Hundred sixty-two 2162-01-01
508 166 Hundred sixty-six 2166-01-01
509 18 Eightteen 2018-01-01
510 182 Hundred eighty-two 2182-01-01
511 186 Hundred eighty-six 2186-01-01
512 2 Two 2002-01-01
513 6 Six 2006-01-01
514 SELECT * FROM tp_h PARTITION (p3);
515 a b c
516 11 Eleven 2011-01-01
517 15 Fifeteen 2015-01-01
518 3 Three 2003-01-01
519 95 Ninety-five 2095-01-01
520 99 Ninety-nine 2099-01-01
521 DROP TABLE IF EXISTS t_11;
522 DROP TABLE IF EXISTS t_10;
523 DROP TABLE IF EXISTS t_100;
524 DROP TABLE IF EXISTS t_1000;
525 DROP TABLE IF EXISTS tp_r;
526 DROP TABLE IF EXISTS tp_rvar;
527 DROP TABLE IF EXISTS tp_rintvar;
528 DROP TABLE IF EXISTS tp_rdate;
529 DROP TABLE IF EXISTS tp_rintdate;
530 DROP TABLE IF EXISTS tsp_r;
531 DROP TABLE IF EXISTS tsp_rvar;
532 DROP TABLE IF EXISTS tsp_rvar1;
533 DROP TABLE IF EXISTS tp_l;
534 DROP TABLE IF EXISTS tsp_l;
535 DROP TABLE IF EXISTS tsp_lvar;
536 DROP TABLE IF EXISTS tsp_ldate;
537 DROP TABLE IF EXISTS tp_k;
538 DROP TABLE IF EXISTS tp_kvar;
539 DROP TABLE IF EXISTS tp_kdate;
540 DROP TABLE IF EXISTS tp_h;
541 DROP TABLE IF EXISTS tp_hvar;
542 DROP TABLE IF EXISTS tp_hdate;
543 DROP TABLE IF EXISTS tsp_00;
544 DROP TABLE IF EXISTS tsp_01;
545 DROP TABLE IF EXISTS tsp_02;
546 DROP TABLE IF EXISTS tsp_03;
547 DROP TABLE IF EXISTS tsp_04;
548 DROP TABLE IF EXISTS t_empty;
549 DROP TABLE IF EXISTS t_null;