Debian Codegraph @

mysql-test/suite/parts/r/partition_sub2.result

raw
1 DROP TABLE IF EXISTS t1;
2 CREATE TABLE t1 (c1 TINYINT,name VARCHAR(30), purchased DATE)
3 PARTITION BY RANGE( YEAR(purchased) )
4 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
5 PARTITION p0 VALUES LESS THAN (1990) (
6 SUBPARTITION s0,
7 SUBPARTITION s1
8 ),
9 PARTITION p1 VALUES LESS THAN (2000) (
10 SUBPARTITION s2,
11 SUBPARTITION s3
12 ),
13 PARTITION p2 VALUES LESS THAN MAXVALUE (
14 SUBPARTITION s4,
15 SUBPARTITION s5
16 )
17 );
18 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
19 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
20 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
21 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
22 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
23 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
24 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
25 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
26 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
27 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
28 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
29 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
30 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
31 SELECT * FROM t1 ORDER BY c1;
32 c1 name purchased
33 1 abc 1994-01-01
34 2 abc 1995-01-01
35 3 abc 1996-01-01
36 4 abc 1997-01-01
37 5 abc 1998-01-01
38 6 abc 1999-01-01
39 7 abc 2000-01-01
40 8 abc 2001-01-01
41 9 abc 2002-01-01
42 10 abc 2003-01-01
43 11 abc 2004-01-01
44 12 abc 2005-01-01
45 13 abc 2006-01-01
46 SHOW TABLES;
47 Tables_in_test
48 t1
49 SHOW CREATE TABLE t1;
50 Table Create Table
51 t1 CREATE TABLE `t1` (
52 `c1` tinyint DEFAULT NULL,
53 `name` varchar(30) DEFAULT NULL,
54 `purchased` date DEFAULT NULL
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
56 /*!50100 PARTITION BY RANGE (year(`purchased`))
57 SUBPARTITION BY HASH (to_days(`purchased`))
58 (PARTITION p0 VALUES LESS THAN (1990)
59 (SUBPARTITION s0 ENGINE = InnoDB,
60 SUBPARTITION s1 ENGINE = InnoDB),
61 PARTITION p1 VALUES LESS THAN (2000)
62 (SUBPARTITION s2 ENGINE = InnoDB,
63 SUBPARTITION s3 ENGINE = InnoDB),
64 PARTITION p2 VALUES LESS THAN MAXVALUE
65 (SUBPARTITION s4 ENGINE = InnoDB,
66 SUBPARTITION s5 ENGINE = InnoDB)) */
67 DROP TABLE t1;
68 SHOW TABLES;
69 Tables_in_test
70 CREATE TABLE t1 (c1 SMALLINT,name VARCHAR(30), purchased DATE)
71 PARTITION BY RANGE( YEAR(purchased) )
72 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
73 PARTITION p0 VALUES LESS THAN (1990) (
74 SUBPARTITION s0,
75 SUBPARTITION s1
76 ),
77 PARTITION p1 VALUES LESS THAN (2000) (
78 SUBPARTITION s2,
79 SUBPARTITION s3
80 ),
81 PARTITION p2 VALUES LESS THAN MAXVALUE (
82 SUBPARTITION s4,
83 SUBPARTITION s5
84 )
85 );
86 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
87 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
88 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
89 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
90 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
91 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
92 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
93 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
94 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
95 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
96 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
97 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
98 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
99 SELECT * FROM t1 ORDER BY c1;
100 c1 name purchased
101 1 abc 1994-01-01
102 2 abc 1995-01-01
103 3 abc 1996-01-01
104 4 abc 1997-01-01
105 5 abc 1998-01-01
106 6 abc 1999-01-01
107 7 abc 2000-01-01
108 8 abc 2001-01-01
109 9 abc 2002-01-01
110 10 abc 2003-01-01
111 11 abc 2004-01-01
112 12 abc 2005-01-01
113 13 abc 2006-01-01
114 SHOW TABLES;
115 Tables_in_test
116 t1
117 SHOW CREATE TABLE t1;
118 Table Create Table
119 t1 CREATE TABLE `t1` (
120 `c1` smallint DEFAULT NULL,
121 `name` varchar(30) DEFAULT NULL,
122 `purchased` date DEFAULT NULL
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
124 /*!50100 PARTITION BY RANGE (year(`purchased`))
125 SUBPARTITION BY HASH (to_days(`purchased`))
126 (PARTITION p0 VALUES LESS THAN (1990)
127 (SUBPARTITION s0 ENGINE = InnoDB,
128 SUBPARTITION s1 ENGINE = InnoDB),
129 PARTITION p1 VALUES LESS THAN (2000)
130 (SUBPARTITION s2 ENGINE = InnoDB,
131 SUBPARTITION s3 ENGINE = InnoDB),
132 PARTITION p2 VALUES LESS THAN MAXVALUE
133 (SUBPARTITION s4 ENGINE = InnoDB,
134 SUBPARTITION s5 ENGINE = InnoDB)) */
135 DROP TABLE t1;
136 SHOW TABLES;
137 Tables_in_test
138 CREATE TABLE t1 (c1 MEDIUMINT,name VARCHAR(30), purchased DATE)
139 PARTITION BY RANGE( YEAR(purchased) )
140 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
141 PARTITION p0 VALUES LESS THAN (1990) (
142 SUBPARTITION s0,
143 SUBPARTITION s1
144 ),
145 PARTITION p1 VALUES LESS THAN (2000) (
146 SUBPARTITION s2,
147 SUBPARTITION s3
148 ),
149 PARTITION p2 VALUES LESS THAN MAXVALUE (
150 SUBPARTITION s4,
151 SUBPARTITION s5
152 )
153 );
154 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
155 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
156 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
157 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
158 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
159 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
160 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
161 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
162 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
163 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
164 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
165 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
166 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
167 SELECT * FROM t1 ORDER BY c1;
168 c1 name purchased
169 1 abc 1994-01-01
170 2 abc 1995-01-01
171 3 abc 1996-01-01
172 4 abc 1997-01-01
173 5 abc 1998-01-01
174 6 abc 1999-01-01
175 7 abc 2000-01-01
176 8 abc 2001-01-01
177 9 abc 2002-01-01
178 10 abc 2003-01-01
179 11 abc 2004-01-01
180 12 abc 2005-01-01
181 13 abc 2006-01-01
182 SHOW TABLES;
183 Tables_in_test
184 t1
185 SHOW CREATE TABLE t1;
186 Table Create Table
187 t1 CREATE TABLE `t1` (
188 `c1` mediumint DEFAULT NULL,
189 `name` varchar(30) DEFAULT NULL,
190 `purchased` date DEFAULT NULL
191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
192 /*!50100 PARTITION BY RANGE (year(`purchased`))
193 SUBPARTITION BY HASH (to_days(`purchased`))
194 (PARTITION p0 VALUES LESS THAN (1990)
195 (SUBPARTITION s0 ENGINE = InnoDB,
196 SUBPARTITION s1 ENGINE = InnoDB),
197 PARTITION p1 VALUES LESS THAN (2000)
198 (SUBPARTITION s2 ENGINE = InnoDB,
199 SUBPARTITION s3 ENGINE = InnoDB),
200 PARTITION p2 VALUES LESS THAN MAXVALUE
201 (SUBPARTITION s4 ENGINE = InnoDB,
202 SUBPARTITION s5 ENGINE = InnoDB)) */
203 DROP TABLE t1;
204 SHOW TABLES;
205 Tables_in_test
206 CREATE TABLE t1 (c1 INT,name VARCHAR(30), purchased DATE)
207 PARTITION BY RANGE( YEAR(purchased) )
208 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
209 PARTITION p0 VALUES LESS THAN (1990) (
210 SUBPARTITION s0,
211 SUBPARTITION s1
212 ),
213 PARTITION p1 VALUES LESS THAN (2000) (
214 SUBPARTITION s2,
215 SUBPARTITION s3
216 ),
217 PARTITION p2 VALUES LESS THAN MAXVALUE (
218 SUBPARTITION s4,
219 SUBPARTITION s5
220 )
221 );
222 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
223 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
224 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
225 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
226 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
227 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
228 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
229 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
230 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
231 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
232 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
233 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
234 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
235 SELECT * FROM t1 ORDER BY c1;
236 c1 name purchased
237 1 abc 1994-01-01
238 2 abc 1995-01-01
239 3 abc 1996-01-01
240 4 abc 1997-01-01
241 5 abc 1998-01-01
242 6 abc 1999-01-01
243 7 abc 2000-01-01
244 8 abc 2001-01-01
245 9 abc 2002-01-01
246 10 abc 2003-01-01
247 11 abc 2004-01-01
248 12 abc 2005-01-01
249 13 abc 2006-01-01
250 SHOW TABLES;
251 Tables_in_test
252 t1
253 SHOW CREATE TABLE t1;
254 Table Create Table
255 t1 CREATE TABLE `t1` (
256 `c1` int DEFAULT NULL,
257 `name` varchar(30) DEFAULT NULL,
258 `purchased` date DEFAULT NULL
259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
260 /*!50100 PARTITION BY RANGE (year(`purchased`))
261 SUBPARTITION BY HASH (to_days(`purchased`))
262 (PARTITION p0 VALUES LESS THAN (1990)
263 (SUBPARTITION s0 ENGINE = InnoDB,
264 SUBPARTITION s1 ENGINE = InnoDB),
265 PARTITION p1 VALUES LESS THAN (2000)
266 (SUBPARTITION s2 ENGINE = InnoDB,
267 SUBPARTITION s3 ENGINE = InnoDB),
268 PARTITION p2 VALUES LESS THAN MAXVALUE
269 (SUBPARTITION s4 ENGINE = InnoDB,
270 SUBPARTITION s5 ENGINE = InnoDB)) */
271 DROP TABLE t1;
272 SHOW TABLES;
273 Tables_in_test
274 CREATE TABLE t1 (c1 INTEGER,name VARCHAR(30), purchased DATE)
275 PARTITION BY RANGE( YEAR(purchased) )
276 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
277 PARTITION p0 VALUES LESS THAN (1990) (
278 SUBPARTITION s0,
279 SUBPARTITION s1
280 ),
281 PARTITION p1 VALUES LESS THAN (2000) (
282 SUBPARTITION s2,
283 SUBPARTITION s3
284 ),
285 PARTITION p2 VALUES LESS THAN MAXVALUE (
286 SUBPARTITION s4,
287 SUBPARTITION s5
288 )
289 );
290 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
291 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
292 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
293 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
294 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
295 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
296 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
297 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
298 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
299 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
300 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
301 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
302 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
303 SELECT * FROM t1 ORDER BY c1;
304 c1 name purchased
305 1 abc 1994-01-01
306 2 abc 1995-01-01
307 3 abc 1996-01-01
308 4 abc 1997-01-01
309 5 abc 1998-01-01
310 6 abc 1999-01-01
311 7 abc 2000-01-01
312 8 abc 2001-01-01
313 9 abc 2002-01-01
314 10 abc 2003-01-01
315 11 abc 2004-01-01
316 12 abc 2005-01-01
317 13 abc 2006-01-01
318 SHOW TABLES;
319 Tables_in_test
320 t1
321 SHOW CREATE TABLE t1;
322 Table Create Table
323 t1 CREATE TABLE `t1` (
324 `c1` int DEFAULT NULL,
325 `name` varchar(30) DEFAULT NULL,
326 `purchased` date DEFAULT NULL
327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
328 /*!50100 PARTITION BY RANGE (year(`purchased`))
329 SUBPARTITION BY HASH (to_days(`purchased`))
330 (PARTITION p0 VALUES LESS THAN (1990)
331 (SUBPARTITION s0 ENGINE = InnoDB,
332 SUBPARTITION s1 ENGINE = InnoDB),
333 PARTITION p1 VALUES LESS THAN (2000)
334 (SUBPARTITION s2 ENGINE = InnoDB,
335 SUBPARTITION s3 ENGINE = InnoDB),
336 PARTITION p2 VALUES LESS THAN MAXVALUE
337 (SUBPARTITION s4 ENGINE = InnoDB,
338 SUBPARTITION s5 ENGINE = InnoDB)) */
339 DROP TABLE t1;
340 SHOW TABLES;
341 Tables_in_test
342 CREATE TABLE t1 (c1 BIGINT,name VARCHAR(30), purchased DATE)
343 PARTITION BY RANGE( YEAR(purchased) )
344 SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
345 PARTITION p0 VALUES LESS THAN (1990) (
346 SUBPARTITION s0,
347 SUBPARTITION s1
348 ),
349 PARTITION p1 VALUES LESS THAN (2000) (
350 SUBPARTITION s2,
351 SUBPARTITION s3
352 ),
353 PARTITION p2 VALUES LESS THAN MAXVALUE (
354 SUBPARTITION s4,
355 SUBPARTITION s5
356 )
357 );
358 INSERT INTO t1 VALUES(1,'abc','1994-01-01');
359 INSERT INTO t1 VALUES(2,'abc','1995-01-01');
360 INSERT INTO t1 VALUES(3,'abc','1996-01-01');
361 INSERT INTO t1 VALUES(4,'abc','1997-01-01');
362 INSERT INTO t1 VALUES(5,'abc','1998-01-01');
363 INSERT INTO t1 VALUES(6,'abc','1999-01-01');
364 INSERT INTO t1 VALUES(7,'abc','2000-01-01');
365 INSERT INTO t1 VALUES(8,'abc','2001-01-01');
366 INSERT INTO t1 VALUES(9,'abc','2002-01-01');
367 INSERT INTO t1 VALUES(10,'abc','2003-01-01');
368 INSERT INTO t1 VALUES(11,'abc','2004-01-01');
369 INSERT INTO t1 VALUES(12,'abc','2005-01-01');
370 INSERT INTO t1 VALUES(13,'abc','2006-01-01');
371 SELECT * FROM t1 ORDER BY c1;
372 c1 name purchased
373 1 abc 1994-01-01
374 2 abc 1995-01-01
375 3 abc 1996-01-01
376 4 abc 1997-01-01
377 5 abc 1998-01-01
378 6 abc 1999-01-01
379 7 abc 2000-01-01
380 8 abc 2001-01-01
381 9 abc 2002-01-01
382 10 abc 2003-01-01
383 11 abc 2004-01-01
384 12 abc 2005-01-01
385 13 abc 2006-01-01
386 SHOW TABLES;
387 Tables_in_test
388 t1
389 SHOW CREATE TABLE t1;
390 Table Create Table
391 t1 CREATE TABLE `t1` (
392 `c1` bigint DEFAULT NULL,
393 `name` varchar(30) DEFAULT NULL,
394 `purchased` date DEFAULT NULL
395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
396 /*!50100 PARTITION BY RANGE (year(`purchased`))
397 SUBPARTITION BY HASH (to_days(`purchased`))
398 (PARTITION p0 VALUES LESS THAN (1990)
399 (SUBPARTITION s0 ENGINE = InnoDB,
400 SUBPARTITION s1 ENGINE = InnoDB),
401 PARTITION p1 VALUES LESS THAN (2000)
402 (SUBPARTITION s2 ENGINE = InnoDB,
403 SUBPARTITION s3 ENGINE = InnoDB),
404 PARTITION p2 VALUES LESS THAN MAXVALUE
405 (SUBPARTITION s4 ENGINE = InnoDB,
406 SUBPARTITION s5 ENGINE = InnoDB)) */
407 DROP TABLE t1;
408 SHOW TABLES;
409 Tables_in_test