Debian Codegraph @

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

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t_10;
3 DROP TABLE IF EXISTS t_100;
4 DROP TABLE IF EXISTS t_1000;
5 DROP TABLE IF EXISTS tp_r;
6 DROP TABLE IF EXISTS tp_rvar;
7 DROP TABLE IF EXISTS tp_rintvar;
8 DROP TABLE IF EXISTS tp_rdate;
9 DROP TABLE IF EXISTS tp_rintdate;
10 DROP TABLE IF EXISTS tsp_r;
11 DROP TABLE IF EXISTS tsp_rvar;
12 DROP TABLE IF EXISTS tsp_rvar1;
13 DROP TABLE IF EXISTS tp_l;
14 DROP TABLE IF EXISTS tsp_l;
15 DROP TABLE IF EXISTS tsp_lvar;
16 DROP TABLE IF EXISTS tsp_ldate;
17 DROP TABLE IF EXISTS tp_k;
18 DROP TABLE IF EXISTS tp_kvar;
19 DROP TABLE IF EXISTS tp_kdate;
20 DROP TABLE IF EXISTS tp_h;
21 DROP TABLE IF EXISTS tp_hvar;
22 DROP TABLE IF EXISTS tp_hdate;
23 DROP TABLE IF EXISTS tsp_00;
24 DROP TABLE IF EXISTS tsp_01;
25 DROP TABLE IF EXISTS tsp_02;
26 DROP TABLE IF EXISTS tsp_03;
27 DROP TABLE IF EXISTS tsp_04;
28 DROP TABLE IF EXISTS t_empty;
29 DROP TABLE IF EXISTS t_null;
30 --enable_warnings
31
32 # Base tables without partitions allowing exchange and insert from.
33 eval CREATE TABLE t_10 (a INT,
34 b VARCHAR(25),
35 c DATE,
36 PRIMARY KEY (a)) $data_directory $index_directory
37 charset latin1
38 ENGINE = $engine_table;
39
40 eval CREATE TABLE t_100 (a INT,
41 b VARCHAR(25),
42 c DATE,
43 PRIMARY KEY (a)) $data_directory $index_directory
44 charset latin1
45 ENGINE = $engine_table;
46
47 eval CREATE TABLE t_1000 (a INT,
48 b VARCHAR(25),
49 c DATE,
50 PRIMARY KEY (a)) $data_directory $index_directory
51 charset latin1
52 ENGINE = $engine_table;
53
54 # Values t_10 (not partitioned)
55 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');
56
57 # Values t_100 (not partitioned)
58 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');
59 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');
60
61 # Values t_1000 (not partitioned)
62 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');
63 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');
64 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');
65 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');
66
67 # Special tables
68 eval CREATE TABLE t_empty (a INT,
69 b VARCHAR(25),
70 c DATE,
71 PRIMARY KEY (a)) $data_directory $index_directory
72 charset latin1
73 ENGINE = $engine_table;
74
75 eval CREATE TABLE t_null (a INT,
76 b VARCHAR(25),
77 c DATE,
78 PRIMARY KEY (a)) $data_directory $index_directory
79 charset latin1
80 ENGINE = $engine_table;
81
82 eval CREATE TABLE tsp_01(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
83 charset latin1
84 ENGINE = $engine_table $data_directory $index_directory
85 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
86 eval CREATE TABLE tsp_02(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
87 charset latin1
88 ENGINE = $engine_table $data_directory $index_directory
89 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
90 eval CREATE TABLE tsp_03(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
91 charset latin1
92 ENGINE = $engine_table $data_directory $index_directory
93 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
94 eval CREATE TABLE tsp_04(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
95 charset latin1
96 ENGINE = $engine_table $data_directory $index_directory
97 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
98 eval CREATE TABLE tsp_00(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (a))
99 charset latin1
100 ENGINE = $engine_table $data_directory $index_directory
101 AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
102
103 # Tables with range partition.
104 eval CREATE TABLE tp_r (a INT,
105 b VARCHAR(25),
106 c DATE,
107 PRIMARY KEY (a)) $data_directory $index_directory
108 charset latin1
109 ENGINE = $engine_part
110 PARTITION BY RANGE (a)
111 (PARTITION p0 VALUES LESS THAN (10) $p_data_directory $p_index_directory,
112 PARTITION p1 VALUES LESS THAN (100) $p_data_directory $p_index_directory,
113 PARTITION p2 VALUES LESS THAN (1000) $p_data_directory $p_index_directory);
114
115 eval CREATE TABLE tsp_r (a INT,
116 b VARCHAR(25),
117 c DATE,
118 PRIMARY KEY (a)) $data_directory $index_directory
119 charset latin1
120 ENGINE = $engine_subpart
121 PARTITION BY RANGE (a)
122 SUBPARTITION BY HASH(a)
123 (PARTITION p0 VALUES LESS THAN (10) $p_data_directory $p_index_directory
124 (SUBPARTITION sp00,
125 SUBPARTITION sp01,
126 SUBPARTITION sp02,
127 SUBPARTITION sp03,
128 SUBPARTITION sp04),
129 PARTITION p1 VALUES LESS THAN (100)
130 (SUBPARTITION sp10 $p_data_directory $p_index_directory,
131 SUBPARTITION sp11 $p_data_directory $p_index_directory,
132 SUBPARTITION sp12 $p_data_directory $p_index_directory,
133 SUBPARTITION sp13 $p_data_directory $p_index_directory,
134 SUBPARTITION sp14 $p_data_directory $p_index_directory),
135 PARTITION p2 VALUES LESS THAN (1000) $p_data_directory $p_index_directory
136 (SUBPARTITION sp20,
137 SUBPARTITION sp21,
138 SUBPARTITION sp22,
139 SUBPARTITION sp23,
140 SUBPARTITION sp24));
141
142 # Values tp_r (partitions)
143 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');
144 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');
145 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');
146 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');
147 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');
148 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');
149
150 # Values tps (subpartitions)
151 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');
152 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');
153 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');
154 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');
155 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');
156 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');
157
158 --error ER_VALUES_IS_NOT_INT_TYPE_ERROR
159 eval CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE,PRIMARY KEY (b))
160 charset latin1
161 ENGINE = $engine_table $data_directory $index_directory
162 PARTITION BY RANGE (b)
163 (PARTITION p0 VALUES LESS THAN ('H') $p_data_directory $p_index_directory,
164 PARTITION p1 VALUES LESS THAN ('Q') $p_data_directory $p_index_directory,
165 PARTITION p2 VALUES LESS THAN ('Z') $p_data_directory $p_index_directory,
166 PARTITION p3 VALUES LESS THAN (MAXVALUE) $p_data_directory $p_index_directory)
167 AS SELECT a, b FROM tp_r;
168
169 eval CREATE TABLE tp_rintvar(a INT,b VARCHAR(25),c DATE,
170 PRIMARY KEY (a,b))
171 charset latin1
172 ENGINE = $engine_table $data_directory $index_directory
173 PARTITION BY RANGE COLUMNS (a,b)
174 (PARTITION p0 VALUES LESS THAN (10,'HHHHHHHHHHHHHHHHHHHHHHHHH'),
175 PARTITION p1 VALUES LESS THAN (100,'PPPPPPPPPPPPPPPPPPPPPPPPP'),
176 PARTITION p2 VALUES LESS THAN (1000,'WWWWWWWWWWWWWWWWWWWWWWWWW'),
177 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE))
178 AS SELECT a, b, c FROM tp_r;
179
180 eval CREATE TABLE tp_rvar(a INT,b VARCHAR(25),c DATE)
181 charset latin1
182 ENGINE = $engine_table $data_directory $index_directory
183 PARTITION BY RANGE COLUMNS (b)
184 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'),
185 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'),
186 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'),
187 PARTITION p3 VALUES LESS THAN (MAXVALUE))
188 AS SELECT a, b, c FROM tp_r;
189
190 eval CREATE TABLE tp_rintdate(a INT,b VARCHAR(25),c DATE,
191 PRIMARY KEY (a,c))
192 charset latin1
193 ENGINE = $engine_table $data_directory $index_directory
194 PARTITION BY RANGE COLUMNS (a,c)
195 (PARTITION p0 VALUES LESS THAN (10,'2010-01-01'),
196 PARTITION p1 VALUES LESS THAN (100,'2200-01-01'),
197 PARTITION p2 VALUES LESS THAN (1000,'2300-01-01'),
198 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE))
199 AS SELECT a, b, c FROM t_10;
200
201 INSERT tp_rintdate SELECT * FROM t_100;
202 INSERT tp_rintdate SELECT * FROM t_1000;
203
204 eval CREATE TABLE tp_rdate(a INT,b VARCHAR(25),c DATE,
205 PRIMARY KEY (c))
206 charset latin1
207 ENGINE = $engine_table $data_directory $index_directory
208 PARTITION BY RANGE COLUMNS (c)
209 (PARTITION p0 VALUES LESS THAN ('2010-01-01'),
210 PARTITION p1 VALUES LESS THAN ('2200-01-01'),
211 PARTITION p2 VALUES LESS THAN ('2300-01-01'),
212 PARTITION p3 VALUES LESS THAN (MAXVALUE))
213 AS SELECT a, b, c FROM tp_r;
214
215 eval CREATE TABLE tsp_rvar(a INT,b VARCHAR(25),c DATE)
216 charset latin1
217 ENGINE = $engine_table $data_directory $index_directory
218 PARTITION BY RANGE COLUMNS (b)
219 SUBPARTITION BY HASH(a)
220 SUBPARTITIONS 5
221 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH')
222 (SUBPARTITION sp00,
223 SUBPARTITION sp01,
224 SUBPARTITION sp02,
225 SUBPARTITION sp03,
226 SUBPARTITION sp04),
227 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP')
228 (SUBPARTITION sp10,
229 SUBPARTITION sp11,
230 SUBPARTITION sp12,
231 SUBPARTITION sp13,
232 SUBPARTITION sp14),
233 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW')
234 (SUBPARTITION sp20,
235 SUBPARTITION sp21,
236 SUBPARTITION sp22,
237 SUBPARTITION sp23,
238 SUBPARTITION sp24))
239 AS SELECT a, b, c FROM tsp_r;
240
241 eval CREATE TABLE tsp_rvar1(a INT,b VARCHAR(25),c DATE)
242 charset latin1
243 ENGINE = $engine_table $data_directory $index_directory
244 PARTITION BY RANGE COLUMNS (b)
245 SUBPARTITION BY HASH(a)
246 SUBPARTITIONS 5
247 (PARTITION p0 VALUES LESS THAN ('HHHHHHHHHHHHHHHHHHHHHHHHH'),
248 PARTITION p1 VALUES LESS THAN ('PPPPPPPPPPPPPPPPPPPPPPPPP'),
249 PARTITION p2 VALUES LESS THAN ('WWWWWWWWWWWWWWWWWWWWWWWWW'))
250 AS SELECT a, b, c FROM tsp_r;
251
252 # Tables partition by list
253 eval CREATE TABLE tp_l (a INT,
254 b VARCHAR(25),
255 c DATE,
256 PRIMARY KEY (a)) $data_directory $index_directory
257 charset latin1
258 ENGINE = $engine_part
259 PARTITION BY LIST (a)
260 (PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9),
261 PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99),
262 PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119,
263 120,121,122,123,124,125,126,127,128,129,
264 130,131,132,133,134,135,136,137,138,139,
265 150,151,152,153,154,155,156,157,158,159,
266 160,161,162,163,164,165,166,167,168,169,
267 180,181,182,183,184,185,186,187,188,189,
268 190,191,192,193,194,195,196,197,198,199))
269 AS SELECT a, b, c FROM tp_r;
270
271 eval CREATE TABLE tsp_l (a INT,
272 b VARCHAR(25),
273 c DATE,
274 PRIMARY KEY (a)) $data_directory $index_directory
275 charset latin1
276 ENGINE = $engine_subpart
277 PARTITION BY LIST (a)
278 SUBPARTITION BY HASH(a)
279 (PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9)
280 (SUBPARTITION sp00,
281 SUBPARTITION sp01,
282 SUBPARTITION sp02,
283 SUBPARTITION sp03,
284 SUBPARTITION sp04),
285 PARTITION p1 VALUES IN (10,11,12,13,14,15,16,17,18,19,90,91,92,93,94,95,96,97,98,99)
286 (SUBPARTITION sp10,
287 SUBPARTITION sp11,
288 SUBPARTITION sp12,
289 SUBPARTITION sp13,
290 SUBPARTITION sp14),
291 PARTITION p2 VALUES IN (110,111,112,113,114,115,116,117,118,119,
292 120,121,122,123,124,125,126,127,128,129,
293 130,131,132,133,134,135,136,137,138,139,
294 150,151,152,153,154,155,156,157,158,159,
295 160,161,162,163,164,165,166,167,168,169,
296 180,181,182,183,184,185,186,187,188,189,
297 190,191,192,193,194,195,196,197,198,199)
298 (SUBPARTITION sp20,
299 SUBPARTITION sp21,
300 SUBPARTITION sp22,
301 SUBPARTITION sp23,
302 SUBPARTITION sp24))
303 AS SELECT a, b, c FROM tsp_r;
304
305 # Tables partition by key
306 eval CREATE TABLE tp_k (a INT,
307 b VARCHAR(25),
308 c DATE,
309 PRIMARY KEY (a)) $data_directory $index_directory
310 charset latin1
311 ENGINE = $engine_subpart
312 PARTITION BY KEY (a)
313 PARTITIONS 3
314 AS SELECT a, b, c FROM tp_r;
315
316 eval CREATE TABLE tp_kvar (a INT,
317 b VARCHAR(25),
318 c DATE,
319 PRIMARY KEY (b)) $data_directory $index_directory
320 charset latin1
321 ENGINE = $engine_subpart
322 PARTITION BY KEY (b)
323 PARTITIONS 3
324 AS SELECT a, b, c FROM tp_r;
325
326 eval CREATE TABLE tp_kdate (a INT,
327 b VARCHAR(25),
328 c DATE,
329 PRIMARY KEY (c)) $data_directory $index_directory
330 charset latin1
331 ENGINE = $engine_subpart
332 PARTITION BY KEY (c)
333 PARTITIONS 3
334 AS SELECT a, b, c FROM tp_r;
335
336 # Tabes partition by hash
337 eval CREATE TABLE tp_h (a INT,
338 b VARCHAR(25),
339 c DATE,
340 PRIMARY KEY (a)) $data_directory $index_directory
341 charset latin1
342 ENGINE = $engine_subpart
343 PARTITION BY HASH (a)
344 PARTITIONS 3
345 AS SELECT a, b, c FROM tp_r;
346