1use test; 2 3call mtr.add_suppression("row in wrong partition.*from REBUILD/REORGANIZED"); 4#--disable_result_log 5#--disable_query_log 6--source suite/parts/inc/part_exch_ext_tabs.inc 7#--enable_result_log 8#--enable_query_log 9 10--echo ######################################################################## 11--echo ### List partitioned tables with/out validation 12 13ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION; 14--sorted_result 15SELECT * FROM tp_l PARTITION (p0); 16--sorted_result 17SELECT * FROM tp_l PARTITION (p1); 18--sorted_result 19SELECT * FROM tp_l PARTITION (p2); 20 21--error ER_ROW_DOES_NOT_MATCH_PARTITION 22ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION; 23ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION; 24--sorted_result 25SELECT * FROM tp_l PARTITION (p0); 26--sorted_result 27SELECT * FROM tp_l PARTITION (p1); 28--sorted_result 29SELECT * FROM tp_l PARTITION (p2); 30 31#--error ER_ROW_IN_WRONG_PARTITION 32#ALTER TABLE tp_l REBUILD PARTITION p0; 33#ALTER TABLE tp_l REORGANIZE PARTITION p2 INTO 34#(PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119, 35# 120,121,122,123,124,125,126,127,128,129, 36# 130,131,132,133,134,135,136,137,138,139, 37# 150,151,152,153,154,155,156,157,158,159, 38# 160,161,162,163,164,165,166,167,168,169, 39# 180,181,182,183,184,185,186,187,188,189, 40# 190,191,192,193,194,195,196,197,198,199, 41# 200,201,202,203,204,205,206,207,208,209)); 42ALTER TABLE tp_l EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION; 43#--error ER_ROW_IN_WRONG_PARTITION 44#ALTER TABLE tp_l REORGANIZE PARTITION p2 INTO 45#(PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119, 46# 120,121,122,123,124,125,126,127,128,129, 47# 130,131,132,133,134,135,136,137,138,139, 48# 150,151,152,153,154,155,156,157,158,159, 49# 160,161,162,163,164,165,166,167,168,169, 50# 180,181,182,183,184,185,186,187,188,189, 51# 190,191,192,193,194,195,196,197,198,199, 52# 200,201,202,203,204,205,206,207,208,209)); 53ALTER TABLE tp_l EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION; 54--sorted_result 55SELECT * FROM tp_l PARTITION (p2); 56ALTER TABLE tp_l ANALYZE PARTITION p0; 57ALTER TABLE tp_l OPTIMIZE PARTITION p0; 58 59ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION; 60--sorted_result 61SELECT * FROM tp_l PARTITION (p0); 62--sorted_result 63SELECT * FROM tp_l PARTITION (p1); 64--sorted_result 65SELECT * FROM tp_l PARTITION (p2); 66CREATE TABLE t_11 LIKE tp_l; 67ALTER TABLE t_11 REMOVE PARTITIONING; 68INSERT INTO t_11 SELECT * FROM t_10; 69INSERT INTO t_11 SELECT * FROM t_100; 70ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION; 71--sorted_result 72SELECT * FROM tp_l PARTITION (p0); 73DELETE FROM tp_l PARTITION (p1); 74--sorted_result 75SELECT * FROM tp_l PARTITION (p1); 76ALTER TABLE tp_l CHECK PARTITION p0 ; 77ALTER TABLE tp_l REPAIR PARTITION p0 ; 78--sorted_result 79SELECT * FROM tp_l PARTITION (p0); 80--sorted_result 81SELECT * FROM tp_l PARTITION (p1); 82--sorted_result 83SELECT * FROM tp_l PARTITION (p2); 84DROP TABLE IF EXISTS t_11; 85 86# Values t_10 (not partitioned) 87DELETE FROM t_10; 88INSERT INTO t_10 VALUES (1, "One", '2001-01-01'), (3, "Three", '2003-01-01'), (5, "Five", '2005-01-01'), (9, "Nine", '2009-01-01'); 89 90# Values t_100 (not partitioned) 91DELETE FROM t_100; 92INSERT INTO t_100 VALUES (11, "Eleven", '2011-01-01'), (13, "Thirdteen", '2013-01-01'), (15, "Fifeteen", '2015-01-01'), (19, "Nineteen", '2019-01-01'); 93INSERT 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'); 94 95# Values tps (subpartitions) 96DELETE FROM tsp_l; 97INSERT INTO tsp_l VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01'); 98INSERT INTO tsp_l VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01'); 99INSERT INTO tsp_l 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'); 100INSERT INTO tsp_l 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'); 101INSERT INTO tsp_l 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'); 102INSERT INTO tsp_l 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'); 103 104--echo ### Range sub/partitioned tables with/out validation 105 106--error ER_ROW_DOES_NOT_MATCH_PARTITION 107ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION; 108--sorted_result 109--error ER_ROW_DOES_NOT_MATCH_PARTITION 110ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_100 WITH VALIDATION; 111ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_100 WITHOUT VALIDATION; 112--sorted_result 113SELECT * FROM t_100; 114--sorted_result 115SELECT * FROM tsp_l PARTITION (sp00); 116#ALTER TABLE tsp_l REBUILD PARTITION sp00; 117--sorted_result 118SELECT * FROM tsp_l PARTITION (sp00); 119#ALTER TABLE tsp_l REORGANIZE PARTITION p2 INTO 120#(PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119, 121# 120,121,122,123,124,125,126,127,128,129, 122# 130,131,132,133,134,135,136,137,138,139, 123# 150,151,152,153,154,155,156,157,158,159, 124# 160,161,162,163,164,165,166,167,168,169, 125# 180,181,182,183,184,185,186,187,188,189, 126# 190,191,192,193,194,195,196,197,198,199, 127# 200,201,202,203,204,205,206,207,208,209 ) 128# (SUBPARTITION sp20, 129# SUBPARTITION sp21, 130# SUBPARTITION sp22, 131# SUBPARTITION sp23, 132# SUBPARTITION sp24)); 133--sorted_result 134SELECT * FROM tsp_l PARTITION (sp22); 135ALTER TABLE tsp_l EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION; 136--sorted_result 137SELECT * FROM tsp_l PARTITION (sp22); 138#--error ER_ROW_IN_WRONG_PARTITION 139#ALTER TABLE tsp_l REORGANIZE PARTITION p2 INTO 140#(PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119, 141# 120,121,122,123,124,125,126,127,128,129, 142# 130,131,132,133,134,135,136,137,138,139, 143# 150,151,152,153,154,155,156,157,158,159, 144# 160,161,162,163,164,165,166,167,168,169, 145# 180,181,182,183,184,185,186,187,188,189, 146# 190,191,192,193,194,195,196,197,198,199, 147# 200,201,202,203,204,205,206,207,208,209 ) 148# (SUBPARTITION sp20, 149# SUBPARTITION sp21, 150# SUBPARTITION sp22, 151# SUBPARTITION sp23, 152# SUBPARTITION sp24)); 153--sorted_result 154SELECT * FROM tsp_l PARTITION (sp22); 155ALTER TABLE tsp_l EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION; 156--sorted_result 157SELECT * FROM tsp_l PARTITION (sp22); 158ALTER TABLE tsp_l ANALYZE PARTITION p0; 159ALTER TABLE tsp_l OPTIMIZE PARTITION p0; 160--error ER_ROW_DOES_NOT_MATCH_PARTITION 161ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION; 162CREATE TABLE t_11 LIKE t_10; 163INSERT INTO t_11 SELECT * FROM t_10; 164INSERT INTO t_11 SELECT * FROM t_100; 165ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION; 166--sorted_result 167SELECT * FROM tsp_l PARTITION (sp00); 168--sorted_result 169SELECT * FROM tsp_l PARTITION (sp01); 170--sorted_result 171SELECT * FROM tsp_l PARTITION (sp03); 172--sorted_result 173SELECT * FROM tsp_l PARTITION (sp04); 174--sorted_result 175SELECT * FROM tsp_l PARTITION (sp02); 176ALTER TABLE tsp_l CHECK PARTITION sp00 ; 177ALTER TABLE tsp_l REPAIR PARTITION sp00 ; 178--sorted_result 179SELECT * FROM tsp_l PARTITION (sp00); 180--sorted_result 181SELECT * FROM tsp_l PARTITION (sp01); 182--sorted_result 183SELECT * FROM tsp_l PARTITION (sp02); 184--sorted_result 185SELECT * FROM tsp_l PARTITION (sp03); 186--sorted_result 187SELECT * FROM tsp_l PARTITION (sp04); 188DROP TABLE IF EXISTS t_11; 189 190--source suite/parts/inc/part_exch_drop_ext_tabs.inc 191 192