Debian Codegraph @

mysql-test/suite/parts/inc/part_exch_valid_list.inc

raw
1 use test;
2
3 call 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
13 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
14 --sorted_result
15 SELECT * FROM tp_l PARTITION (p0);
16 --sorted_result
17 SELECT * FROM tp_l PARTITION (p1);
18 --sorted_result
19 SELECT * FROM tp_l PARTITION (p2);
20
21 --error ER_ROW_DOES_NOT_MATCH_PARTITION
22 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_100 WITH VALIDATION;
23 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_100 WITHOUT VALIDATION;
24 --sorted_result
25 SELECT * FROM tp_l PARTITION (p0);
26 --sorted_result
27 SELECT * FROM tp_l PARTITION (p1);
28 --sorted_result
29 SELECT * 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));
42 ALTER 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));
53 ALTER TABLE tp_l EXCHANGE PARTITION p2 WITH TABLE t_100 WITHOUT VALIDATION;
54 --sorted_result
55 SELECT * FROM tp_l PARTITION (p2);
56 ALTER TABLE tp_l ANALYZE PARTITION p0;
57 ALTER TABLE tp_l OPTIMIZE PARTITION p0;
58
59 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_10 WITH VALIDATION;
60 --sorted_result
61 SELECT * FROM tp_l PARTITION (p0);
62 --sorted_result
63 SELECT * FROM tp_l PARTITION (p1);
64 --sorted_result
65 SELECT * FROM tp_l PARTITION (p2);
66 CREATE TABLE t_11 LIKE tp_l;
67 ALTER TABLE t_11 REMOVE PARTITIONING;
68 INSERT INTO t_11 SELECT * FROM t_10;
69 INSERT INTO t_11 SELECT * FROM t_100;
70 ALTER TABLE tp_l EXCHANGE PARTITION p0 WITH TABLE t_11 WITHOUT VALIDATION;
71 --sorted_result
72 SELECT * FROM tp_l PARTITION (p0);
73 DELETE FROM tp_l PARTITION (p1);
74 --sorted_result
75 SELECT * FROM tp_l PARTITION (p1);
76 ALTER TABLE tp_l CHECK PARTITION p0 ;
77 ALTER TABLE tp_l REPAIR PARTITION p0 ;
78 --sorted_result
79 SELECT * FROM tp_l PARTITION (p0);
80 --sorted_result
81 SELECT * FROM tp_l PARTITION (p1);
82 --sorted_result
83 SELECT * FROM tp_l PARTITION (p2);
84 DROP TABLE IF EXISTS t_11;
85
86 # Values t_10 (not partitioned)
87 DELETE FROM t_10;
88 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');
89
90 # Values t_100 (not partitioned)
91 DELETE FROM t_100;
92 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');
93 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');
94
95 # Values tps (subpartitions)
96 DELETE FROM tsp_l;
97 INSERT INTO tsp_l VALUES (2, "Two", '2002-01-01'), (4, "Four", '2004-01-01'), (6, "Six", '2006-01-01'), (8, "Eight", '2008-01-01');
98 INSERT INTO tsp_l VALUES (12, "twelve", '2012-01-01'), (14, "Fourteen", '2014-01-01'), (16, "Sixteen", '2016-01-01'), (18, "Eightteen", '2018-01-01');
99 INSERT 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');
100 INSERT 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');
101 INSERT 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');
102 INSERT 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
107 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
108 --sorted_result
109 --error ER_ROW_DOES_NOT_MATCH_PARTITION
110 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_100 WITH VALIDATION;
111 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_100 WITHOUT VALIDATION;
112 --sorted_result
113 SELECT * FROM t_100;
114 --sorted_result
115 SELECT * FROM tsp_l PARTITION (sp00);
116 #ALTER TABLE tsp_l REBUILD PARTITION sp00;
117 --sorted_result
118 SELECT * 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
134 SELECT * FROM tsp_l PARTITION (sp22);
135 ALTER TABLE tsp_l EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
136 --sorted_result
137 SELECT * 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
154 SELECT * FROM tsp_l PARTITION (sp22);
155 ALTER TABLE tsp_l EXCHANGE PARTITION sp22 WITH TABLE t_100 WITHOUT VALIDATION;
156 --sorted_result
157 SELECT * FROM tsp_l PARTITION (sp22);
158 ALTER TABLE tsp_l ANALYZE PARTITION p0;
159 ALTER TABLE tsp_l OPTIMIZE PARTITION p0;
160 --error ER_ROW_DOES_NOT_MATCH_PARTITION
161 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_10 WITH VALIDATION;
162 CREATE TABLE t_11 LIKE t_10;
163 INSERT INTO t_11 SELECT * FROM t_10;
164 INSERT INTO t_11 SELECT * FROM t_100;
165 ALTER TABLE tsp_l EXCHANGE PARTITION sp00 WITH TABLE t_11 WITHOUT VALIDATION;
166 --sorted_result
167 SELECT * FROM tsp_l PARTITION (sp00);
168 --sorted_result
169 SELECT * FROM tsp_l PARTITION (sp01);
170 --sorted_result
171 SELECT * FROM tsp_l PARTITION (sp03);
172 --sorted_result
173 SELECT * FROM tsp_l PARTITION (sp04);
174 --sorted_result
175 SELECT * FROM tsp_l PARTITION (sp02);
176 ALTER TABLE tsp_l CHECK PARTITION sp00 ;
177 ALTER TABLE tsp_l REPAIR PARTITION sp00 ;
178 --sorted_result
179 SELECT * FROM tsp_l PARTITION (sp00);
180 --sorted_result
181 SELECT * FROM tsp_l PARTITION (sp01);
182 --sorted_result
183 SELECT * FROM tsp_l PARTITION (sp02);
184 --sorted_result
185 SELECT * FROM tsp_l PARTITION (sp03);
186 --sorted_result
187 SELECT * FROM tsp_l PARTITION (sp04);
188 DROP TABLE IF EXISTS t_11;
189
190 --source suite/parts/inc/part_exch_drop_ext_tabs.inc
191