1
################################################################################
2
# inc/partition_syntax.inc #
5
# Tests around Create partitioned tables syntax #
7
#------------------------------------------------------------------------------#
8
# Original Author: mleich #
9
# Original Date: 2006-03-05 #
13
################################################################################
15
# FIXME Implement testcases, where it is checked that all create and
16
# alter table statements
17
# - with missing mandatory parameters are rejected
18
# - with optional parameters are accepted
19
# - with wrong combinations of optional parameters are rejected
23
--echo #========================================================================
24
--echo # 1. Any PRIMARY KEYs or UNIQUE INDEXes must contain the columns used
25
--echo # within the partitioning functions
26
--echo #========================================================================
28
DROP TABLE IF EXISTS t1;
31
--echo #------------------------------------------------------------------------
32
--echo # 1.1 column of partitioning function not included in PRIMARY KEY
33
--echo # PARTITION BY HASH/KEY/LIST/RANGE
34
--echo #------------------------------------------------------------------------
35
#----------- PARTITION BY HASH
36
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
37
eval CREATE TABLE t1 (
41
PARTITION BY HASH(f_int1) PARTITIONS 2;
42
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
43
eval CREATE TABLE t1 (
47
PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
48
#----------- PARTITION BY KEY
49
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
50
eval CREATE TABLE t1 (
54
PARTITION BY KEY(f_int1) PARTITIONS 2;
55
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
56
eval CREATE TABLE t1 (
60
PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2;
61
#----------- PARTITION BY LIST
62
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
63
eval CREATE TABLE t1 (
67
PARTITION BY LIST(f_int1)
68
(PARTITION part1 VALUES IN (1));
69
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
70
eval CREATE TABLE t1 (
74
PARTITION BY LIST(f_int1 + f_int2)
75
(PARTITION part1 VALUES IN (1));
76
#----------- PARTITION BY RANGE
77
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
78
eval CREATE TABLE t1 (
82
PARTITION BY RANGE(f_int1)
83
(PARTITION part1 VALUES LESS THAN (1));
84
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
85
eval CREATE TABLE t1 (
89
PARTITION BY RANGE(f_int1 + f_int2)
90
(PARTITION part1 VALUES LESS THAN (1));
93
--echo #------------------------------------------------------------------------
94
--echo # 1.2 column of partitioning function not included in UNIQUE INDEX
95
--echo # PARTITION BY HASH/KEY/LIST/RANGE
96
--echo # Variant a) Without additional PRIMARY KEY
97
--echo # Variant b) With correct additional PRIMARY KEY
98
--echo # Variant 1) one column in partitioning function
99
--echo # Variant 2) two columns in partitioning function
100
--echo #------------------------------------------------------------------------
101
# Note: If the CREATE TABLE statement contains no PRIMARY KEY but
102
# UNIQUE INDEXes the MySQL layer tells the storage to use
103
# the first UNIQUE INDEX as PRIMARY KEY.
105
let $unique_index= UNIQUE INDEX (f_int2);
107
#----------- PARTITION BY HASH
108
let $partition_scheme= PARTITION BY HASH(f_int1) PARTITIONS 2;
109
--source suite/parts/inc/partition_syntax_2.inc
110
let $partition_scheme= PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2;
111
--source suite/parts/inc/partition_syntax_2.inc
112
#----------- PARTITION BY KEY
113
let $partition_scheme= PARTITION BY KEY(f_int1) PARTITIONS 2;
114
--source suite/parts/inc/partition_syntax_2.inc
115
let $partition_scheme= PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2;
116
--source suite/parts/inc/partition_syntax_2.inc
117
#----------- PARTITION BY LIST
118
let $partition_scheme= PARTITION BY LIST(MOD(f_int1,3))
119
(PARTITION partN VALUES IN (NULL),
120
PARTITION part0 VALUES IN (0),
121
PARTITION part1 VALUES IN (1),
122
PARTITION part2 VALUES IN (2));
123
--source suite/parts/inc/partition_syntax_2.inc
124
let $partition_scheme= PARTITION BY LIST(MOD(f_int1 + f_int2,3))
125
(PARTITION partN VALUES IN (NULL),
126
PARTITION part0 VALUES IN (0),
127
PARTITION part1 VALUES IN (1),
128
PARTITION part2 VALUES IN (2));
129
--source suite/parts/inc/partition_syntax_2.inc
130
#----------- PARTITION BY RANGE
131
let $partition_scheme= PARTITION BY RANGE(f_int1)
132
(PARTITION part1 VALUES LESS THAN (1),
133
PARTITION part2 VALUES LESS THAN (2147483646));
134
--source suite/parts/inc/partition_syntax_2.inc
135
let $partition_scheme= PARTITION BY RANGE(f_int1 + f_int2)
136
(PARTITION part1 VALUES LESS THAN (1),
137
PARTITION part2 VALUES LESS THAN (2147483646));
138
--source suite/parts/inc/partition_syntax_2.inc
141
--echo #------------------------------------------------------------------------
142
--echo # 1.3 column of subpartitioning function not included in PRIMARY KEY
143
--echo # PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY
144
--echo #------------------------------------------------------------------------
146
#----------- PARTITION BY RANGE -- SUBPARTITION BY HASH
147
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
148
eval CREATE TABLE t1 (
152
PARTITION BY RANGE(f_int2) SUBPARTITION BY HASH(f_int1)
153
(PARTITION part1 VALUES LESS THAN (1)
154
(SUBPARTITION subpart1));
155
#----------- PARTITION BY RANGE -- SUBPARTITION BY KEY
156
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
157
eval CREATE TABLE t1 (
161
PARTITION BY RANGE(f_int2) SUBPARTITION BY KEY(f_int1)
162
(PARTITION part1 VALUES LESS THAN (1)
163
(SUBPARTITION subpart1));
164
#----------- PARTITION BY LIST -- SUBPARTITION BY HASH
165
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
166
eval CREATE TABLE t1 (
170
PARTITION BY LIST(f_int2) SUBPARTITION BY HASH(f_int1)
171
(PARTITION part1 VALUES IN (1)
172
(SUBPARTITION subpart1));
173
#----------- PARTITION BY LIST -- SUBPARTITION BY KEY
174
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
175
eval CREATE TABLE t1 (
179
PARTITION BY LIST(f_int2) SUBPARTITION BY KEY(f_int1)
180
(PARTITION part1 VALUES IN (1)
181
(SUBPARTITION subpart1));
184
--echo #------------------------------------------------------------------------
185
--echo # 1.4 column of subpartitioning function not included in UNIQUE INDEX
186
--echo # PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY
187
--echo # Variant a) Without additional PRIMARY KEY
188
--echo # Variant b) With correct additional PRIMARY KEY
189
--echo #------------------------------------------------------------------------
190
let $partition_scheme= PARTITION BY RANGE(f_int2)
191
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 3
192
(PARTITION part1 VALUES LESS THAN (1),
193
PARTITION part2 VALUES LESS THAN (2147483646));
194
--source suite/parts/inc/partition_syntax_2.inc
195
#----------- PARTITION BY RANGE -- SUBPARTITION BY KEY
196
let $partition_scheme= PARTITION BY RANGE(f_int2)
197
SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 3
198
(PARTITION part1 VALUES LESS THAN (1),
199
PARTITION part2 VALUES LESS THAN (2147483646));
200
--source suite/parts/inc/partition_syntax_2.inc
201
#----------- PARTITION BY LIST -- SUBPARTITION BY HASH
202
let $partition_scheme= PARTITION BY LIST(MOD(f_int2,3))
203
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
204
(PARTITION partN VALUES IN (NULL),
205
PARTITION part0 VALUES IN (0),
206
PARTITION part1 VALUES IN (1),
207
PARTITION part2 VALUES IN (2));
208
--source suite/parts/inc/partition_syntax_2.inc
209
#----------- PARTITION BY LIST -- SUBPARTITION BY KEY
210
let $partition_scheme= PARTITION BY LIST(MOD(f_int2,3))
211
SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 2
212
(PARTITION partN VALUES IN (NULL),
213
PARTITION part0 VALUES IN (0),
214
PARTITION part1 VALUES IN (1),
215
PARTITION part2 VALUES IN (2));
216
--source suite/parts/inc/partition_syntax_2.inc
219
--echo #========================================================================
220
--echo # 2 Some properties around subpartitioning
221
--echo #========================================================================
222
--echo #------------------------------------------------------------------------
223
--echo # 2.1 Subpartioned table without subpartitioning rule must be rejected
224
--echo #------------------------------------------------------------------------
226
DROP TABLE IF EXISTS t1;
228
# Bug#15961 Partitions: Creation of subpart. table without subpart. rule not rejected
229
--error ER_SUBPARTITION_ERROR
230
eval CREATE TABLE t1 (
233
PARTITION BY RANGE(f_int1)
234
( PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11));
235
--echo #------------------------------------------------------------------------
236
--echo # 2.2 Every partition must have the same number of subpartitions.
237
--echo # This is a limitation of MySQL 5.1, which could be removed in
238
--echo # later releases.
239
--echo #------------------------------------------------------------------------
240
--error ER_PARSE_ERROR
241
eval CREATE TABLE t1 (
245
PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
247
PARTITION part1 VALUES LESS THAN (0)
248
(SUBPARTITION subpart1),
249
PARTITION part2 VALUES LESS THAN ($max_row_div4)
250
(SUBPARTITION subpart1, SUBPARTITION subpart2));
253
--echo #========================================================================
254
--echo # 3 VALUES clauses
255
--echo #========================================================================
256
--echo #------------------------------------------------------------------------
257
--echo # 3.1 The constants in VALUES IN clauses must differ
258
--echo #------------------------------------------------------------------------
259
--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
260
eval CREATE TABLE t1 (
263
PARTITION BY LIST(MOD(f_int1,2))
264
( PARTITION part1 VALUES IN (-1),
265
PARTITION part2 VALUES IN (0),
266
PARTITION part3 VALUES IN (-1));
267
# constant followed by the same constant
268
--error ER_RANGE_NOT_INCREASING_ERROR
269
CREATE TABLE t1 (f1 BIGINT, f2 BIGINT)
270
PARTITION BY RANGE(f1)
271
(PARTITION part1 VALUES LESS THAN (0),
272
PARTITION part2 VALUES LESS THAN (0),
273
PARTITION part3 VALUES LESS THAN (10000));
275
--echo #------------------------------------------------------------------------
276
--echo # 3.2 The constants in VALUES LESS must be in increasing order
277
--echo #------------------------------------------------------------------------
278
# constant followed somewhere by the smaller constant
279
--error ER_RANGE_NOT_INCREASING_ERROR
280
CREATE TABLE t1 (f1 BIGINT, f2 BIGINT)
281
PARTITION BY RANGE(f1)
282
(PARTITION part1 VALUES LESS THAN (0),
283
PARTITION part2 VALUES LESS THAN (-1),
284
PARTITION part3 VALUES LESS THAN (10000));
286
--echo #------------------------------------------------------------------------
287
--echo # 3.3 LIST partitions must be defined with VALUES IN
288
--echo #------------------------------------------------------------------------
289
--error ER_PARTITION_WRONG_VALUES_ERROR
290
eval CREATE TABLE t1 (
293
PARTITION BY LIST(MOD(f_int1,2))
294
( PARTITION part1 VALUES LESS THAN (-1),
295
PARTITION part2 VALUES LESS THAN (0),
296
PARTITION part3 VALUES LESS THAN (1000));
298
--echo #------------------------------------------------------------------------
299
--echo # 3.4 RANGE partitions must be defined with VALUES LESS THAN
300
--echo #------------------------------------------------------------------------
301
--error ER_PARTITION_WRONG_VALUES_ERROR
302
eval CREATE TABLE t1 (
305
PARTITION BY RANGE(f_int1)
306
( PARTITION part1 VALUES IN (-1),
307
PARTITION part2 VALUES IN (0),
308
PARTITION part3 VALUES IN (1000));
310
--echo #------------------------------------------------------------------------
311
--echo # 3.5 Use of NULL in VALUES clauses
312
--echo #------------------------------------------------------------------------
313
--echo # 3.5.1 NULL in RANGE partitioning clause
314
--echo # 3.5.1.1 VALUE LESS THAN (NULL) is not allowed
315
--error ER_PARSE_ERROR
316
eval CREATE TABLE t1 (
319
PARTITION BY RANGE(f_int1)
320
( PARTITION part1 VALUES LESS THAN (NULL),
321
PARTITION part2 VALUES LESS THAN (1000));
322
--echo # 3.5.1.2 VALUE LESS THAN (NULL) is not allowed
323
--error ER_PARSE_ERROR
324
eval CREATE TABLE t1 (
327
PARTITION BY RANGE(f_int1)
328
( PARTITION part1 VALUES LESS THAN (NULL),
329
PARTITION part2 VALUES LESS THAN (1000));
330
--echo # 3.5.2 NULL in LIST partitioning clause
331
--echo # 3.5.2.1 VALUE IN (NULL)
332
eval CREATE TABLE t1 (
335
PARTITION BY LIST(MOD(f_int1,2))
336
( PARTITION part1 VALUES IN (NULL),
337
PARTITION part2 VALUES IN (0),
338
PARTITION part3 VALUES IN (1));
340
--echo # 3.5.2.2 VALUE IN (NULL)
341
# Attention: It is intended that there is no partition with
342
# VALUES IN (0), because there was a time where NULL was treated as zero
343
eval CREATE TABLE t1 (
346
PARTITION BY LIST(MOD(f_int1,2))
347
( PARTITION part1 VALUES IN (NULL),
348
PARTITION part3 VALUES IN (1));
349
--source suite/parts/inc/partition_layout_check1.inc
351
--echo # 3.5.3 Reveal that IN (...NULL) is not mapped to IN(0)
352
# Bug#15447: Partitions: NULL is treated as zero
353
# We would get a clash here if such a mapping would be done.
354
eval CREATE TABLE t1 (
357
PARTITION BY LIST(MOD(f_int1,2))
358
( PARTITION part1 VALUES IN (NULL),
359
PARTITION part2 VALUES IN (0),
360
PARTITION part3 VALUES IN (1));
361
--source suite/parts/inc/partition_layout_check1.inc
364
# FIXME Implement some non integer constant tests
368
--echo #========================================================================
369
--echo # 4. Check assigning the number of partitions and subpartitions
370
--echo # with and without named partitions/subpartitions
371
--echo #========================================================================
373
DROP TABLE IF EXISTS t1;
375
--echo #------------------------------------------------------------------------
376
--echo # 4.1 (positive) without partition/subpartition number assignment
377
--echo #------------------------------------------------------------------------
378
--echo # 4.1.1 no partition number, no named partitions
379
eval CREATE TABLE t1 (
382
PARTITION BY HASH(f_int1);
383
--source suite/parts/inc/partition_layout_check1.inc
385
--echo # 4.1.2 no partition number, named partitions
386
eval CREATE TABLE t1 (
389
PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part2);
390
--source suite/parts/inc/partition_layout_check1.inc
392
# Attention: Several combinations are impossible
393
# If subpartitioning exists
394
# - partitioning algorithm must be RANGE or LIST
395
# This implies the assignment of named partitions.
396
# - subpartitioning algorithm must be HASH or KEY
397
--echo # 4.1.3 variations on no partition/subpartition number, named partitions,
398
--echo # different subpartitions are/are not named
400
# Partition name -- "properties"
401
# part1 -- first/non last
402
# part2 -- non first/non last
403
# part3 -- non first/ last
406
# named subpartitions in
407
# Partition part1 part2 part3
417
let $part01= CREATE TABLE t1 ( ;
419
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1);
421
eval SET @aux = '(PARTITION part1 VALUES LESS THAN ($max_row_div2),';
422
let $part1_N= `SELECT @AUX`;
423
eval SET @aux = '(PARTITION part1 VALUES LESS THAN ($max_row_div2)
424
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ),';
425
let $part1_Y= `SELECT @AUX`;
427
eval SET @aux = 'PARTITION part2 VALUES LESS THAN ($max_row),';
428
let $part2_N= `SELECT @AUX`;
429
eval SET @aux = 'PARTITION part2 VALUES LESS THAN ($max_row)
430
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ),';
431
let $part2_Y= `SELECT @AUX`;
433
eval SET @aux = 'PARTITION part3 VALUES LESS THAN $MAX_VALUE)';
434
let $part3_N= `SELECT @AUX`;
435
eval SET @aux = 'PARTITION part3 VALUES LESS THAN $MAX_VALUE
436
(SUBPARTITION subpart31 , SUBPARTITION subpart32 ))';
437
let $part3_Y= `SELECT @AUX`;
440
eval $part01 $column_list $part02 $part1_N $part2_N $part3_N ;
442
# Bug#15407 Partitions: crash if subpartition
443
--error ER_PARSE_ERROR
444
eval $part01 $column_list $part02 $part1_N $part2_N $part3_Y ;
445
--error ER_PARSE_ERROR
446
eval $part01 $column_list $part02 $part1_N $part2_Y $part3_N ;
447
--error ER_PARSE_ERROR
448
eval $part01 $column_list $part02 $part1_N $part2_Y $part3_Y ;
449
--error ER_PARSE_ERROR
450
eval $part01 $column_list $part02 $part1_Y $part2_N $part3_N ;
451
--error ER_PARSE_ERROR
452
eval $part01 $column_list $part02 $part1_Y $part2_N $part3_Y ;
453
--error ER_PARSE_ERROR
454
eval $part01 $column_list $part02 $part1_Y $part2_Y $part3_N ;
455
eval $part01 $column_list $part02 $part1_Y $part2_Y $part3_Y ;
456
--source suite/parts/inc/partition_layout_check1.inc
459
--echo #------------------------------------------------------------------------
460
--echo # 4.2 partition/subpartition numbers good and bad values and notations
461
--echo #------------------------------------------------------------------------
463
DROP TABLE IF EXISTS t1;
465
--echo # 4.2.1 partition/subpartition numbers INTEGER notation
466
# mleich: "positive/negative" is my private judgement. It need not to
467
# correspond with the server response.
468
# (positive) number = 2
470
--source suite/parts/inc/partition_syntax_1.inc
471
# (positive) special case number = 1
473
--source suite/parts/inc/partition_syntax_1.inc
474
# (negative) 0 is non sense
476
--source suite/parts/inc/partition_syntax_1.inc
477
# (negative) -1 is non sense
478
let $part_number= -1;
479
--source suite/parts/inc/partition_syntax_1.inc
480
# (negative) 1000000 is too huge
481
let $part_number= 1000000;
482
--source suite/parts/inc/partition_syntax_1.inc
484
--echo # 4.2.2 partition/subpartition numbers DECIMAL notation
485
# (positive) number = 2.0
486
let $part_number= 2.0;
487
--source suite/parts/inc/partition_syntax_1.inc
488
# (negative) -2.0 is non sense
489
let $part_number= -2.0;
490
--source suite/parts/inc/partition_syntax_1.inc
491
# (negative) case number = 0.0 is non sense
492
let $part_number= 0.0;
493
--source suite/parts/inc/partition_syntax_1.inc
494
# Bug#15890 Partitions: Strange interpretation of partition number
495
# (negative) number = 1.6 is non sense
496
let $part_number= 1.6;
497
--source suite/parts/inc/partition_syntax_1.inc
498
# (negative) number is too huge
499
let $part_number= 999999999999999999999999999999.999999999999999999999999999999;
500
--source suite/parts/inc/partition_syntax_1.inc
501
# (negative) number is nearly zero
502
let $part_number= 0.000000000000000000000000000001;
503
--source suite/parts/inc/partition_syntax_1.inc
505
--echo # 4.2.3 partition/subpartition numbers FLOAT notation
507
# (positive) number = 2.0E+0
508
let $part_number= 2.0E+0;
509
--source suite/parts/inc/partition_syntax_1.inc
510
# Bug#15890 Partitions: Strange interpretation of partition number
511
# (positive) number = 0.2E+1
512
let $part_number= 0.2E+1;
513
--source suite/parts/inc/partition_syntax_1.inc
514
# (negative) -2.0E+0 is non sense
515
let $part_number= -2.0E+0;
516
--source suite/parts/inc/partition_syntax_1.inc
517
# Bug#15890 Partitions: Strange interpretation of partition number
518
# (negative) 0.16E+1 is non sense
519
let $part_number= 0.16E+1;
520
--source suite/parts/inc/partition_syntax_1.inc
521
# (negative) 0.0E+300 is zero
522
let $part_number= 0.0E+300;
523
--source suite/parts/inc/partition_syntax_1.inc
524
# Bug#15890 Partitions: Strange interpretation of partition number
525
# (negative) 1E+300 is too huge
526
let $part_number= 1E+300;
527
--source suite/parts/inc/partition_syntax_1.inc
528
# (negative) 1E-300 is nearly zero
529
let $part_number= 1E-300;
530
--source suite/parts/inc/partition_syntax_1.inc
532
--echo # 4.2.4 partition/subpartition numbers STRING notation
533
##### STRING notation
534
# (negative?) case number = '2'
535
let $part_number= '2';
536
--source suite/parts/inc/partition_syntax_1.inc
537
# (negative?) case number = '2.0'
538
let $part_number= '2.0';
539
--source suite/parts/inc/partition_syntax_1.inc
540
# (negative?) case number = '0.2E+1'
541
let $part_number= '0.2E+1';
542
--source suite/parts/inc/partition_syntax_1.inc
543
# (negative) Strings starts with digit, but 'A' follows
544
let $part_number= '2A';
545
--source suite/parts/inc/partition_syntax_1.inc
546
# (negative) Strings starts with 'A', but digit follows
547
let $part_number= 'A2';
548
--source suite/parts/inc/partition_syntax_1.inc
549
# (negative) empty string
550
let $part_number= '';
551
--source suite/parts/inc/partition_syntax_1.inc
552
# (negative) string without any digits
553
let $part_number= 'GARBAGE';
554
--source suite/parts/inc/partition_syntax_1.inc
556
--echo # 4.2.5 partition/subpartition numbers other notations
557
# (negative) Strings starts with digit, but 'A' follows
558
let $part_number= 2A;
559
--source suite/parts/inc/partition_syntax_1.inc
560
# (negative) Strings starts with 'A', but digit follows
561
let $part_number= A2;
562
--source suite/parts/inc/partition_syntax_1.inc
563
# (negative) string without any digits
564
let $part_number= GARBAGE;
565
--source suite/parts/inc/partition_syntax_1.inc
567
# (negative?) double quotes
568
let $part_number= "2";
569
--source suite/parts/inc/partition_syntax_1.inc
570
# (negative) Strings starts with digit, but 'A' follows
571
let $part_number= "2A";
572
--source suite/parts/inc/partition_syntax_1.inc
573
# (negative) Strings starts with 'A', but digit follows
574
let $part_number= "A2";
575
--source suite/parts/inc/partition_syntax_1.inc
576
# (negative) string without any digits
577
let $part_number= "GARBAGE";
578
--source suite/parts/inc/partition_syntax_1.inc
580
--echo # 4.2.6 (negative) partition/subpartition numbers per @variables
582
--error ER_PARSE_ERROR
583
eval CREATE TABLE t1 (
586
PARTITION BY HASH(f_int1) PARTITIONS @aux;
587
--error ER_PARSE_ERROR
588
eval CREATE TABLE t1 (
591
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
592
SUBPARTITIONS @aux = 5
593
(PARTITION part1 VALUES LESS THAN ($max_row_div2),
594
PARTITION part2 VALUES LESS THAN $MAX_VALUE);
597
--echo #------------------------------------------------------------------------
598
--echo # 4.3 Mixups of assigned partition/subpartition numbers and names
599
--echo #------------------------------------------------------------------------
600
--echo # 4.3.1 (positive) number of partition/subpartition
601
--echo # = number of named partition/subpartition
602
eval CREATE TABLE t1 (
605
PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
606
--source suite/parts/inc/partition_layout_check1.inc
608
eval CREATE TABLE t1 (
611
PARTITION BY RANGE(f_int1) PARTITIONS 2
612
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
613
( PARTITION part1 VALUES LESS THAN (1000)
614
(SUBPARTITION subpart11, SUBPARTITION subpart12),
615
PARTITION part2 VALUES LESS THAN $MAX_VALUE
616
(SUBPARTITION subpart21, SUBPARTITION subpart22)
618
--source suite/parts/inc/partition_layout_check1.inc
620
--echo # 4.3.2 (positive) number of partition/subpartition ,
621
--echo # 0 (= no) named partition/subpartition
622
--echo # already checked above
623
--echo # 4.3.3 (negative) number of partitions/subpartitions
624
--echo # > number of named partitions/subpartitions
625
--error ER_PARSE_ERROR
626
eval CREATE TABLE t1 (
629
PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1 ) ;
630
# Wrong number of named subpartitions in first partition
631
--error ER_PARSE_ERROR
632
eval CREATE TABLE t1 (
635
PARTITION BY RANGE(f_int1)
636
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
637
( PARTITION part1 VALUES LESS THAN (1000)
638
(SUBPARTITION subpart11 ),
639
PARTITION part2 VALUES LESS THAN $MAX_VALUE
640
(SUBPARTITION subpart21, SUBPARTITION subpart22)
642
# Wrong number of named subpartitions in non first/non last partition
643
--error ER_PARSE_ERROR
644
eval CREATE TABLE t1 (
647
PARTITION BY RANGE(f_int1)
648
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
649
( PARTITION part1 VALUES LESS THAN (1000)
650
(SUBPARTITION subpart11, SUBPARTITION subpart12),
651
PARTITION part2 VALUES LESS THAN (2000)
652
(SUBPARTITION subpart21 ),
653
PARTITION part3 VALUES LESS THAN $MAX_VALUE
654
(SUBPARTITION subpart31, SUBPARTITION subpart32)
656
# Wrong number of named subpartitions in last partition
657
--error ER_PARSE_ERROR
658
eval CREATE TABLE t1 (
661
PARTITION BY RANGE(f_int1) PARTITIONS 2
662
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
663
( PARTITION part1 VALUES LESS THAN (1000)
664
(SUBPARTITION subpart11, SUBPARTITION subpart12),
665
PARTITION part2 VALUES LESS THAN $MAX_VALUE
666
(SUBPARTITION subpart21 )
668
--echo # 4.3.4 (negative) number of partitions < number of named partitions
669
--error ER_PARSE_ERROR
670
eval CREATE TABLE t1 (
673
PARTITION BY HASH(f_int1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
674
# Wrong number of named subpartitions in first partition
675
--error ER_PARSE_ERROR
676
eval CREATE TABLE t1 (
679
PARTITION BY RANGE(f_int1)
680
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
681
( PARTITION part1 VALUES LESS THAN (1000)
682
(SUBPARTITION subpart11, SUBPARTITION subpart12),
683
PARTITION part2 VALUES LESS THAN $MAX_VALUE
684
(SUBPARTITION subpart21, SUBPARTITION subpart22)
686
# Wrong number of named subpartitions in non first/non last partition
687
--error ER_PARSE_ERROR
688
eval CREATE TABLE t1 (
691
PARTITION BY RANGE(f_int1)
692
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
693
( PARTITION part1 VALUES LESS THAN (1000)
694
(SUBPARTITION subpart11, SUBPARTITION subpart12),
695
PARTITION part2 VALUES LESS THAN (2000)
696
(SUBPARTITION subpart21 ),
697
PARTITION part3 VALUES LESS THAN $MAX_VALUE
698
(SUBPARTITION subpart31, SUBPARTITION subpart32)
700
# Wrong number of named subpartitions in last partition
701
--error ER_PARSE_ERROR
702
eval CREATE TABLE t1 (
705
PARTITION BY RANGE(f_int1)
706
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
707
( PARTITION part1 VALUES LESS THAN (1000)
708
(SUBPARTITION subpart11, SUBPARTITION subpart12),
709
PARTITION part2 VALUES LESS THAN $MAX_VALUE
710
(SUBPARTITION subpart21, SUBPARTITION subpart22)
715
--echo #========================================================================
716
--echo # 5. Checks of logical partition/subpartition name
717
--echo # file name clashes during CREATE TABLE
718
--echo #========================================================================
720
DROP TABLE IF EXISTS t1;
723
--echo #------------------------------------------------------------------------
724
--echo # 5.1 (negative) A partition/subpartition name used more than once
725
--echo #------------------------------------------------------------------------
726
--echo # 5.1.1 duplicate partition name
727
--error ER_SAME_NAME_PARTITION
728
eval CREATE TABLE t1 (
731
PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part1);
733
--echo # 5.1.2 duplicate subpartition name
734
# Bug#15408 Partitions: subpartition names are not unique
735
--error ER_SAME_NAME_PARTITION
736
eval CREATE TABLE t1 (
739
PARTITION BY RANGE(f_int1)
740
SUBPARTITION BY HASH(f_int1)
741
( PARTITION part1 VALUES LESS THAN (1000)
742
(SUBPARTITION subpart11, SUBPARTITION subpart11)
745
# FIXME Implement testcases with filename problems
746
# existing file of other table --- partition/subpartition file name
747
# partition/subpartition file name --- file of the same table