Debian Codegraph @

mysql-test/suite/parts/r/partition-dml-1-9-innodb.result

raw
1 # Additional tests for WL#5217 by QA, testplan 1.1
2 CREATE TABLE t1
3 (a INT NOT NULL,
4 b varchar (64),
5 INDEX ind_t1 (b,a),
6 PRIMARY KEY (a))
7 ENGINE = InnoDB
8 PARTITION BY RANGE (a)
9 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
10 (PARTITION pNeg VALUES LESS THAN (0)
11 (SUBPARTITION subp0 ,
12 SUBPARTITION subp1 ,
13 SUBPARTITION subp2 ),
14 PARTITION `p0-29` VALUES LESS THAN (30)
15 (SUBPARTITION subp3 ,
16 SUBPARTITION subp4 ,
17 SUBPARTITION subp5 ),
18 PARTITION `p30-299` VALUES LESS THAN (300)
19 (SUBPARTITION subp6 ,
20 SUBPARTITION subp7 ,
21 SUBPARTITION subp8 ),
22 PARTITION `p300-2999` VALUES LESS THAN (3000)
23 (SUBPARTITION subp9 ,
24 SUBPARTITION subp10 ,
25 SUBPARTITION subp11 ),
26 PARTITION `p3000-299999` VALUES LESS THAN (300000)
27 (SUBPARTITION subp12 ,
28 SUBPARTITION subp13 ,
29 SUBPARTITION subp14 ));
30 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
31 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
32 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
33 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
34 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
35 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
36 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
37 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
38 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
39 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
40 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
41 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
42 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
43 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
44 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
45 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
46 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
47 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
48 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
49 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
50 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
51 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
52 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
53 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
54 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
55 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
56 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
57 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
58 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
59 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
60 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
61 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
62 CREATE TABLE t2
63 (a INT NOT NULL,
64 b varchar (64),
65 INDEX ind_t2 (b,a),
66 PRIMARY KEY (a))
67 ENGINE = InnoDB
68 PARTITION BY RANGE (a)
69 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
70 (PARTITION pNeg VALUES LESS THAN (0)
71 (SUBPARTITION subp0 ,
72 SUBPARTITION subp1 ,
73 SUBPARTITION subp2 ),
74 PARTITION `p0-29` VALUES LESS THAN (30)
75 (SUBPARTITION subp3 ,
76 SUBPARTITION subp4 ,
77 SUBPARTITION subp5 ),
78 PARTITION `p30-299` VALUES LESS THAN (300)
79 (SUBPARTITION subp6 ,
80 SUBPARTITION subp7 ,
81 SUBPARTITION subp8 ),
82 PARTITION `p300-2999` VALUES LESS THAN (3000)
83 (SUBPARTITION subp9 ,
84 SUBPARTITION subp10 ,
85 SUBPARTITION subp11 ),
86 PARTITION `p3000-299999` VALUES LESS THAN (300000)
87 (SUBPARTITION subp12 ,
88 SUBPARTITION subp13 ,
89 SUBPARTITION subp14 ));
90 INSERT INTO t2 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
91 INSERT INTO t2 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
92 INSERT INTO t2 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
93 INSERT INTO t2 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
94 INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
95 INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
96 INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
97 INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
98 INSERT INTO t2 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
99 INSERT INTO t2 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
100 INSERT INTO t2 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
101 INSERT INTO t2 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
102 INSERT INTO t2 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
103 INSERT INTO t2 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
104 INSERT INTO t2 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
105 INSERT INTO t2 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
106 INSERT INTO t2 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
107 INSERT INTO t2 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
108 INSERT INTO t2 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
109 INSERT INTO t2 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
110 INSERT INTO t2 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
111 INSERT INTO t2 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
112 INSERT INTO t2 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
113 INSERT INTO t2 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
114 INSERT INTO t2 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
115 INSERT INTO t2 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
116 INSERT INTO t2 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
117 INSERT INTO t2 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
118 INSERT INTO t2 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
119 INSERT INTO t2 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
120 INSERT INTO t2 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
121 INSERT INTO t2 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
122 CREATE USER test_user_1 IDENTIFIED BY 'testpw';
123 GRANT ALL PRIVILEGES ON test.* TO test_user_1;
124 connect session1, localhost, test_user_1,'testpw',test;
125 CREATE VIEW v1 AS
126 SELECT t1.a, t2.b FROM t1 PARTITION (`p0-29`), t2
127 WHERE t1.a = t2.a;
128 SELECT * FROM v1;
129 a b
130 1 (p0-29-)subp3
131 2 (p0-29-)subp3
132 21 (p0-29-)subp5
133 22 (p0-29-)subp5
134 23 (p0-29-)subp5
135 24 (p0-29-)subp5
136 3 (p0-29-)subp3
137 4 (p0-29-)subp3
138 SELECT t1.a, t2.b FROM t1 PARTITION (`p0-29`), t2
139 WHERE t1.a = t2.a;
140 a b
141 1 (p0-29-)subp3
142 2 (p0-29-)subp3
143 21 (p0-29-)subp5
144 22 (p0-29-)subp5
145 23 (p0-29-)subp5
146 24 (p0-29-)subp5
147 3 (p0-29-)subp3
148 4 (p0-29-)subp3
149 SELECT * FROM t1 PARTITION (`p0-29`);
150 a b
151 1 (p0-29-)subp3
152 2 (p0-29-)subp3
153 21 (p0-29-)subp5
154 22 (p0-29-)subp5
155 23 (p0-29-)subp5
156 24 (p0-29-)subp5
157 3 (p0-29-)subp3
158 4 (p0-29-)subp3
159 SELECT * FROM t2 PARTITION (`p30-299`);
160 a b
161 231 (p30-299-)subp8
162 232 (p30-299-)subp8
163 233 (p30-299-)subp8
164 234 (p30-299-)subp8
165 31 (p30-299-)subp6
166 32 (p30-299-)subp6
167 33 (p30-299-)subp6
168 34 (p30-299-)subp6
169 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`),
170 t2 PARTITION (`p30-299`);
171 t1a t2a t1b t2b
172 1 231 (p0-29-)subp3 (p30-299-)subp8
173 1 232 (p0-29-)subp3 (p30-299-)subp8
174 1 233 (p0-29-)subp3 (p30-299-)subp8
175 1 234 (p0-29-)subp3 (p30-299-)subp8
176 1 31 (p0-29-)subp3 (p30-299-)subp6
177 1 32 (p0-29-)subp3 (p30-299-)subp6
178 1 33 (p0-29-)subp3 (p30-299-)subp6
179 1 34 (p0-29-)subp3 (p30-299-)subp6
180 2 231 (p0-29-)subp3 (p30-299-)subp8
181 2 232 (p0-29-)subp3 (p30-299-)subp8
182 2 233 (p0-29-)subp3 (p30-299-)subp8
183 2 234 (p0-29-)subp3 (p30-299-)subp8
184 2 31 (p0-29-)subp3 (p30-299-)subp6
185 2 32 (p0-29-)subp3 (p30-299-)subp6
186 2 33 (p0-29-)subp3 (p30-299-)subp6
187 2 34 (p0-29-)subp3 (p30-299-)subp6
188 21 231 (p0-29-)subp5 (p30-299-)subp8
189 21 232 (p0-29-)subp5 (p30-299-)subp8
190 21 233 (p0-29-)subp5 (p30-299-)subp8
191 21 234 (p0-29-)subp5 (p30-299-)subp8
192 21 31 (p0-29-)subp5 (p30-299-)subp6
193 21 32 (p0-29-)subp5 (p30-299-)subp6
194 21 33 (p0-29-)subp5 (p30-299-)subp6
195 21 34 (p0-29-)subp5 (p30-299-)subp6
196 22 231 (p0-29-)subp5 (p30-299-)subp8
197 22 232 (p0-29-)subp5 (p30-299-)subp8
198 22 233 (p0-29-)subp5 (p30-299-)subp8
199 22 234 (p0-29-)subp5 (p30-299-)subp8
200 22 31 (p0-29-)subp5 (p30-299-)subp6
201 22 32 (p0-29-)subp5 (p30-299-)subp6
202 22 33 (p0-29-)subp5 (p30-299-)subp6
203 22 34 (p0-29-)subp5 (p30-299-)subp6
204 23 231 (p0-29-)subp5 (p30-299-)subp8
205 23 232 (p0-29-)subp5 (p30-299-)subp8
206 23 233 (p0-29-)subp5 (p30-299-)subp8
207 23 234 (p0-29-)subp5 (p30-299-)subp8
208 23 31 (p0-29-)subp5 (p30-299-)subp6
209 23 32 (p0-29-)subp5 (p30-299-)subp6
210 23 33 (p0-29-)subp5 (p30-299-)subp6
211 23 34 (p0-29-)subp5 (p30-299-)subp6
212 24 231 (p0-29-)subp5 (p30-299-)subp8
213 24 232 (p0-29-)subp5 (p30-299-)subp8
214 24 233 (p0-29-)subp5 (p30-299-)subp8
215 24 234 (p0-29-)subp5 (p30-299-)subp8
216 24 31 (p0-29-)subp5 (p30-299-)subp6
217 24 32 (p0-29-)subp5 (p30-299-)subp6
218 24 33 (p0-29-)subp5 (p30-299-)subp6
219 24 34 (p0-29-)subp5 (p30-299-)subp6
220 3 231 (p0-29-)subp3 (p30-299-)subp8
221 3 232 (p0-29-)subp3 (p30-299-)subp8
222 3 233 (p0-29-)subp3 (p30-299-)subp8
223 3 234 (p0-29-)subp3 (p30-299-)subp8
224 3 31 (p0-29-)subp3 (p30-299-)subp6
225 3 32 (p0-29-)subp3 (p30-299-)subp6
226 3 33 (p0-29-)subp3 (p30-299-)subp6
227 3 34 (p0-29-)subp3 (p30-299-)subp6
228 4 231 (p0-29-)subp3 (p30-299-)subp8
229 4 232 (p0-29-)subp3 (p30-299-)subp8
230 4 233 (p0-29-)subp3 (p30-299-)subp8
231 4 234 (p0-29-)subp3 (p30-299-)subp8
232 4 31 (p0-29-)subp3 (p30-299-)subp6
233 4 32 (p0-29-)subp3 (p30-299-)subp6
234 4 33 (p0-29-)subp3 (p30-299-)subp6
235 4 34 (p0-29-)subp3 (p30-299-)subp6
236 SELECT * FROM t1 PARTITION (subp3);
237 a b
238 21 (p0-29-)subp5
239 24 (p0-29-)subp5
240 3 (p0-29-)subp3
241 SELECT * FROM t2 PARTITION (subp6);
242 a b
243 231 (p30-299-)subp8
244 234 (p30-299-)subp8
245 33 (p30-299-)subp6
246 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (subp3),
247 t2 PARTITION (subp6);
248 t1a t2a t1b t2b
249 21 231 (p0-29-)subp5 (p30-299-)subp8
250 21 234 (p0-29-)subp5 (p30-299-)subp8
251 21 33 (p0-29-)subp5 (p30-299-)subp6
252 24 231 (p0-29-)subp5 (p30-299-)subp8
253 24 234 (p0-29-)subp5 (p30-299-)subp8
254 24 33 (p0-29-)subp5 (p30-299-)subp6
255 3 231 (p0-29-)subp3 (p30-299-)subp8
256 3 234 (p0-29-)subp3 (p30-299-)subp8
257 3 33 (p0-29-)subp3 (p30-299-)subp6
258 SELECT * FROM t1 PARTITION (subp3,subp5);
259 a b
260 2 (p0-29-)subp3
261 21 (p0-29-)subp5
262 23 (p0-29-)subp5
263 24 (p0-29-)subp5
264 3 (p0-29-)subp3
265 SELECT * FROM t2 PARTITION (subp6,subp8);
266 a b
267 231 (p30-299-)subp8
268 233 (p30-299-)subp8
269 234 (p30-299-)subp8
270 32 (p30-299-)subp6
271 33 (p30-299-)subp6
272 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (subp3,subp5),
273 t2 PARTITION (subp6,subp8);
274 t1a t2a t1b t2b
275 2 231 (p0-29-)subp3 (p30-299-)subp8
276 2 233 (p0-29-)subp3 (p30-299-)subp8
277 2 234 (p0-29-)subp3 (p30-299-)subp8
278 2 32 (p0-29-)subp3 (p30-299-)subp6
279 2 33 (p0-29-)subp3 (p30-299-)subp6
280 21 231 (p0-29-)subp5 (p30-299-)subp8
281 21 233 (p0-29-)subp5 (p30-299-)subp8
282 21 234 (p0-29-)subp5 (p30-299-)subp8
283 21 32 (p0-29-)subp5 (p30-299-)subp6
284 21 33 (p0-29-)subp5 (p30-299-)subp6
285 23 231 (p0-29-)subp5 (p30-299-)subp8
286 23 233 (p0-29-)subp5 (p30-299-)subp8
287 23 234 (p0-29-)subp5 (p30-299-)subp8
288 23 32 (p0-29-)subp5 (p30-299-)subp6
289 23 33 (p0-29-)subp5 (p30-299-)subp6
290 24 231 (p0-29-)subp5 (p30-299-)subp8
291 24 233 (p0-29-)subp5 (p30-299-)subp8
292 24 234 (p0-29-)subp5 (p30-299-)subp8
293 24 32 (p0-29-)subp5 (p30-299-)subp6
294 24 33 (p0-29-)subp5 (p30-299-)subp6
295 3 231 (p0-29-)subp3 (p30-299-)subp8
296 3 233 (p0-29-)subp3 (p30-299-)subp8
297 3 234 (p0-29-)subp3 (p30-299-)subp8
298 3 32 (p0-29-)subp3 (p30-299-)subp6
299 3 33 (p0-29-)subp3 (p30-299-)subp6
300 DELETE FROM t2 PARTITION (`p30-299`);
301 SELECT * FROM t1 PARTITION (`p0-29`);
302 a b
303 1 (p0-29-)subp3
304 2 (p0-29-)subp3
305 21 (p0-29-)subp5
306 22 (p0-29-)subp5
307 23 (p0-29-)subp5
308 24 (p0-29-)subp5
309 3 (p0-29-)subp3
310 4 (p0-29-)subp3
311 SELECT * FROM t2 PARTITION (`p30-299`);
312 a b
313 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`),
314 t2 PARTITION (`p30-299`);
315 t1a t2a t1b t2b
316 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
317 t1 PARTITION (`p0-29`,`p30-299`)
318 LEFT JOIN
319 t2
320 USING (a,b);
321 t1a t2a t1b t2b
322 1 1 (p0-29-)subp3 (p0-29-)subp3
323 2 2 (p0-29-)subp3 (p0-29-)subp3
324 21 21 (p0-29-)subp5 (p0-29-)subp5
325 22 22 (p0-29-)subp5 (p0-29-)subp5
326 23 23 (p0-29-)subp5 (p0-29-)subp5
327 231 NULL (p30-299-)subp8 NULL
328 232 NULL (p30-299-)subp8 NULL
329 233 NULL (p30-299-)subp8 NULL
330 234 NULL (p30-299-)subp8 NULL
331 24 24 (p0-29-)subp5 (p0-29-)subp5
332 3 3 (p0-29-)subp3 (p0-29-)subp3
333 31 NULL (p30-299-)subp6 NULL
334 32 NULL (p30-299-)subp6 NULL
335 33 NULL (p30-299-)subp6 NULL
336 34 NULL (p30-299-)subp6 NULL
337 4 4 (p0-29-)subp3 (p0-29-)subp3
338 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
339 t1
340 LEFT JOIN
341 t2 PARTITION (`p0-29`,`p30-299`)
342 USING (a,b);
343 t1a t2a t1b t2b
344 -1 NULL (pNeg-)subp0 NULL
345 -2 NULL (pNeg-)subp0 NULL
346 -3 NULL (pNeg-)subp0 NULL
347 -4 NULL (pNeg-)subp0 NULL
348 1 1 (p0-29-)subp3 (p0-29-)subp3
349 2 2 (p0-29-)subp3 (p0-29-)subp3
350 21 21 (p0-29-)subp5 (p0-29-)subp5
351 22 22 (p0-29-)subp5 (p0-29-)subp5
352 23 23 (p0-29-)subp5 (p0-29-)subp5
353 231 NULL (p30-299-)subp8 NULL
354 232 NULL (p30-299-)subp8 NULL
355 233 NULL (p30-299-)subp8 NULL
356 234 NULL (p30-299-)subp8 NULL
357 24 24 (p0-29-)subp5 (p0-29-)subp5
358 299996 NULL (p3000-299999-)subp14 NULL
359 299997 NULL (p3000-299999-)subp14 NULL
360 299998 NULL (p3000-299999-)subp14 NULL
361 299999 NULL (p3000-299999-)subp14 NULL
362 3 3 (p0-29-)subp3 (p0-29-)subp3
363 3001 NULL (p3000-299999-)subp12 NULL
364 3002 NULL (p3000-299999-)subp12 NULL
365 3003 NULL (p3000-299999-)subp12 NULL
366 3004 NULL (p3000-299999-)subp12 NULL
367 301 NULL (p300-2999-)subp8 NULL
368 302 NULL (p300-2999-)subp8 NULL
369 303 NULL (p300-2999-)subp8 NULL
370 304 NULL (p300-2999-)subp8 NULL
371 31 NULL (p30-299-)subp6 NULL
372 32 NULL (p30-299-)subp6 NULL
373 33 NULL (p30-299-)subp6 NULL
374 34 NULL (p30-299-)subp6 NULL
375 4 4 (p0-29-)subp3 (p0-29-)subp3
376 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
377 t1 PARTITION (`p0-29`,`p30-299`)
378 RIGHT JOIN
379 t2
380 USING (a,b);
381 t1a t2a t1b t2b
382 1 1 (p0-29-)subp3 (p0-29-)subp3
383 2 2 (p0-29-)subp3 (p0-29-)subp3
384 21 21 (p0-29-)subp5 (p0-29-)subp5
385 22 22 (p0-29-)subp5 (p0-29-)subp5
386 23 23 (p0-29-)subp5 (p0-29-)subp5
387 24 24 (p0-29-)subp5 (p0-29-)subp5
388 3 3 (p0-29-)subp3 (p0-29-)subp3
389 4 4 (p0-29-)subp3 (p0-29-)subp3
390 NULL -1 NULL (pNeg-)subp0
391 NULL -2 NULL (pNeg-)subp0
392 NULL -3 NULL (pNeg-)subp0
393 NULL -4 NULL (pNeg-)subp0
394 NULL 299996 NULL (p3000-299999-)subp14
395 NULL 299997 NULL (p3000-299999-)subp14
396 NULL 299998 NULL (p3000-299999-)subp14
397 NULL 299999 NULL (p3000-299999-)subp14
398 NULL 3001 NULL (p3000-299999-)subp12
399 NULL 3002 NULL (p3000-299999-)subp12
400 NULL 3003 NULL (p3000-299999-)subp12
401 NULL 3004 NULL (p3000-299999-)subp12
402 NULL 301 NULL (p300-2999-)subp8
403 NULL 302 NULL (p300-2999-)subp8
404 NULL 303 NULL (p300-2999-)subp8
405 NULL 304 NULL (p300-2999-)subp8
406 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
407 t1
408 RIGHT JOIN
409 t2 PARTITION (`p0-29`,`p30-299`)
410 USING (a,b);
411 t1a t2a t1b t2b
412 1 1 (p0-29-)subp3 (p0-29-)subp3
413 2 2 (p0-29-)subp3 (p0-29-)subp3
414 21 21 (p0-29-)subp5 (p0-29-)subp5
415 22 22 (p0-29-)subp5 (p0-29-)subp5
416 23 23 (p0-29-)subp5 (p0-29-)subp5
417 24 24 (p0-29-)subp5 (p0-29-)subp5
418 3 3 (p0-29-)subp3 (p0-29-)subp3
419 4 4 (p0-29-)subp3 (p0-29-)subp3
420 DELETE FROM t1 PARTITION (`p0-29`);
421 SELECT * FROM t1 PARTITION (`p0-29`);
422 a b
423 SELECT * FROM t2 PARTITION (`p30-299`);
424 a b
425 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`),
426 t2 PARTITION (`p30-299`);
427 t1a t2a t1b t2b
428 INSERT INTO t1 VALUES (1,'t1-p0-29-ins');
429 SELECT * FROM t1 PARTITION (`p0-29`);
430 a b
431 1 t1-p0-29-ins
432 SELECT * FROM t2 PARTITION (`p30-299`);
433 a b
434 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`),
435 t2 PARTITION (`p30-299`);
436 t1a t2a t1b t2b
437 INSERT INTO t2 VALUES (32,'t2-p30-299-ins');
438 SELECT * FROM t1 PARTITION (`p0-29`);
439 a b
440 1 t1-p0-29-ins
441 SELECT * FROM t2 PARTITION (`p30-299`);
442 a b
443 32 t2-p30-299-ins
444 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`),
445 t2 PARTITION (`p30-299`);
446 t1a t2a t1b t2b
447 1 32 t1-p0-29-ins t2-p30-299-ins
448 DELETE FROM t1 PARTITION (`p30-299`);
449 INSERT INTO t1 VALUES (31,'t1-p30-299-ins');
450 DELETE FROM t2 PARTITION (`p0-29`);
451 INSERT INTO t2 VALUES (2,'t2-p0-29-ins');
452 SELECT * FROM t1 PARTITION (`p0-29`,`p30-299`);
453 a b
454 1 t1-p0-29-ins
455 31 t1-p30-299-ins
456 SELECT * FROM t2 PARTITION (`p0-29`,`p30-299`);
457 a b
458 2 t2-p0-29-ins
459 32 t2-p30-299-ins
460 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`),
461 t2 PARTITION (`p0-29`,`p30-299`);
462 t1a t2a t1b t2b
463 1 2 t1-p0-29-ins t2-p0-29-ins
464 1 32 t1-p0-29-ins t2-p30-299-ins
465 31 2 t1-p30-299-ins t2-p0-29-ins
466 31 32 t1-p30-299-ins t2-p30-299-ins
467 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
468 t1 PARTITION (`p0-29`,`p30-299`)
469 JOIN
470 t2 PARTITION (`p0-29`,`p30-299`);
471 t1a t2a t1b t2b
472 1 2 t1-p0-29-ins t2-p0-29-ins
473 1 32 t1-p0-29-ins t2-p30-299-ins
474 31 2 t1-p30-299-ins t2-p0-29-ins
475 31 32 t1-p30-299-ins t2-p30-299-ins
476 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
477 t1 PARTITION (`p0-29`,`p30-299`)
478 INNER JOIN
479 t2 PARTITION (`p0-29`,`p30-299`);
480 t1a t2a t1b t2b
481 1 2 t1-p0-29-ins t2-p0-29-ins
482 1 32 t1-p0-29-ins t2-p30-299-ins
483 31 2 t1-p30-299-ins t2-p0-29-ins
484 31 32 t1-p30-299-ins t2-p30-299-ins
485 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
486 t1 PARTITION (`p0-29`,`p30-299`)
487 INNER JOIN
488 t2 PARTITION (`p0-29`,`p30-299`)
489 USING (a,b);
490 t1a t2a t1b t2b
491 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
492 t1 PARTITION (`p0-29`,`p30-299`)
493 NATURAL JOIN
494 t2 PARTITION (`p0-29`,`p30-299`);
495 t1a t2a t1b t2b
496 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
497 t1 PARTITION (`p0-29`,`p30-299`)
498 LEFT JOIN
499 t2 PARTITION (`p0-29`,`p30-299`)
500 USING (a,b);
501 t1a t2a t1b t2b
502 1 NULL t1-p0-29-ins NULL
503 31 NULL t1-p30-299-ins NULL
504 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
505 t1 PARTITION (`p0-29`,`p30-299`)
506 RIGHT JOIN
507 t2 PARTITION (`p0-29`,`p30-299`)
508 USING (a,b);
509 t1a t2a t1b t2b
510 NULL 2 NULL t2-p0-29-ins
511 NULL 32 NULL t2-p30-299-ins
512 INSERT INTO t1 VALUES (32,'t2-p30-299-ins');
513 INSERT INTO t2 VALUES (1,'t1-p0-29-ins');
514 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
515 t1 PARTITION (`p0-29`,`p30-299`)
516 INNER JOIN
517 t2 PARTITION (`p0-29`,`p30-299`)
518 USING (a,b);
519 t1a t2a t1b t2b
520 1 1 t1-p0-29-ins t1-p0-29-ins
521 32 32 t2-p30-299-ins t2-p30-299-ins
522 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM
523 t1 PARTITION (`p0-29`,`p30-299`)
524 NATURAL JOIN
525 t2 PARTITION (`p0-29`,`p30-299`);
526 t1a t2a t1b t2b
527 1 1 t1-p0-29-ins t1-p0-29-ins
528 32 32 t2-p30-299-ins t2-p30-299-ins
529 disconnect session1;
530 connection default;
531 DROP VIEW v1;
532 DROP TABLE t1, t2;
533 DROP USER test_user_1;