Debian Codegraph @

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

raw
1 SET @max_row = 20;
2 SET @@session.default_storage_engine = 'InnoDB';
3
4 #------------------------------------------------------------------------
5 # 0. Setting of auxiliary variables + Creation of an auxiliary tables
6 # needed in many testcases
7 #------------------------------------------------------------------------
8 SELECT @max_row DIV 2 INTO @max_row_div2;
9 SELECT @max_row DIV 3 INTO @max_row_div3;
10 SELECT @max_row DIV 4 INTO @max_row_div4;
11 SET @max_int_4 = 2147483647;
12 DROP TABLE IF EXISTS t0_template;
13 CREATE TABLE t0_template (
14 f_int1 INTEGER,
15 f_int2 INTEGER,
16 f_char1 CHAR(20),
17 f_char2 CHAR(20),
18 f_charbig VARCHAR(1000) ,
19 PRIMARY KEY(f_int1))
20 ENGINE = MEMORY;
21 # Logging of <max_row> INSERTs into t0_template suppressed
22 DROP TABLE IF EXISTS t0_definition;
23 CREATE TABLE t0_definition (
24 state CHAR(3),
25 create_command VARBINARY(5000),
26 file_list VARBINARY(10000),
27 PRIMARY KEY (state)
28 ) ENGINE = MEMORY;
29 DROP TABLE IF EXISTS t0_aux;
30 CREATE TABLE t0_aux ( f_int1 INTEGER,
31 f_int2 INTEGER,
32 f_char1 CHAR(20),
33 f_char2 CHAR(20),
34 f_charbig VARCHAR(1000) )
35 ENGINE = MEMORY;
36 SET AUTOCOMMIT= 1;
37 SET @@session.sql_mode= '';
38 # End of basic preparations needed for all tests
39 #-----------------------------------------------
40
41 #========================================================================
42 # 1. Any PRIMARY KEYs or UNIQUE INDEXes must contain the columns used
43 # within the partitioning functions
44 #========================================================================
45 DROP TABLE IF EXISTS t1;
46 #------------------------------------------------------------------------
47 # 1.1 column of partitioning function not included in PRIMARY KEY
48 # PARTITION BY HASH/KEY/LIST/RANGE
49 #------------------------------------------------------------------------
50 CREATE TABLE t1 (
51 f_int1 INTEGER,
52 f_int2 INTEGER,
53 f_char1 CHAR(20),
54 f_char2 CHAR(20),
55 f_charbig VARCHAR(1000),
56 PRIMARY KEY (f_int2)
57 )
58 PARTITION BY HASH(f_int1) PARTITIONS 2;
59 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
60 CREATE TABLE t1 (
61 f_int1 INTEGER,
62 f_int2 INTEGER,
63 f_char1 CHAR(20),
64 f_char2 CHAR(20),
65 f_charbig VARCHAR(1000),
66 PRIMARY KEY (f_int2)
67 )
68 PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
69 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
70 CREATE TABLE t1 (
71 f_int1 INTEGER,
72 f_int2 INTEGER,
73 f_char1 CHAR(20),
74 f_char2 CHAR(20),
75 f_charbig VARCHAR(1000),
76 PRIMARY KEY (f_int2)
77 )
78 PARTITION BY KEY(f_int1) PARTITIONS 2;
79 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
80 CREATE TABLE t1 (
81 f_int1 INTEGER,
82 f_int2 INTEGER,
83 f_char1 CHAR(20),
84 f_char2 CHAR(20),
85 f_charbig VARCHAR(1000),
86 PRIMARY KEY (f_int2)
87 )
88 PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2;
89 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
90 CREATE TABLE t1 (
91 f_int1 INTEGER,
92 f_int2 INTEGER,
93 f_char1 CHAR(20),
94 f_char2 CHAR(20),
95 f_charbig VARCHAR(1000),
96 PRIMARY KEY (f_int2)
97 )
98 PARTITION BY LIST(f_int1)
99 (PARTITION part1 VALUES IN (1));
100 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
101 CREATE TABLE t1 (
102 f_int1 INTEGER,
103 f_int2 INTEGER,
104 f_char1 CHAR(20),
105 f_char2 CHAR(20),
106 f_charbig VARCHAR(1000),
107 PRIMARY KEY (f_int2)
108 )
109 PARTITION BY LIST(f_int1 + f_int2)
110 (PARTITION part1 VALUES IN (1));
111 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
112 CREATE TABLE t1 (
113 f_int1 INTEGER,
114 f_int2 INTEGER,
115 f_char1 CHAR(20),
116 f_char2 CHAR(20),
117 f_charbig VARCHAR(1000),
118 PRIMARY KEY (f_int2)
119 )
120 PARTITION BY RANGE(f_int1)
121 (PARTITION part1 VALUES LESS THAN (1));
122 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
123 CREATE TABLE t1 (
124 f_int1 INTEGER,
125 f_int2 INTEGER,
126 f_char1 CHAR(20),
127 f_char2 CHAR(20),
128 f_charbig VARCHAR(1000),
129 PRIMARY KEY (f_int2)
130 )
131 PARTITION BY RANGE(f_int1 + f_int2)
132 (PARTITION part1 VALUES LESS THAN (1));
133 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
134 #------------------------------------------------------------------------
135 # 1.2 column of partitioning function not included in UNIQUE INDEX
136 # PARTITION BY HASH/KEY/LIST/RANGE
137 # Variant a) Without additional PRIMARY KEY
138 # Variant b) With correct additional PRIMARY KEY
139 # Variant 1) one column in partitioning function
140 # Variant 2) two columns in partitioning function
141 #------------------------------------------------------------------------
142 CREATE TABLE t1 (
143 f_int1 INTEGER,
144 f_int2 INTEGER,
145 f_char1 CHAR(20),
146 f_char2 CHAR(20),
147 f_charbig VARCHAR(1000),
148 UNIQUE INDEX (f_int2)
149 )
150 PARTITION BY HASH(f_int1) PARTITIONS 2;
151 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
152 CREATE TABLE t1 (
153 f_int1 INTEGER,
154 f_int2 INTEGER,
155 f_char1 CHAR(20),
156 f_char2 CHAR(20),
157 f_charbig VARCHAR(1000),
158 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
159 )
160 PARTITION BY HASH(f_int1) PARTITIONS 2;
161 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
162 CREATE TABLE t1 (
163 f_int1 INTEGER,
164 f_int2 INTEGER,
165 f_char1 CHAR(20),
166 f_char2 CHAR(20),
167 f_charbig VARCHAR(1000),
168 UNIQUE INDEX (f_int2)
169 )
170 PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
171 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
172 CREATE TABLE t1 (
173 f_int1 INTEGER,
174 f_int2 INTEGER,
175 f_char1 CHAR(20),
176 f_char2 CHAR(20),
177 f_charbig VARCHAR(1000),
178 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
179 )
180 PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
181 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
182 CREATE TABLE t1 (
183 f_int1 INTEGER,
184 f_int2 INTEGER,
185 f_char1 CHAR(20),
186 f_char2 CHAR(20),
187 f_charbig VARCHAR(1000),
188 UNIQUE INDEX (f_int2)
189 )
190 PARTITION BY KEY(f_int1) PARTITIONS 2;
191 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
192 CREATE TABLE t1 (
193 f_int1 INTEGER,
194 f_int2 INTEGER,
195 f_char1 CHAR(20),
196 f_char2 CHAR(20),
197 f_charbig VARCHAR(1000),
198 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
199 )
200 PARTITION BY KEY(f_int1) PARTITIONS 2;
201 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
202 CREATE TABLE t1 (
203 f_int1 INTEGER,
204 f_int2 INTEGER,
205 f_char1 CHAR(20),
206 f_char2 CHAR(20),
207 f_charbig VARCHAR(1000),
208 UNIQUE INDEX (f_int2)
209 )
210 PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2;
211 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
212 CREATE TABLE t1 (
213 f_int1 INTEGER,
214 f_int2 INTEGER,
215 f_char1 CHAR(20),
216 f_char2 CHAR(20),
217 f_charbig VARCHAR(1000),
218 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
219 )
220 PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2;
221 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
222 CREATE TABLE t1 (
223 f_int1 INTEGER,
224 f_int2 INTEGER,
225 f_char1 CHAR(20),
226 f_char2 CHAR(20),
227 f_charbig VARCHAR(1000),
228 UNIQUE INDEX (f_int2)
229 )
230 PARTITION BY LIST(MOD(f_int1,3))
231 (PARTITION partN VALUES IN (NULL),
232 PARTITION part0 VALUES IN (0),
233 PARTITION part1 VALUES IN (1),
234 PARTITION part2 VALUES IN (2));
235 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
236 CREATE TABLE t1 (
237 f_int1 INTEGER,
238 f_int2 INTEGER,
239 f_char1 CHAR(20),
240 f_char2 CHAR(20),
241 f_charbig VARCHAR(1000),
242 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
243 )
244 PARTITION BY LIST(MOD(f_int1,3))
245 (PARTITION partN VALUES IN (NULL),
246 PARTITION part0 VALUES IN (0),
247 PARTITION part1 VALUES IN (1),
248 PARTITION part2 VALUES IN (2));
249 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
250 CREATE TABLE t1 (
251 f_int1 INTEGER,
252 f_int2 INTEGER,
253 f_char1 CHAR(20),
254 f_char2 CHAR(20),
255 f_charbig VARCHAR(1000),
256 UNIQUE INDEX (f_int2)
257 )
258 PARTITION BY LIST(MOD(f_int1 + f_int2,3))
259 (PARTITION partN VALUES IN (NULL),
260 PARTITION part0 VALUES IN (0),
261 PARTITION part1 VALUES IN (1),
262 PARTITION part2 VALUES IN (2));
263 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
264 CREATE TABLE t1 (
265 f_int1 INTEGER,
266 f_int2 INTEGER,
267 f_char1 CHAR(20),
268 f_char2 CHAR(20),
269 f_charbig VARCHAR(1000),
270 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
271 )
272 PARTITION BY LIST(MOD(f_int1 + f_int2,3))
273 (PARTITION partN VALUES IN (NULL),
274 PARTITION part0 VALUES IN (0),
275 PARTITION part1 VALUES IN (1),
276 PARTITION part2 VALUES IN (2));
277 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
278 CREATE TABLE t1 (
279 f_int1 INTEGER,
280 f_int2 INTEGER,
281 f_char1 CHAR(20),
282 f_char2 CHAR(20),
283 f_charbig VARCHAR(1000),
284 UNIQUE INDEX (f_int2)
285 )
286 PARTITION BY RANGE(f_int1)
287 (PARTITION part1 VALUES LESS THAN (1),
288 PARTITION part2 VALUES LESS THAN (2147483646));
289 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
290 CREATE TABLE t1 (
291 f_int1 INTEGER,
292 f_int2 INTEGER,
293 f_char1 CHAR(20),
294 f_char2 CHAR(20),
295 f_charbig VARCHAR(1000),
296 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
297 )
298 PARTITION BY RANGE(f_int1)
299 (PARTITION part1 VALUES LESS THAN (1),
300 PARTITION part2 VALUES LESS THAN (2147483646));
301 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
302 CREATE TABLE t1 (
303 f_int1 INTEGER,
304 f_int2 INTEGER,
305 f_char1 CHAR(20),
306 f_char2 CHAR(20),
307 f_charbig VARCHAR(1000),
308 UNIQUE INDEX (f_int2)
309 )
310 PARTITION BY RANGE(f_int1 + f_int2)
311 (PARTITION part1 VALUES LESS THAN (1),
312 PARTITION part2 VALUES LESS THAN (2147483646));
313 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
314 CREATE TABLE t1 (
315 f_int1 INTEGER,
316 f_int2 INTEGER,
317 f_char1 CHAR(20),
318 f_char2 CHAR(20),
319 f_charbig VARCHAR(1000),
320 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
321 )
322 PARTITION BY RANGE(f_int1 + f_int2)
323 (PARTITION part1 VALUES LESS THAN (1),
324 PARTITION part2 VALUES LESS THAN (2147483646));
325 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
326 #------------------------------------------------------------------------
327 # 1.3 column of subpartitioning function not included in PRIMARY KEY
328 # PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY
329 #------------------------------------------------------------------------
330 CREATE TABLE t1 (
331 f_int1 INTEGER,
332 f_int2 INTEGER,
333 f_char1 CHAR(20),
334 f_char2 CHAR(20),
335 f_charbig VARCHAR(1000),
336 PRIMARY KEY (f_int2)
337 )
338 PARTITION BY RANGE(f_int2) SUBPARTITION BY HASH(f_int1)
339 (PARTITION part1 VALUES LESS THAN (1)
340 (SUBPARTITION subpart1));
341 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
342 CREATE TABLE t1 (
343 f_int1 INTEGER,
344 f_int2 INTEGER,
345 f_char1 CHAR(20),
346 f_char2 CHAR(20),
347 f_charbig VARCHAR(1000),
348 PRIMARY KEY (f_int2)
349 )
350 PARTITION BY RANGE(f_int2) SUBPARTITION BY KEY(f_int1)
351 (PARTITION part1 VALUES LESS THAN (1)
352 (SUBPARTITION subpart1));
353 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
354 CREATE TABLE t1 (
355 f_int1 INTEGER,
356 f_int2 INTEGER,
357 f_char1 CHAR(20),
358 f_char2 CHAR(20),
359 f_charbig VARCHAR(1000),
360 PRIMARY KEY (f_int2)
361 )
362 PARTITION BY LIST(f_int2) SUBPARTITION BY HASH(f_int1)
363 (PARTITION part1 VALUES IN (1)
364 (SUBPARTITION subpart1));
365 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
366 CREATE TABLE t1 (
367 f_int1 INTEGER,
368 f_int2 INTEGER,
369 f_char1 CHAR(20),
370 f_char2 CHAR(20),
371 f_charbig VARCHAR(1000),
372 PRIMARY KEY (f_int2)
373 )
374 PARTITION BY LIST(f_int2) SUBPARTITION BY KEY(f_int1)
375 (PARTITION part1 VALUES IN (1)
376 (SUBPARTITION subpart1));
377 ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
378 #------------------------------------------------------------------------
379 # 1.4 column of subpartitioning function not included in UNIQUE INDEX
380 # PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY
381 # Variant a) Without additional PRIMARY KEY
382 # Variant b) With correct additional PRIMARY KEY
383 #------------------------------------------------------------------------
384 CREATE TABLE t1 (
385 f_int1 INTEGER,
386 f_int2 INTEGER,
387 f_char1 CHAR(20),
388 f_char2 CHAR(20),
389 f_charbig VARCHAR(1000),
390 UNIQUE INDEX (f_int2)
391 )
392 PARTITION BY RANGE(f_int2)
393 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 3
394 (PARTITION part1 VALUES LESS THAN (1),
395 PARTITION part2 VALUES LESS THAN (2147483646));
396 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
397 CREATE TABLE t1 (
398 f_int1 INTEGER,
399 f_int2 INTEGER,
400 f_char1 CHAR(20),
401 f_char2 CHAR(20),
402 f_charbig VARCHAR(1000),
403 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
404 )
405 PARTITION BY RANGE(f_int2)
406 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 3
407 (PARTITION part1 VALUES LESS THAN (1),
408 PARTITION part2 VALUES LESS THAN (2147483646));
409 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
410 CREATE TABLE t1 (
411 f_int1 INTEGER,
412 f_int2 INTEGER,
413 f_char1 CHAR(20),
414 f_char2 CHAR(20),
415 f_charbig VARCHAR(1000),
416 UNIQUE INDEX (f_int2)
417 )
418 PARTITION BY RANGE(f_int2)
419 SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 3
420 (PARTITION part1 VALUES LESS THAN (1),
421 PARTITION part2 VALUES LESS THAN (2147483646));
422 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
423 CREATE TABLE t1 (
424 f_int1 INTEGER,
425 f_int2 INTEGER,
426 f_char1 CHAR(20),
427 f_char2 CHAR(20),
428 f_charbig VARCHAR(1000),
429 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
430 )
431 PARTITION BY RANGE(f_int2)
432 SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 3
433 (PARTITION part1 VALUES LESS THAN (1),
434 PARTITION part2 VALUES LESS THAN (2147483646));
435 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
436 CREATE TABLE t1 (
437 f_int1 INTEGER,
438 f_int2 INTEGER,
439 f_char1 CHAR(20),
440 f_char2 CHAR(20),
441 f_charbig VARCHAR(1000),
442 UNIQUE INDEX (f_int2)
443 )
444 PARTITION BY LIST(MOD(f_int2,3))
445 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
446 (PARTITION partN VALUES IN (NULL),
447 PARTITION part0 VALUES IN (0),
448 PARTITION part1 VALUES IN (1),
449 PARTITION part2 VALUES IN (2));
450 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
451 CREATE TABLE t1 (
452 f_int1 INTEGER,
453 f_int2 INTEGER,
454 f_char1 CHAR(20),
455 f_char2 CHAR(20),
456 f_charbig VARCHAR(1000),
457 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
458 )
459 PARTITION BY LIST(MOD(f_int2,3))
460 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
461 (PARTITION partN VALUES IN (NULL),
462 PARTITION part0 VALUES IN (0),
463 PARTITION part1 VALUES IN (1),
464 PARTITION part2 VALUES IN (2));
465 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
466 CREATE TABLE t1 (
467 f_int1 INTEGER,
468 f_int2 INTEGER,
469 f_char1 CHAR(20),
470 f_char2 CHAR(20),
471 f_charbig VARCHAR(1000),
472 UNIQUE INDEX (f_int2)
473 )
474 PARTITION BY LIST(MOD(f_int2,3))
475 SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 2
476 (PARTITION partN VALUES IN (NULL),
477 PARTITION part0 VALUES IN (0),
478 PARTITION part1 VALUES IN (1),
479 PARTITION part2 VALUES IN (2));
480 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
481 CREATE TABLE t1 (
482 f_int1 INTEGER,
483 f_int2 INTEGER,
484 f_char1 CHAR(20),
485 f_char2 CHAR(20),
486 f_charbig VARCHAR(1000),
487 PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
488 )
489 PARTITION BY LIST(MOD(f_int2,3))
490 SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 2
491 (PARTITION partN VALUES IN (NULL),
492 PARTITION part0 VALUES IN (0),
493 PARTITION part1 VALUES IN (1),
494 PARTITION part2 VALUES IN (2));
495 ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
496
497 #========================================================================
498 # 2 Some properties around subpartitioning
499 #========================================================================
500 #------------------------------------------------------------------------
501 # 2.1 Subpartioned table without subpartitioning rule must be rejected
502 #------------------------------------------------------------------------
503 DROP TABLE IF EXISTS t1;
504 CREATE TABLE t1 (
505 f_int1 INTEGER,
506 f_int2 INTEGER,
507 f_char1 CHAR(20),
508 f_char2 CHAR(20),
509 f_charbig VARCHAR(1000)
510 )
511 PARTITION BY RANGE(f_int1)
512 ( PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11));
513 ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
514 #------------------------------------------------------------------------
515 # 2.2 Every partition must have the same number of subpartitions.
516 # This is a limitation of MySQL 5.1, which could be removed in
517 # later releases.
518 #------------------------------------------------------------------------
519 CREATE TABLE t1 (
520 f_int1 INTEGER,
521 f_int2 INTEGER,
522 f_char1 CHAR(20),
523 f_char2 CHAR(20),
524 f_charbig VARCHAR(1000),
525 PRIMARY KEY (f_int1)
526 )
527 PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
528 (
529 PARTITION part1 VALUES LESS THAN (0)
530 (SUBPARTITION subpart1),
531 PARTITION part2 VALUES LESS THAN (5)
532 (SUBPARTITION subpart1, SUBPARTITION subpart2));
533 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '(SUBPARTITION subpart1, SUBPARTITION subpart2))' at line 14
534
535 #========================================================================
536 # 3 VALUES clauses
537 #========================================================================
538 #------------------------------------------------------------------------
539 # 3.1 The constants in VALUES IN clauses must differ
540 #------------------------------------------------------------------------
541 CREATE TABLE t1 (
542 f_int1 INTEGER,
543 f_int2 INTEGER,
544 f_char1 CHAR(20),
545 f_char2 CHAR(20),
546 f_charbig VARCHAR(1000)
547 )
548 PARTITION BY LIST(MOD(f_int1,2))
549 ( PARTITION part1 VALUES IN (-1),
550 PARTITION part2 VALUES IN (0),
551 PARTITION part3 VALUES IN (-1));
552 ERROR HY000: Multiple definition of same constant in list partitioning
553 CREATE TABLE t1 (f1 BIGINT, f2 BIGINT)
554 PARTITION BY RANGE(f1)
555 (PARTITION part1 VALUES LESS THAN (0),
556 PARTITION part2 VALUES LESS THAN (0),
557 PARTITION part3 VALUES LESS THAN (10000));
558 ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
559 #------------------------------------------------------------------------
560 # 3.2 The constants in VALUES LESS must be in increasing order
561 #------------------------------------------------------------------------
562 CREATE TABLE t1 (f1 BIGINT, f2 BIGINT)
563 PARTITION BY RANGE(f1)
564 (PARTITION part1 VALUES LESS THAN (0),
565 PARTITION part2 VALUES LESS THAN (-1),
566 PARTITION part3 VALUES LESS THAN (10000));
567 ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
568 #------------------------------------------------------------------------
569 # 3.3 LIST partitions must be defined with VALUES IN
570 #------------------------------------------------------------------------
571 CREATE TABLE t1 (
572 f_int1 INTEGER,
573 f_int2 INTEGER,
574 f_char1 CHAR(20),
575 f_char2 CHAR(20),
576 f_charbig VARCHAR(1000)
577 )
578 PARTITION BY LIST(MOD(f_int1,2))
579 ( PARTITION part1 VALUES LESS THAN (-1),
580 PARTITION part2 VALUES LESS THAN (0),
581 PARTITION part3 VALUES LESS THAN (1000));
582 ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
583 #------------------------------------------------------------------------
584 # 3.4 RANGE partitions must be defined with VALUES LESS THAN
585 #------------------------------------------------------------------------
586 CREATE TABLE t1 (
587 f_int1 INTEGER,
588 f_int2 INTEGER,
589 f_char1 CHAR(20),
590 f_char2 CHAR(20),
591 f_charbig VARCHAR(1000)
592 )
593 PARTITION BY RANGE(f_int1)
594 ( PARTITION part1 VALUES IN (-1),
595 PARTITION part2 VALUES IN (0),
596 PARTITION part3 VALUES IN (1000));
597 ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
598 #------------------------------------------------------------------------
599 # 3.5 Use of NULL in VALUES clauses
600 #------------------------------------------------------------------------
601 # 3.5.1 NULL in RANGE partitioning clause
602 # 3.5.1.1 VALUE LESS THAN (NULL) is not allowed
603 CREATE TABLE t1 (
604 f_int1 INTEGER,
605 f_int2 INTEGER,
606 f_char1 CHAR(20),
607 f_char2 CHAR(20),
608 f_charbig VARCHAR(1000)
609 )
610 PARTITION BY RANGE(f_int1)
611 ( PARTITION part1 VALUES LESS THAN (NULL),
612 PARTITION part2 VALUES LESS THAN (1000));
613 ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN
614 # 3.5.1.2 VALUE LESS THAN (NULL) is not allowed
615 CREATE TABLE t1 (
616 f_int1 INTEGER,
617 f_int2 INTEGER,
618 f_char1 CHAR(20),
619 f_char2 CHAR(20),
620 f_charbig VARCHAR(1000)
621 )
622 PARTITION BY RANGE(f_int1)
623 ( PARTITION part1 VALUES LESS THAN (NULL),
624 PARTITION part2 VALUES LESS THAN (1000));
625 ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN
626 # 3.5.2 NULL in LIST partitioning clause
627 # 3.5.2.1 VALUE IN (NULL)
628 CREATE TABLE t1 (
629 f_int1 INTEGER,
630 f_int2 INTEGER,
631 f_char1 CHAR(20),
632 f_char2 CHAR(20),
633 f_charbig VARCHAR(1000)
634 )
635 PARTITION BY LIST(MOD(f_int1,2))
636 ( PARTITION part1 VALUES IN (NULL),
637 PARTITION part2 VALUES IN (0),
638 PARTITION part3 VALUES IN (1));
639 DROP TABLE t1;
640 # 3.5.2.2 VALUE IN (NULL)
641 CREATE TABLE t1 (
642 f_int1 INTEGER,
643 f_int2 INTEGER,
644 f_char1 CHAR(20),
645 f_char2 CHAR(20),
646 f_charbig VARCHAR(1000)
647 )
648 PARTITION BY LIST(MOD(f_int1,2))
649 ( PARTITION part1 VALUES IN (NULL),
650 PARTITION part3 VALUES IN (1));
651 create_command
652 SHOW CREATE TABLE t1;
653 Table Create Table
654 t1 CREATE TABLE `t1` (
655 `f_int1` int DEFAULT NULL,
656 `f_int2` int DEFAULT NULL,
657 `f_char1` char(20) DEFAULT NULL,
658 `f_char2` char(20) DEFAULT NULL,
659 `f_charbig` varchar(1000) DEFAULT NULL
660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
661 /*!50100 PARTITION BY LIST ((`f_int1` % 2))
662 (PARTITION part1 VALUES IN (NULL) ENGINE = InnoDB,
663 PARTITION part3 VALUES IN (1) ENGINE = InnoDB) */
664
665 DROP TABLE t1;
666 # 3.5.3 Reveal that IN (...NULL) is not mapped to IN(0)
667 CREATE TABLE t1 (
668 f_int1 INTEGER,
669 f_int2 INTEGER,
670 f_char1 CHAR(20),
671 f_char2 CHAR(20),
672 f_charbig VARCHAR(1000)
673 )
674 PARTITION BY LIST(MOD(f_int1,2))
675 ( PARTITION part1 VALUES IN (NULL),
676 PARTITION part2 VALUES IN (0),
677 PARTITION part3 VALUES IN (1));
678 create_command
679 SHOW CREATE TABLE t1;
680 Table Create Table
681 t1 CREATE TABLE `t1` (
682 `f_int1` int DEFAULT NULL,
683 `f_int2` int DEFAULT NULL,
684 `f_char1` char(20) DEFAULT NULL,
685 `f_char2` char(20) DEFAULT NULL,
686 `f_charbig` varchar(1000) DEFAULT NULL
687 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
688 /*!50100 PARTITION BY LIST ((`f_int1` % 2))
689 (PARTITION part1 VALUES IN (NULL) ENGINE = InnoDB,
690 PARTITION part2 VALUES IN (0) ENGINE = InnoDB,
691 PARTITION part3 VALUES IN (1) ENGINE = InnoDB) */
692
693 DROP TABLE t1;
694
695 #========================================================================
696 # 4. Check assigning the number of partitions and subpartitions
697 # with and without named partitions/subpartitions
698 #========================================================================
699 DROP TABLE IF EXISTS t1;
700 #------------------------------------------------------------------------
701 # 4.1 (positive) without partition/subpartition number assignment
702 #------------------------------------------------------------------------
703 # 4.1.1 no partition number, no named partitions
704 CREATE TABLE t1 (
705 f_int1 INTEGER,
706 f_int2 INTEGER,
707 f_char1 CHAR(20),
708 f_char2 CHAR(20),
709 f_charbig VARCHAR(1000)
710 )
711 PARTITION BY HASH(f_int1);
712 create_command
713 SHOW CREATE TABLE t1;
714 Table Create Table
715 t1 CREATE TABLE `t1` (
716 `f_int1` int DEFAULT NULL,
717 `f_int2` int DEFAULT NULL,
718 `f_char1` char(20) DEFAULT NULL,
719 `f_char2` char(20) DEFAULT NULL,
720 `f_charbig` varchar(1000) DEFAULT NULL
721 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
722 /*!50100 PARTITION BY HASH (`f_int1`) */
723
724 DROP TABLE t1;
725 # 4.1.2 no partition number, named partitions
726 CREATE TABLE t1 (
727 f_int1 INTEGER,
728 f_int2 INTEGER,
729 f_char1 CHAR(20),
730 f_char2 CHAR(20),
731 f_charbig VARCHAR(1000)
732 )
733 PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part2);
734 create_command
735 SHOW CREATE TABLE t1;
736 Table Create Table
737 t1 CREATE TABLE `t1` (
738 `f_int1` int DEFAULT NULL,
739 `f_int2` int DEFAULT NULL,
740 `f_char1` char(20) DEFAULT NULL,
741 `f_char2` char(20) DEFAULT NULL,
742 `f_charbig` varchar(1000) DEFAULT NULL
743 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
744 /*!50100 PARTITION BY HASH (`f_int1`)
745 (PARTITION part1 ENGINE = InnoDB,
746 PARTITION part2 ENGINE = InnoDB) */
747
748 DROP TABLE t1;
749 # 4.1.3 variations on no partition/subpartition number, named partitions,
750 # different subpartitions are/are not named
751 CREATE TABLE t1 ( f_int1 INTEGER,
752 f_int2 INTEGER,
753 f_char1 CHAR(20),
754 f_char2 CHAR(20),
755 f_charbig VARCHAR(1000) )
756 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ;
757 DROP TABLE t1;
758 CREATE TABLE t1 ( f_int1 INTEGER,
759 f_int2 INTEGER,
760 f_char1 CHAR(20),
761 f_char2 CHAR(20),
762 f_charbig VARCHAR(1000) )
763 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)
764 (SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
765 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart31 , SUBPARTITION subpart32 ))' at line 7
766 CREATE TABLE t1 ( f_int1 INTEGER,
767 f_int2 INTEGER,
768 f_char1 CHAR(20),
769 f_char2 CHAR(20),
770 f_charbig VARCHAR(1000) )
771 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20)
772 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ;
773 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS T' at line 7
774 CREATE TABLE t1 ( f_int1 INTEGER,
775 f_int2 INTEGER,
776 f_char1 CHAR(20),
777 f_char2 CHAR(20),
778 f_charbig VARCHAR(1000) )
779 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20)
780 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)
781 (SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
782 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS T' at line 7
783 CREATE TABLE t1 ( f_int1 INTEGER,
784 f_int2 INTEGER,
785 f_char1 CHAR(20),
786 f_char2 CHAR(20),
787 f_charbig VARCHAR(1000) )
788 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
789 (SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ;
790 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ', PARTITION part3 VALUES LESS THAN (2147483646))' at line 7
791 CREATE TABLE t1 ( f_int1 INTEGER,
792 f_int2 INTEGER,
793 f_char1 CHAR(20),
794 f_char2 CHAR(20),
795 f_charbig VARCHAR(1000) )
796 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
797 (SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)
798 (SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
799 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ', PARTITION part3 VALUES LESS THAN (2147483646)
800 (SUBPARTITION subpart31 , SUBPAR' at line 7
801 CREATE TABLE t1 ( f_int1 INTEGER,
802 f_int2 INTEGER,
803 f_char1 CHAR(20),
804 f_char2 CHAR(20),
805 f_charbig VARCHAR(1000) )
806 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
807 (SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20)
808 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ;
809 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')' at line 8
810 CREATE TABLE t1 ( f_int1 INTEGER,
811 f_int2 INTEGER,
812 f_char1 CHAR(20),
813 f_char2 CHAR(20),
814 f_charbig VARCHAR(1000) )
815 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
816 (SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20)
817 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)
818 (SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
819 create_command
820 SHOW CREATE TABLE t1;
821 Table Create Table
822 t1 CREATE TABLE `t1` (
823 `f_int1` int DEFAULT NULL,
824 `f_int2` int DEFAULT NULL,
825 `f_char1` char(20) DEFAULT NULL,
826 `f_char2` char(20) DEFAULT NULL,
827 `f_charbig` varchar(1000) DEFAULT NULL
828 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
829 /*!50100 PARTITION BY RANGE (`f_int1`)
830 SUBPARTITION BY HASH (`f_int1`)
831 (PARTITION part1 VALUES LESS THAN (10)
832 (SUBPARTITION subpart11 ENGINE = InnoDB,
833 SUBPARTITION subpart12 ENGINE = InnoDB),
834 PARTITION part2 VALUES LESS THAN (20)
835 (SUBPARTITION subpart21 ENGINE = InnoDB,
836 SUBPARTITION subpart22 ENGINE = InnoDB),
837 PARTITION part3 VALUES LESS THAN (2147483646)
838 (SUBPARTITION subpart31 ENGINE = InnoDB,
839 SUBPARTITION subpart32 ENGINE = InnoDB)) */
840
841 DROP TABLE t1;
842 #------------------------------------------------------------------------
843 # 4.2 partition/subpartition numbers good and bad values and notations
844 #------------------------------------------------------------------------
845 DROP TABLE IF EXISTS t1;
846 # 4.2.1 partition/subpartition numbers INTEGER notation
847 CREATE TABLE t1 (
848 f_int1 INTEGER,
849 f_int2 INTEGER,
850 f_char1 CHAR(20),
851 f_char2 CHAR(20),
852 f_charbig VARCHAR(1000)
853 )
854 PARTITION BY HASH(f_int1) PARTITIONS 2;
855 create_command
856 SHOW CREATE TABLE t1;
857 Table Create Table
858 t1 CREATE TABLE `t1` (
859 `f_int1` int DEFAULT NULL,
860 `f_int2` int DEFAULT NULL,
861 `f_char1` char(20) DEFAULT NULL,
862 `f_char2` char(20) DEFAULT NULL,
863 `f_charbig` varchar(1000) DEFAULT NULL
864 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
865 /*!50100 PARTITION BY HASH (`f_int1`)
866 PARTITIONS 2 */
867
868 DROP TABLE t1;
869 CREATE TABLE t1 (
870 f_int1 INTEGER,
871 f_int2 INTEGER,
872 f_char1 CHAR(20),
873 f_char2 CHAR(20),
874 f_charbig VARCHAR(1000)
875 )
876 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
877 SUBPARTITIONS 2
878 (PARTITION part1 VALUES LESS THAN (10),
879 PARTITION part2 VALUES LESS THAN (2147483646));
880 create_command
881 SHOW CREATE TABLE t1;
882 Table Create Table
883 t1 CREATE TABLE `t1` (
884 `f_int1` int DEFAULT NULL,
885 `f_int2` int DEFAULT NULL,
886 `f_char1` char(20) DEFAULT NULL,
887 `f_char2` char(20) DEFAULT NULL,
888 `f_charbig` varchar(1000) DEFAULT NULL
889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
890 /*!50100 PARTITION BY RANGE (`f_int1`)
891 SUBPARTITION BY HASH (`f_int1`)
892 SUBPARTITIONS 2
893 (PARTITION part1 VALUES LESS THAN (10) ENGINE = InnoDB,
894 PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = InnoDB) */
895
896 DROP TABLE t1;
897 CREATE TABLE t1 (
898 f_int1 INTEGER,
899 f_int2 INTEGER,
900 f_char1 CHAR(20),
901 f_char2 CHAR(20),
902 f_charbig VARCHAR(1000)
903 )
904 PARTITION BY HASH(f_int1) PARTITIONS 1;
905 create_command
906 SHOW CREATE TABLE t1;
907 Table Create Table
908 t1 CREATE TABLE `t1` (
909 `f_int1` int DEFAULT NULL,
910 `f_int2` int DEFAULT NULL,
911 `f_char1` char(20) DEFAULT NULL,
912 `f_char2` char(20) DEFAULT NULL,
913 `f_charbig` varchar(1000) DEFAULT NULL
914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
915 /*!50100 PARTITION BY HASH (`f_int1`)
916 PARTITIONS 1 */
917
918 DROP TABLE t1;
919 CREATE TABLE t1 (
920 f_int1 INTEGER,
921 f_int2 INTEGER,
922 f_char1 CHAR(20),
923 f_char2 CHAR(20),
924 f_charbig VARCHAR(1000)
925 )
926 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
927 SUBPARTITIONS 1
928 (PARTITION part1 VALUES LESS THAN (10),
929 PARTITION part2 VALUES LESS THAN (2147483646));
930 create_command
931 SHOW CREATE TABLE t1;
932 Table Create Table
933 t1 CREATE TABLE `t1` (
934 `f_int1` int DEFAULT NULL,
935 `f_int2` int DEFAULT NULL,
936 `f_char1` char(20) DEFAULT NULL,
937 `f_char2` char(20) DEFAULT NULL,
938 `f_charbig` varchar(1000) DEFAULT NULL
939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
940 /*!50100 PARTITION BY RANGE (`f_int1`)
941 SUBPARTITION BY HASH (`f_int1`)
942 SUBPARTITIONS 1
943 (PARTITION part1 VALUES LESS THAN (10) ENGINE = InnoDB,
944 PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = InnoDB) */
945
946 DROP TABLE t1;
947 CREATE TABLE t1 (
948 f_int1 INTEGER,
949 f_int2 INTEGER,
950 f_char1 CHAR(20),
951 f_char2 CHAR(20),
952 f_charbig VARCHAR(1000)
953 )
954 PARTITION BY HASH(f_int1) PARTITIONS 0;
955 ERROR HY000: Number of partitions = 0 is not an allowed value
956 CREATE TABLE t1 (
957 f_int1 INTEGER,
958 f_int2 INTEGER,
959 f_char1 CHAR(20),
960 f_char2 CHAR(20),
961 f_charbig VARCHAR(1000)
962 )
963 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
964 SUBPARTITIONS 0
965 (PARTITION part1 VALUES LESS THAN (10),
966 PARTITION part2 VALUES LESS THAN (2147483646));
967 ERROR HY000: Number of subpartitions = 0 is not an allowed value
968 CREATE TABLE t1 (
969 f_int1 INTEGER,
970 f_int2 INTEGER,
971 f_char1 CHAR(20),
972 f_char2 CHAR(20),
973 f_charbig VARCHAR(1000)
974 )
975 PARTITION BY HASH(f_int1) PARTITIONS -1;
976 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 8
977 CREATE TABLE t1 (
978 f_int1 INTEGER,
979 f_int2 INTEGER,
980 f_char1 CHAR(20),
981 f_char2 CHAR(20),
982 f_charbig VARCHAR(1000)
983 )
984 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
985 SUBPARTITIONS -1
986 (PARTITION part1 VALUES LESS THAN (10),
987 PARTITION part2 VALUES LESS THAN (2147483646));
988 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1
989 (PARTITION part1 VALUES LESS THAN (10),
990 PARTITION part2 VALUES LESS THAN (214' at line 9
991 CREATE TABLE t1 (
992 f_int1 INTEGER,
993 f_int2 INTEGER,
994 f_char1 CHAR(20),
995 f_char2 CHAR(20),
996 f_charbig VARCHAR(1000)
997 )
998 PARTITION BY HASH(f_int1) PARTITIONS 1000000;
999 ERROR HY000: Too many partitions (including subpartitions) were defined
1000 CREATE TABLE t1 (
1001 f_int1 INTEGER,
1002 f_int2 INTEGER,
1003 f_char1 CHAR(20),
1004 f_char2 CHAR(20),
1005 f_charbig VARCHAR(1000)
1006 )
1007 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1008 SUBPARTITIONS 1000000
1009 (PARTITION part1 VALUES LESS THAN (10),
1010 PARTITION part2 VALUES LESS THAN (2147483646));
1011 ERROR HY000: Too many partitions (including subpartitions) were defined
1012 # 4.2.2 partition/subpartition numbers DECIMAL notation
1013 CREATE TABLE t1 (
1014 f_int1 INTEGER,
1015 f_int2 INTEGER,
1016 f_char1 CHAR(20),
1017 f_char2 CHAR(20),
1018 f_charbig VARCHAR(1000)
1019 )
1020 PARTITION BY HASH(f_int1) PARTITIONS 2.0;
1021 ERROR 42000: Only integers allowed as number here near '2.0' at line 8
1022 CREATE TABLE t1 (
1023 f_int1 INTEGER,
1024 f_int2 INTEGER,
1025 f_char1 CHAR(20),
1026 f_char2 CHAR(20),
1027 f_charbig VARCHAR(1000)
1028 )
1029 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1030 SUBPARTITIONS 2.0
1031 (PARTITION part1 VALUES LESS THAN (10),
1032 PARTITION part2 VALUES LESS THAN (2147483646));
1033 ERROR 42000: Only integers allowed as number here near '2.0
1034 (PARTITION part1 VALUES LESS THAN (10),
1035 PARTITION part2 VALUES LESS THAN (21' at line 9
1036 CREATE TABLE t1 (
1037 f_int1 INTEGER,
1038 f_int2 INTEGER,
1039 f_char1 CHAR(20),
1040 f_char2 CHAR(20),
1041 f_charbig VARCHAR(1000)
1042 )
1043 PARTITION BY HASH(f_int1) PARTITIONS -2.0;
1044 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0' at line 8
1045 CREATE TABLE t1 (
1046 f_int1 INTEGER,
1047 f_int2 INTEGER,
1048 f_char1 CHAR(20),
1049 f_char2 CHAR(20),
1050 f_charbig VARCHAR(1000)
1051 )
1052 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1053 SUBPARTITIONS -2.0
1054 (PARTITION part1 VALUES LESS THAN (10),
1055 PARTITION part2 VALUES LESS THAN (2147483646));
1056 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0
1057 (PARTITION part1 VALUES LESS THAN (10),
1058 PARTITION part2 VALUES LESS THAN (2' at line 9
1059 CREATE TABLE t1 (
1060 f_int1 INTEGER,
1061 f_int2 INTEGER,
1062 f_char1 CHAR(20),
1063 f_char2 CHAR(20),
1064 f_charbig VARCHAR(1000)
1065 )
1066 PARTITION BY HASH(f_int1) PARTITIONS 0.0;
1067 ERROR 42000: Only integers allowed as number here near '0.0' at line 8
1068 CREATE TABLE t1 (
1069 f_int1 INTEGER,
1070 f_int2 INTEGER,
1071 f_char1 CHAR(20),
1072 f_char2 CHAR(20),
1073 f_charbig VARCHAR(1000)
1074 )
1075 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1076 SUBPARTITIONS 0.0
1077 (PARTITION part1 VALUES LESS THAN (10),
1078 PARTITION part2 VALUES LESS THAN (2147483646));
1079 ERROR 42000: Only integers allowed as number here near '0.0
1080 (PARTITION part1 VALUES LESS THAN (10),
1081 PARTITION part2 VALUES LESS THAN (21' at line 9
1082 CREATE TABLE t1 (
1083 f_int1 INTEGER,
1084 f_int2 INTEGER,
1085 f_char1 CHAR(20),
1086 f_char2 CHAR(20),
1087 f_charbig VARCHAR(1000)
1088 )
1089 PARTITION BY HASH(f_int1) PARTITIONS 1.6;
1090 ERROR 42000: Only integers allowed as number here near '1.6' at line 8
1091 CREATE TABLE t1 (
1092 f_int1 INTEGER,
1093 f_int2 INTEGER,
1094 f_char1 CHAR(20),
1095 f_char2 CHAR(20),
1096 f_charbig VARCHAR(1000)
1097 )
1098 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1099 SUBPARTITIONS 1.6
1100 (PARTITION part1 VALUES LESS THAN (10),
1101 PARTITION part2 VALUES LESS THAN (2147483646));
1102 ERROR 42000: Only integers allowed as number here near '1.6
1103 (PARTITION part1 VALUES LESS THAN (10),
1104 PARTITION part2 VALUES LESS THAN (21' at line 9
1105 CREATE TABLE t1 (
1106 f_int1 INTEGER,
1107 f_int2 INTEGER,
1108 f_char1 CHAR(20),
1109 f_char2 CHAR(20),
1110 f_charbig VARCHAR(1000)
1111 )
1112 PARTITION BY HASH(f_int1) PARTITIONS 999999999999999999999999999999.999999999999999999999999999999;
1113 ERROR 42000: Only integers allowed as number here near '999999999999999999999999999999.999999999999999999999999999999' at line 8
1114 CREATE TABLE t1 (
1115 f_int1 INTEGER,
1116 f_int2 INTEGER,
1117 f_char1 CHAR(20),
1118 f_char2 CHAR(20),
1119 f_charbig VARCHAR(1000)
1120 )
1121 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1122 SUBPARTITIONS 999999999999999999999999999999.999999999999999999999999999999
1123 (PARTITION part1 VALUES LESS THAN (10),
1124 PARTITION part2 VALUES LESS THAN (2147483646));
1125 ERROR 42000: Only integers allowed as number here near '999999999999999999999999999999.999999999999999999999999999999
1126 (PARTITION part1 V' at line 9
1127 CREATE TABLE t1 (
1128 f_int1 INTEGER,
1129 f_int2 INTEGER,
1130 f_char1 CHAR(20),
1131 f_char2 CHAR(20),
1132 f_charbig VARCHAR(1000)
1133 )
1134 PARTITION BY HASH(f_int1) PARTITIONS 0.000000000000000000000000000001;
1135 ERROR 42000: Only integers allowed as number here near '0.000000000000000000000000000001' at line 8
1136 CREATE TABLE t1 (
1137 f_int1 INTEGER,
1138 f_int2 INTEGER,
1139 f_char1 CHAR(20),
1140 f_char2 CHAR(20),
1141 f_charbig VARCHAR(1000)
1142 )
1143 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1144 SUBPARTITIONS 0.000000000000000000000000000001
1145 (PARTITION part1 VALUES LESS THAN (10),
1146 PARTITION part2 VALUES LESS THAN (2147483646));
1147 ERROR 42000: Only integers allowed as number here near '0.000000000000000000000000000001
1148 (PARTITION part1 VALUES LESS THAN (10),
1149 PARTITI' at line 9
1150 # 4.2.3 partition/subpartition numbers FLOAT notation
1151 CREATE TABLE t1 (
1152 f_int1 INTEGER,
1153 f_int2 INTEGER,
1154 f_char1 CHAR(20),
1155 f_char2 CHAR(20),
1156 f_charbig VARCHAR(1000)
1157 )
1158 PARTITION BY HASH(f_int1) PARTITIONS 2.0E+0;
1159 ERROR 42000: Only integers allowed as number here near '2.0E+0' at line 8
1160 CREATE TABLE t1 (
1161 f_int1 INTEGER,
1162 f_int2 INTEGER,
1163 f_char1 CHAR(20),
1164 f_char2 CHAR(20),
1165 f_charbig VARCHAR(1000)
1166 )
1167 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1168 SUBPARTITIONS 2.0E+0
1169 (PARTITION part1 VALUES LESS THAN (10),
1170 PARTITION part2 VALUES LESS THAN (2147483646));
1171 ERROR 42000: Only integers allowed as number here near '2.0E+0
1172 (PARTITION part1 VALUES LESS THAN (10),
1173 PARTITION part2 VALUES LESS THAN ' at line 9
1174 CREATE TABLE t1 (
1175 f_int1 INTEGER,
1176 f_int2 INTEGER,
1177 f_char1 CHAR(20),
1178 f_char2 CHAR(20),
1179 f_charbig VARCHAR(1000)
1180 )
1181 PARTITION BY HASH(f_int1) PARTITIONS 0.2E+1;
1182 ERROR 42000: Only integers allowed as number here near '0.2E+1' at line 8
1183 CREATE TABLE t1 (
1184 f_int1 INTEGER,
1185 f_int2 INTEGER,
1186 f_char1 CHAR(20),
1187 f_char2 CHAR(20),
1188 f_charbig VARCHAR(1000)
1189 )
1190 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1191 SUBPARTITIONS 0.2E+1
1192 (PARTITION part1 VALUES LESS THAN (10),
1193 PARTITION part2 VALUES LESS THAN (2147483646));
1194 ERROR 42000: Only integers allowed as number here near '0.2E+1
1195 (PARTITION part1 VALUES LESS THAN (10),
1196 PARTITION part2 VALUES LESS THAN ' at line 9
1197 CREATE TABLE t1 (
1198 f_int1 INTEGER,
1199 f_int2 INTEGER,
1200 f_char1 CHAR(20),
1201 f_char2 CHAR(20),
1202 f_charbig VARCHAR(1000)
1203 )
1204 PARTITION BY HASH(f_int1) PARTITIONS -2.0E+0;
1205 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0E+0' at line 8
1206 CREATE TABLE t1 (
1207 f_int1 INTEGER,
1208 f_int2 INTEGER,
1209 f_char1 CHAR(20),
1210 f_char2 CHAR(20),
1211 f_charbig VARCHAR(1000)
1212 )
1213 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1214 SUBPARTITIONS -2.0E+0
1215 (PARTITION part1 VALUES LESS THAN (10),
1216 PARTITION part2 VALUES LESS THAN (2147483646));
1217 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0E+0
1218 (PARTITION part1 VALUES LESS THAN (10),
1219 PARTITION part2 VALUES LESS THAN' at line 9
1220 CREATE TABLE t1 (
1221 f_int1 INTEGER,
1222 f_int2 INTEGER,
1223 f_char1 CHAR(20),
1224 f_char2 CHAR(20),
1225 f_charbig VARCHAR(1000)
1226 )
1227 PARTITION BY HASH(f_int1) PARTITIONS 0.16E+1;
1228 ERROR 42000: Only integers allowed as number here near '0.16E+1' at line 8
1229 CREATE TABLE t1 (
1230 f_int1 INTEGER,
1231 f_int2 INTEGER,
1232 f_char1 CHAR(20),
1233 f_char2 CHAR(20),
1234 f_charbig VARCHAR(1000)
1235 )
1236 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1237 SUBPARTITIONS 0.16E+1
1238 (PARTITION part1 VALUES LESS THAN (10),
1239 PARTITION part2 VALUES LESS THAN (2147483646));
1240 ERROR 42000: Only integers allowed as number here near '0.16E+1
1241 (PARTITION part1 VALUES LESS THAN (10),
1242 PARTITION part2 VALUES LESS THAN' at line 9
1243 CREATE TABLE t1 (
1244 f_int1 INTEGER,
1245 f_int2 INTEGER,
1246 f_char1 CHAR(20),
1247 f_char2 CHAR(20),
1248 f_charbig VARCHAR(1000)
1249 )
1250 PARTITION BY HASH(f_int1) PARTITIONS 0.0E+300;
1251 ERROR 42000: Only integers allowed as number here near '0.0E+300' at line 8
1252 CREATE TABLE t1 (
1253 f_int1 INTEGER,
1254 f_int2 INTEGER,
1255 f_char1 CHAR(20),
1256 f_char2 CHAR(20),
1257 f_charbig VARCHAR(1000)
1258 )
1259 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1260 SUBPARTITIONS 0.0E+300
1261 (PARTITION part1 VALUES LESS THAN (10),
1262 PARTITION part2 VALUES LESS THAN (2147483646));
1263 ERROR 42000: Only integers allowed as number here near '0.0E+300
1264 (PARTITION part1 VALUES LESS THAN (10),
1265 PARTITION part2 VALUES LESS THA' at line 9
1266 CREATE TABLE t1 (
1267 f_int1 INTEGER,
1268 f_int2 INTEGER,
1269 f_char1 CHAR(20),
1270 f_char2 CHAR(20),
1271 f_charbig VARCHAR(1000)
1272 )
1273 PARTITION BY HASH(f_int1) PARTITIONS 1E+300;
1274 ERROR 42000: Only integers allowed as number here near '1E+300' at line 8
1275 CREATE TABLE t1 (
1276 f_int1 INTEGER,
1277 f_int2 INTEGER,
1278 f_char1 CHAR(20),
1279 f_char2 CHAR(20),
1280 f_charbig VARCHAR(1000)
1281 )
1282 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1283 SUBPARTITIONS 1E+300
1284 (PARTITION part1 VALUES LESS THAN (10),
1285 PARTITION part2 VALUES LESS THAN (2147483646));
1286 ERROR 42000: Only integers allowed as number here near '1E+300
1287 (PARTITION part1 VALUES LESS THAN (10),
1288 PARTITION part2 VALUES LESS THAN ' at line 9
1289 CREATE TABLE t1 (
1290 f_int1 INTEGER,
1291 f_int2 INTEGER,
1292 f_char1 CHAR(20),
1293 f_char2 CHAR(20),
1294 f_charbig VARCHAR(1000)
1295 )
1296 PARTITION BY HASH(f_int1) PARTITIONS 1E-300;
1297 ERROR 42000: Only integers allowed as number here near '1E-300' at line 8
1298 CREATE TABLE t1 (
1299 f_int1 INTEGER,
1300 f_int2 INTEGER,
1301 f_char1 CHAR(20),
1302 f_char2 CHAR(20),
1303 f_charbig VARCHAR(1000)
1304 )
1305 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1306 SUBPARTITIONS 1E-300
1307 (PARTITION part1 VALUES LESS THAN (10),
1308 PARTITION part2 VALUES LESS THAN (2147483646));
1309 ERROR 42000: Only integers allowed as number here near '1E-300
1310 (PARTITION part1 VALUES LESS THAN (10),
1311 PARTITION part2 VALUES LESS THAN ' at line 9
1312 # 4.2.4 partition/subpartition numbers STRING notation
1313 CREATE TABLE t1 (
1314 f_int1 INTEGER,
1315 f_int2 INTEGER,
1316 f_char1 CHAR(20),
1317 f_char2 CHAR(20),
1318 f_charbig VARCHAR(1000)
1319 )
1320 PARTITION BY HASH(f_int1) PARTITIONS '2';
1321 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 8
1322 CREATE TABLE t1 (
1323 f_int1 INTEGER,
1324 f_int2 INTEGER,
1325 f_char1 CHAR(20),
1326 f_char2 CHAR(20),
1327 f_charbig VARCHAR(1000)
1328 )
1329 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1330 SUBPARTITIONS '2'
1331 (PARTITION part1 VALUES LESS THAN (10),
1332 PARTITION part2 VALUES LESS THAN (2147483646));
1333 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'
1334 (PARTITION part1 VALUES LESS THAN (10),
1335 PARTITION part2 VALUES LESS THAN (21' at line 9
1336 CREATE TABLE t1 (
1337 f_int1 INTEGER,
1338 f_int2 INTEGER,
1339 f_char1 CHAR(20),
1340 f_char2 CHAR(20),
1341 f_charbig VARCHAR(1000)
1342 )
1343 PARTITION BY HASH(f_int1) PARTITIONS '2.0';
1344 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 8
1345 CREATE TABLE t1 (
1346 f_int1 INTEGER,
1347 f_int2 INTEGER,
1348 f_char1 CHAR(20),
1349 f_char2 CHAR(20),
1350 f_charbig VARCHAR(1000)
1351 )
1352 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1353 SUBPARTITIONS '2.0'
1354 (PARTITION part1 VALUES LESS THAN (10),
1355 PARTITION part2 VALUES LESS THAN (2147483646));
1356 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'
1357 (PARTITION part1 VALUES LESS THAN (10),
1358 PARTITION part2 VALUES LESS THAN (' at line 9
1359 CREATE TABLE t1 (
1360 f_int1 INTEGER,
1361 f_int2 INTEGER,
1362 f_char1 CHAR(20),
1363 f_char2 CHAR(20),
1364 f_charbig VARCHAR(1000)
1365 )
1366 PARTITION BY HASH(f_int1) PARTITIONS '0.2E+1';
1367 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 8
1368 CREATE TABLE t1 (
1369 f_int1 INTEGER,
1370 f_int2 INTEGER,
1371 f_char1 CHAR(20),
1372 f_char2 CHAR(20),
1373 f_charbig VARCHAR(1000)
1374 )
1375 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1376 SUBPARTITIONS '0.2E+1'
1377 (PARTITION part1 VALUES LESS THAN (10),
1378 PARTITION part2 VALUES LESS THAN (2147483646));
1379 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'
1380 (PARTITION part1 VALUES LESS THAN (10),
1381 PARTITION part2 VALUES LESS THA' at line 9
1382 CREATE TABLE t1 (
1383 f_int1 INTEGER,
1384 f_int2 INTEGER,
1385 f_char1 CHAR(20),
1386 f_char2 CHAR(20),
1387 f_charbig VARCHAR(1000)
1388 )
1389 PARTITION BY HASH(f_int1) PARTITIONS '2A';
1390 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 8
1391 CREATE TABLE t1 (
1392 f_int1 INTEGER,
1393 f_int2 INTEGER,
1394 f_char1 CHAR(20),
1395 f_char2 CHAR(20),
1396 f_charbig VARCHAR(1000)
1397 )
1398 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1399 SUBPARTITIONS '2A'
1400 (PARTITION part1 VALUES LESS THAN (10),
1401 PARTITION part2 VALUES LESS THAN (2147483646));
1402 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'
1403 (PARTITION part1 VALUES LESS THAN (10),
1404 PARTITION part2 VALUES LESS THAN (2' at line 9
1405 CREATE TABLE t1 (
1406 f_int1 INTEGER,
1407 f_int2 INTEGER,
1408 f_char1 CHAR(20),
1409 f_char2 CHAR(20),
1410 f_charbig VARCHAR(1000)
1411 )
1412 PARTITION BY HASH(f_int1) PARTITIONS 'A2';
1413 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 8
1414 CREATE TABLE t1 (
1415 f_int1 INTEGER,
1416 f_int2 INTEGER,
1417 f_char1 CHAR(20),
1418 f_char2 CHAR(20),
1419 f_charbig VARCHAR(1000)
1420 )
1421 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1422 SUBPARTITIONS 'A2'
1423 (PARTITION part1 VALUES LESS THAN (10),
1424 PARTITION part2 VALUES LESS THAN (2147483646));
1425 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'
1426 (PARTITION part1 VALUES LESS THAN (10),
1427 PARTITION part2 VALUES LESS THAN (2' at line 9
1428 CREATE TABLE t1 (
1429 f_int1 INTEGER,
1430 f_int2 INTEGER,
1431 f_char1 CHAR(20),
1432 f_char2 CHAR(20),
1433 f_charbig VARCHAR(1000)
1434 )
1435 PARTITION BY HASH(f_int1) PARTITIONS '';
1436 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 8
1437 CREATE TABLE t1 (
1438 f_int1 INTEGER,
1439 f_int2 INTEGER,
1440 f_char1 CHAR(20),
1441 f_char2 CHAR(20),
1442 f_charbig VARCHAR(1000)
1443 )
1444 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1445 SUBPARTITIONS ''
1446 (PARTITION part1 VALUES LESS THAN (10),
1447 PARTITION part2 VALUES LESS THAN (2147483646));
1448 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''
1449 (PARTITION part1 VALUES LESS THAN (10),
1450 PARTITION part2 VALUES LESS THAN (214' at line 9
1451 CREATE TABLE t1 (
1452 f_int1 INTEGER,
1453 f_int2 INTEGER,
1454 f_char1 CHAR(20),
1455 f_char2 CHAR(20),
1456 f_charbig VARCHAR(1000)
1457 )
1458 PARTITION BY HASH(f_int1) PARTITIONS 'GARBAGE';
1459 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 8
1460 CREATE TABLE t1 (
1461 f_int1 INTEGER,
1462 f_int2 INTEGER,
1463 f_char1 CHAR(20),
1464 f_char2 CHAR(20),
1465 f_charbig VARCHAR(1000)
1466 )
1467 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1468 SUBPARTITIONS 'GARBAGE'
1469 (PARTITION part1 VALUES LESS THAN (10),
1470 PARTITION part2 VALUES LESS THAN (2147483646));
1471 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'
1472 (PARTITION part1 VALUES LESS THAN (10),
1473 PARTITION part2 VALUES LESS TH' at line 9
1474 # 4.2.5 partition/subpartition numbers other notations
1475 CREATE TABLE t1 (
1476 f_int1 INTEGER,
1477 f_int2 INTEGER,
1478 f_char1 CHAR(20),
1479 f_char2 CHAR(20),
1480 f_charbig VARCHAR(1000)
1481 )
1482 PARTITION BY HASH(f_int1) PARTITIONS 2A;
1483 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A' at line 8
1484 CREATE TABLE t1 (
1485 f_int1 INTEGER,
1486 f_int2 INTEGER,
1487 f_char1 CHAR(20),
1488 f_char2 CHAR(20),
1489 f_charbig VARCHAR(1000)
1490 )
1491 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1492 SUBPARTITIONS 2A
1493 (PARTITION part1 VALUES LESS THAN (10),
1494 PARTITION part2 VALUES LESS THAN (2147483646));
1495 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A
1496 (PARTITION part1 VALUES LESS THAN (10),
1497 PARTITION part2 VALUES LESS THAN (214' at line 9
1498 CREATE TABLE t1 (
1499 f_int1 INTEGER,
1500 f_int2 INTEGER,
1501 f_char1 CHAR(20),
1502 f_char2 CHAR(20),
1503 f_charbig VARCHAR(1000)
1504 )
1505 PARTITION BY HASH(f_int1) PARTITIONS A2;
1506 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 8
1507 CREATE TABLE t1 (
1508 f_int1 INTEGER,
1509 f_int2 INTEGER,
1510 f_char1 CHAR(20),
1511 f_char2 CHAR(20),
1512 f_charbig VARCHAR(1000)
1513 )
1514 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1515 SUBPARTITIONS A2
1516 (PARTITION part1 VALUES LESS THAN (10),
1517 PARTITION part2 VALUES LESS THAN (2147483646));
1518 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2
1519 (PARTITION part1 VALUES LESS THAN (10),
1520 PARTITION part2 VALUES LESS THAN (214' at line 9
1521 CREATE TABLE t1 (
1522 f_int1 INTEGER,
1523 f_int2 INTEGER,
1524 f_char1 CHAR(20),
1525 f_char2 CHAR(20),
1526 f_charbig VARCHAR(1000)
1527 )
1528 PARTITION BY HASH(f_int1) PARTITIONS GARBAGE;
1529 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 8
1530 CREATE TABLE t1 (
1531 f_int1 INTEGER,
1532 f_int2 INTEGER,
1533 f_char1 CHAR(20),
1534 f_char2 CHAR(20),
1535 f_charbig VARCHAR(1000)
1536 )
1537 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1538 SUBPARTITIONS GARBAGE
1539 (PARTITION part1 VALUES LESS THAN (10),
1540 PARTITION part2 VALUES LESS THAN (2147483646));
1541 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE
1542 (PARTITION part1 VALUES LESS THAN (10),
1543 PARTITION part2 VALUES LESS THAN' at line 9
1544 CREATE TABLE t1 (
1545 f_int1 INTEGER,
1546 f_int2 INTEGER,
1547 f_char1 CHAR(20),
1548 f_char2 CHAR(20),
1549 f_charbig VARCHAR(1000)
1550 )
1551 PARTITION BY HASH(f_int1) PARTITIONS "2";
1552 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 8
1553 CREATE TABLE t1 (
1554 f_int1 INTEGER,
1555 f_int2 INTEGER,
1556 f_char1 CHAR(20),
1557 f_char2 CHAR(20),
1558 f_charbig VARCHAR(1000)
1559 )
1560 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1561 SUBPARTITIONS "2"
1562 (PARTITION part1 VALUES LESS THAN (10),
1563 PARTITION part2 VALUES LESS THAN (2147483646));
1564 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"
1565 (PARTITION part1 VALUES LESS THAN (10),
1566 PARTITION part2 VALUES LESS THAN (21' at line 9
1567 CREATE TABLE t1 (
1568 f_int1 INTEGER,
1569 f_int2 INTEGER,
1570 f_char1 CHAR(20),
1571 f_char2 CHAR(20),
1572 f_charbig VARCHAR(1000)
1573 )
1574 PARTITION BY HASH(f_int1) PARTITIONS "2A";
1575 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 8
1576 CREATE TABLE t1 (
1577 f_int1 INTEGER,
1578 f_int2 INTEGER,
1579 f_char1 CHAR(20),
1580 f_char2 CHAR(20),
1581 f_charbig VARCHAR(1000)
1582 )
1583 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1584 SUBPARTITIONS "2A"
1585 (PARTITION part1 VALUES LESS THAN (10),
1586 PARTITION part2 VALUES LESS THAN (2147483646));
1587 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"
1588 (PARTITION part1 VALUES LESS THAN (10),
1589 PARTITION part2 VALUES LESS THAN (2' at line 9
1590 CREATE TABLE t1 (
1591 f_int1 INTEGER,
1592 f_int2 INTEGER,
1593 f_char1 CHAR(20),
1594 f_char2 CHAR(20),
1595 f_charbig VARCHAR(1000)
1596 )
1597 PARTITION BY HASH(f_int1) PARTITIONS "A2";
1598 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 8
1599 CREATE TABLE t1 (
1600 f_int1 INTEGER,
1601 f_int2 INTEGER,
1602 f_char1 CHAR(20),
1603 f_char2 CHAR(20),
1604 f_charbig VARCHAR(1000)
1605 )
1606 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1607 SUBPARTITIONS "A2"
1608 (PARTITION part1 VALUES LESS THAN (10),
1609 PARTITION part2 VALUES LESS THAN (2147483646));
1610 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"
1611 (PARTITION part1 VALUES LESS THAN (10),
1612 PARTITION part2 VALUES LESS THAN (2' at line 9
1613 CREATE TABLE t1 (
1614 f_int1 INTEGER,
1615 f_int2 INTEGER,
1616 f_char1 CHAR(20),
1617 f_char2 CHAR(20),
1618 f_charbig VARCHAR(1000)
1619 )
1620 PARTITION BY HASH(f_int1) PARTITIONS "GARBAGE";
1621 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 8
1622 CREATE TABLE t1 (
1623 f_int1 INTEGER,
1624 f_int2 INTEGER,
1625 f_char1 CHAR(20),
1626 f_char2 CHAR(20),
1627 f_charbig VARCHAR(1000)
1628 )
1629 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1630 SUBPARTITIONS "GARBAGE"
1631 (PARTITION part1 VALUES LESS THAN (10),
1632 PARTITION part2 VALUES LESS THAN (2147483646));
1633 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"
1634 (PARTITION part1 VALUES LESS THAN (10),
1635 PARTITION part2 VALUES LESS TH' at line 9
1636 # 4.2.6 (negative) partition/subpartition numbers per @variables
1637 SET @aux = 5;
1638 CREATE TABLE t1 (
1639 f_int1 INTEGER,
1640 f_int2 INTEGER,
1641 f_char1 CHAR(20),
1642 f_char2 CHAR(20),
1643 f_charbig VARCHAR(1000)
1644 )
1645 PARTITION BY HASH(f_int1) PARTITIONS @aux;
1646 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aux' at line 8
1647 CREATE TABLE t1 (
1648 f_int1 INTEGER,
1649 f_int2 INTEGER,
1650 f_char1 CHAR(20),
1651 f_char2 CHAR(20),
1652 f_charbig VARCHAR(1000)
1653 )
1654 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1655 SUBPARTITIONS @aux = 5
1656 (PARTITION part1 VALUES LESS THAN (10),
1657 PARTITION part2 VALUES LESS THAN (2147483646));
1658 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aux = 5
1659 (PARTITION part1 VALUES LESS THAN (10),
1660 PARTITION part2 VALUES LESS THA' at line 9
1661 #------------------------------------------------------------------------
1662 # 4.3 Mixups of assigned partition/subpartition numbers and names
1663 #------------------------------------------------------------------------
1664 # 4.3.1 (positive) number of partition/subpartition
1665 # = number of named partition/subpartition
1666 CREATE TABLE t1 (
1667 f_int1 INTEGER,
1668 f_int2 INTEGER,
1669 f_char1 CHAR(20),
1670 f_char2 CHAR(20),
1671 f_charbig VARCHAR(1000)
1672 )
1673 PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
1674 create_command
1675 SHOW CREATE TABLE t1;
1676 Table Create Table
1677 t1 CREATE TABLE `t1` (
1678 `f_int1` int DEFAULT NULL,
1679 `f_int2` int DEFAULT NULL,
1680 `f_char1` char(20) DEFAULT NULL,
1681 `f_char2` char(20) DEFAULT NULL,
1682 `f_charbig` varchar(1000) DEFAULT NULL
1683 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1684 /*!50100 PARTITION BY HASH (`f_int1`)
1685 (PARTITION part1 ENGINE = InnoDB,
1686 PARTITION part2 ENGINE = InnoDB) */
1687
1688 DROP TABLE t1;
1689 CREATE TABLE t1 (
1690 f_int1 INTEGER,
1691 f_int2 INTEGER,
1692 f_char1 CHAR(20),
1693 f_char2 CHAR(20),
1694 f_charbig VARCHAR(1000)
1695 )
1696 PARTITION BY RANGE(f_int1) PARTITIONS 2
1697 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1698 ( PARTITION part1 VALUES LESS THAN (1000)
1699 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1700 PARTITION part2 VALUES LESS THAN (2147483646)
1701 (SUBPARTITION subpart21, SUBPARTITION subpart22)
1702 );
1703 create_command
1704 SHOW CREATE TABLE t1;
1705 Table Create Table
1706 t1 CREATE TABLE `t1` (
1707 `f_int1` int DEFAULT NULL,
1708 `f_int2` int DEFAULT NULL,
1709 `f_char1` char(20) DEFAULT NULL,
1710 `f_char2` char(20) DEFAULT NULL,
1711 `f_charbig` varchar(1000) DEFAULT NULL
1712 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1713 /*!50100 PARTITION BY RANGE (`f_int1`)
1714 SUBPARTITION BY HASH (`f_int1`)
1715 (PARTITION part1 VALUES LESS THAN (1000)
1716 (SUBPARTITION subpart11 ENGINE = InnoDB,
1717 SUBPARTITION subpart12 ENGINE = InnoDB),
1718 PARTITION part2 VALUES LESS THAN (2147483646)
1719 (SUBPARTITION subpart21 ENGINE = InnoDB,
1720 SUBPARTITION subpart22 ENGINE = InnoDB)) */
1721
1722 DROP TABLE t1;
1723 # 4.3.2 (positive) number of partition/subpartition ,
1724 # 0 (= no) named partition/subpartition
1725 # already checked above
1726 # 4.3.3 (negative) number of partitions/subpartitions
1727 # > number of named partitions/subpartitions
1728 CREATE TABLE t1 (
1729 f_int1 INTEGER,
1730 f_int2 INTEGER,
1731 f_char1 CHAR(20),
1732 f_char2 CHAR(20),
1733 f_charbig VARCHAR(1000)
1734 )
1735 PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1 ) ;
1736 ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near '( PARTITION part1 )' at line 8
1737 CREATE TABLE t1 (
1738 f_int1 INTEGER,
1739 f_int2 INTEGER,
1740 f_char1 CHAR(20),
1741 f_char2 CHAR(20),
1742 f_charbig VARCHAR(1000)
1743 )
1744 PARTITION BY RANGE(f_int1)
1745 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1746 ( PARTITION part1 VALUES LESS THAN (1000)
1747 (SUBPARTITION subpart11 ),
1748 PARTITION part2 VALUES LESS THAN (2147483646)
1749 (SUBPARTITION subpart21, SUBPARTITION subpart22)
1750 );
1751 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '(SUBPARTITION subpart11 ),
1752 PARTITION part2 VALUES LESS THAN (2147483646)
1753 (SUBPAR' at line 11
1754 CREATE TABLE t1 (
1755 f_int1 INTEGER,
1756 f_int2 INTEGER,
1757 f_char1 CHAR(20),
1758 f_char2 CHAR(20),
1759 f_charbig VARCHAR(1000)
1760 )
1761 PARTITION BY RANGE(f_int1)
1762 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1763 ( PARTITION part1 VALUES LESS THAN (1000)
1764 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1765 PARTITION part2 VALUES LESS THAN (2000)
1766 (SUBPARTITION subpart21 ),
1767 PARTITION part3 VALUES LESS THAN (2147483646)
1768 (SUBPARTITION subpart31, SUBPARTITION subpart32)
1769 );
1770 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '(SUBPARTITION subpart21 ),
1771 PARTITION part3 VALUES LESS THAN (2147483646)
1772 (SUBPAR' at line 13
1773 CREATE TABLE t1 (
1774 f_int1 INTEGER,
1775 f_int2 INTEGER,
1776 f_char1 CHAR(20),
1777 f_char2 CHAR(20),
1778 f_charbig VARCHAR(1000)
1779 )
1780 PARTITION BY RANGE(f_int1) PARTITIONS 2
1781 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1782 ( PARTITION part1 VALUES LESS THAN (1000)
1783 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1784 PARTITION part2 VALUES LESS THAN (2147483646)
1785 (SUBPARTITION subpart21 )
1786 );
1787 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '(SUBPARTITION subpart21 )
1788 )' at line 13
1789 # 4.3.4 (negative) number of partitions < number of named partitions
1790 CREATE TABLE t1 (
1791 f_int1 INTEGER,
1792 f_int2 INTEGER,
1793 f_char1 CHAR(20),
1794 f_char2 CHAR(20),
1795 f_charbig VARCHAR(1000)
1796 )
1797 PARTITION BY HASH(f_int1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
1798 ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near '( PARTITION part1, PARTITION part2 )' at line 8
1799 CREATE TABLE t1 (
1800 f_int1 INTEGER,
1801 f_int2 INTEGER,
1802 f_char1 CHAR(20),
1803 f_char2 CHAR(20),
1804 f_charbig VARCHAR(1000)
1805 )
1806 PARTITION BY RANGE(f_int1)
1807 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1808 ( PARTITION part1 VALUES LESS THAN (1000)
1809 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1810 PARTITION part2 VALUES LESS THAN (2147483646)
1811 (SUBPARTITION subpart21, SUBPARTITION subpart22)
1812 );
1813 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '(SUBPARTITION subpart11, SUBPARTITION subpart12),
1814 PARTITION part2 VALUES LESS TH' at line 11
1815 CREATE TABLE t1 (
1816 f_int1 INTEGER,
1817 f_int2 INTEGER,
1818 f_char1 CHAR(20),
1819 f_char2 CHAR(20),
1820 f_charbig VARCHAR(1000)
1821 )
1822 PARTITION BY RANGE(f_int1)
1823 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1824 ( PARTITION part1 VALUES LESS THAN (1000)
1825 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1826 PARTITION part2 VALUES LESS THAN (2000)
1827 (SUBPARTITION subpart21 ),
1828 PARTITION part3 VALUES LESS THAN (2147483646)
1829 (SUBPARTITION subpart31, SUBPARTITION subpart32)
1830 );
1831 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '(SUBPARTITION subpart11, SUBPARTITION subpart12),
1832 PARTITION part2 VALUES LESS TH' at line 11
1833 CREATE TABLE t1 (
1834 f_int1 INTEGER,
1835 f_int2 INTEGER,
1836 f_char1 CHAR(20),
1837 f_char2 CHAR(20),
1838 f_charbig VARCHAR(1000)
1839 )
1840 PARTITION BY RANGE(f_int1)
1841 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1842 ( PARTITION part1 VALUES LESS THAN (1000)
1843 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1844 PARTITION part2 VALUES LESS THAN (2147483646)
1845 (SUBPARTITION subpart21, SUBPARTITION subpart22)
1846 );
1847 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '(SUBPARTITION subpart11, SUBPARTITION subpart12),
1848 PARTITION part2 VALUES LESS TH' at line 11
1849
1850 #========================================================================
1851 # 5. Checks of logical partition/subpartition name
1852 # file name clashes during CREATE TABLE
1853 #========================================================================
1854 DROP TABLE IF EXISTS t1;
1855 #------------------------------------------------------------------------
1856 # 5.1 (negative) A partition/subpartition name used more than once
1857 #------------------------------------------------------------------------
1858 # 5.1.1 duplicate partition name
1859 CREATE TABLE t1 (
1860 f_int1 INTEGER,
1861 f_int2 INTEGER,
1862 f_char1 CHAR(20),
1863 f_char2 CHAR(20),
1864 f_charbig VARCHAR(1000)
1865 )
1866 PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part1);
1867 ERROR HY000: Duplicate partition name part1
1868 # 5.1.2 duplicate subpartition name
1869 CREATE TABLE t1 (
1870 f_int1 INTEGER,
1871 f_int2 INTEGER,
1872 f_char1 CHAR(20),
1873 f_char2 CHAR(20),
1874 f_charbig VARCHAR(1000)
1875 )
1876 PARTITION BY RANGE(f_int1)
1877 SUBPARTITION BY HASH(f_int1)
1878 ( PARTITION part1 VALUES LESS THAN (1000)
1879 (SUBPARTITION subpart11, SUBPARTITION subpart11)
1880 );
1881 ERROR HY000: Duplicate partition name subpart11
1882 DROP VIEW IF EXISTS v1;
1883 DROP TABLE IF EXISTS t1;
1884 DROP TABLE IF EXISTS t0_aux;
1885 DROP TABLE IF EXISTS t0_definition;
1886 DROP TABLE IF EXISTS t0_template;