Debian Codegraph @

mysql-test/suite/parts/r/part_exch_valid_key_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 ### Key partitioned tables with/out validation
310 ALTER TABLE tp_k 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_k PARTITION (p0);
313 a b c
314 116 Hundred sixteen 2116-01-01
315 122 Hundred twenty-two 2122-01-01
316 128 Hundred twenty-eight 2128-01-01
317 16 Sixteen 2016-01-01
318 166 Hundred sixty-six 2166-01-01
319 184 Hundred eighty-four 2184-01-01
320 4 Four 2004-01-01
321 SELECT * FROM tp_k PARTITION (p1);
322 a b c
323 112 Hundred twelve 2112-01-01
324 118 Hundred eightteen 2118-01-01
325 12 twelve 2012-01-01
326 124 Hundred twenty-four 2124-01-01
327 162 Hundred sixty-two 2162-01-01
328 168 Hundred sixty-eight 2168-01-01
329 18 Eightteen 2018-01-01
330 186 Hundred eighty-six 2186-01-01
331 6 Six 2006-01-01
332 SELECT * FROM tp_k PARTITION (p2);
333 a b c
334 114 Hundred fourteen 2114-01-01
335 126 Hundred twenty-six 2126-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_k 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_k EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION;
345 SELECT * FROM tp_k 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_k PARTITION (p1);
356 a b c
357 112 Hundred twelve 2112-01-01
358 118 Hundred eightteen 2118-01-01
359 12 twelve 2012-01-01
360 124 Hundred twenty-four 2124-01-01
361 162 Hundred sixty-two 2162-01-01
362 168 Hundred sixty-eight 2168-01-01
363 18 Eightteen 2018-01-01
364 186 Hundred eighty-six 2186-01-01
365 6 Six 2006-01-01
366 SELECT * FROM tp_k PARTITION (p2);
367 a b c
368 114 Hundred fourteen 2114-01-01
369 126 Hundred twenty-six 2126-01-01
370 14 Fourteen 2014-01-01
371 164 Hundred sixty-four 2164-01-01
372 182 Hundred eighty-two 2182-01-01
373 188 Hundred eighty-eight 2188-01-01
374 2 Two 2002-01-01
375 8 Eight 2008-01-01
376 ALTER TABLE tp_k COALESCE PARTITION 1;
377 SELECT * FROM tp_k PARTITION (p0);
378 a b c
379 11 Eleven 2011-01-01
380 13 Thirdteen 2013-01-01
381 15 Fifeteen 2015-01-01
382 19 Nineteen 2019-01-01
383 91 Ninety-one 2091-01-01
384 93 Ninety-three 2093-01-01
385 95 Ninety-five 2095-01-01
386 99 Ninety-nine 2099-01-01
387 SELECT * FROM tp_k PARTITION (p1);
388 a b c
389 112 Hundred twelve 2112-01-01
390 114 Hundred fourteen 2114-01-01
391 118 Hundred eightteen 2118-01-01
392 12 twelve 2012-01-01
393 124 Hundred twenty-four 2124-01-01
394 126 Hundred twenty-six 2126-01-01
395 14 Fourteen 2014-01-01
396 162 Hundred sixty-two 2162-01-01
397 164 Hundred sixty-four 2164-01-01
398 168 Hundred sixty-eight 2168-01-01
399 18 Eightteen 2018-01-01
400 182 Hundred eighty-two 2182-01-01
401 186 Hundred eighty-six 2186-01-01
402 188 Hundred eighty-eight 2188-01-01
403 2 Two 2002-01-01
404 6 Six 2006-01-01
405 8 Eight 2008-01-01
406 ALTER TABLE tp_k ADD PARTITION PARTITIONS 4;
407 ALTER TABLE tp_k EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
408 SELECT * FROM tp_k PARTITION (p2);
409 a b c
410 116 Hundred sixteen 2116-01-01
411 122 Hundred twenty-two 2122-01-01
412 128 Hundred twenty-eight 2128-01-01
413 16 Sixteen 2016-01-01
414 166 Hundred sixty-six 2166-01-01
415 184 Hundred eighty-four 2184-01-01
416 4 Four 2004-01-01
417 ALTER TABLE tp_k ANALYZE PARTITION p0;
418 Table Op Msg_type Msg_text
419 test.tp_k analyze status OK
420 ALTER TABLE tp_k OPTIMIZE PARTITION p0;
421 Table Op Msg_type Msg_text
422 test.tp_k optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
423 test.tp_k optimize status OK
424 ALTER TABLE tp_k EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
425 ERROR HY000: Found a row that does not match the partition
426 SELECT * FROM tp_k PARTITION (p0);
427 a b c
428 11 Eleven 2011-01-01
429 91 Ninety-one 2091-01-01
430 99 Ninety-nine 2099-01-01
431 SELECT * FROM tp_k PARTITION (p1);
432 a b c
433 112 Hundred twelve 2112-01-01
434 118 Hundred eightteen 2118-01-01
435 12 twelve 2012-01-01
436 124 Hundred twenty-four 2124-01-01
437 162 Hundred sixty-two 2162-01-01
438 168 Hundred sixty-eight 2168-01-01
439 18 Eightteen 2018-01-01
440 186 Hundred eighty-six 2186-01-01
441 6 Six 2006-01-01
442 SELECT * FROM tp_k PARTITION (p2);
443 a b c
444 116 Hundred sixteen 2116-01-01
445 122 Hundred twenty-two 2122-01-01
446 128 Hundred twenty-eight 2128-01-01
447 16 Sixteen 2016-01-01
448 166 Hundred sixty-six 2166-01-01
449 184 Hundred eighty-four 2184-01-01
450 4 Four 2004-01-01
451 CREATE TABLE t_11 LIKE tp_k;
452 ALTER TABLE t_11 REMOVE PARTITIONING;
453 INSERT INTO t_11 SELECT * FROM t_10;
454 INSERT INTO t_11 SELECT * FROM t_100;
455 ALTER TABLE tp_k EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
456 SELECT * FROM tp_k PARTITION (p0);
457 a b c
458 1 One 2001-01-01
459 15 Fifeteen 2015-01-01
460 3 Three 2003-01-01
461 5 Five 2005-01-01
462 9 Nine 2009-01-01
463 95 Ninety-five 2095-01-01
464 DELETE FROM tp_k PARTITION (p1);
465 SELECT * FROM tp_k PARTITION (p1);
466 a b c
467 ALTER TABLE tp_k CHECK PARTITION p0 ;
468 Table Op Msg_type Msg_text
469 test.tp_k check error Found a misplaced row in part 0 should be in part 4:
470 a:1
471 test.tp_k check error Partition p0 returned error
472 test.tp_k check error Table upgrade required. Please do "REPAIR TABLE `tp_k`" or dump/reload to fix it!
473 ALTER TABLE tp_k REPAIR PARTITION p0 ;
474 Table Op Msg_type Msg_text
475 test.tp_k repair warning Moved 5 misplaced rows
476 test.tp_k repair status OK
477 SELECT * FROM tp_k PARTITION (p0);
478 a b c
479 5 Five 2005-01-01
480 SELECT * FROM tp_k PARTITION (p1);
481 a b c
482 SELECT * FROM tp_k PARTITION (p2);
483 a b c
484 116 Hundred sixteen 2116-01-01
485 122 Hundred twenty-two 2122-01-01
486 128 Hundred twenty-eight 2128-01-01
487 15 Fifeteen 2015-01-01
488 16 Sixteen 2016-01-01
489 166 Hundred sixty-six 2166-01-01
490 184 Hundred eighty-four 2184-01-01
491 3 Three 2003-01-01
492 4 Four 2004-01-01
493 9 Nine 2009-01-01
494 95 Ninety-five 2095-01-01
495 SELECT * FROM tp_k PARTITION (p3);
496 a b c
497 SELECT * FROM tp_k PARTITION (p4);
498 a b c
499 1 One 2001-01-01
500 13 Thirdteen 2013-01-01
501 19 Nineteen 2019-01-01
502 93 Ninety-three 2093-01-01
503 SELECT * FROM tp_k PARTITION (p5);
504 a b c
505 114 Hundred fourteen 2114-01-01
506 126 Hundred twenty-six 2126-01-01
507 14 Fourteen 2014-01-01
508 164 Hundred sixty-four 2164-01-01
509 182 Hundred eighty-two 2182-01-01
510 188 Hundred eighty-eight 2188-01-01
511 2 Two 2002-01-01
512 8 Eight 2008-01-01
513 ALTER TABLE tp_k COALESCE PARTITION 2;
514 SELECT * FROM tp_k PARTITION (p0);
515 a b c
516 1 One 2001-01-01
517 13 Thirdteen 2013-01-01
518 5 Five 2005-01-01
519 9 Nine 2009-01-01
520 93 Ninety-three 2093-01-01
521 SELECT * FROM tp_k PARTITION (p1);
522 a b c
523 116 Hundred sixteen 2116-01-01
524 128 Hundred twenty-eight 2128-01-01
525 16 Sixteen 2016-01-01
526 164 Hundred sixty-four 2164-01-01
527 184 Hundred eighty-four 2184-01-01
528 188 Hundred eighty-eight 2188-01-01
529 4 Four 2004-01-01
530 8 Eight 2008-01-01
531 SELECT * FROM tp_k PARTITION (p2);
532 a b c
533 15 Fifeteen 2015-01-01
534 19 Nineteen 2019-01-01
535 3 Three 2003-01-01
536 95 Ninety-five 2095-01-01
537 SELECT * FROM tp_k PARTITION (p3);
538 a b c
539 114 Hundred fourteen 2114-01-01
540 122 Hundred twenty-two 2122-01-01
541 126 Hundred twenty-six 2126-01-01
542 14 Fourteen 2014-01-01
543 166 Hundred sixty-six 2166-01-01
544 182 Hundred eighty-two 2182-01-01
545 2 Two 2002-01-01
546 DROP TABLE IF EXISTS t_11;
547 DELETE FROM t_10;
548 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');
549 DELETE FROM t_100;
550 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');
551 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');
552 ALTER TABLE tp_kvar COALESCE PARTITION 1;
553 SELECT * FROM tp_kvar PARTITION (p0);
554 a b c
555 114 Hundred fourteen 2114-01-01
556 126 Hundred twenty-six 2126-01-01
557 14 Fourteen 2014-01-01
558 162 Hundred sixty-two 2162-01-01
559 164 Hundred sixty-four 2164-01-01
560 168 Hundred sixty-eight 2168-01-01
561 186 Hundred eighty-six 2186-01-01
562 2 Two 2002-01-01
563 4 Four 2004-01-01
564 8 Eight 2008-01-01
565 SELECT * FROM tp_kvar PARTITION (p1);
566 a b c
567 112 Hundred twelve 2112-01-01
568 116 Hundred sixteen 2116-01-01
569 118 Hundred eightteen 2118-01-01
570 12 twelve 2012-01-01
571 122 Hundred twenty-two 2122-01-01
572 124 Hundred twenty-four 2124-01-01
573 128 Hundred twenty-eight 2128-01-01
574 16 Sixteen 2016-01-01
575 166 Hundred sixty-six 2166-01-01
576 18 Eightteen 2018-01-01
577 182 Hundred eighty-two 2182-01-01
578 184 Hundred eighty-four 2184-01-01
579 188 Hundred eighty-eight 2188-01-01
580 6 Six 2006-01-01
581 ALTER TABLE tp_kvar ADD PARTITION PARTITIONS 4;
582 SELECT * FROM tp_kvar PARTITION (p2);
583 a b c
584 162 Hundred sixty-two 2162-01-01
585 164 Hundred sixty-four 2164-01-01
586 SELECT * FROM tp_kvar PARTITION (p5);
587 a b c
588 112 Hundred twelve 2112-01-01
589 12 twelve 2012-01-01
590 16 Sixteen 2016-01-01
591 184 Hundred eighty-four 2184-01-01
592 6 Six 2006-01-01
593 CREATE TABLE t_11 LIKE tp_kvar;
594 ALTER TABLE t_11 REMOVE PARTITIONING;
595 INSERT INTO t_11 SELECT * FROM t_10;
596 INSERT INTO t_11 SELECT * FROM t_100;
597 ALTER TABLE tp_kvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
598 SELECT * FROM tp_kvar PARTITION (p0);
599 a b c
600 1 One 2001-01-01
601 11 Eleven 2011-01-01
602 13 Thirdteen 2013-01-01
603 15 Fifeteen 2015-01-01
604 19 Nineteen 2019-01-01
605 3 Three 2003-01-01
606 5 Five 2005-01-01
607 9 Nine 2009-01-01
608 91 Ninety-one 2091-01-01
609 93 Ninety-three 2093-01-01
610 95 Ninety-five 2095-01-01
611 99 Ninety-nine 2099-01-01
612 DELETE FROM tp_kvar PARTITION (p1);
613 SELECT * FROM tp_kvar PARTITION (p1);
614 a b c
615 ALTER TABLE tp_kvar ANALYZE PARTITION p0;
616 Table Op Msg_type Msg_text
617 test.tp_kvar analyze status OK
618 ALTER TABLE tp_kvar OPTIMIZE PARTITION p0;
619 Table Op Msg_type Msg_text
620 test.tp_kvar optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
621 test.tp_kvar optimize status OK
622 ALTER TABLE tp_kvar CHECK PARTITION p0 ;
623 Table Op Msg_type Msg_text
624 test.tp_kvar check error Found a misplaced row in part 0 should be in part 4:
625 b:Eleven
626 test.tp_kvar check error Partition p0 returned error
627 test.tp_kvar check error Table upgrade required. Please do "REPAIR TABLE `tp_kvar`" or dump/reload to fix it!
628 ALTER TABLE tp_kvar REPAIR PARTITION p0 ;
629 Table Op Msg_type Msg_text
630 test.tp_kvar repair warning Moved 11 misplaced rows
631 test.tp_kvar repair status OK
632 SELECT * FROM tp_kvar PARTITION (p0);
633 a b c
634 3 Three 2003-01-01
635 SELECT * FROM tp_kvar PARTITION (p1);
636 a b c
637 93 Ninety-three 2093-01-01
638 SELECT * FROM tp_kvar PARTITION (p2);
639 a b c
640 15 Fifeteen 2015-01-01
641 162 Hundred sixty-two 2162-01-01
642 164 Hundred sixty-four 2164-01-01
643 19 Nineteen 2019-01-01
644 95 Ninety-five 2095-01-01
645 99 Ninety-nine 2099-01-01
646 ALTER TABLE tp_kvar COALESCE PARTITION 2;
647 SELECT * FROM tp_kvar PARTITION (p0);
648 a b c
649 114 Hundred fourteen 2114-01-01
650 15 Fifeteen 2015-01-01
651 19 Nineteen 2019-01-01
652 3 Three 2003-01-01
653 SELECT * FROM tp_kvar PARTITION (p1);
654 a b c
655 122 Hundred twenty-two 2122-01-01
656 124 Hundred twenty-four 2124-01-01
657 16 Sixteen 2016-01-01
658 166 Hundred sixty-six 2166-01-01
659 182 Hundred eighty-two 2182-01-01
660 184 Hundred eighty-four 2184-01-01
661 91 Ninety-one 2091-01-01
662 93 Ninety-three 2093-01-01
663 SELECT * FROM tp_kvar PARTITION (p2);
664 a b c
665 1 One 2001-01-01
666 11 Eleven 2011-01-01
667 162 Hundred sixty-two 2162-01-01
668 164 Hundred sixty-four 2164-01-01
669 95 Ninety-five 2095-01-01
670 99 Ninety-nine 2099-01-01
671 SELECT * FROM tp_kvar PARTITION (p3);
672 a b c
673 112 Hundred twelve 2112-01-01
674 118 Hundred eightteen 2118-01-01
675 12 twelve 2012-01-01
676 13 Thirdteen 2013-01-01
677 18 Eightteen 2018-01-01
678 5 Five 2005-01-01
679 6 Six 2006-01-01
680 9 Nine 2009-01-01
681 DROP TABLE IF EXISTS t_11;
682 DELETE FROM t_10;
683 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');
684 DELETE FROM t_100;
685 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');
686 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');
687 ALTER TABLE tp_kdate COALESCE PARTITION 1;
688 SELECT * FROM tp_kdate PARTITION (p0);
689 a b c
690 112 Hundred twelve 2112-01-01
691 114 Hundred fourteen 2114-01-01
692 116 Hundred sixteen 2116-01-01
693 118 Hundred eightteen 2118-01-01
694 12 twelve 2012-01-01
695 122 Hundred twenty-two 2122-01-01
696 124 Hundred twenty-four 2124-01-01
697 126 Hundred twenty-six 2126-01-01
698 128 Hundred twenty-eight 2128-01-01
699 14 Fourteen 2014-01-01
700 16 Sixteen 2016-01-01
701 162 Hundred sixty-two 2162-01-01
702 168 Hundred sixty-eight 2168-01-01
703 18 Eightteen 2018-01-01
704 182 Hundred eighty-two 2182-01-01
705 184 Hundred eighty-four 2184-01-01
706 186 Hundred eighty-six 2186-01-01
707 188 Hundred eighty-eight 2188-01-01
708 2 Two 2002-01-01
709 4 Four 2004-01-01
710 6 Six 2006-01-01
711 8 Eight 2008-01-01
712 SELECT * FROM tp_kdate PARTITION (p1);
713 a b c
714 164 Hundred sixty-four 2164-01-01
715 166 Hundred sixty-six 2166-01-01
716 ALTER TABLE tp_kdate ADD PARTITION PARTITIONS 4;
717 CREATE TABLE t_11 LIKE tp_kdate;
718 ALTER TABLE t_11 REMOVE PARTITIONING;
719 INSERT INTO t_11 SELECT * FROM t_10;
720 INSERT INTO t_11 SELECT * FROM t_100;
721 ALTER TABLE tp_kdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
722 SELECT * FROM tp_kdate PARTITION (p0);
723 a b c
724 1 One 2001-01-01
725 11 Eleven 2011-01-01
726 13 Thirdteen 2013-01-01
727 15 Fifeteen 2015-01-01
728 19 Nineteen 2019-01-01
729 3 Three 2003-01-01
730 5 Five 2005-01-01
731 9 Nine 2009-01-01
732 91 Ninety-one 2091-01-01
733 93 Ninety-three 2093-01-01
734 95 Ninety-five 2095-01-01
735 99 Ninety-nine 2099-01-01
736 DELETE FROM tp_kdate PARTITION (p1);
737 SELECT * FROM tp_kdate PARTITION (p1);
738 a b c
739 ALTER TABLE tp_kdate ANALYZE PARTITION p0;
740 Table Op Msg_type Msg_text
741 test.tp_kdate analyze status OK
742 ALTER TABLE tp_kdate OPTIMIZE PARTITION p0;
743 Table Op Msg_type Msg_text
744 test.tp_kdate optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
745 test.tp_kdate optimize status OK
746 ALTER TABLE tp_kdate CHECK PARTITION p0 ;
747 Table Op Msg_type Msg_text
748 test.tp_kdate check error Found a misplaced row in part 0 should be in part 4:
749 c:2003-01-01
750 test.tp_kdate check error Partition p0 returned error
751 test.tp_kdate check error Table upgrade required. Please do "REPAIR TABLE `tp_kdate`" or dump/reload to fix it!
752 ALTER TABLE tp_kdate REPAIR PARTITION p0 ;
753 Table Op Msg_type Msg_text
754 test.tp_kdate repair warning Moved 8 misplaced rows
755 test.tp_kdate repair status OK
756 SELECT * FROM tp_kdate PARTITION (p0);
757 a b c
758 1 One 2001-01-01
759 15 Fifeteen 2015-01-01
760 9 Nine 2009-01-01
761 99 Ninety-nine 2099-01-01
762 SELECT * FROM tp_kdate PARTITION (p1);
763 a b c
764 SELECT * FROM tp_kdate PARTITION (p2);
765 a b c
766 122 Hundred twenty-two 2122-01-01
767 124 Hundred twenty-four 2124-01-01
768 18 Eightteen 2018-01-01
769 182 Hundred eighty-two 2182-01-01
770 188 Hundred eighty-eight 2188-01-01
771 19 Nineteen 2019-01-01
772 5 Five 2005-01-01
773 6 Six 2006-01-01
774 91 Ninety-one 2091-01-01
775 SELECT * FROM tp_kdate PARTITION (p3);
776 a b c
777 164 Hundred sixty-four 2164-01-01
778 SELECT * FROM tp_kdate PARTITION (p4);
779 a b c
780 11 Eleven 2011-01-01
781 116 Hundred sixteen 2116-01-01
782 12 twelve 2012-01-01
783 13 Thirdteen 2013-01-01
784 14 Fourteen 2014-01-01
785 162 Hundred sixty-two 2162-01-01
786 168 Hundred sixty-eight 2168-01-01
787 186 Hundred eighty-six 2186-01-01
788 2 Two 2002-01-01
789 3 Three 2003-01-01
790 4 Four 2004-01-01
791 93 Ninety-three 2093-01-01
792 95 Ninety-five 2095-01-01
793 SELECT * FROM tp_kdate PARTITION (p5);
794 a b c
795 166 Hundred sixty-six 2166-01-01
796 ALTER TABLE tp_kdate COALESCE PARTITION 2;
797 SELECT * FROM tp_kdate PARTITION (p0);
798 a b c
799 116 Hundred sixteen 2116-01-01
800 122 Hundred twenty-two 2122-01-01
801 124 Hundred twenty-four 2124-01-01
802 162 Hundred sixty-two 2162-01-01
803 168 Hundred sixty-eight 2168-01-01
804 SELECT * FROM tp_kdate PARTITION (p1);
805 a b c
806 SELECT * FROM tp_kdate PARTITION (p2);
807 a b c
808 1 One 2001-01-01
809 11 Eleven 2011-01-01
810 12 twelve 2012-01-01
811 13 Thirdteen 2013-01-01
812 14 Fourteen 2014-01-01
813 15 Fifeteen 2015-01-01
814 18 Eightteen 2018-01-01
815 182 Hundred eighty-two 2182-01-01
816 186 Hundred eighty-six 2186-01-01
817 188 Hundred eighty-eight 2188-01-01
818 19 Nineteen 2019-01-01
819 2 Two 2002-01-01
820 3 Three 2003-01-01
821 4 Four 2004-01-01
822 5 Five 2005-01-01
823 6 Six 2006-01-01
824 9 Nine 2009-01-01
825 91 Ninety-one 2091-01-01
826 93 Ninety-three 2093-01-01
827 95 Ninety-five 2095-01-01
828 99 Ninety-nine 2099-01-01
829 SELECT * FROM tp_kdate PARTITION (p3);
830 a b c
831 164 Hundred sixty-four 2164-01-01
832 166 Hundred sixty-six 2166-01-01
833 DROP TABLE IF EXISTS t_11;
834 DROP TABLE IF EXISTS t_10;
835 DROP TABLE IF EXISTS t_100;
836 DROP TABLE IF EXISTS t_1000;
837 DROP TABLE IF EXISTS tp_r;
838 DROP TABLE IF EXISTS tp_rvar;
839 DROP TABLE IF EXISTS tp_rintvar;
840 DROP TABLE IF EXISTS tp_rdate;
841 DROP TABLE IF EXISTS tp_rintdate;
842 DROP TABLE IF EXISTS tsp_r;
843 DROP TABLE IF EXISTS tsp_rvar;
844 DROP TABLE IF EXISTS tsp_rvar1;
845 DROP TABLE IF EXISTS tp_l;
846 DROP TABLE IF EXISTS tsp_l;
847 DROP TABLE IF EXISTS tsp_lvar;
848 DROP TABLE IF EXISTS tsp_ldate;
849 DROP TABLE IF EXISTS tp_k;
850 DROP TABLE IF EXISTS tp_kvar;
851 DROP TABLE IF EXISTS tp_kdate;
852 DROP TABLE IF EXISTS tp_h;
853 DROP TABLE IF EXISTS tp_hvar;
854 DROP TABLE IF EXISTS tp_hdate;
855 DROP TABLE IF EXISTS tsp_00;
856 DROP TABLE IF EXISTS tsp_01;
857 DROP TABLE IF EXISTS tsp_02;
858 DROP TABLE IF EXISTS tsp_03;
859 DROP TABLE IF EXISTS tsp_04;
860 DROP TABLE IF EXISTS t_empty;
861 DROP TABLE IF EXISTS t_null;