1use test; 2call mtr.add_suppression("row in wrong partition.*from REBUILD/REORGANIZED"); 3DROP TABLE IF EXISTS t_10; 4DROP TABLE IF EXISTS t_100; 5DROP TABLE IF EXISTS t_1000; 6DROP TABLE IF EXISTS tp_r; 7DROP TABLE IF EXISTS tp_rvar; 8DROP TABLE IF EXISTS tp_rintvar; 9DROP TABLE IF EXISTS tp_rdate; 10DROP TABLE IF EXISTS tp_rintdate; 11DROP TABLE IF EXISTS tsp_r; 12DROP TABLE IF EXISTS tsp_rvar; 13DROP TABLE IF EXISTS tsp_rvar1; 14DROP TABLE IF EXISTS tp_l; 15DROP TABLE IF EXISTS tsp_l; 16DROP TABLE IF EXISTS tsp_lvar; 17DROP TABLE IF EXISTS tsp_ldate; 18DROP TABLE IF EXISTS tp_k; 19DROP TABLE IF EXISTS tp_kvar; 20DROP TABLE IF EXISTS tp_kdate; 21DROP TABLE IF EXISTS tp_h; 22DROP TABLE IF EXISTS tp_hvar; 23DROP TABLE IF EXISTS tp_hdate; 24DROP TABLE IF EXISTS tsp_00; 25DROP TABLE IF EXISTS tsp_01; 26DROP TABLE IF EXISTS tsp_02; 27DROP TABLE IF EXISTS tsp_03; 28DROP TABLE IF EXISTS tsp_04; 29DROP TABLE IF EXISTS t_empty; 30DROP TABLE IF EXISTS t_null; 31CREATE TABLE t_10 (a INT, 32b VARCHAR(25), 33c DATE, 34PRIMARY KEY (a)) 35ENGINE = InnoDB; 36CREATE TABLE t_100 (a INT, 37b VARCHAR(25), 38c DATE, 39PRIMARY KEY (a)) 40ENGINE = InnoDB; 41CREATE TABLE t_1000 (a INT, 42b VARCHAR(25), 43c DATE, 44PRIMARY KEY (a)) 45ENGINE = InnoDB; 46INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 47INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 48INSERT 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'); 49INSERT 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'); 50INSERT 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'); 51INSERT 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'); 52INSERT 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'); 53CREATE TABLE t_empty (a INT, 54b VARCHAR(25), 55c DATE, 56PRIMARY KEY (a)) 57ENGINE = InnoDB; 58CREATE TABLE t_null (a INT, 59b VARCHAR(25), 60c DATE, 61PRIMARY KEY (a)) 62ENGINE = InnoDB; 63CREATE TABLE tsp_01(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 64ENGINE = InnoDB 65AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; 66CREATE TABLE tsp_02(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 67ENGINE = InnoDB 68AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; 69CREATE TABLE tsp_03(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 70ENGINE = InnoDB 71AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; 72CREATE TABLE tsp_04(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 73ENGINE = InnoDB 74AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; 75CREATE TABLE tsp_00(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a)) 76ENGINE = InnoDB 77AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; 78CREATE TABLE tp_r (a INT, 79b VARCHAR(25), 80c DATE, 81PRIMARY KEY (a)) 82ENGINE = InnoDB 83PARTITION BY RANGE (a) 84(PARTITION p0 VALUES LESS THAN (10) , 85PARTITION p1 VALUES LESS THAN (100) , 86PARTITION p2 VALUES LESS THAN (1000) ); 87CREATE TABLE tsp_r (a INT, 88b VARCHAR(25), 89c DATE, 90PRIMARY KEY (a)) 91ENGINE = InnoDB 92PARTITION BY RANGE (a) 93SUBPARTITION BY HASH(a) 94(PARTITION p0 VALUES LESS THAN (10) 95(SUBPARTITION sp00, 96SUBPARTITION sp01, 97SUBPARTITION sp02, 98SUBPARTITION sp03, 99SUBPARTITION sp04), 100PARTITION p1 VALUES LESS THAN (100) 101(SUBPARTITION sp10 , 102SUBPARTITION sp11 , 103SUBPARTITION sp12 , 104SUBPARTITION sp13 , 105SUBPARTITION sp14 ), 106PARTITION p2 VALUES LESS THAN (1000) 107(SUBPARTITION sp20, 108SUBPARTITION sp21, 109SUBPARTITION sp22, 110SUBPARTITION sp23, 111SUBPARTITION sp24)); 112INSERT INTO tp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01'); 113INSERT INTO tp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01'); 114INSERT 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'); 115INSERT 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'); 116INSERT 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'); 117INSERT 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'); 118INSERT INTO tsp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01'); 119INSERT INTO tsp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01'); 120INSERT 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'); 121INSERT 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'); 122INSERT 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'); 123INSERT 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'); 124CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (b)) 125ENGINE = InnoDB 126PARTITION BY RANGE (b) 127(PARTITION p0 VALUES LESS THAN ('H') , 128PARTITION p1 VALUES LESS THAN ('Q') , 129PARTITION p2 VALUES LESS THAN ('Z') , 130PARTITION p3 VALUES LESS THAN (MAXVALUE) ) 131AS SELECT a, b FROM tp_r; 132ERROR HY000: VALUES value for partition 'p0' must have type INT 133CREATE TABLE tp_rintvar(a INT,b VARCHAR(25),c DATE, 134PRIMARY KEY (a,b)) 135ENGINE = InnoDB 136PARTITION BY RANGE COLUMNS (a,b) 137(PARTITION p0 VALUES LESS THAN (10,'HHHHHHHHHHHHHHHHHHHHHHHHH'), 138PARTITION p1 VALUES LESS THAN (100,'PPPPPPPPPPPPPPPPPPPPPPPPP'), 139PARTITION p2 VALUES LESS THAN (1000,'WWWWWWWWWWWWWWWWWWWWWWWWW'), 140PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)) 141AS SELECT a, b, c FROM tp_r; 142CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE) 143ENGINE = InnoDB 144PARTITION BY RANGE COLUMNS (b) 145(PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'), 146PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'), 147PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'), 148PARTITION p3 VALUES LESS THAN (MAXVALUE)) 149AS SELECT a, b, c FROM tp_r; 150CREATE TABLE tp_rintdate(a INT,b VARCHAR(25),c DATE, 151PRIMARY KEY (a,c)) 152ENGINE = InnoDB 153PARTITION BY RANGE COLUMNS (a,c) 154(PARTITION p0 VALUES LESS THAN (10,'2010-01-01'), 155PARTITION p1 VALUES LESS THAN (100,'2200-01-01'), 156PARTITION p2 VALUES LESS THAN (1000,'2300-01-01'), 157PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)) 158AS SELECT a, b, c FROM t_10; 159INSERT tp_rintdate SELECT * FROM t_100; 160INSERT tp_rintdate SELECT * FROM t_1000; 161CREATE TABLE tp_rdate(a INT,b VARCHAR(25),c DATE, 162PRIMARY KEY (c)) 163ENGINE = InnoDB 164PARTITION BY RANGE COLUMNS (c) 165(PARTITION p0 VALUES LESS THAN ('2010-01-01'), 166PARTITION p1 VALUES LESS THAN ('2200-01-01'), 167PARTITION p2 VALUES LESS THAN ('2300-01-01'), 168PARTITION p3 VALUES LESS THAN (MAXVALUE)) 169AS SELECT a, b, c FROM tp_r; 170CREATE TABLE tsp_rvar(a INT,b VARCHAR(25),c DATE) 171ENGINE = InnoDB 172PARTITION BY RANGE COLUMNS (b) 173SUBPARTITION BY HASH(a) 174SUBPARTITIONS 5 175(PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH') 176(SUBPARTITION sp00, 177SUBPARTITION sp01, 178SUBPARTITION sp02, 179SUBPARTITION sp03, 180SUBPARTITION sp04), 181PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP') 182(SUBPARTITION sp10, 183SUBPARTITION sp11, 184SUBPARTITION sp12, 185SUBPARTITION sp13, 186SUBPARTITION sp14), 187PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW') 188(SUBPARTITION sp20, 189SUBPARTITION sp21, 190SUBPARTITION sp22, 191SUBPARTITION sp23, 192SUBPARTITION sp24)) 193AS SELECT a, b, c FROM tsp_r; 194CREATE TABLE tsp_rvar1(a INT,b VARCHAR(25),c DATE) 195ENGINE = InnoDB 196PARTITION BY RANGE COLUMNS (b) 197SUBPARTITION BY HASH(a) 198SUBPARTITIONS 5 199(PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'), 200PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'), 201PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW')) 202AS SELECT a, b, c FROM tsp_r; 203CREATE TABLE tp_l (a INT, 204b VARCHAR(25), 205c DATE, 206PRIMARY KEY (a)) 207ENGINE = InnoDB 208PARTITION BY LIST (a) 209(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9), 210PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99), 211PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119, 212120,121,122,123,124,125,126,127,128,129, 213130,131,132,133,134,135,136,137,138,139, 214150,151,152,153,154,155,156,157,158,159, 215160,161,162,163,164,165,166,167,168,169, 216180,181,182,183,184,185,186,187,188,189, 217190,191,192,193,194,195,196,197,198,199)) 218AS SELECT a, b, c FROM tp_r; 219CREATE TABLE tsp_l (a INT, 220b VARCHAR(25), 221c DATE, 222PRIMARY KEY (a)) 223ENGINE = InnoDB 224PARTITION BY LIST (a) 225SUBPARTITION BY HASH(a) 226(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9) 227(SUBPARTITION sp00, 228SUBPARTITION sp01, 229SUBPARTITION sp02, 230SUBPARTITION sp03, 231SUBPARTITION sp04), 232PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99) 233(SUBPARTITION sp10, 234SUBPARTITION sp11, 235SUBPARTITION sp12, 236SUBPARTITION sp13, 237SUBPARTITION sp14), 238PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119, 239120,121,122,123,124,125,126,127,128,129, 240130,131,132,133,134,135,136,137,138,139, 241150,151,152,153,154,155,156,157,158,159, 242160,161,162,163,164,165,166,167,168,169, 243180,181,182,183,184,185,186,187,188,189, 244190,191,192,193,194,195,196,197,198,199) 245(SUBPARTITION sp20, 246SUBPARTITION sp21, 247SUBPARTITION sp22, 248SUBPARTITION sp23, 249SUBPARTITION sp24)) 250AS SELECT a, b, c FROM tsp_r; 251CREATE TABLE tp_k (a INT, 252b VARCHAR(25), 253c DATE, 254PRIMARY KEY (a)) 255ENGINE = InnoDB 256PARTITION BY KEY (a) 257PARTITIONS 3 258AS SELECT a, b, c FROM tp_r; 259CREATE TABLE tp_kvar (a INT, 260b VARCHAR(25), 261c DATE, 262PRIMARY KEY (b)) 263ENGINE = InnoDB 264PARTITION BY KEY (b) 265PARTITIONS 3 266AS SELECT a, b, c FROM tp_r; 267CREATE TABLE tp_kdate (a INT, 268b VARCHAR(25), 269c DATE, 270PRIMARY KEY (c)) 271ENGINE = InnoDB 272PARTITION BY KEY (c) 273PARTITIONS 3 274AS SELECT a, b, c FROM tp_r; 275CREATE TABLE tp_h (a INT, 276b VARCHAR(25), 277c DATE, 278PRIMARY KEY (a)) 279ENGINE = InnoDB 280PARTITION BY HASH (a) 281PARTITIONS 3 282AS SELECT a, b, c FROM tp_r; 283############################################################################# 284### Range partitioned tables with/out validation 285ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION; 286SELECT * FROM tp_r PARTITION (p0); 287a b c 2881 One 2001-01-01 2893 Three 2003-01-01 2905 Five 2005-01-01 2919 Nine 2009-01-01 292SELECT * FROM tp_r PARTITION (p1); 293a b c 29412 twelve 2012-01-01 29514 Fourteen 2014-01-01 29616 Sixteen 2016-01-01 29718 Eightteen 2018-01-01 298SELECT * FROM tp_r PARTITION (p2); 299a b c 300112 Hundred twelve 2112-01-01 301114 Hundred fourteen 2114-01-01 302116 Hundred sixteen 2116-01-01 303118 Hundred eightteen 2118-01-01 304122 Hundred twenty-two 2122-01-01 305124 Hundred twenty-four 2124-01-01 306126 Hundred twenty-six 2126-01-01 307128 Hundred twenty-eight 2128-01-01 308162 Hundred sixty-two 2162-01-01 309164 Hundred sixty-four 2164-01-01 310166 Hundred sixty-six 2166-01-01 311168 Hundred sixty-eight 2168-01-01 312182 Hundred eighty-two 2182-01-01 313184 Hundred eighty-four 2184-01-01 314186 Hundred eighty-six 2186-01-01 315188 Hundred eighty-eight 2188-01-01 316SELECT * FROM t_10; 317a b c 3182 Two 2002-01-01 3194 Four 2004-01-01 3206 Six 2006-01-01 3218 Eight 2008-01-01 322ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION; 323ERROR HY000: Found a row that does not match the partition 324ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION; 325SELECT * FROM tp_r PARTITION (p0); 326a b c 32711 Eleven 2011-01-01 32813 Thirdteen 2013-01-01 32915 Fifeteen 2015-01-01 33019 Nineteen 2019-01-01 33191 Ninety-one 2091-01-01 33293 Ninety-three 2093-01-01 33395 Ninety-five 2095-01-01 33499 Ninety-nine 2099-01-01 335SELECT * FROM tp_r PARTITION (p1); 336a b c 33712 twelve 2012-01-01 33814 Fourteen 2014-01-01 33916 Sixteen 2016-01-01 34018 Eightteen 2018-01-01 341SELECT * FROM tp_r PARTITION (p2); 342a b c 343112 Hundred twelve 2112-01-01 344114 Hundred fourteen 2114-01-01 345116 Hundred sixteen 2116-01-01 346118 Hundred eightteen 2118-01-01 347122 Hundred twenty-two 2122-01-01 348124 Hundred twenty-four 2124-01-01 349126 Hundred twenty-six 2126-01-01 350128 Hundred twenty-eight 2128-01-01 351162 Hundred sixty-two 2162-01-01 352164 Hundred sixty-four 2164-01-01 353166 Hundred sixty-six 2166-01-01 354168 Hundred sixty-eight 2168-01-01 355182 Hundred eighty-two 2182-01-01 356184 Hundred eighty-four 2184-01-01 357186 Hundred eighty-six 2186-01-01 358188 Hundred eighty-eight 2188-01-01 359SELECT * FROM t_100; 360a b c 3611 One 2001-01-01 3623 Three 2003-01-01 3635 Five 2005-01-01 3649 Nine 2009-01-01 365ALTER TABLE tp_r EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION; 366ALTER TABLE tp_r EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION; 367SELECT * FROM tp_r PARTITION (p2); 368a b c 369112 Hundred twelve 2112-01-01 370114 Hundred fourteen 2114-01-01 371116 Hundred sixteen 2116-01-01 372118 Hundred eightteen 2118-01-01 373122 Hundred twenty-two 2122-01-01 374124 Hundred twenty-four 2124-01-01 375126 Hundred twenty-six 2126-01-01 376128 Hundred twenty-eight 2128-01-01 377162 Hundred sixty-two 2162-01-01 378164 Hundred sixty-four 2164-01-01 379166 Hundred sixty-six 2166-01-01 380168 Hundred sixty-eight 2168-01-01 381182 Hundred eighty-two 2182-01-01 382184 Hundred eighty-four 2184-01-01 383186 Hundred eighty-six 2186-01-01 384188 Hundred eighty-eight 2188-01-01 385ALTER TABLE tp_r ANALYZE PARTITION p0; 386Table Op Msg_type Msg_text 387test.tp_r analyze status OK 388ALTER TABLE tp_r OPTIMIZE PARTITION p0; 389Table Op Msg_type Msg_text 390test.tp_r optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. 391test.tp_r optimize status OK 392DELETE FROM t_10; 393INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 394DELETE FROM t_100; 395INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 396INSERT 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'); 397CREATE TABLE t_11 LIKE tp_r; 398ALTER TABLE t_11 REMOVE PARTITIONING; 399INSERT INTO t_11 SELECT * FROM t_10; 400INSERT INTO t_11 SELECT * FROM t_100; 401ALTER TABLE tp_r EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION; 402SELECT * FROM tp_r PARTITION (p0); 403a b c 4041 One 2001-01-01 40511 Eleven 2011-01-01 40613 Thirdteen 2013-01-01 40715 Fifeteen 2015-01-01 40819 Nineteen 2019-01-01 4093 Three 2003-01-01 4105 Five 2005-01-01 4119 Nine 2009-01-01 41291 Ninety-one 2091-01-01 41393 Ninety-three 2093-01-01 41495 Ninety-five 2095-01-01 41599 Ninety-nine 2099-01-01 416DELETE FROM tp_r PARTITION (p1); 417SELECT * FROM tp_r PARTITION (p1); 418a b c 419SELECT * FROM t_11; 420a b c 42111 Eleven 2011-01-01 42213 Thirdteen 2013-01-01 42315 Fifeteen 2015-01-01 42419 Nineteen 2019-01-01 42591 Ninety-one 2091-01-01 42693 Ninety-three 2093-01-01 42795 Ninety-five 2095-01-01 42899 Ninety-nine 2099-01-01 429ALTER TABLE tp_r CHECK PARTITION p0 ; 430Table Op Msg_type Msg_text 431test.tp_r check error Found a misplaced row in part 0 should be in part 1: 432 a:11 433test.tp_r check error Partition p0 returned error 434test.tp_r check error Table upgrade required. Please do "REPAIR TABLE `tp_r`" or dump/reload to fix it! 435ALTER TABLE tp_r REPAIR PARTITION p0 ; 436Table Op Msg_type Msg_text 437test.tp_r repair warning Moved 8 misplaced rows 438test.tp_r repair status OK 439SELECT * FROM tp_r PARTITION (p0); 440a b c 4411 One 2001-01-01 4423 Three 2003-01-01 4435 Five 2005-01-01 4449 Nine 2009-01-01 445SELECT * FROM tp_r PARTITION (p1); 446a b c 44711 Eleven 2011-01-01 44813 Thirdteen 2013-01-01 44915 Fifeteen 2015-01-01 45019 Nineteen 2019-01-01 45191 Ninety-one 2091-01-01 45293 Ninety-three 2093-01-01 45395 Ninety-five 2095-01-01 45499 Ninety-nine 2099-01-01 455SELECT * FROM tp_r PARTITION (p2); 456a b c 457112 Hundred twelve 2112-01-01 458114 Hundred fourteen 2114-01-01 459116 Hundred sixteen 2116-01-01 460118 Hundred eightteen 2118-01-01 461122 Hundred twenty-two 2122-01-01 462124 Hundred twenty-four 2124-01-01 463126 Hundred twenty-six 2126-01-01 464128 Hundred twenty-eight 2128-01-01 465162 Hundred sixty-two 2162-01-01 466164 Hundred sixty-four 2164-01-01 467166 Hundred sixty-six 2166-01-01 468168 Hundred sixty-eight 2168-01-01 469182 Hundred eighty-two 2182-01-01 470184 Hundred eighty-four 2184-01-01 471186 Hundred eighty-six 2186-01-01 472188 Hundred eighty-eight 2188-01-01 473DROP TABLE IF EXISTS t_11; 474DELETE FROM t_10; 475INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 476DELETE FROM t_100; 477INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 478INSERT 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'); 479DELETE FROM tsp_r; 480INSERT INTO tsp_r VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01'); 481INSERT INTO tsp_r VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01'); 482INSERT 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'); 483INSERT 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'); 484INSERT 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'); 485INSERT 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'); 486### Range sub/partitioned tables with/out validation 487ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION; 488ERROR HY000: Found a row that does not match the partition 489ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_100 WITH VALIDATION; 490ERROR HY000: Found a row that does not match the partition 491ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_100 WITHOUT VALIDATION; 492SELECT * FROM t_100; 493a b c 494SELECT * FROM tsp_r PARTITION (sp00); 495a b c 49611 Eleven 2011-01-01 49713 Thirdteen 2013-01-01 49815 Fifeteen 2015-01-01 49919 Nineteen 2019-01-01 50091 Ninety-one 2091-01-01 50193 Ninety-three 2093-01-01 50295 Ninety-five 2095-01-01 50399 Ninety-nine 2099-01-01 504SELECT * FROM tsp_r PARTITION (sp22); 505a b c 506112 Hundred twelve 2112-01-01 507122 Hundred twenty-two 2122-01-01 508162 Hundred sixty-two 2162-01-01 509182 Hundred eight-two 2182-01-01 510ALTER TABLE tsp_r EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION; 511SELECT * FROM tsp_r PARTITION (sp22); 512a b c 513SELECT * FROM tsp_r PARTITION (sp22); 514a b c 515ALTER TABLE tsp_r EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION; 516SELECT * FROM tsp_r PARTITION (sp22); 517a b c 518112 Hundred twelve 2112-01-01 519122 Hundred twenty-two 2122-01-01 520162 Hundred sixty-two 2162-01-01 521182 Hundred eight-two 2182-01-01 522ALTER TABLE tsp_r ANALYZE PARTITION p0; 523Table Op Msg_type Msg_text 524test.tsp_r analyze status OK 525ALTER TABLE tsp_r OPTIMIZE PARTITION p0; 526Table Op Msg_type Msg_text 527test.tsp_r optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. 528test.tsp_r optimize status OK 529ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION; 530ERROR HY000: Found a row that does not match the partition 531CREATE TABLE t_11 LIKE t_10; 532INSERT INTO t_11 SELECT * FROM t_10; 533INSERT INTO t_11 SELECT * FROM t_100; 534ALTER TABLE tsp_r EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION; 535SELECT * FROM tsp_r PARTITION (sp00); 536a b c 5371 One 2001-01-01 5383 Three 2003-01-01 5395 Five 2005-01-01 5409 Nine 2009-01-01 541SELECT * FROM tsp_r PARTITION (sp01); 542a b c 5436 Six 2006-01-01 544SELECT * FROM tsp_r PARTITION (sp03); 545a b c 5468 Eight 2008-01-01 547SELECT * FROM tsp_r PARTITION (sp04); 548a b c 5494 Four 2004-01-01 550SELECT * FROM tsp_r PARTITION (sp02); 551a b c 5522 Two 2002-01-01 553ALTER TABLE tsp_r CHECK PARTITION sp00 ; 554Table Op Msg_type Msg_text 555test.tsp_r check error Found a misplaced row in part 0 should be in part 1: 556 a:1 557test.tsp_r check error Subpartition sp00 returned error 558test.tsp_r check error Table upgrade required. Please do "REPAIR TABLE `tsp_r`" or dump/reload to fix it! 559ALTER TABLE tsp_r REPAIR PARTITION sp00 ; 560Table Op Msg_type Msg_text 561test.tsp_r repair warning Moved 3 misplaced rows 562test.tsp_r repair status OK 563SELECT * FROM tsp_r PARTITION (sp00); 564a b c 5655 Five 2005-01-01 566SELECT * FROM tsp_r PARTITION (sp01); 567a b c 5681 One 2001-01-01 5696 Six 2006-01-01 570SELECT * FROM tsp_r PARTITION (sp02); 571a b c 5722 Two 2002-01-01 573SELECT * FROM tsp_r PARTITION (sp03); 574a b c 5753 Three 2003-01-01 5768 Eight 2008-01-01 577SELECT * FROM tsp_r PARTITION (sp04); 578a b c 5794 Four 2004-01-01 5809 Nine 2009-01-01 581DROP TABLE IF EXISTS t_11; 582DELETE FROM t_10; 583INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 584DELETE FROM t_100; 585INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 586INSERT 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'); 587CREATE TABLE t_11 LIKE tp_rintvar; 588ALTER TABLE t_11 REMOVE PARTITIONING; 589INSERT INTO t_11 SELECT * FROM t_10; 590INSERT INTO t_11 SELECT * FROM t_100; 591ALTER TABLE tp_rintvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION; 592ERROR HY000: Found a row that does not match the partition 593ALTER TABLE tp_rintvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION; 594SELECT * FROM tp_rintvar PARTITION (p0); 595a b c 5961 One 2001-01-01 59711 Eleven 2011-01-01 59813 Thirdteen 2013-01-01 59915 Fifeteen 2015-01-01 60019 Nineteen 2019-01-01 6013 Three 2003-01-01 6025 Five 2005-01-01 6039 Nine 2009-01-01 60491 Ninety-one 2091-01-01 60593 Ninety-three 2093-01-01 60695 Ninety-five 2095-01-01 60799 Ninety-nine 2099-01-01 608DELETE FROM tp_rintvar PARTITION (p1); 609SELECT * FROM tp_rintvar PARTITION (p1); 610a b c 611SELECT * FROM t_11; 612a b c 6132 Two 2002-01-01 6144 Four 2004-01-01 6156 Six 2006-01-01 6168 Eight 2008-01-01 617ALTER TABLE tp_rintvar CHECK PARTITION p0 ; 618Table Op Msg_type Msg_text 619test.tp_rintvar check error Found a misplaced row in part 0 should be in part 1: 620 a:11 b:Eleven 621test.tp_rintvar check error Partition p0 returned error 622test.tp_rintvar check error Table upgrade required. Please do "REPAIR TABLE `tp_rintvar`" or dump/reload to fix it! 623ALTER TABLE tp_rintvar REPAIR PARTITION p0 ; 624Table Op Msg_type Msg_text 625test.tp_rintvar repair warning Moved 8 misplaced rows 626test.tp_rintvar repair status OK 627SELECT * FROM tp_rintvar PARTITION (p0); 628a b c 6291 One 2001-01-01 6303 Three 2003-01-01 6315 Five 2005-01-01 6329 Nine 2009-01-01 633SELECT * FROM tp_rintvar PARTITION (p1); 634a b c 63511 Eleven 2011-01-01 63613 Thirdteen 2013-01-01 63715 Fifeteen 2015-01-01 63819 Nineteen 2019-01-01 63991 Ninety-one 2091-01-01 64093 Ninety-three 2093-01-01 64195 Ninety-five 2095-01-01 64299 Ninety-nine 2099-01-01 643SELECT * FROM tp_rintvar PARTITION (p2); 644a b c 645112 Hundred twelve 2112-01-01 646114 Hundred fourteen 2114-01-01 647116 Hundred sixteen 2116-01-01 648118 Hundred eightteen 2118-01-01 649122 Hundred twenty-two 2122-01-01 650124 Hundred twenty-four 2124-01-01 651126 Hundred twenty-six 2126-01-01 652128 Hundred twenty-eight 2128-01-01 653162 Hundred sixty-two 2162-01-01 654164 Hundred sixty-four 2164-01-01 655166 Hundred sixty-six 2166-01-01 656168 Hundred sixty-eight 2168-01-01 657182 Hundred eighty-two 2182-01-01 658184 Hundred eighty-four 2184-01-01 659186 Hundred eighty-six 2186-01-01 660188 Hundred eighty-eight 2188-01-01 661DROP TABLE IF EXISTS t_11; 662DELETE FROM t_10; 663INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 664DELETE FROM t_100; 665INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 666INSERT 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'); 667CREATE TABLE t_11 LIKE tp_rvar; 668ALTER TABLE t_11 REMOVE PARTITIONING; 669INSERT INTO t_11 SELECT * FROM t_10; 670INSERT INTO t_11 SELECT * FROM t_100; 671ALTER TABLE tp_rvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION; 672ERROR HY000: Found a row that does not match the partition 673ALTER TABLE tp_rvar EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION; 674SELECT * FROM tp_rvar PARTITION (p0); 675a b c 6761 One 2001-01-01 67711 Eleven 2011-01-01 67813 Thirdteen 2013-01-01 67915 Fifeteen 2015-01-01 68019 Nineteen 2019-01-01 6813 Three 2003-01-01 6825 Five 2005-01-01 6839 Nine 2009-01-01 68491 Ninety-one 2091-01-01 68593 Ninety-three 2093-01-01 68695 Ninety-five 2095-01-01 68799 Ninety-nine 2099-01-01 688DELETE FROM tp_rvar PARTITION (p1); 689SELECT * FROM tp_rvar PARTITION (p1); 690a b c 691SELECT * FROM t_11; 692a b c 69314 Fourteen 2014-01-01 69418 Eightteen 2018-01-01 6954 Four 2004-01-01 6968 Eight 2008-01-01 697ALTER TABLE tp_rvar CHECK PARTITION p0 ; 698Table Op Msg_type Msg_text 699test.tp_rvar check error Found a misplaced row in part 0 should be in part 1: 700 b:One 701test.tp_rvar check error Partition p0 returned error 702test.tp_rvar check error Table upgrade required. Please do "REPAIR TABLE `tp_rvar`" or dump/reload to fix it! 703ALTER TABLE tp_rvar REPAIR PARTITION p0 ; 704Table Op Msg_type Msg_text 705test.tp_rvar repair warning Moved 9 misplaced rows 706test.tp_rvar repair status OK 707SELECT * FROM tp_rvar PARTITION (p0); 708a b c 70911 Eleven 2011-01-01 71015 Fifeteen 2015-01-01 7115 Five 2005-01-01 712SELECT * FROM tp_rvar PARTITION (p1); 713a b c 7141 One 2001-01-01 71519 Nineteen 2019-01-01 7169 Nine 2009-01-01 71791 Ninety-one 2091-01-01 71893 Ninety-three 2093-01-01 71995 Ninety-five 2095-01-01 72099 Ninety-nine 2099-01-01 721SELECT * FROM tp_rvar PARTITION (p2); 722a b c 72312 twelve 2012-01-01 72413 Thirdteen 2013-01-01 72516 Sixteen 2016-01-01 7262 Two 2002-01-01 7273 Three 2003-01-01 7286 Six 2006-01-01 729DROP TABLE IF EXISTS t_11; 730DELETE FROM t_10; 731INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 732DELETE FROM t_100; 733INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 734INSERT 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'); 735CREATE TABLE t_11 LIKE tp_rintdate; 736ALTER TABLE t_11 REMOVE PARTITIONING; 737INSERT INTO t_11 SELECT * FROM t_10; 738INSERT INTO t_11 SELECT * FROM t_100; 739ALTER TABLE tp_rintdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION; 740ERROR HY000: Found a row that does not match the partition 741ALTER TABLE tp_rintdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION; 742SELECT * FROM tp_rintdate PARTITION (p0); 743a b c 7441 One 2001-01-01 74511 Eleven 2011-01-01 74613 Thirdteen 2013-01-01 74715 Fifeteen 2015-01-01 74819 Nineteen 2019-01-01 7493 Three 2003-01-01 7505 Five 2005-01-01 7519 Nine 2009-01-01 75291 Ninety-one 2091-01-01 75393 Ninety-three 2093-01-01 75495 Ninety-five 2095-01-01 75599 Ninety-nine 2099-01-01 756DELETE FROM tp_rintdate PARTITION (p1); 757SELECT * FROM tp_rintdate PARTITION (p1); 758a b c 759SELECT * FROM t_11; 760a b c 7611 One 2001-01-01 7623 Three 2003-01-01 7635 Five 2005-01-01 7649 Nine 2009-01-01 765ALTER TABLE tp_rintdate CHECK PARTITION p0 ; 766Table Op Msg_type Msg_text 767test.tp_rintdate check error Found a misplaced row in part 0 should be in part 1: 768 a:11 c:2011-01-01 769test.tp_rintdate check error Partition p0 returned error 770test.tp_rintdate check error Table upgrade required. Please do "REPAIR TABLE `tp_rintdate`" or dump/reload to fix it! 771ALTER TABLE tp_rintdate REPAIR PARTITION p0 ; 772Table Op Msg_type Msg_text 773test.tp_rintdate repair warning Moved 8 misplaced rows 774test.tp_rintdate repair status OK 775SELECT * FROM tp_rintdate PARTITION (p0); 776a b c 7771 One 2001-01-01 7783 Three 2003-01-01 7795 Five 2005-01-01 7809 Nine 2009-01-01 781SELECT * FROM tp_rintdate PARTITION (p1); 782a b c 78311 Eleven 2011-01-01 78413 Thirdteen 2013-01-01 78515 Fifeteen 2015-01-01 78619 Nineteen 2019-01-01 78791 Ninety-one 2091-01-01 78893 Ninety-three 2093-01-01 78995 Ninety-five 2095-01-01 79099 Ninety-nine 2099-01-01 791SELECT * FROM tp_rintdate PARTITION (p2); 792a b c 793111 Hundred elven 2111-01-01 794113 Hundred thirdteen 2113-01-01 795115 Hundred fiveteen 2115-01-01 796119 Hundred nineteen 2119-01-01 797131 Hundred thirty-one 2131-01-01 798133 Hundred thirty-three 2133-01-01 799135 Hundred thirty-five 2135-01-01 800139 Hundred thirty-nine 2139-01-01 801151 Hundred fifty-one 2151-01-01 802153 Hundred fifty-three 2153-01-01 803155 Hundred fity-five 2155-01-01 804159 Hundred fifty-nine 2159-01-01 805191 Hundred ninety-one 2191-01-01 806193 Hundred ninety-three 2193-01-01 807195 Hundred ninety-five 2195-01-01 808199 Hundred ninety-nine 2199-01-01 809DROP TABLE IF EXISTS t_11; 810DELETE FROM t_10; 811INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 812DELETE FROM t_100; 813INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 814INSERT 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'); 815CREATE TABLE t_11 LIKE tp_rdate; 816ALTER TABLE t_11 REMOVE PARTITIONING; 817INSERT INTO t_11 SELECT * FROM t_10; 818INSERT INTO t_11 SELECT * FROM t_100; 819ALTER TABLE tp_rdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITH VALIDATION; 820ERROR HY000: Found a row that does not match the partition 821ALTER TABLE tp_rdate EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION; 822SELECT * FROM tp_rdate PARTITION (p0); 823a b c 8241 One 2001-01-01 82511 Eleven 2011-01-01 82613 Thirdteen 2013-01-01 82715 Fifeteen 2015-01-01 82819 Nineteen 2019-01-01 8293 Three 2003-01-01 8305 Five 2005-01-01 8319 Nine 2009-01-01 83291 Ninety-one 2091-01-01 83393 Ninety-three 2093-01-01 83495 Ninety-five 2095-01-01 83599 Ninety-nine 2099-01-01 836DELETE FROM tp_rdate PARTITION (p1); 837SELECT * FROM tp_rdate PARTITION (p1); 838a b c 839SELECT * FROM t_11; 840a b c 8412 Two 2002-01-01 8424 Four 2004-01-01 8436 Six 2006-01-01 8448 Eight 2008-01-01 845ALTER TABLE tp_rdate CHECK PARTITION p0 ; 846Table Op Msg_type Msg_text 847test.tp_rdate check error Found a misplaced row in part 0 should be in part 1: 848 c:2011-01-01 849test.tp_rdate check error Partition p0 returned error 850test.tp_rdate check error Table upgrade required. Please do "REPAIR TABLE `tp_rdate`" or dump/reload to fix it! 851ALTER TABLE tp_rdate REPAIR PARTITION p0 ; 852Table Op Msg_type Msg_text 853test.tp_rdate repair warning Moved 8 misplaced rows 854test.tp_rdate repair status OK 855SELECT * FROM tp_rdate PARTITION (p0); 856a b c 8571 One 2001-01-01 8583 Three 2003-01-01 8595 Five 2005-01-01 8609 Nine 2009-01-01 861SELECT * FROM tp_rdate PARTITION (p1); 862a b c 86311 Eleven 2011-01-01 86413 Thirdteen 2013-01-01 86515 Fifeteen 2015-01-01 86619 Nineteen 2019-01-01 86791 Ninety-one 2091-01-01 86893 Ninety-three 2093-01-01 86995 Ninety-five 2095-01-01 87099 Ninety-nine 2099-01-01 871SELECT * FROM tp_rdate PARTITION (p2); 872a b c 873DROP TABLE IF EXISTS t_11; 874DELETE FROM t_10; 875INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 876DELETE FROM t_100; 877INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 878INSERT 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'); 879CREATE TABLE t_11 LIKE tsp_rvar; 880ALTER TABLE t_11 REMOVE PARTITIONING; 881INSERT INTO t_11 SELECT * FROM t_10; 882INSERT INTO t_11 SELECT * FROM t_100; 883ALTER TABLE tsp_rvar EXCHANGE PARTITION sp00 WITH TABLE t_11 WITH VALIDATION; 884ERROR HY000: Found a row that does not match the partition 885ALTER TABLE tsp_rvar EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION; 886SELECT * FROM tsp_rvar PARTITION (sp00); 887a b c 8881 One 2001-01-01 88911 Eleven 2011-01-01 89013 Thirdteen 2013-01-01 89115 Fifeteen 2015-01-01 89219 Nineteen 2019-01-01 8933 Three 2003-01-01 8945 Five 2005-01-01 8959 Nine 2009-01-01 89691 Ninety-one 2091-01-01 89793 Ninety-three 2093-01-01 89895 Ninety-five 2095-01-01 89999 Ninety-nine 2099-01-01 900DELETE FROM tsp_rvar PARTITION (sp01); 901SELECT * FROM tsp_rvar PARTITION (sp01); 902a b c 903SELECT * FROM t_11; 904a b c 905ALTER TABLE tsp_rvar CHECK PARTITION p0 ; 906Table Op Msg_type Msg_text 907test.tsp_rvar check error Found a misplaced row in part 0 should be in part 6: 908 a:1 b:One 909test.tsp_rvar check error Subpartition sp00 returned error 910test.tsp_rvar check error Table upgrade required. Please do "REPAIR TABLE `tsp_rvar`" or dump/reload to fix it! 911ALTER TABLE tsp_rvar REPAIR PARTITION p0 ; 912Table Op Msg_type Msg_text 913test.tsp_rvar repair warning Moved 10 misplaced rows 914test.tsp_rvar repair status OK 915SELECT * FROM tsp_rvar PARTITION (sp00); 916a b c 91715 Fifeteen 2015-01-01 9185 Five 2005-01-01 919SELECT * FROM tsp_rvar PARTITION (sp01); 920a b c 92111 Eleven 2011-01-01 922SELECT * FROM tsp_rvar PARTITION (sp02); 923a b c 924SELECT * FROM tsp_rvar PARTITION (sp03); 925a b c 92618 Eightteen 2018-01-01 9278 Eight 2008-01-01 928SELECT * FROM tsp_rvar PARTITION (sp04); 929a b c 93014 Fourteen 2014-01-01 9314 Four 2004-01-01 932DROP TABLE IF EXISTS t_11; 933DELETE FROM t_10; 934INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 935DELETE FROM t_100; 936INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 937INSERT 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'); 938CREATE TABLE t_11 LIKE tsp_rvar1; 939ALTER TABLE t_11 REMOVE PARTITIONING; 940INSERT INTO t_11 SELECT * FROM t_10; 941INSERT INTO t_11 SELECT * FROM t_100; 942ALTER TABLE tsp_rvar1 EXCHANGE PARTITION p0sp0 WITH TABLE t_11 WITH VALIDATION; 943ERROR HY000: Found a row that does not match the partition 944ALTER TABLE tsp_rvar1 EXCHANGE PARTITION p0sp0 WITH TABLE t_11 WITHOUT VALIDATION; 945SELECT * FROM tsp_rvar1 PARTITION (p0sp0); 946a b c 9471 One 2001-01-01 94811 Eleven 2011-01-01 94913 Thirdteen 2013-01-01 95015 Fifeteen 2015-01-01 95119 Nineteen 2019-01-01 9523 Three 2003-01-01 9535 Five 2005-01-01 9549 Nine 2009-01-01 95591 Ninety-one 2091-01-01 95693 Ninety-three 2093-01-01 95795 Ninety-five 2095-01-01 95899 Ninety-nine 2099-01-01 959DELETE FROM tsp_rvar1 PARTITION (p0sp1); 960SELECT * FROM tsp_rvar1 PARTITION (p0sp1); 961a b c 962SELECT * FROM t_11; 963a b c 964ALTER TABLE tsp_rvar1 CHECK PARTITION p0 ; 965Table Op Msg_type Msg_text 966test.tsp_rvar1 check error Found a misplaced row in part 0 should be in part 6: 967 a:1 b:One 968test.tsp_rvar1 check error Subpartition p0sp0 returned error 969test.tsp_rvar1 check error Table upgrade required. Please do "REPAIR TABLE `tsp_rvar1`" or dump/reload to fix it! 970ALTER TABLE tsp_rvar1 REPAIR PARTITION p0 ; 971Table Op Msg_type Msg_text 972test.tsp_rvar1 repair warning Moved 10 misplaced rows 973test.tsp_rvar1 repair status OK 974SELECT * FROM tsp_rvar1 PARTITION (p0sp0); 975a b c 97615 Fifeteen 2015-01-01 9775 Five 2005-01-01 978SELECT * FROM tsp_rvar1 PARTITION (p0sp1); 979a b c 98011 Eleven 2011-01-01 981SELECT * FROM tsp_rvar1 PARTITION (p0sp2); 982a b c 983SELECT * FROM tsp_rvar1 PARTITION (p0sp3); 984a b c 98518 Eightteen 2018-01-01 9868 Eight 2008-01-01 987SELECT * FROM tsp_rvar1 PARTITION (p0sp4); 988a b c 98914 Fourteen 2014-01-01 9904 Four 2004-01-01 991DROP TABLE IF EXISTS t_11; 992DROP TABLE IF EXISTS t_10; 993DROP TABLE IF EXISTS t_100; 994DROP TABLE IF EXISTS t_1000; 995DROP TABLE IF EXISTS tp_r; 996DROP TABLE IF EXISTS tp_rvar; 997DROP TABLE IF EXISTS tp_rintvar; 998DROP TABLE IF EXISTS tp_rdate; 999DROP TABLE IF EXISTS tp_rintdate; 1000DROP TABLE IF EXISTS tsp_r; 1001DROP TABLE IF EXISTS tsp_rvar; 1002DROP TABLE IF EXISTS tsp_rvar1; 1003DROP TABLE IF EXISTS tp_l; 1004DROP TABLE IF EXISTS tsp_l; 1005DROP TABLE IF EXISTS tsp_lvar; 1006DROP TABLE IF EXISTS tsp_ldate; 1007DROP TABLE IF EXISTS tp_k; 1008DROP TABLE IF EXISTS tp_kvar; 1009DROP TABLE IF EXISTS tp_kdate; 1010DROP TABLE IF EXISTS tp_h; 1011DROP TABLE IF EXISTS tp_hvar; 1012DROP TABLE IF EXISTS tp_hdate; 1013DROP TABLE IF EXISTS tsp_00; 1014DROP TABLE IF EXISTS tsp_01; 1015DROP TABLE IF EXISTS tsp_02; 1016DROP TABLE IF EXISTS tsp_03; 1017DROP TABLE IF EXISTS tsp_04; 1018DROP TABLE IF EXISTS t_empty; 1019DROP TABLE IF EXISTS t_null; 1020