Debian Codegraph @

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

raw
1 SET @max_row = 20;
2 SET @@session.default_storage_engine = 'MyISAM';
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 DEFAULT 0,
15 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
31 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
52 f_int2 INTEGER DEFAULT 0,
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
60 CREATE TABLE t1 (
61 f_int1 INTEGER DEFAULT 0,
62 f_int2 INTEGER DEFAULT 0,
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
70 CREATE TABLE t1 (
71 f_int1 INTEGER DEFAULT 0,
72 f_int2 INTEGER DEFAULT 0,
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
80 CREATE TABLE t1 (
81 f_int1 INTEGER DEFAULT 0,
82 f_int2 INTEGER DEFAULT 0,
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
90 CREATE TABLE t1 (
91 f_int1 INTEGER DEFAULT 0,
92 f_int2 INTEGER DEFAULT 0,
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
101 CREATE TABLE t1 (
102 f_int1 INTEGER DEFAULT 0,
103 f_int2 INTEGER DEFAULT 0,
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
112 CREATE TABLE t1 (
113 f_int1 INTEGER DEFAULT 0,
114 f_int2 INTEGER DEFAULT 0,
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
123 CREATE TABLE t1 (
124 f_int1 INTEGER DEFAULT 0,
125 f_int2 INTEGER DEFAULT 0,
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
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 DEFAULT 0,
144 f_int2 INTEGER DEFAULT 0,
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
152 CREATE TABLE t1 (
153 f_int1 INTEGER DEFAULT 0,
154 f_int2 INTEGER DEFAULT 0,
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
162 CREATE TABLE t1 (
163 f_int1 INTEGER DEFAULT 0,
164 f_int2 INTEGER DEFAULT 0,
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
172 CREATE TABLE t1 (
173 f_int1 INTEGER DEFAULT 0,
174 f_int2 INTEGER DEFAULT 0,
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
182 CREATE TABLE t1 (
183 f_int1 INTEGER DEFAULT 0,
184 f_int2 INTEGER DEFAULT 0,
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
192 CREATE TABLE t1 (
193 f_int1 INTEGER DEFAULT 0,
194 f_int2 INTEGER DEFAULT 0,
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
202 CREATE TABLE t1 (
203 f_int1 INTEGER DEFAULT 0,
204 f_int2 INTEGER DEFAULT 0,
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
212 CREATE TABLE t1 (
213 f_int1 INTEGER DEFAULT 0,
214 f_int2 INTEGER DEFAULT 0,
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
222 CREATE TABLE t1 (
223 f_int1 INTEGER DEFAULT 0,
224 f_int2 INTEGER DEFAULT 0,
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
236 CREATE TABLE t1 (
237 f_int1 INTEGER DEFAULT 0,
238 f_int2 INTEGER DEFAULT 0,
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
250 CREATE TABLE t1 (
251 f_int1 INTEGER DEFAULT 0,
252 f_int2 INTEGER DEFAULT 0,
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
264 CREATE TABLE t1 (
265 f_int1 INTEGER DEFAULT 0,
266 f_int2 INTEGER DEFAULT 0,
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
278 CREATE TABLE t1 (
279 f_int1 INTEGER DEFAULT 0,
280 f_int2 INTEGER DEFAULT 0,
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
290 CREATE TABLE t1 (
291 f_int1 INTEGER DEFAULT 0,
292 f_int2 INTEGER DEFAULT 0,
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
302 CREATE TABLE t1 (
303 f_int1 INTEGER DEFAULT 0,
304 f_int2 INTEGER DEFAULT 0,
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
314 CREATE TABLE t1 (
315 f_int1 INTEGER DEFAULT 0,
316 f_int2 INTEGER DEFAULT 0,
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
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 DEFAULT 0,
332 f_int2 INTEGER DEFAULT 0,
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
342 CREATE TABLE t1 (
343 f_int1 INTEGER DEFAULT 0,
344 f_int2 INTEGER DEFAULT 0,
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
354 CREATE TABLE t1 (
355 f_int1 INTEGER DEFAULT 0,
356 f_int2 INTEGER DEFAULT 0,
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
366 CREATE TABLE t1 (
367 f_int1 INTEGER DEFAULT 0,
368 f_int2 INTEGER DEFAULT 0,
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
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 DEFAULT 0,
386 f_int2 INTEGER DEFAULT 0,
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
397 CREATE TABLE t1 (
398 f_int1 INTEGER DEFAULT 0,
399 f_int2 INTEGER DEFAULT 0,
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
410 CREATE TABLE t1 (
411 f_int1 INTEGER DEFAULT 0,
412 f_int2 INTEGER DEFAULT 0,
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
423 CREATE TABLE t1 (
424 f_int1 INTEGER DEFAULT 0,
425 f_int2 INTEGER DEFAULT 0,
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
436 CREATE TABLE t1 (
437 f_int1 INTEGER DEFAULT 0,
438 f_int2 INTEGER DEFAULT 0,
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
451 CREATE TABLE t1 (
452 f_int1 INTEGER DEFAULT 0,
453 f_int2 INTEGER DEFAULT 0,
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
466 CREATE TABLE t1 (
467 f_int1 INTEGER DEFAULT 0,
468 f_int2 INTEGER DEFAULT 0,
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
481 CREATE TABLE t1 (
482 f_int1 INTEGER DEFAULT 0,
483 f_int2 INTEGER DEFAULT 0,
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
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 DEFAULT 0,
506 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
521 f_int2 INTEGER DEFAULT 0,
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 '))' 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 DEFAULT 0,
543 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
573 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
588 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
605 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
617 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
630 f_int2 INTEGER DEFAULT 0,
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 DEFAULT 0,
643 f_int2 INTEGER DEFAULT 0,
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(11) DEFAULT 0,
656 `f_int2` int(11) DEFAULT 0,
657 `f_char1` char(20) DEFAULT NULL,
658 `f_char2` char(20) DEFAULT NULL,
659 `f_charbig` varchar(1000) DEFAULT NULL
660 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
661 PARTITION BY LIST (`f_int1` MOD 2)
662 (PARTITION `part1` VALUES IN (NULL) ENGINE = MyISAM,
663 PARTITION `part3` VALUES IN (1) ENGINE = MyISAM)
664
665 unified filelist
666 t1#P#part1.MYD
667 t1#P#part1.MYI
668 t1#P#part3.MYD
669 t1#P#part3.MYI
670 t1.frm
671 t1.par
672
673 DROP TABLE t1;
674 # 3.5.3 Reveal that IN (...NULL) is not mapped to IN(0)
675 CREATE TABLE t1 (
676 f_int1 INTEGER DEFAULT 0,
677 f_int2 INTEGER DEFAULT 0,
678 f_char1 CHAR(20),
679 f_char2 CHAR(20),
680 f_charbig VARCHAR(1000)
681 )
682 PARTITION BY LIST(MOD(f_int1,2))
683 ( PARTITION part1 VALUES IN (NULL),
684 PARTITION part2 VALUES IN (0),
685 PARTITION part3 VALUES IN (1));
686 create_command
687 SHOW CREATE TABLE t1;
688 Table Create Table
689 t1 CREATE TABLE `t1` (
690 `f_int1` int(11) DEFAULT 0,
691 `f_int2` int(11) DEFAULT 0,
692 `f_char1` char(20) DEFAULT NULL,
693 `f_char2` char(20) DEFAULT NULL,
694 `f_charbig` varchar(1000) DEFAULT NULL
695 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
696 PARTITION BY LIST (`f_int1` MOD 2)
697 (PARTITION `part1` VALUES IN (NULL) ENGINE = MyISAM,
698 PARTITION `part2` VALUES IN (0) ENGINE = MyISAM,
699 PARTITION `part3` VALUES IN (1) ENGINE = MyISAM)
700
701 unified filelist
702 t1#P#part1.MYD
703 t1#P#part1.MYI
704 t1#P#part2.MYD
705 t1#P#part2.MYI
706 t1#P#part3.MYD
707 t1#P#part3.MYI
708 t1.frm
709 t1.par
710
711 DROP TABLE t1;
712
713 #========================================================================
714 # 4. Check assigning the number of partitions and subpartitions
715 # with and without named partitions/subpartitions
716 #========================================================================
717 DROP TABLE IF EXISTS t1;
718 #------------------------------------------------------------------------
719 # 4.1 (positive) without partition/subpartition number assignment
720 #------------------------------------------------------------------------
721 # 4.1.1 no partition number, no named partitions
722 CREATE TABLE t1 (
723 f_int1 INTEGER DEFAULT 0,
724 f_int2 INTEGER DEFAULT 0,
725 f_char1 CHAR(20),
726 f_char2 CHAR(20),
727 f_charbig VARCHAR(1000)
728 )
729 PARTITION BY HASH(f_int1);
730 create_command
731 SHOW CREATE TABLE t1;
732 Table Create Table
733 t1 CREATE TABLE `t1` (
734 `f_int1` int(11) DEFAULT 0,
735 `f_int2` int(11) DEFAULT 0,
736 `f_char1` char(20) DEFAULT NULL,
737 `f_char2` char(20) DEFAULT NULL,
738 `f_charbig` varchar(1000) DEFAULT NULL
739 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
740 PARTITION BY HASH (`f_int1`)
741
742 unified filelist
743 t1#P#p0.MYD
744 t1#P#p0.MYI
745 t1.frm
746 t1.par
747
748 DROP TABLE t1;
749 # 4.1.2 no partition number, named partitions
750 CREATE TABLE t1 (
751 f_int1 INTEGER DEFAULT 0,
752 f_int2 INTEGER DEFAULT 0,
753 f_char1 CHAR(20),
754 f_char2 CHAR(20),
755 f_charbig VARCHAR(1000)
756 )
757 PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part2);
758 create_command
759 SHOW CREATE TABLE t1;
760 Table Create Table
761 t1 CREATE TABLE `t1` (
762 `f_int1` int(11) DEFAULT 0,
763 `f_int2` int(11) DEFAULT 0,
764 `f_char1` char(20) DEFAULT NULL,
765 `f_char2` char(20) DEFAULT NULL,
766 `f_charbig` varchar(1000) DEFAULT NULL
767 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
768 PARTITION BY HASH (`f_int1`)
769 (PARTITION `part1` ENGINE = MyISAM,
770 PARTITION `part2` ENGINE = MyISAM)
771
772 unified filelist
773 t1#P#part1.MYD
774 t1#P#part1.MYI
775 t1#P#part2.MYD
776 t1#P#part2.MYI
777 t1.frm
778 t1.par
779
780 DROP TABLE t1;
781 # 4.1.3 variations on no partition/subpartition number, named partitions,
782 # different subpartitions are/are not named
783 CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0,
784 f_int2 INTEGER DEFAULT 0,
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), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ;
789 DROP TABLE t1;
790 CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0,
791 f_int2 INTEGER DEFAULT 0,
792 f_char1 CHAR(20),
793 f_char2 CHAR(20),
794 f_charbig VARCHAR(1000) )
795 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)
796 (SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
797 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart31 , SUBPARTITION subpart32 ))' at line 7
798 CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0,
799 f_int2 INTEGER DEFAULT 0,
800 f_char1 CHAR(20),
801 f_char2 CHAR(20),
802 f_charbig VARCHAR(1000) )
803 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20)
804 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ;
805 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LES...' at line 7
806 CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0,
807 f_int2 INTEGER DEFAULT 0,
808 f_char1 CHAR(20),
809 f_char2 CHAR(20),
810 f_charbig VARCHAR(1000) )
811 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20)
812 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)
813 (SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
814 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LES...' at line 7
815 CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0,
816 f_int2 INTEGER DEFAULT 0,
817 f_char1 CHAR(20),
818 f_char2 CHAR(20),
819 f_charbig VARCHAR(1000) )
820 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
821 (SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ;
822 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION part3 VALUES LESS THAN (2147483646))' at line 7
823 CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0,
824 f_int2 INTEGER DEFAULT 0,
825 f_char1 CHAR(20),
826 f_char2 CHAR(20),
827 f_charbig VARCHAR(1000) )
828 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
829 (SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)
830 (SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
831 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION part3 VALUES LESS THAN (2147483646)
832 (SUBPARTITION subpart31 , SUBP...' at line 7
833 CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0,
834 f_int2 INTEGER DEFAULT 0,
835 f_char1 CHAR(20),
836 f_char2 CHAR(20),
837 f_charbig VARCHAR(1000) )
838 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
839 (SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20)
840 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ;
841 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')' at line 8
842 CREATE TABLE t1 ( f_int1 INTEGER DEFAULT 0,
843 f_int2 INTEGER DEFAULT 0,
844 f_char1 CHAR(20),
845 f_char2 CHAR(20),
846 f_charbig VARCHAR(1000) )
847 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
848 (SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20)
849 (SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)
850 (SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
851 create_command
852 SHOW CREATE TABLE t1;
853 Table Create Table
854 t1 CREATE TABLE `t1` (
855 `f_int1` int(11) DEFAULT 0,
856 `f_int2` int(11) DEFAULT 0,
857 `f_char1` char(20) DEFAULT NULL,
858 `f_char2` char(20) DEFAULT NULL,
859 `f_charbig` varchar(1000) DEFAULT NULL
860 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
861 PARTITION BY RANGE (`f_int1`)
862 SUBPARTITION BY HASH (`f_int1`)
863 (PARTITION `part1` VALUES LESS THAN (10)
864 (SUBPARTITION `subpart11` ENGINE = MyISAM,
865 SUBPARTITION `subpart12` ENGINE = MyISAM),
866 PARTITION `part2` VALUES LESS THAN (20)
867 (SUBPARTITION `subpart21` ENGINE = MyISAM,
868 SUBPARTITION `subpart22` ENGINE = MyISAM),
869 PARTITION `part3` VALUES LESS THAN (2147483646)
870 (SUBPARTITION `subpart31` ENGINE = MyISAM,
871 SUBPARTITION `subpart32` ENGINE = MyISAM))
872
873 unified filelist
874 t1#P#part1#SP#subpart11.MYD
875 t1#P#part1#SP#subpart11.MYI
876 t1#P#part1#SP#subpart12.MYD
877 t1#P#part1#SP#subpart12.MYI
878 t1#P#part2#SP#subpart21.MYD
879 t1#P#part2#SP#subpart21.MYI
880 t1#P#part2#SP#subpart22.MYD
881 t1#P#part2#SP#subpart22.MYI
882 t1#P#part3#SP#subpart31.MYD
883 t1#P#part3#SP#subpart31.MYI
884 t1#P#part3#SP#subpart32.MYD
885 t1#P#part3#SP#subpart32.MYI
886 t1.frm
887 t1.par
888
889 DROP TABLE t1;
890 #------------------------------------------------------------------------
891 # 4.2 partition/subpartition numbers good and bad values and notations
892 #------------------------------------------------------------------------
893 DROP TABLE IF EXISTS t1;
894 # 4.2.1 partition/subpartition numbers INTEGER notation
895 CREATE TABLE t1 (
896 f_int1 INTEGER DEFAULT 0,
897 f_int2 INTEGER DEFAULT 0,
898 f_char1 CHAR(20),
899 f_char2 CHAR(20),
900 f_charbig VARCHAR(1000)
901 )
902 PARTITION BY HASH(f_int1) PARTITIONS 2;
903 create_command
904 SHOW CREATE TABLE t1;
905 Table Create Table
906 t1 CREATE TABLE `t1` (
907 `f_int1` int(11) DEFAULT 0,
908 `f_int2` int(11) DEFAULT 0,
909 `f_char1` char(20) DEFAULT NULL,
910 `f_char2` char(20) DEFAULT NULL,
911 `f_charbig` varchar(1000) DEFAULT NULL
912 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
913 PARTITION BY HASH (`f_int1`)
914 PARTITIONS 2
915
916 unified filelist
917 t1#P#p0.MYD
918 t1#P#p0.MYI
919 t1#P#p1.MYD
920 t1#P#p1.MYI
921 t1.frm
922 t1.par
923
924 DROP TABLE t1;
925 CREATE TABLE t1 (
926 f_int1 INTEGER DEFAULT 0,
927 f_int2 INTEGER DEFAULT 0,
928 f_char1 CHAR(20),
929 f_char2 CHAR(20),
930 f_charbig VARCHAR(1000)
931 )
932 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
933 SUBPARTITIONS 2
934 (PARTITION part1 VALUES LESS THAN (10),
935 PARTITION part2 VALUES LESS THAN (2147483646));
936 create_command
937 SHOW CREATE TABLE t1;
938 Table Create Table
939 t1 CREATE TABLE `t1` (
940 `f_int1` int(11) DEFAULT 0,
941 `f_int2` int(11) DEFAULT 0,
942 `f_char1` char(20) DEFAULT NULL,
943 `f_char2` char(20) DEFAULT NULL,
944 `f_charbig` varchar(1000) DEFAULT NULL
945 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
946 PARTITION BY RANGE (`f_int1`)
947 SUBPARTITION BY HASH (`f_int1`)
948 SUBPARTITIONS 2
949 (PARTITION `part1` VALUES LESS THAN (10) ENGINE = MyISAM,
950 PARTITION `part2` VALUES LESS THAN (2147483646) ENGINE = MyISAM)
951
952 unified filelist
953 t1#P#part1#SP#part1sp0.MYD
954 t1#P#part1#SP#part1sp0.MYI
955 t1#P#part1#SP#part1sp1.MYD
956 t1#P#part1#SP#part1sp1.MYI
957 t1#P#part2#SP#part2sp0.MYD
958 t1#P#part2#SP#part2sp0.MYI
959 t1#P#part2#SP#part2sp1.MYD
960 t1#P#part2#SP#part2sp1.MYI
961 t1.frm
962 t1.par
963
964 DROP TABLE t1;
965 CREATE TABLE t1 (
966 f_int1 INTEGER DEFAULT 0,
967 f_int2 INTEGER DEFAULT 0,
968 f_char1 CHAR(20),
969 f_char2 CHAR(20),
970 f_charbig VARCHAR(1000)
971 )
972 PARTITION BY HASH(f_int1) PARTITIONS 1;
973 create_command
974 SHOW CREATE TABLE t1;
975 Table Create Table
976 t1 CREATE TABLE `t1` (
977 `f_int1` int(11) DEFAULT 0,
978 `f_int2` int(11) DEFAULT 0,
979 `f_char1` char(20) DEFAULT NULL,
980 `f_char2` char(20) DEFAULT NULL,
981 `f_charbig` varchar(1000) DEFAULT NULL
982 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
983 PARTITION BY HASH (`f_int1`)
984 PARTITIONS 1
985
986 unified filelist
987 t1#P#p0.MYD
988 t1#P#p0.MYI
989 t1.frm
990 t1.par
991
992 DROP TABLE t1;
993 CREATE TABLE t1 (
994 f_int1 INTEGER DEFAULT 0,
995 f_int2 INTEGER DEFAULT 0,
996 f_char1 CHAR(20),
997 f_char2 CHAR(20),
998 f_charbig VARCHAR(1000)
999 )
1000 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1001 SUBPARTITIONS 1
1002 (PARTITION part1 VALUES LESS THAN (10),
1003 PARTITION part2 VALUES LESS THAN (2147483646));
1004 create_command
1005 SHOW CREATE TABLE t1;
1006 Table Create Table
1007 t1 CREATE TABLE `t1` (
1008 `f_int1` int(11) DEFAULT 0,
1009 `f_int2` int(11) DEFAULT 0,
1010 `f_char1` char(20) DEFAULT NULL,
1011 `f_char2` char(20) DEFAULT NULL,
1012 `f_charbig` varchar(1000) DEFAULT NULL
1013 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1014 PARTITION BY RANGE (`f_int1`)
1015 SUBPARTITION BY HASH (`f_int1`)
1016 SUBPARTITIONS 1
1017 (PARTITION `part1` VALUES LESS THAN (10) ENGINE = MyISAM,
1018 PARTITION `part2` VALUES LESS THAN (2147483646) ENGINE = MyISAM)
1019
1020 unified filelist
1021 t1#P#part1#SP#part1sp0.MYD
1022 t1#P#part1#SP#part1sp0.MYI
1023 t1#P#part2#SP#part2sp0.MYD
1024 t1#P#part2#SP#part2sp0.MYI
1025 t1.frm
1026 t1.par
1027
1028 DROP TABLE t1;
1029 CREATE TABLE t1 (
1030 f_int1 INTEGER DEFAULT 0,
1031 f_int2 INTEGER DEFAULT 0,
1032 f_char1 CHAR(20),
1033 f_char2 CHAR(20),
1034 f_charbig VARCHAR(1000)
1035 )
1036 PARTITION BY HASH(f_int1) PARTITIONS 0;
1037 ERROR HY000: Number of partitions = 0 is not an allowed value
1038 CREATE TABLE t1 (
1039 f_int1 INTEGER DEFAULT 0,
1040 f_int2 INTEGER DEFAULT 0,
1041 f_char1 CHAR(20),
1042 f_char2 CHAR(20),
1043 f_charbig VARCHAR(1000)
1044 )
1045 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1046 SUBPARTITIONS 0
1047 (PARTITION part1 VALUES LESS THAN (10),
1048 PARTITION part2 VALUES LESS THAN (2147483646));
1049 ERROR HY000: Number of subpartitions = 0 is not an allowed value
1050 CREATE TABLE t1 (
1051 f_int1 INTEGER DEFAULT 0,
1052 f_int2 INTEGER DEFAULT 0,
1053 f_char1 CHAR(20),
1054 f_char2 CHAR(20),
1055 f_charbig VARCHAR(1000)
1056 )
1057 PARTITION BY HASH(f_int1) PARTITIONS -1;
1058 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1' at line 8
1059 CREATE TABLE t1 (
1060 f_int1 INTEGER DEFAULT 0,
1061 f_int2 INTEGER DEFAULT 0,
1062 f_char1 CHAR(20),
1063 f_char2 CHAR(20),
1064 f_charbig VARCHAR(1000)
1065 )
1066 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1067 SUBPARTITIONS -1
1068 (PARTITION part1 VALUES LESS THAN (10),
1069 PARTITION part2 VALUES LESS THAN (2147483646));
1070 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1
1071 (PARTITION part1 VALUES LESS THAN (10),
1072 PARTITION part2 VALUES LESS THAN (...' at line 9
1073 CREATE TABLE t1 (
1074 f_int1 INTEGER DEFAULT 0,
1075 f_int2 INTEGER DEFAULT 0,
1076 f_char1 CHAR(20),
1077 f_char2 CHAR(20),
1078 f_charbig VARCHAR(1000)
1079 )
1080 PARTITION BY HASH(f_int1) PARTITIONS 1000000;
1081 ERROR HY000: Too many partitions (including subpartitions) were defined
1082 CREATE TABLE t1 (
1083 f_int1 INTEGER DEFAULT 0,
1084 f_int2 INTEGER DEFAULT 0,
1085 f_char1 CHAR(20),
1086 f_char2 CHAR(20),
1087 f_charbig VARCHAR(1000)
1088 )
1089 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1090 SUBPARTITIONS 1000000
1091 (PARTITION part1 VALUES LESS THAN (10),
1092 PARTITION part2 VALUES LESS THAN (2147483646));
1093 ERROR HY000: Too many partitions (including subpartitions) were defined
1094 # 4.2.2 partition/subpartition numbers DECIMAL notation
1095 CREATE TABLE t1 (
1096 f_int1 INTEGER DEFAULT 0,
1097 f_int2 INTEGER DEFAULT 0,
1098 f_char1 CHAR(20),
1099 f_char2 CHAR(20),
1100 f_charbig VARCHAR(1000)
1101 )
1102 PARTITION BY HASH(f_int1) PARTITIONS 2.0;
1103 ERROR 42000: Only integers allowed as number here near '2.0' at line 8
1104 CREATE TABLE t1 (
1105 f_int1 INTEGER DEFAULT 0,
1106 f_int2 INTEGER DEFAULT 0,
1107 f_char1 CHAR(20),
1108 f_char2 CHAR(20),
1109 f_charbig VARCHAR(1000)
1110 )
1111 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1112 SUBPARTITIONS 2.0
1113 (PARTITION part1 VALUES LESS THAN (10),
1114 PARTITION part2 VALUES LESS THAN (2147483646));
1115 ERROR 42000: Only integers allowed as number here near '2.0
1116 (PARTITION part1 VALUES LESS THAN (10),
1117 PARTITION part2 VALUES LESS THAN ...' at line 9
1118 CREATE TABLE t1 (
1119 f_int1 INTEGER DEFAULT 0,
1120 f_int2 INTEGER DEFAULT 0,
1121 f_char1 CHAR(20),
1122 f_char2 CHAR(20),
1123 f_charbig VARCHAR(1000)
1124 )
1125 PARTITION BY HASH(f_int1) PARTITIONS -2.0;
1126 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2.0' at line 8
1127 CREATE TABLE t1 (
1128 f_int1 INTEGER DEFAULT 0,
1129 f_int2 INTEGER DEFAULT 0,
1130 f_char1 CHAR(20),
1131 f_char2 CHAR(20),
1132 f_charbig VARCHAR(1000)
1133 )
1134 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1135 SUBPARTITIONS -2.0
1136 (PARTITION part1 VALUES LESS THAN (10),
1137 PARTITION part2 VALUES LESS THAN (2147483646));
1138 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2.0
1139 (PARTITION part1 VALUES LESS THAN (10),
1140 PARTITION part2 VALUES LESS THAN...' at line 9
1141 CREATE TABLE t1 (
1142 f_int1 INTEGER DEFAULT 0,
1143 f_int2 INTEGER DEFAULT 0,
1144 f_char1 CHAR(20),
1145 f_char2 CHAR(20),
1146 f_charbig VARCHAR(1000)
1147 )
1148 PARTITION BY HASH(f_int1) PARTITIONS 0.0;
1149 ERROR 42000: Only integers allowed as number here near '0.0' at line 8
1150 CREATE TABLE t1 (
1151 f_int1 INTEGER DEFAULT 0,
1152 f_int2 INTEGER DEFAULT 0,
1153 f_char1 CHAR(20),
1154 f_char2 CHAR(20),
1155 f_charbig VARCHAR(1000)
1156 )
1157 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1158 SUBPARTITIONS 0.0
1159 (PARTITION part1 VALUES LESS THAN (10),
1160 PARTITION part2 VALUES LESS THAN (2147483646));
1161 ERROR 42000: Only integers allowed as number here near '0.0
1162 (PARTITION part1 VALUES LESS THAN (10),
1163 PARTITION part2 VALUES LESS THAN ...' at line 9
1164 CREATE TABLE t1 (
1165 f_int1 INTEGER DEFAULT 0,
1166 f_int2 INTEGER DEFAULT 0,
1167 f_char1 CHAR(20),
1168 f_char2 CHAR(20),
1169 f_charbig VARCHAR(1000)
1170 )
1171 PARTITION BY HASH(f_int1) PARTITIONS 1.6;
1172 ERROR 42000: Only integers allowed as number here near '1.6' at line 8
1173 CREATE TABLE t1 (
1174 f_int1 INTEGER DEFAULT 0,
1175 f_int2 INTEGER DEFAULT 0,
1176 f_char1 CHAR(20),
1177 f_char2 CHAR(20),
1178 f_charbig VARCHAR(1000)
1179 )
1180 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1181 SUBPARTITIONS 1.6
1182 (PARTITION part1 VALUES LESS THAN (10),
1183 PARTITION part2 VALUES LESS THAN (2147483646));
1184 ERROR 42000: Only integers allowed as number here near '1.6
1185 (PARTITION part1 VALUES LESS THAN (10),
1186 PARTITION part2 VALUES LESS THAN ...' at line 9
1187 CREATE TABLE t1 (
1188 f_int1 INTEGER DEFAULT 0,
1189 f_int2 INTEGER DEFAULT 0,
1190 f_char1 CHAR(20),
1191 f_char2 CHAR(20),
1192 f_charbig VARCHAR(1000)
1193 )
1194 PARTITION BY HASH(f_int1) PARTITIONS 999999999999999999999999999999.999999999999999999999999999999;
1195 ERROR 42000: Only integers allowed as number here near '999999999999999999999999999999.999999999999999999999999999999' at line 8
1196 CREATE TABLE t1 (
1197 f_int1 INTEGER DEFAULT 0,
1198 f_int2 INTEGER DEFAULT 0,
1199 f_char1 CHAR(20),
1200 f_char2 CHAR(20),
1201 f_charbig VARCHAR(1000)
1202 )
1203 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1204 SUBPARTITIONS 999999999999999999999999999999.999999999999999999999999999999
1205 (PARTITION part1 VALUES LESS THAN (10),
1206 PARTITION part2 VALUES LESS THAN (2147483646));
1207 ERROR 42000: Only integers allowed as number here near '999999999999999999999999999999.999999999999999999999999999999
1208 (PARTITION part...' at line 9
1209 CREATE TABLE t1 (
1210 f_int1 INTEGER DEFAULT 0,
1211 f_int2 INTEGER DEFAULT 0,
1212 f_char1 CHAR(20),
1213 f_char2 CHAR(20),
1214 f_charbig VARCHAR(1000)
1215 )
1216 PARTITION BY HASH(f_int1) PARTITIONS 0.000000000000000000000000000001;
1217 ERROR 42000: Only integers allowed as number here near '0.000000000000000000000000000001' at line 8
1218 CREATE TABLE t1 (
1219 f_int1 INTEGER DEFAULT 0,
1220 f_int2 INTEGER DEFAULT 0,
1221 f_char1 CHAR(20),
1222 f_char2 CHAR(20),
1223 f_charbig VARCHAR(1000)
1224 )
1225 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1226 SUBPARTITIONS 0.000000000000000000000000000001
1227 (PARTITION part1 VALUES LESS THAN (10),
1228 PARTITION part2 VALUES LESS THAN (2147483646));
1229 ERROR 42000: Only integers allowed as number here near '0.000000000000000000000000000001
1230 (PARTITION part1 VALUES LESS THAN (10),
1231 PART...' at line 9
1232 # 4.2.3 partition/subpartition numbers FLOAT notation
1233 CREATE TABLE t1 (
1234 f_int1 INTEGER DEFAULT 0,
1235 f_int2 INTEGER DEFAULT 0,
1236 f_char1 CHAR(20),
1237 f_char2 CHAR(20),
1238 f_charbig VARCHAR(1000)
1239 )
1240 PARTITION BY HASH(f_int1) PARTITIONS 2.0E+0;
1241 ERROR 42000: Only integers allowed as number here near '2.0E+0' at line 8
1242 CREATE TABLE t1 (
1243 f_int1 INTEGER DEFAULT 0,
1244 f_int2 INTEGER DEFAULT 0,
1245 f_char1 CHAR(20),
1246 f_char2 CHAR(20),
1247 f_charbig VARCHAR(1000)
1248 )
1249 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1250 SUBPARTITIONS 2.0E+0
1251 (PARTITION part1 VALUES LESS THAN (10),
1252 PARTITION part2 VALUES LESS THAN (2147483646));
1253 ERROR 42000: Only integers allowed as number here near '2.0E+0
1254 (PARTITION part1 VALUES LESS THAN (10),
1255 PARTITION part2 VALUES LESS TH...' at line 9
1256 CREATE TABLE t1 (
1257 f_int1 INTEGER DEFAULT 0,
1258 f_int2 INTEGER DEFAULT 0,
1259 f_char1 CHAR(20),
1260 f_char2 CHAR(20),
1261 f_charbig VARCHAR(1000)
1262 )
1263 PARTITION BY HASH(f_int1) PARTITIONS 0.2E+1;
1264 ERROR 42000: Only integers allowed as number here near '0.2E+1' at line 8
1265 CREATE TABLE t1 (
1266 f_int1 INTEGER DEFAULT 0,
1267 f_int2 INTEGER DEFAULT 0,
1268 f_char1 CHAR(20),
1269 f_char2 CHAR(20),
1270 f_charbig VARCHAR(1000)
1271 )
1272 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1273 SUBPARTITIONS 0.2E+1
1274 (PARTITION part1 VALUES LESS THAN (10),
1275 PARTITION part2 VALUES LESS THAN (2147483646));
1276 ERROR 42000: Only integers allowed as number here near '0.2E+1
1277 (PARTITION part1 VALUES LESS THAN (10),
1278 PARTITION part2 VALUES LESS TH...' at line 9
1279 CREATE TABLE t1 (
1280 f_int1 INTEGER DEFAULT 0,
1281 f_int2 INTEGER DEFAULT 0,
1282 f_char1 CHAR(20),
1283 f_char2 CHAR(20),
1284 f_charbig VARCHAR(1000)
1285 )
1286 PARTITION BY HASH(f_int1) PARTITIONS -2.0E+0;
1287 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2.0E+0' at line 8
1288 CREATE TABLE t1 (
1289 f_int1 INTEGER DEFAULT 0,
1290 f_int2 INTEGER DEFAULT 0,
1291 f_char1 CHAR(20),
1292 f_char2 CHAR(20),
1293 f_charbig VARCHAR(1000)
1294 )
1295 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1296 SUBPARTITIONS -2.0E+0
1297 (PARTITION part1 VALUES LESS THAN (10),
1298 PARTITION part2 VALUES LESS THAN (2147483646));
1299 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2.0E+0
1300 (PARTITION part1 VALUES LESS THAN (10),
1301 PARTITION part2 VALUES LESS T...' at line 9
1302 CREATE TABLE t1 (
1303 f_int1 INTEGER DEFAULT 0,
1304 f_int2 INTEGER DEFAULT 0,
1305 f_char1 CHAR(20),
1306 f_char2 CHAR(20),
1307 f_charbig VARCHAR(1000)
1308 )
1309 PARTITION BY HASH(f_int1) PARTITIONS 0.16E+1;
1310 ERROR 42000: Only integers allowed as number here near '0.16E+1' at line 8
1311 CREATE TABLE t1 (
1312 f_int1 INTEGER DEFAULT 0,
1313 f_int2 INTEGER DEFAULT 0,
1314 f_char1 CHAR(20),
1315 f_char2 CHAR(20),
1316 f_charbig VARCHAR(1000)
1317 )
1318 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1319 SUBPARTITIONS 0.16E+1
1320 (PARTITION part1 VALUES LESS THAN (10),
1321 PARTITION part2 VALUES LESS THAN (2147483646));
1322 ERROR 42000: Only integers allowed as number here near '0.16E+1
1323 (PARTITION part1 VALUES LESS THAN (10),
1324 PARTITION part2 VALUES LESS T...' at line 9
1325 CREATE TABLE t1 (
1326 f_int1 INTEGER DEFAULT 0,
1327 f_int2 INTEGER DEFAULT 0,
1328 f_char1 CHAR(20),
1329 f_char2 CHAR(20),
1330 f_charbig VARCHAR(1000)
1331 )
1332 PARTITION BY HASH(f_int1) PARTITIONS 0.0E+300;
1333 ERROR 42000: Only integers allowed as number here near '0.0E+300' at line 8
1334 CREATE TABLE t1 (
1335 f_int1 INTEGER DEFAULT 0,
1336 f_int2 INTEGER DEFAULT 0,
1337 f_char1 CHAR(20),
1338 f_char2 CHAR(20),
1339 f_charbig VARCHAR(1000)
1340 )
1341 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1342 SUBPARTITIONS 0.0E+300
1343 (PARTITION part1 VALUES LESS THAN (10),
1344 PARTITION part2 VALUES LESS THAN (2147483646));
1345 ERROR 42000: Only integers allowed as number here near '0.0E+300
1346 (PARTITION part1 VALUES LESS THAN (10),
1347 PARTITION part2 VALUES LESS ...' at line 9
1348 CREATE TABLE t1 (
1349 f_int1 INTEGER DEFAULT 0,
1350 f_int2 INTEGER DEFAULT 0,
1351 f_char1 CHAR(20),
1352 f_char2 CHAR(20),
1353 f_charbig VARCHAR(1000)
1354 )
1355 PARTITION BY HASH(f_int1) PARTITIONS 1E+300;
1356 ERROR 42000: Only integers allowed as number here near '1E+300' at line 8
1357 CREATE TABLE t1 (
1358 f_int1 INTEGER DEFAULT 0,
1359 f_int2 INTEGER DEFAULT 0,
1360 f_char1 CHAR(20),
1361 f_char2 CHAR(20),
1362 f_charbig VARCHAR(1000)
1363 )
1364 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1365 SUBPARTITIONS 1E+300
1366 (PARTITION part1 VALUES LESS THAN (10),
1367 PARTITION part2 VALUES LESS THAN (2147483646));
1368 ERROR 42000: Only integers allowed as number here near '1E+300
1369 (PARTITION part1 VALUES LESS THAN (10),
1370 PARTITION part2 VALUES LESS TH...' at line 9
1371 CREATE TABLE t1 (
1372 f_int1 INTEGER DEFAULT 0,
1373 f_int2 INTEGER DEFAULT 0,
1374 f_char1 CHAR(20),
1375 f_char2 CHAR(20),
1376 f_charbig VARCHAR(1000)
1377 )
1378 PARTITION BY HASH(f_int1) PARTITIONS 1E-300;
1379 ERROR 42000: Only integers allowed as number here near '1E-300' at line 8
1380 CREATE TABLE t1 (
1381 f_int1 INTEGER DEFAULT 0,
1382 f_int2 INTEGER DEFAULT 0,
1383 f_char1 CHAR(20),
1384 f_char2 CHAR(20),
1385 f_charbig VARCHAR(1000)
1386 )
1387 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1388 SUBPARTITIONS 1E-300
1389 (PARTITION part1 VALUES LESS THAN (10),
1390 PARTITION part2 VALUES LESS THAN (2147483646));
1391 ERROR 42000: Only integers allowed as number here near '1E-300
1392 (PARTITION part1 VALUES LESS THAN (10),
1393 PARTITION part2 VALUES LESS TH...' at line 9
1394 # 4.2.4 partition/subpartition numbers STRING notation
1395 CREATE TABLE t1 (
1396 f_int1 INTEGER DEFAULT 0,
1397 f_int2 INTEGER DEFAULT 0,
1398 f_char1 CHAR(20),
1399 f_char2 CHAR(20),
1400 f_charbig VARCHAR(1000)
1401 )
1402 PARTITION BY HASH(f_int1) PARTITIONS '2';
1403 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2'' at line 8
1404 CREATE TABLE t1 (
1405 f_int1 INTEGER DEFAULT 0,
1406 f_int2 INTEGER DEFAULT 0,
1407 f_char1 CHAR(20),
1408 f_char2 CHAR(20),
1409 f_charbig VARCHAR(1000)
1410 )
1411 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1412 SUBPARTITIONS '2'
1413 (PARTITION part1 VALUES LESS THAN (10),
1414 PARTITION part2 VALUES LESS THAN (2147483646));
1415 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2'
1416 (PARTITION part1 VALUES LESS THAN (10),
1417 PARTITION part2 VALUES LESS THAN ...' at line 9
1418 CREATE TABLE t1 (
1419 f_int1 INTEGER DEFAULT 0,
1420 f_int2 INTEGER DEFAULT 0,
1421 f_char1 CHAR(20),
1422 f_char2 CHAR(20),
1423 f_charbig VARCHAR(1000)
1424 )
1425 PARTITION BY HASH(f_int1) PARTITIONS '2.0';
1426 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2.0'' at line 8
1427 CREATE TABLE t1 (
1428 f_int1 INTEGER DEFAULT 0,
1429 f_int2 INTEGER DEFAULT 0,
1430 f_char1 CHAR(20),
1431 f_char2 CHAR(20),
1432 f_charbig VARCHAR(1000)
1433 )
1434 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1435 SUBPARTITIONS '2.0'
1436 (PARTITION part1 VALUES LESS THAN (10),
1437 PARTITION part2 VALUES LESS THAN (2147483646));
1438 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2.0'
1439 (PARTITION part1 VALUES LESS THAN (10),
1440 PARTITION part2 VALUES LESS THA...' at line 9
1441 CREATE TABLE t1 (
1442 f_int1 INTEGER DEFAULT 0,
1443 f_int2 INTEGER DEFAULT 0,
1444 f_char1 CHAR(20),
1445 f_char2 CHAR(20),
1446 f_charbig VARCHAR(1000)
1447 )
1448 PARTITION BY HASH(f_int1) PARTITIONS '0.2E+1';
1449 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''0.2E+1'' at line 8
1450 CREATE TABLE t1 (
1451 f_int1 INTEGER DEFAULT 0,
1452 f_int2 INTEGER DEFAULT 0,
1453 f_char1 CHAR(20),
1454 f_char2 CHAR(20),
1455 f_charbig VARCHAR(1000)
1456 )
1457 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1458 SUBPARTITIONS '0.2E+1'
1459 (PARTITION part1 VALUES LESS THAN (10),
1460 PARTITION part2 VALUES LESS THAN (2147483646));
1461 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''0.2E+1'
1462 (PARTITION part1 VALUES LESS THAN (10),
1463 PARTITION part2 VALUES LESS ...' at line 9
1464 CREATE TABLE t1 (
1465 f_int1 INTEGER DEFAULT 0,
1466 f_int2 INTEGER DEFAULT 0,
1467 f_char1 CHAR(20),
1468 f_char2 CHAR(20),
1469 f_charbig VARCHAR(1000)
1470 )
1471 PARTITION BY HASH(f_int1) PARTITIONS '2A';
1472 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2A'' at line 8
1473 CREATE TABLE t1 (
1474 f_int1 INTEGER DEFAULT 0,
1475 f_int2 INTEGER DEFAULT 0,
1476 f_char1 CHAR(20),
1477 f_char2 CHAR(20),
1478 f_charbig VARCHAR(1000)
1479 )
1480 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1481 SUBPARTITIONS '2A'
1482 (PARTITION part1 VALUES LESS THAN (10),
1483 PARTITION part2 VALUES LESS THAN (2147483646));
1484 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2A'
1485 (PARTITION part1 VALUES LESS THAN (10),
1486 PARTITION part2 VALUES LESS THAN...' at line 9
1487 CREATE TABLE t1 (
1488 f_int1 INTEGER DEFAULT 0,
1489 f_int2 INTEGER DEFAULT 0,
1490 f_char1 CHAR(20),
1491 f_char2 CHAR(20),
1492 f_charbig VARCHAR(1000)
1493 )
1494 PARTITION BY HASH(f_int1) PARTITIONS 'A2';
1495 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''A2'' at line 8
1496 CREATE TABLE t1 (
1497 f_int1 INTEGER DEFAULT 0,
1498 f_int2 INTEGER DEFAULT 0,
1499 f_char1 CHAR(20),
1500 f_char2 CHAR(20),
1501 f_charbig VARCHAR(1000)
1502 )
1503 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1504 SUBPARTITIONS 'A2'
1505 (PARTITION part1 VALUES LESS THAN (10),
1506 PARTITION part2 VALUES LESS THAN (2147483646));
1507 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''A2'
1508 (PARTITION part1 VALUES LESS THAN (10),
1509 PARTITION part2 VALUES LESS THAN...' at line 9
1510 CREATE TABLE t1 (
1511 f_int1 INTEGER DEFAULT 0,
1512 f_int2 INTEGER DEFAULT 0,
1513 f_char1 CHAR(20),
1514 f_char2 CHAR(20),
1515 f_charbig VARCHAR(1000)
1516 )
1517 PARTITION BY HASH(f_int1) PARTITIONS '';
1518 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '''' at line 8
1519 CREATE TABLE t1 (
1520 f_int1 INTEGER DEFAULT 0,
1521 f_int2 INTEGER DEFAULT 0,
1522 f_char1 CHAR(20),
1523 f_char2 CHAR(20),
1524 f_charbig VARCHAR(1000)
1525 )
1526 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1527 SUBPARTITIONS ''
1528 (PARTITION part1 VALUES LESS THAN (10),
1529 PARTITION part2 VALUES LESS THAN (2147483646));
1530 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '''
1531 (PARTITION part1 VALUES LESS THAN (10),
1532 PARTITION part2 VALUES LESS THAN (...' at line 9
1533 CREATE TABLE t1 (
1534 f_int1 INTEGER DEFAULT 0,
1535 f_int2 INTEGER DEFAULT 0,
1536 f_char1 CHAR(20),
1537 f_char2 CHAR(20),
1538 f_charbig VARCHAR(1000)
1539 )
1540 PARTITION BY HASH(f_int1) PARTITIONS 'GARBAGE';
1541 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''GARBAGE'' at line 8
1542 CREATE TABLE t1 (
1543 f_int1 INTEGER DEFAULT 0,
1544 f_int2 INTEGER DEFAULT 0,
1545 f_char1 CHAR(20),
1546 f_char2 CHAR(20),
1547 f_charbig VARCHAR(1000)
1548 )
1549 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1550 SUBPARTITIONS 'GARBAGE'
1551 (PARTITION part1 VALUES LESS THAN (10),
1552 PARTITION part2 VALUES LESS THAN (2147483646));
1553 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''GARBAGE'
1554 (PARTITION part1 VALUES LESS THAN (10),
1555 PARTITION part2 VALUES LESS...' at line 9
1556 # 4.2.5 partition/subpartition numbers other notations
1557 CREATE TABLE t1 (
1558 f_int1 INTEGER DEFAULT 0,
1559 f_int2 INTEGER DEFAULT 0,
1560 f_char1 CHAR(20),
1561 f_char2 CHAR(20),
1562 f_charbig VARCHAR(1000)
1563 )
1564 PARTITION BY HASH(f_int1) PARTITIONS 2A;
1565 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2A' at line 8
1566 CREATE TABLE t1 (
1567 f_int1 INTEGER DEFAULT 0,
1568 f_int2 INTEGER DEFAULT 0,
1569 f_char1 CHAR(20),
1570 f_char2 CHAR(20),
1571 f_charbig VARCHAR(1000)
1572 )
1573 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1574 SUBPARTITIONS 2A
1575 (PARTITION part1 VALUES LESS THAN (10),
1576 PARTITION part2 VALUES LESS THAN (2147483646));
1577 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2A
1578 (PARTITION part1 VALUES LESS THAN (10),
1579 PARTITION part2 VALUES LESS THAN (...' at line 9
1580 CREATE TABLE t1 (
1581 f_int1 INTEGER DEFAULT 0,
1582 f_int2 INTEGER DEFAULT 0,
1583 f_char1 CHAR(20),
1584 f_char2 CHAR(20),
1585 f_charbig VARCHAR(1000)
1586 )
1587 PARTITION BY HASH(f_int1) PARTITIONS A2;
1588 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'A2' at line 8
1589 CREATE TABLE t1 (
1590 f_int1 INTEGER DEFAULT 0,
1591 f_int2 INTEGER DEFAULT 0,
1592 f_char1 CHAR(20),
1593 f_char2 CHAR(20),
1594 f_charbig VARCHAR(1000)
1595 )
1596 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1597 SUBPARTITIONS A2
1598 (PARTITION part1 VALUES LESS THAN (10),
1599 PARTITION part2 VALUES LESS THAN (2147483646));
1600 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'A2
1601 (PARTITION part1 VALUES LESS THAN (10),
1602 PARTITION part2 VALUES LESS THAN (...' at line 9
1603 CREATE TABLE t1 (
1604 f_int1 INTEGER DEFAULT 0,
1605 f_int2 INTEGER DEFAULT 0,
1606 f_char1 CHAR(20),
1607 f_char2 CHAR(20),
1608 f_charbig VARCHAR(1000)
1609 )
1610 PARTITION BY HASH(f_int1) PARTITIONS GARBAGE;
1611 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GARBAGE' at line 8
1612 CREATE TABLE t1 (
1613 f_int1 INTEGER DEFAULT 0,
1614 f_int2 INTEGER DEFAULT 0,
1615 f_char1 CHAR(20),
1616 f_char2 CHAR(20),
1617 f_charbig VARCHAR(1000)
1618 )
1619 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1620 SUBPARTITIONS GARBAGE
1621 (PARTITION part1 VALUES LESS THAN (10),
1622 PARTITION part2 VALUES LESS THAN (2147483646));
1623 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GARBAGE
1624 (PARTITION part1 VALUES LESS THAN (10),
1625 PARTITION part2 VALUES LESS T...' at line 9
1626 CREATE TABLE t1 (
1627 f_int1 INTEGER DEFAULT 0,
1628 f_int2 INTEGER DEFAULT 0,
1629 f_char1 CHAR(20),
1630 f_char2 CHAR(20),
1631 f_charbig VARCHAR(1000)
1632 )
1633 PARTITION BY HASH(f_int1) PARTITIONS "2";
1634 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"2"' at line 8
1635 CREATE TABLE t1 (
1636 f_int1 INTEGER DEFAULT 0,
1637 f_int2 INTEGER DEFAULT 0,
1638 f_char1 CHAR(20),
1639 f_char2 CHAR(20),
1640 f_charbig VARCHAR(1000)
1641 )
1642 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1643 SUBPARTITIONS "2"
1644 (PARTITION part1 VALUES LESS THAN (10),
1645 PARTITION part2 VALUES LESS THAN (2147483646));
1646 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"2"
1647 (PARTITION part1 VALUES LESS THAN (10),
1648 PARTITION part2 VALUES LESS THAN ...' at line 9
1649 CREATE TABLE t1 (
1650 f_int1 INTEGER DEFAULT 0,
1651 f_int2 INTEGER DEFAULT 0,
1652 f_char1 CHAR(20),
1653 f_char2 CHAR(20),
1654 f_charbig VARCHAR(1000)
1655 )
1656 PARTITION BY HASH(f_int1) PARTITIONS "2A";
1657 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"2A"' at line 8
1658 CREATE TABLE t1 (
1659 f_int1 INTEGER DEFAULT 0,
1660 f_int2 INTEGER DEFAULT 0,
1661 f_char1 CHAR(20),
1662 f_char2 CHAR(20),
1663 f_charbig VARCHAR(1000)
1664 )
1665 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1666 SUBPARTITIONS "2A"
1667 (PARTITION part1 VALUES LESS THAN (10),
1668 PARTITION part2 VALUES LESS THAN (2147483646));
1669 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"2A"
1670 (PARTITION part1 VALUES LESS THAN (10),
1671 PARTITION part2 VALUES LESS THAN...' at line 9
1672 CREATE TABLE t1 (
1673 f_int1 INTEGER DEFAULT 0,
1674 f_int2 INTEGER DEFAULT 0,
1675 f_char1 CHAR(20),
1676 f_char2 CHAR(20),
1677 f_charbig VARCHAR(1000)
1678 )
1679 PARTITION BY HASH(f_int1) PARTITIONS "A2";
1680 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"A2"' at line 8
1681 CREATE TABLE t1 (
1682 f_int1 INTEGER DEFAULT 0,
1683 f_int2 INTEGER DEFAULT 0,
1684 f_char1 CHAR(20),
1685 f_char2 CHAR(20),
1686 f_charbig VARCHAR(1000)
1687 )
1688 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1689 SUBPARTITIONS "A2"
1690 (PARTITION part1 VALUES LESS THAN (10),
1691 PARTITION part2 VALUES LESS THAN (2147483646));
1692 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"A2"
1693 (PARTITION part1 VALUES LESS THAN (10),
1694 PARTITION part2 VALUES LESS THAN...' at line 9
1695 CREATE TABLE t1 (
1696 f_int1 INTEGER DEFAULT 0,
1697 f_int2 INTEGER DEFAULT 0,
1698 f_char1 CHAR(20),
1699 f_char2 CHAR(20),
1700 f_charbig VARCHAR(1000)
1701 )
1702 PARTITION BY HASH(f_int1) PARTITIONS "GARBAGE";
1703 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"GARBAGE"' at line 8
1704 CREATE TABLE t1 (
1705 f_int1 INTEGER DEFAULT 0,
1706 f_int2 INTEGER DEFAULT 0,
1707 f_char1 CHAR(20),
1708 f_char2 CHAR(20),
1709 f_charbig VARCHAR(1000)
1710 )
1711 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1712 SUBPARTITIONS "GARBAGE"
1713 (PARTITION part1 VALUES LESS THAN (10),
1714 PARTITION part2 VALUES LESS THAN (2147483646));
1715 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"GARBAGE"
1716 (PARTITION part1 VALUES LESS THAN (10),
1717 PARTITION part2 VALUES LESS...' at line 9
1718 # 4.2.6 (negative) partition/subpartition numbers per @variables
1719 SET @aux = 5;
1720 CREATE TABLE t1 (
1721 f_int1 INTEGER DEFAULT 0,
1722 f_int2 INTEGER DEFAULT 0,
1723 f_char1 CHAR(20),
1724 f_char2 CHAR(20),
1725 f_charbig VARCHAR(1000)
1726 )
1727 PARTITION BY HASH(f_int1) PARTITIONS @aux;
1728 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@aux' at line 8
1729 CREATE TABLE t1 (
1730 f_int1 INTEGER DEFAULT 0,
1731 f_int2 INTEGER DEFAULT 0,
1732 f_char1 CHAR(20),
1733 f_char2 CHAR(20),
1734 f_charbig VARCHAR(1000)
1735 )
1736 PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1737 SUBPARTITIONS @aux = 5
1738 (PARTITION part1 VALUES LESS THAN (10),
1739 PARTITION part2 VALUES LESS THAN (2147483646));
1740 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@aux = 5
1741 (PARTITION part1 VALUES LESS THAN (10),
1742 PARTITION part2 VALUES LESS ...' at line 9
1743 #------------------------------------------------------------------------
1744 # 4.3 Mixups of assigned partition/subpartition numbers and names
1745 #------------------------------------------------------------------------
1746 # 4.3.1 (positive) number of partition/subpartition
1747 # = number of named partition/subpartition
1748 CREATE TABLE t1 (
1749 f_int1 INTEGER DEFAULT 0,
1750 f_int2 INTEGER DEFAULT 0,
1751 f_char1 CHAR(20),
1752 f_char2 CHAR(20),
1753 f_charbig VARCHAR(1000)
1754 )
1755 PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
1756 create_command
1757 SHOW CREATE TABLE t1;
1758 Table Create Table
1759 t1 CREATE TABLE `t1` (
1760 `f_int1` int(11) DEFAULT 0,
1761 `f_int2` int(11) DEFAULT 0,
1762 `f_char1` char(20) DEFAULT NULL,
1763 `f_char2` char(20) DEFAULT NULL,
1764 `f_charbig` varchar(1000) DEFAULT NULL
1765 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1766 PARTITION BY HASH (`f_int1`)
1767 (PARTITION `part1` ENGINE = MyISAM,
1768 PARTITION `part2` ENGINE = MyISAM)
1769
1770 unified filelist
1771 t1#P#part1.MYD
1772 t1#P#part1.MYI
1773 t1#P#part2.MYD
1774 t1#P#part2.MYI
1775 t1.frm
1776 t1.par
1777
1778 DROP TABLE t1;
1779 CREATE TABLE t1 (
1780 f_int1 INTEGER DEFAULT 0,
1781 f_int2 INTEGER DEFAULT 0,
1782 f_char1 CHAR(20),
1783 f_char2 CHAR(20),
1784 f_charbig VARCHAR(1000)
1785 )
1786 PARTITION BY RANGE(f_int1) PARTITIONS 2
1787 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1788 ( PARTITION part1 VALUES LESS THAN (1000)
1789 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1790 PARTITION part2 VALUES LESS THAN (2147483646)
1791 (SUBPARTITION subpart21, SUBPARTITION subpart22)
1792 );
1793 create_command
1794 SHOW CREATE TABLE t1;
1795 Table Create Table
1796 t1 CREATE TABLE `t1` (
1797 `f_int1` int(11) DEFAULT 0,
1798 `f_int2` int(11) DEFAULT 0,
1799 `f_char1` char(20) DEFAULT NULL,
1800 `f_char2` char(20) DEFAULT NULL,
1801 `f_charbig` varchar(1000) DEFAULT NULL
1802 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1803 PARTITION BY RANGE (`f_int1`)
1804 SUBPARTITION BY HASH (`f_int1`)
1805 (PARTITION `part1` VALUES LESS THAN (1000)
1806 (SUBPARTITION `subpart11` ENGINE = MyISAM,
1807 SUBPARTITION `subpart12` ENGINE = MyISAM),
1808 PARTITION `part2` VALUES LESS THAN (2147483646)
1809 (SUBPARTITION `subpart21` ENGINE = MyISAM,
1810 SUBPARTITION `subpart22` ENGINE = MyISAM))
1811
1812 unified filelist
1813 t1#P#part1#SP#subpart11.MYD
1814 t1#P#part1#SP#subpart11.MYI
1815 t1#P#part1#SP#subpart12.MYD
1816 t1#P#part1#SP#subpart12.MYI
1817 t1#P#part2#SP#subpart21.MYD
1818 t1#P#part2#SP#subpart21.MYI
1819 t1#P#part2#SP#subpart22.MYD
1820 t1#P#part2#SP#subpart22.MYI
1821 t1.frm
1822 t1.par
1823
1824 DROP TABLE t1;
1825 # 4.3.2 (positive) number of partition/subpartition ,
1826 # 0 (= no) named partition/subpartition
1827 # already checked above
1828 # 4.3.3 (negative) number of partitions/subpartitions
1829 # > number of named partitions/subpartitions
1830 CREATE TABLE t1 (
1831 f_int1 INTEGER DEFAULT 0,
1832 f_int2 INTEGER DEFAULT 0,
1833 f_char1 CHAR(20),
1834 f_char2 CHAR(20),
1835 f_charbig VARCHAR(1000)
1836 )
1837 PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1 ) ;
1838 ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8
1839 CREATE TABLE t1 (
1840 f_int1 INTEGER DEFAULT 0,
1841 f_int2 INTEGER DEFAULT 0,
1842 f_char1 CHAR(20),
1843 f_char2 CHAR(20),
1844 f_charbig VARCHAR(1000)
1845 )
1846 PARTITION BY RANGE(f_int1)
1847 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1848 ( PARTITION part1 VALUES LESS THAN (1000)
1849 (SUBPARTITION subpart11 ),
1850 PARTITION part2 VALUES LESS THAN (2147483646)
1851 (SUBPARTITION subpart21, SUBPARTITION subpart22)
1852 );
1853 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1854 PARTITION part2 VALUES LESS THAN (2147483646)
1855 (SUBPARTITION subpart21, SUB...' at line 11
1856 CREATE TABLE t1 (
1857 f_int1 INTEGER DEFAULT 0,
1858 f_int2 INTEGER DEFAULT 0,
1859 f_char1 CHAR(20),
1860 f_char2 CHAR(20),
1861 f_charbig VARCHAR(1000)
1862 )
1863 PARTITION BY RANGE(f_int1)
1864 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1865 ( PARTITION part1 VALUES LESS THAN (1000)
1866 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1867 PARTITION part2 VALUES LESS THAN (2000)
1868 (SUBPARTITION subpart21 ),
1869 PARTITION part3 VALUES LESS THAN (2147483646)
1870 (SUBPARTITION subpart31, SUBPARTITION subpart32)
1871 );
1872 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1873 PARTITION part3 VALUES LESS THAN (2147483646)
1874 (SUBPARTITION subpart31, SUB...' at line 13
1875 CREATE TABLE t1 (
1876 f_int1 INTEGER DEFAULT 0,
1877 f_int2 INTEGER DEFAULT 0,
1878 f_char1 CHAR(20),
1879 f_char2 CHAR(20),
1880 f_charbig VARCHAR(1000)
1881 )
1882 PARTITION BY RANGE(f_int1) PARTITIONS 2
1883 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1884 ( PARTITION part1 VALUES LESS THAN (1000)
1885 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1886 PARTITION part2 VALUES LESS THAN (2147483646)
1887 (SUBPARTITION subpart21 )
1888 );
1889 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')
1890 )' at line 13
1891 # 4.3.4 (negative) number of partitions < number of named partitions
1892 CREATE TABLE t1 (
1893 f_int1 INTEGER DEFAULT 0,
1894 f_int2 INTEGER DEFAULT 0,
1895 f_char1 CHAR(20),
1896 f_char2 CHAR(20),
1897 f_charbig VARCHAR(1000)
1898 )
1899 PARTITION BY HASH(f_int1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
1900 ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8
1901 CREATE TABLE t1 (
1902 f_int1 INTEGER DEFAULT 0,
1903 f_int2 INTEGER DEFAULT 0,
1904 f_char1 CHAR(20),
1905 f_char2 CHAR(20),
1906 f_charbig VARCHAR(1000)
1907 )
1908 PARTITION BY RANGE(f_int1)
1909 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1910 ( PARTITION part1 VALUES LESS THAN (1000)
1911 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1912 PARTITION part2 VALUES LESS THAN (2147483646)
1913 (SUBPARTITION subpart21, SUBPARTITION subpart22)
1914 );
1915 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1916 PARTITION part2 VALUES LESS THAN (2147483646)
1917 (SUBPARTITION subpart21, SUB...' at line 11
1918 CREATE TABLE t1 (
1919 f_int1 INTEGER DEFAULT 0,
1920 f_int2 INTEGER DEFAULT 0,
1921 f_char1 CHAR(20),
1922 f_char2 CHAR(20),
1923 f_charbig VARCHAR(1000)
1924 )
1925 PARTITION BY RANGE(f_int1)
1926 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1927 ( PARTITION part1 VALUES LESS THAN (1000)
1928 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1929 PARTITION part2 VALUES LESS THAN (2000)
1930 (SUBPARTITION subpart21 ),
1931 PARTITION part3 VALUES LESS THAN (2147483646)
1932 (SUBPARTITION subpart31, SUBPARTITION subpart32)
1933 );
1934 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1935 PARTITION part2 VALUES LESS THAN (2000)
1936 (SUBPARTITION subpart21 ...' at line 11
1937 CREATE TABLE t1 (
1938 f_int1 INTEGER DEFAULT 0,
1939 f_int2 INTEGER DEFAULT 0,
1940 f_char1 CHAR(20),
1941 f_char2 CHAR(20),
1942 f_charbig VARCHAR(1000)
1943 )
1944 PARTITION BY RANGE(f_int1)
1945 SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1946 ( PARTITION part1 VALUES LESS THAN (1000)
1947 (SUBPARTITION subpart11, SUBPARTITION subpart12),
1948 PARTITION part2 VALUES LESS THAN (2147483646)
1949 (SUBPARTITION subpart21, SUBPARTITION subpart22)
1950 );
1951 ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1952 PARTITION part2 VALUES LESS THAN (2147483646)
1953 (SUBPARTITION subpart21, SUB...' at line 11
1954
1955 #========================================================================
1956 # 5. Checks of logical partition/subpartition name
1957 # file name clashes during CREATE TABLE
1958 #========================================================================
1959 DROP TABLE IF EXISTS t1;
1960 #------------------------------------------------------------------------
1961 # 5.1 (negative) A partition/subpartition name used more than once
1962 #------------------------------------------------------------------------
1963 # 5.1.1 duplicate partition name
1964 CREATE TABLE t1 (
1965 f_int1 INTEGER DEFAULT 0,
1966 f_int2 INTEGER DEFAULT 0,
1967 f_char1 CHAR(20),
1968 f_char2 CHAR(20),
1969 f_charbig VARCHAR(1000)
1970 )
1971 PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part1);
1972 ERROR HY000: Duplicate partition name part1
1973 # 5.1.2 duplicate subpartition name
1974 CREATE TABLE t1 (
1975 f_int1 INTEGER DEFAULT 0,
1976 f_int2 INTEGER DEFAULT 0,
1977 f_char1 CHAR(20),
1978 f_char2 CHAR(20),
1979 f_charbig VARCHAR(1000)
1980 )
1981 PARTITION BY RANGE(f_int1)
1982 SUBPARTITION BY HASH(f_int1)
1983 ( PARTITION part1 VALUES LESS THAN (1000)
1984 (SUBPARTITION subpart11, SUBPARTITION subpart11)
1985 );
1986 ERROR HY000: Duplicate partition name subpart11
1987 DROP VIEW IF EXISTS v1;
1988 DROP TABLE IF EXISTS t1;
1989 DROP TABLE IF EXISTS t0_aux;
1990 DROP TABLE IF EXISTS t0_definition;
1991 DROP TABLE IF EXISTS t0_template;