Debian Codegraph @

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