Debian Codegraph @

mysql-test/suite/parts/r/part_exch_valid_range_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 ### Range partitioned tables with/out validation
310 ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
311 SELECT * FROM tp_r PARTITION (p0);
312 a b c
313 1 One 2001-01-01
314 3 Three 2003-01-01
315 5 Five 2005-01-01
316 9 Nine 2009-01-01
317 SELECT * FROM tp_r PARTITION (p1);
318 a b c
319 12 twelve 2012-01-01
320 14 Fourteen 2014-01-01
321 16 Sixteen 2016-01-01
322 18 Eightteen 2018-01-01
323 SELECT * FROM tp_r PARTITION (p2);
324 a b c
325 112 Hundred twelve 2112-01-01
326 114 Hundred fourteen 2114-01-01
327 116 Hundred sixteen 2116-01-01
328 118 Hundred eightteen 2118-01-01
329 122 Hundred twenty-two 2122-01-01
330 124 Hundred twenty-four 2124-01-01
331 126 Hundred twenty-six 2126-01-01
332 128 Hundred twenty-eight 2128-01-01
333 162 Hundred sixty-two 2162-01-01
334 164 Hundred sixty-four 2164-01-01
335 166 Hundred sixty-six 2166-01-01
336 168 Hundred sixty-eight 2168-01-01
337 182 Hundred eighty-two 2182-01-01
338 184 Hundred eighty-four 2184-01-01
339 186 Hundred eighty-six 2186-01-01
340 188 Hundred eighty-eight 2188-01-01
341 SELECT * FROM t_10;
342 a b c
343 2 Two 2002-01-01
344 4 Four 2004-01-01
345 6 Six 2006-01-01
346 8 Eight 2008-01-01
347 ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION;
348 ERROR HY000: Found a row that does not match the partition
349 ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION;
350 SELECT * FROM tp_r PARTITION (p0);
351 a b c
352 11 Eleven 2011-01-01
353 13 Thirdteen 2013-01-01
354 15 Fifeteen 2015-01-01
355 19 Nineteen 2019-01-01
356 91 Ninety-one 2091-01-01
357 93 Ninety-three 2093-01-01
358 95 Ninety-five 2095-01-01
359 99 Ninety-nine 2099-01-01
360 SELECT * FROM tp_r PARTITION (p1);
361 a b c
362 12 twelve 2012-01-01
363 14 Fourteen 2014-01-01
364 16 Sixteen 2016-01-01
365 18 Eightteen 2018-01-01
366 SELECT * FROM tp_r PARTITION (p2);
367 a b c
368 112 Hundred twelve 2112-01-01
369 114 Hundred fourteen 2114-01-01
370 116 Hundred sixteen 2116-01-01
371 118 Hundred eightteen 2118-01-01
372 122 Hundred twenty-two 2122-01-01
373 124 Hundred twenty-four 2124-01-01
374 126 Hundred twenty-six 2126-01-01
375 128 Hundred twenty-eight 2128-01-01
376 162 Hundred sixty-two 2162-01-01
377 164 Hundred sixty-four 2164-01-01
378 166 Hundred sixty-six 2166-01-01
379 168 Hundred sixty-eight 2168-01-01
380 182 Hundred eighty-two 2182-01-01
381 184 Hundred eighty-four 2184-01-01
382 186 Hundred eighty-six 2186-01-01
383 188 Hundred eighty-eight 2188-01-01
384 SELECT * FROM t_100;
385 a b c
386 1 One 2001-01-01
387 3 Three 2003-01-01
388 5 Five 2005-01-01
389 9 Nine 2009-01-01
390 ALTER TABLE tp_r EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
391 ALTER TABLE tp_r EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
392 SELECT * FROM tp_r PARTITION (p2);
393 a b c
394 112 Hundred twelve 2112-01-01
395 114 Hundred fourteen 2114-01-01
396 116 Hundred sixteen 2116-01-01
397 118 Hundred eightteen 2118-01-01
398 122 Hundred twenty-two 2122-01-01
399 124 Hundred twenty-four 2124-01-01
400 126 Hundred twenty-six 2126-01-01
401 128 Hundred twenty-eight 2128-01-01
402 162 Hundred sixty-two 2162-01-01
403 164 Hundred sixty-four 2164-01-01
404 166 Hundred sixty-six 2166-01-01
405 168 Hundred sixty-eight 2168-01-01
406 182 Hundred eighty-two 2182-01-01
407 184 Hundred eighty-four 2184-01-01
408 186 Hundred eighty-six 2186-01-01
409 188 Hundred eighty-eight 2188-01-01
410 ALTER TABLE tp_r ANALYZE PARTITION p0;
411 Table Op Msg_type Msg_text
412 test.tp_r analyze status OK
413 ALTER TABLE tp_r OPTIMIZE PARTITION p0;
414 Table Op Msg_type Msg_text
415 test.tp_r optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
416 test.tp_r optimize status OK
417 DELETE FROM t_10;
418 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');
419 DELETE FROM t_100;
420 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');
421 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');
422 CREATE TABLE t_11 LIKE tp_r;
423 ALTER TABLE t_11 REMOVE PARTITIONING;
424 INSERT INTO t_11 SELECT * FROM t_10;
425 INSERT INTO t_11 SELECT * FROM t_100;
426 ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
427 SELECT * FROM tp_r PARTITION (p0);
428 a b c
429 1 One 2001-01-01
430 11 Eleven 2011-01-01
431 13 Thirdteen 2013-01-01
432 15 Fifeteen 2015-01-01
433 19 Nineteen 2019-01-01
434 3 Three 2003-01-01
435 5 Five 2005-01-01
436 9 Nine 2009-01-01
437 91 Ninety-one 2091-01-01
438 93 Ninety-three 2093-01-01
439 95 Ninety-five 2095-01-01
440 99 Ninety-nine 2099-01-01
441 DELETE FROM tp_r PARTITION (p1);
442 SELECT * FROM tp_r PARTITION (p1);
443 a b c
444 SELECT * FROM t_11;
445 a b c
446 11 Eleven 2011-01-01
447 13 Thirdteen 2013-01-01
448 15 Fifeteen 2015-01-01
449 19 Nineteen 2019-01-01
450 91 Ninety-one 2091-01-01
451 93 Ninety-three 2093-01-01
452 95 Ninety-five 2095-01-01
453 99 Ninety-nine 2099-01-01
454 ALTER TABLE tp_r CHECK PARTITION p0 ;
455 Table Op Msg_type Msg_text
456 test.tp_r check error Found a misplaced row in part 0 should be in part 1:
457 a:11
458 test.tp_r check error Partition p0 returned error
459 test.tp_r check error Table upgrade required. Please do "REPAIR TABLE `tp_r`" or dump/reload to fix it!
460 ALTER TABLE tp_r REPAIR PARTITION p0 ;
461 Table Op Msg_type Msg_text
462 test.tp_r repair warning Moved 8 misplaced rows
463 test.tp_r repair status OK
464 SELECT * FROM tp_r PARTITION (p0);
465 a b c
466 1 One 2001-01-01
467 3 Three 2003-01-01
468 5 Five 2005-01-01
469 9 Nine 2009-01-01
470 SELECT * FROM tp_r PARTITION (p1);
471 a b c
472 11 Eleven 2011-01-01
473 13 Thirdteen 2013-01-01
474 15 Fifeteen 2015-01-01
475 19 Nineteen 2019-01-01
476 91 Ninety-one 2091-01-01
477 93 Ninety-three 2093-01-01
478 95 Ninety-five 2095-01-01
479 99 Ninety-nine 2099-01-01
480 SELECT * FROM tp_r PARTITION (p2);
481 a b c
482 112 Hundred twelve 2112-01-01
483 114 Hundred fourteen 2114-01-01
484 116 Hundred sixteen 2116-01-01
485 118 Hundred eightteen 2118-01-01
486 122 Hundred twenty-two 2122-01-01
487 124 Hundred twenty-four 2124-01-01
488 126 Hundred twenty-six 2126-01-01
489 128 Hundred twenty-eight 2128-01-01
490 162 Hundred sixty-two 2162-01-01
491 164 Hundred sixty-four 2164-01-01
492 166 Hundred sixty-six 2166-01-01
493 168 Hundred sixty-eight 2168-01-01
494 182 Hundred eighty-two 2182-01-01
495 184 Hundred eighty-four 2184-01-01
496 186 Hundred eighty-six 2186-01-01
497 188 Hundred eighty-eight 2188-01-01
498 DROP TABLE IF EXISTS t_11;
499 DELETE FROM t_10;
500 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');
501 DELETE FROM t_100;
502 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');
503 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');
504 DELETE FROM tsp_r;
505 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');
506 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');
507 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');
508 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');
509 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');
510 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');
511 ### Range sub/partitioned tables with/out validation
512 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
513 ERROR HY000: Found a row that does not match the partition
514 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_100 WITH VALIDATION;
515 ERROR HY000: Found a row that does not match the partition
516 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_100 WITHOUT VALIDATION;
517 SELECT * FROM t_100;
518 a b c
519 SELECT * FROM tsp_r PARTITION (sp00);
520 a b c
521 11 Eleven 2011-01-01
522 13 Thirdteen 2013-01-01
523 15 Fifeteen 2015-01-01
524 19 Nineteen 2019-01-01
525 91 Ninety-one 2091-01-01
526 93 Ninety-three 2093-01-01
527 95 Ninety-five 2095-01-01
528 99 Ninety-nine 2099-01-01
529 SELECT * FROM tsp_r PARTITION (sp22);
530 a b c
531 112 Hundred twelve 2112-01-01
532 122 Hundred twenty-two 2122-01-01
533 162 Hundred sixty-two 2162-01-01
534 182 Hundred eight-two 2182-01-01
535 ALTER TABLE tsp_r EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
536 SELECT * FROM tsp_r PARTITION (sp22);
537 a b c
538 SELECT * FROM tsp_r PARTITION (sp22);
539 a b c
540 ALTER TABLE tsp_r EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
541 SELECT * FROM tsp_r PARTITION (sp22);
542 a b c
543 112 Hundred twelve 2112-01-01
544 122 Hundred twenty-two 2122-01-01
545 162 Hundred sixty-two 2162-01-01
546 182 Hundred eight-two 2182-01-01
547 ALTER TABLE tsp_r ANALYZE PARTITION p0;
548 Table Op Msg_type Msg_text
549 test.tsp_r analyze status OK
550 ALTER TABLE tsp_r OPTIMIZE PARTITION p0;
551 Table Op Msg_type Msg_text
552 test.tsp_r optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
553 test.tsp_r optimize status OK
554 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
555 ERROR HY000: Found a row that does not match the partition
556 CREATE TABLE t_11 LIKE t_10;
557 INSERT INTO t_11 SELECT * FROM t_10;
558 INSERT INTO t_11 SELECT * FROM t_100;
559 ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION;
560 SELECT * FROM tsp_r PARTITION (sp00);
561 a b c
562 1 One 2001-01-01
563 3 Three 2003-01-01
564 5 Five 2005-01-01
565 9 Nine 2009-01-01
566 SELECT * FROM tsp_r PARTITION (sp01);
567 a b c
568 6 Six 2006-01-01
569 SELECT * FROM tsp_r PARTITION (sp03);
570 a b c
571 8 Eight 2008-01-01
572 SELECT * FROM tsp_r PARTITION (sp04);
573 a b c
574 4 Four 2004-01-01
575 SELECT * FROM tsp_r PARTITION (sp02);
576 a b c
577 2 Two 2002-01-01
578 ALTER TABLE tsp_r CHECK PARTITION sp00 ;
579 Table Op Msg_type Msg_text
580 test.tsp_r check error Found a misplaced row in part 0 should be in part 1:
581 a:1
582 test.tsp_r check error Subpartition sp00 returned error
583 test.tsp_r check error Table upgrade required. Please do "REPAIR TABLE `tsp_r`" or dump/reload to fix it!
584 ALTER TABLE tsp_r REPAIR PARTITION sp00 ;
585 Table Op Msg_type Msg_text
586 test.tsp_r repair warning Moved 3 misplaced rows
587 test.tsp_r repair status OK
588 SELECT * FROM tsp_r PARTITION (sp00);
589 a b c
590 5 Five 2005-01-01
591 SELECT * FROM tsp_r PARTITION (sp01);
592 a b c
593 1 One 2001-01-01
594 6 Six 2006-01-01
595 SELECT * FROM tsp_r PARTITION (sp02);
596 a b c
597 2 Two 2002-01-01
598 SELECT * FROM tsp_r PARTITION (sp03);
599 a b c
600 3 Three 2003-01-01
601 8 Eight 2008-01-01
602 SELECT * FROM tsp_r PARTITION (sp04);
603 a b c
604 4 Four 2004-01-01
605 9 Nine 2009-01-01
606 DROP TABLE IF EXISTS t_11;
607 DELETE FROM t_10;
608 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');
609 DELETE FROM t_100;
610 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');
611 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');
612 CREATE TABLE t_11 LIKE tp_rintvar;
613 ALTER TABLE t_11 REMOVE PARTITIONING;
614 INSERT INTO t_11 SELECT * FROM t_10;
615 INSERT INTO t_11 SELECT * FROM t_100;
616 ALTER TABLE tp_rintvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION;
617 ERROR HY000: Found a row that does not match the partition
618 ALTER TABLE tp_rintvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
619 SELECT * FROM tp_rintvar PARTITION (p0);
620 a b c
621 1 One 2001-01-01
622 11 Eleven 2011-01-01
623 13 Thirdteen 2013-01-01
624 15 Fifeteen 2015-01-01
625 19 Nineteen 2019-01-01
626 3 Three 2003-01-01
627 5 Five 2005-01-01
628 9 Nine 2009-01-01
629 91 Ninety-one 2091-01-01
630 93 Ninety-three 2093-01-01
631 95 Ninety-five 2095-01-01
632 99 Ninety-nine 2099-01-01
633 DELETE FROM tp_rintvar PARTITION (p1);
634 SELECT * FROM tp_rintvar PARTITION (p1);
635 a b c
636 SELECT * FROM t_11;
637 a b c
638 2 Two 2002-01-01
639 4 Four 2004-01-01
640 6 Six 2006-01-01
641 8 Eight 2008-01-01
642 ALTER TABLE tp_rintvar CHECK PARTITION p0 ;
643 Table Op Msg_type Msg_text
644 test.tp_rintvar check error Found a misplaced row in part 0 should be in part 1:
645 a:11 b:Eleven
646 test.tp_rintvar check error Partition p0 returned error
647 test.tp_rintvar check error Table upgrade required. Please do "REPAIR TABLE `tp_rintvar`" or dump/reload to fix it!
648 ALTER TABLE tp_rintvar REPAIR PARTITION p0 ;
649 Table Op Msg_type Msg_text
650 test.tp_rintvar repair warning Moved 8 misplaced rows
651 test.tp_rintvar repair status OK
652 SELECT * FROM tp_rintvar PARTITION (p0);
653 a b c
654 1 One 2001-01-01
655 3 Three 2003-01-01
656 5 Five 2005-01-01
657 9 Nine 2009-01-01
658 SELECT * FROM tp_rintvar PARTITION (p1);
659 a b c
660 11 Eleven 2011-01-01
661 13 Thirdteen 2013-01-01
662 15 Fifeteen 2015-01-01
663 19 Nineteen 2019-01-01
664 91 Ninety-one 2091-01-01
665 93 Ninety-three 2093-01-01
666 95 Ninety-five 2095-01-01
667 99 Ninety-nine 2099-01-01
668 SELECT * FROM tp_rintvar PARTITION (p2);
669 a b c
670 112 Hundred twelve 2112-01-01
671 114 Hundred fourteen 2114-01-01
672 116 Hundred sixteen 2116-01-01
673 118 Hundred eightteen 2118-01-01
674 122 Hundred twenty-two 2122-01-01
675 124 Hundred twenty-four 2124-01-01
676 126 Hundred twenty-six 2126-01-01
677 128 Hundred twenty-eight 2128-01-01
678 162 Hundred sixty-two 2162-01-01
679 164 Hundred sixty-four 2164-01-01
680 166 Hundred sixty-six 2166-01-01
681 168 Hundred sixty-eight 2168-01-01
682 182 Hundred eighty-two 2182-01-01
683 184 Hundred eighty-four 2184-01-01
684 186 Hundred eighty-six 2186-01-01
685 188 Hundred eighty-eight 2188-01-01
686 DROP TABLE IF EXISTS t_11;
687 DELETE FROM t_10;
688 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');
689 DELETE FROM t_100;
690 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');
691 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');
692 CREATE TABLE t_11 LIKE tp_rvar;
693 ALTER TABLE t_11 REMOVE PARTITIONING;
694 INSERT INTO t_11 SELECT * FROM t_10;
695 INSERT INTO t_11 SELECT * FROM t_100;
696 ALTER TABLE tp_rvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION;
697 ERROR HY000: Found a row that does not match the partition
698 ALTER TABLE tp_rvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
699 SELECT * FROM tp_rvar PARTITION (p0);
700 a b c
701 1 One 2001-01-01
702 11 Eleven 2011-01-01
703 13 Thirdteen 2013-01-01
704 15 Fifeteen 2015-01-01
705 19 Nineteen 2019-01-01
706 3 Three 2003-01-01
707 5 Five 2005-01-01
708 9 Nine 2009-01-01
709 91 Ninety-one 2091-01-01
710 93 Ninety-three 2093-01-01
711 95 Ninety-five 2095-01-01
712 99 Ninety-nine 2099-01-01
713 DELETE FROM tp_rvar PARTITION (p1);
714 SELECT * FROM tp_rvar PARTITION (p1);
715 a b c
716 SELECT * FROM t_11;
717 a b c
718 14 Fourteen 2014-01-01
719 18 Eightteen 2018-01-01
720 4 Four 2004-01-01
721 8 Eight 2008-01-01
722 ALTER TABLE tp_rvar CHECK PARTITION p0 ;
723 Table Op Msg_type Msg_text
724 test.tp_rvar check error Found a misplaced row in part 0 should be in part 1:
725 b:One
726 test.tp_rvar check error Partition p0 returned error
727 test.tp_rvar check error Table upgrade required. Please do "REPAIR TABLE `tp_rvar`" or dump/reload to fix it!
728 ALTER TABLE tp_rvar REPAIR PARTITION p0 ;
729 Table Op Msg_type Msg_text
730 test.tp_rvar repair warning Moved 9 misplaced rows
731 test.tp_rvar repair status OK
732 SELECT * FROM tp_rvar PARTITION (p0);
733 a b c
734 11 Eleven 2011-01-01
735 15 Fifeteen 2015-01-01
736 5 Five 2005-01-01
737 SELECT * FROM tp_rvar PARTITION (p1);
738 a b c
739 1 One 2001-01-01
740 19 Nineteen 2019-01-01
741 9 Nine 2009-01-01
742 91 Ninety-one 2091-01-01
743 93 Ninety-three 2093-01-01
744 95 Ninety-five 2095-01-01
745 99 Ninety-nine 2099-01-01
746 SELECT * FROM tp_rvar PARTITION (p2);
747 a b c
748 12 twelve 2012-01-01
749 13 Thirdteen 2013-01-01
750 16 Sixteen 2016-01-01
751 2 Two 2002-01-01
752 3 Three 2003-01-01
753 6 Six 2006-01-01
754 DROP TABLE IF EXISTS t_11;
755 DELETE FROM t_10;
756 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');
757 DELETE FROM t_100;
758 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');
759 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');
760 CREATE TABLE t_11 LIKE tp_rintdate;
761 ALTER TABLE t_11 REMOVE PARTITIONING;
762 INSERT INTO t_11 SELECT * FROM t_10;
763 INSERT INTO t_11 SELECT * FROM t_100;
764 ALTER TABLE tp_rintdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION;
765 ERROR HY000: Found a row that does not match the partition
766 ALTER TABLE tp_rintdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
767 SELECT * FROM tp_rintdate PARTITION (p0);
768 a b c
769 1 One 2001-01-01
770 11 Eleven 2011-01-01
771 13 Thirdteen 2013-01-01
772 15 Fifeteen 2015-01-01
773 19 Nineteen 2019-01-01
774 3 Three 2003-01-01
775 5 Five 2005-01-01
776 9 Nine 2009-01-01
777 91 Ninety-one 2091-01-01
778 93 Ninety-three 2093-01-01
779 95 Ninety-five 2095-01-01
780 99 Ninety-nine 2099-01-01
781 DELETE FROM tp_rintdate PARTITION (p1);
782 SELECT * FROM tp_rintdate PARTITION (p1);
783 a b c
784 SELECT * FROM t_11;
785 a b c
786 1 One 2001-01-01
787 3 Three 2003-01-01
788 5 Five 2005-01-01
789 9 Nine 2009-01-01
790 ALTER TABLE tp_rintdate CHECK PARTITION p0 ;
791 Table Op Msg_type Msg_text
792 test.tp_rintdate check error Found a misplaced row in part 0 should be in part 1:
793 a:11 c:2011-01-01
794 test.tp_rintdate check error Partition p0 returned error
795 test.tp_rintdate check error Table upgrade required. Please do "REPAIR TABLE `tp_rintdate`" or dump/reload to fix it!
796 ALTER TABLE tp_rintdate REPAIR PARTITION p0 ;
797 Table Op Msg_type Msg_text
798 test.tp_rintdate repair warning Moved 8 misplaced rows
799 test.tp_rintdate repair status OK
800 SELECT * FROM tp_rintdate PARTITION (p0);
801 a b c
802 1 One 2001-01-01
803 3 Three 2003-01-01
804 5 Five 2005-01-01
805 9 Nine 2009-01-01
806 SELECT * FROM tp_rintdate PARTITION (p1);
807 a b c
808 11 Eleven 2011-01-01
809 13 Thirdteen 2013-01-01
810 15 Fifeteen 2015-01-01
811 19 Nineteen 2019-01-01
812 91 Ninety-one 2091-01-01
813 93 Ninety-three 2093-01-01
814 95 Ninety-five 2095-01-01
815 99 Ninety-nine 2099-01-01
816 SELECT * FROM tp_rintdate PARTITION (p2);
817 a b c
818 111 Hundred elven 2111-01-01
819 113 Hundred thirdteen 2113-01-01
820 115 Hundred fiveteen 2115-01-01
821 119 Hundred nineteen 2119-01-01
822 131 Hundred thirty-one 2131-01-01
823 133 Hundred thirty-three 2133-01-01
824 135 Hundred thirty-five 2135-01-01
825 139 Hundred thirty-nine 2139-01-01
826 151 Hundred fifty-one 2151-01-01
827 153 Hundred fifty-three 2153-01-01
828 155 Hundred fity-five 2155-01-01
829 159 Hundred fifty-nine 2159-01-01
830 191 Hundred ninety-one 2191-01-01
831 193 Hundred ninety-three 2193-01-01
832 195 Hundred ninety-five 2195-01-01
833 199 Hundred ninety-nine 2199-01-01
834 DROP TABLE IF EXISTS t_11;
835 DELETE FROM t_10;
836 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');
837 DELETE FROM t_100;
838 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');
839 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');
840 CREATE TABLE t_11 LIKE tp_rdate;
841 ALTER TABLE t_11 REMOVE PARTITIONING;
842 INSERT INTO t_11 SELECT * FROM t_10;
843 INSERT INTO t_11 SELECT * FROM t_100;
844 ALTER TABLE tp_rdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION;
845 ERROR HY000: Found a row that does not match the partition
846 ALTER TABLE tp_rdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
847 SELECT * FROM tp_rdate PARTITION (p0);
848 a b c
849 1 One 2001-01-01
850 11 Eleven 2011-01-01
851 13 Thirdteen 2013-01-01
852 15 Fifeteen 2015-01-01
853 19 Nineteen 2019-01-01
854 3 Three 2003-01-01
855 5 Five 2005-01-01
856 9 Nine 2009-01-01
857 91 Ninety-one 2091-01-01
858 93 Ninety-three 2093-01-01
859 95 Ninety-five 2095-01-01
860 99 Ninety-nine 2099-01-01
861 DELETE FROM tp_rdate PARTITION (p1);
862 SELECT * FROM tp_rdate PARTITION (p1);
863 a b c
864 SELECT * FROM t_11;
865 a b c
866 2 Two 2002-01-01
867 4 Four 2004-01-01
868 6 Six 2006-01-01
869 8 Eight 2008-01-01
870 ALTER TABLE tp_rdate CHECK PARTITION p0 ;
871 Table Op Msg_type Msg_text
872 test.tp_rdate check error Found a misplaced row in part 0 should be in part 1:
873 c:2011-01-01
874 test.tp_rdate check error Partition p0 returned error
875 test.tp_rdate check error Table upgrade required. Please do "REPAIR TABLE `tp_rdate`" or dump/reload to fix it!
876 ALTER TABLE tp_rdate REPAIR PARTITION p0 ;
877 Table Op Msg_type Msg_text
878 test.tp_rdate repair warning Moved 8 misplaced rows
879 test.tp_rdate repair status OK
880 SELECT * FROM tp_rdate PARTITION (p0);
881 a b c
882 1 One 2001-01-01
883 3 Three 2003-01-01
884 5 Five 2005-01-01
885 9 Nine 2009-01-01
886 SELECT * FROM tp_rdate PARTITION (p1);
887 a b c
888 11 Eleven 2011-01-01
889 13 Thirdteen 2013-01-01
890 15 Fifeteen 2015-01-01
891 19 Nineteen 2019-01-01
892 91 Ninety-one 2091-01-01
893 93 Ninety-three 2093-01-01
894 95 Ninety-five 2095-01-01
895 99 Ninety-nine 2099-01-01
896 SELECT * FROM tp_rdate PARTITION (p2);
897 a b c
898 DROP TABLE IF EXISTS t_11;
899 DELETE FROM t_10;
900 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');
901 DELETE FROM t_100;
902 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');
903 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');
904 CREATE TABLE t_11 LIKE tsp_rvar;
905 ALTER TABLE t_11 REMOVE PARTITIONING;
906 INSERT INTO t_11 SELECT * FROM t_10;
907 INSERT INTO t_11 SELECT * FROM t_100;
908 ALTER TABLE tsp_rvar EXCHANGE PARTITION sp00 WITH TABLE t_11 WITH VALIDATION;
909 ERROR HY000: Found a row that does not match the partition
910 ALTER TABLE tsp_rvar EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION;
911 SELECT * FROM tsp_rvar PARTITION (sp00);
912 a b c
913 1 One 2001-01-01
914 11 Eleven 2011-01-01
915 13 Thirdteen 2013-01-01
916 15 Fifeteen 2015-01-01
917 19 Nineteen 2019-01-01
918 3 Three 2003-01-01
919 5 Five 2005-01-01
920 9 Nine 2009-01-01
921 91 Ninety-one 2091-01-01
922 93 Ninety-three 2093-01-01
923 95 Ninety-five 2095-01-01
924 99 Ninety-nine 2099-01-01
925 DELETE FROM tsp_rvar PARTITION (sp01);
926 SELECT * FROM tsp_rvar PARTITION (sp01);
927 a b c
928 SELECT * FROM t_11;
929 a b c
930 ALTER TABLE tsp_rvar CHECK PARTITION p0 ;
931 Table Op Msg_type Msg_text
932 test.tsp_rvar check error Found a misplaced row in part 0 should be in part 6:
933 a:1 b:One
934 test.tsp_rvar check error Subpartition sp00 returned error
935 test.tsp_rvar check error Table upgrade required. Please do "REPAIR TABLE `tsp_rvar`" or dump/reload to fix it!
936 ALTER TABLE tsp_rvar REPAIR PARTITION p0 ;
937 Table Op Msg_type Msg_text
938 test.tsp_rvar repair warning Moved 10 misplaced rows
939 test.tsp_rvar repair status OK
940 SELECT * FROM tsp_rvar PARTITION (sp00);
941 a b c
942 15 Fifeteen 2015-01-01
943 5 Five 2005-01-01
944 SELECT * FROM tsp_rvar PARTITION (sp01);
945 a b c
946 11 Eleven 2011-01-01
947 SELECT * FROM tsp_rvar PARTITION (sp02);
948 a b c
949 SELECT * FROM tsp_rvar PARTITION (sp03);
950 a b c
951 18 Eightteen 2018-01-01
952 8 Eight 2008-01-01
953 SELECT * FROM tsp_rvar PARTITION (sp04);
954 a b c
955 14 Fourteen 2014-01-01
956 4 Four 2004-01-01
957 DROP TABLE IF EXISTS t_11;
958 DELETE FROM t_10;
959 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');
960 DELETE FROM t_100;
961 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');
962 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');
963 CREATE TABLE t_11 LIKE tsp_rvar1;
964 ALTER TABLE t_11 REMOVE PARTITIONING;
965 INSERT INTO t_11 SELECT * FROM t_10;
966 INSERT INTO t_11 SELECT * FROM t_100;
967 ALTER TABLE tsp_rvar1 EXCHANGE PARTITION p0sp0 WITH TABLE t_11 WITH VALIDATION;
968 ERROR HY000: Found a row that does not match the partition
969 ALTER TABLE tsp_rvar1 EXCHANGE PARTITION p0sp0 WITH TABLE t_11 WITHOUT VALIDATION;
970 SELECT * FROM tsp_rvar1 PARTITION (p0sp0);
971 a b c
972 1 One 2001-01-01
973 11 Eleven 2011-01-01
974 13 Thirdteen 2013-01-01
975 15 Fifeteen 2015-01-01
976 19 Nineteen 2019-01-01
977 3 Three 2003-01-01
978 5 Five 2005-01-01
979 9 Nine 2009-01-01
980 91 Ninety-one 2091-01-01
981 93 Ninety-three 2093-01-01
982 95 Ninety-five 2095-01-01
983 99 Ninety-nine 2099-01-01
984 DELETE FROM tsp_rvar1 PARTITION (p0sp1);
985 SELECT * FROM tsp_rvar1 PARTITION (p0sp1);
986 a b c
987 SELECT * FROM t_11;
988 a b c
989 ALTER TABLE tsp_rvar1 CHECK PARTITION p0 ;
990 Table Op Msg_type Msg_text
991 test.tsp_rvar1 check error Found a misplaced row in part 0 should be in part 6:
992 a:1 b:One
993 test.tsp_rvar1 check error Subpartition p0sp0 returned error
994 test.tsp_rvar1 check error Table upgrade required. Please do "REPAIR TABLE `tsp_rvar1`" or dump/reload to fix it!
995 ALTER TABLE tsp_rvar1 REPAIR PARTITION p0 ;
996 Table Op Msg_type Msg_text
997 test.tsp_rvar1 repair warning Moved 10 misplaced rows
998 test.tsp_rvar1 repair status OK
999 SELECT * FROM tsp_rvar1 PARTITION (p0sp0);
1000 a b c
1001 15 Fifeteen 2015-01-01
1002 5 Five 2005-01-01
1003 SELECT * FROM tsp_rvar1 PARTITION (p0sp1);
1004 a b c
1005 11 Eleven 2011-01-01
1006 SELECT * FROM tsp_rvar1 PARTITION (p0sp2);
1007 a b c
1008 SELECT * FROM tsp_rvar1 PARTITION (p0sp3);
1009 a b c
1010 18 Eightteen 2018-01-01
1011 8 Eight 2008-01-01
1012 SELECT * FROM tsp_rvar1 PARTITION (p0sp4);
1013 a b c
1014 14 Fourteen 2014-01-01
1015 4 Four 2004-01-01
1016 DROP TABLE IF EXISTS t_11;
1017 DROP TABLE IF EXISTS t_10;
1018 DROP TABLE IF EXISTS t_100;
1019 DROP TABLE IF EXISTS t_1000;
1020 DROP TABLE IF EXISTS tp_r;
1021 DROP TABLE IF EXISTS tp_rvar;
1022 DROP TABLE IF EXISTS tp_rintvar;
1023 DROP TABLE IF EXISTS tp_rdate;
1024 DROP TABLE IF EXISTS tp_rintdate;
1025 DROP TABLE IF EXISTS tsp_r;
1026 DROP TABLE IF EXISTS tsp_rvar;
1027 DROP TABLE IF EXISTS tsp_rvar1;
1028 DROP TABLE IF EXISTS tp_l;
1029 DROP TABLE IF EXISTS tsp_l;
1030 DROP TABLE IF EXISTS tsp_lvar;
1031 DROP TABLE IF EXISTS tsp_ldate;
1032 DROP TABLE IF EXISTS tp_k;
1033 DROP TABLE IF EXISTS tp_kvar;
1034 DROP TABLE IF EXISTS tp_kdate;
1035 DROP TABLE IF EXISTS tp_h;
1036 DROP TABLE IF EXISTS tp_hvar;
1037 DROP TABLE IF EXISTS tp_hdate;
1038 DROP TABLE IF EXISTS tsp_00;
1039 DROP TABLE IF EXISTS tsp_01;
1040 DROP TABLE IF EXISTS tsp_02;
1041 DROP TABLE IF EXISTS tsp_03;
1042 DROP TABLE IF EXISTS tsp_04;
1043 DROP TABLE IF EXISTS t_empty;
1044 DROP TABLE IF EXISTS t_null;