2
SET @@session.storage_engine = 'MyISAM';
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 (
18
f_charbig VARCHAR(1000) ,
21
# Logging of <max_row> INSERTs into t0_template suppressed
22
DROP TABLE IF EXISTS t0_definition;
23
CREATE TABLE t0_definition (
25
create_command VARBINARY(5000),
26
file_list VARBINARY(10000),
29
DROP TABLE IF EXISTS t0_aux;
30
CREATE TABLE t0_aux ( f_int1 INTEGER,
34
f_charbig VARCHAR(1000) )
37
SET @@session.sql_mode= '';
38
# End of basic preparations needed for all tests
39
#-----------------------------------------------
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
#------------------------------------------------------------------------
55
f_charbig VARCHAR(1000),
58
PARTITION BY HASH(f_int1) PARTITIONS 2;
59
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
65
f_charbig VARCHAR(1000),
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
75
f_charbig VARCHAR(1000),
78
PARTITION BY KEY(f_int1) PARTITIONS 2;
79
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
85
f_charbig VARCHAR(1000),
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
95
f_charbig VARCHAR(1000),
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
106
f_charbig VARCHAR(1000),
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
117
f_charbig VARCHAR(1000),
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
128
f_charbig VARCHAR(1000),
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
#------------------------------------------------------------------------
147
f_charbig VARCHAR(1000),
148
UNIQUE INDEX (f_int2)
150
PARTITION BY HASH(f_int1) PARTITIONS 2;
151
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
157
f_charbig VARCHAR(1000),
158
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
160
PARTITION BY HASH(f_int1) PARTITIONS 2;
161
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
167
f_charbig VARCHAR(1000),
168
UNIQUE INDEX (f_int2)
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
177
f_charbig VARCHAR(1000),
178
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
187
f_charbig VARCHAR(1000),
188
UNIQUE INDEX (f_int2)
190
PARTITION BY KEY(f_int1) PARTITIONS 2;
191
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
197
f_charbig VARCHAR(1000),
198
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
200
PARTITION BY KEY(f_int1) PARTITIONS 2;
201
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
207
f_charbig VARCHAR(1000),
208
UNIQUE INDEX (f_int2)
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
217
f_charbig VARCHAR(1000),
218
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
227
f_charbig VARCHAR(1000),
228
UNIQUE INDEX (f_int2)
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
241
f_charbig VARCHAR(1000),
242
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
255
f_charbig VARCHAR(1000),
256
UNIQUE INDEX (f_int2)
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
269
f_charbig VARCHAR(1000),
270
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
283
f_charbig VARCHAR(1000),
284
UNIQUE INDEX (f_int2)
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
295
f_charbig VARCHAR(1000),
296
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
307
f_charbig VARCHAR(1000),
308
UNIQUE INDEX (f_int2)
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
319
f_charbig VARCHAR(1000),
320
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
#------------------------------------------------------------------------
335
f_charbig VARCHAR(1000),
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
347
f_charbig VARCHAR(1000),
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
359
f_charbig VARCHAR(1000),
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
371
f_charbig VARCHAR(1000),
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
#------------------------------------------------------------------------
389
f_charbig VARCHAR(1000),
390
UNIQUE INDEX (f_int2)
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
402
f_charbig VARCHAR(1000),
403
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
415
f_charbig VARCHAR(1000),
416
UNIQUE INDEX (f_int2)
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
428
f_charbig VARCHAR(1000),
429
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
441
f_charbig VARCHAR(1000),
442
UNIQUE INDEX (f_int2)
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
456
f_charbig VARCHAR(1000),
457
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
471
f_charbig VARCHAR(1000),
472
UNIQUE INDEX (f_int2)
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
486
f_charbig VARCHAR(1000),
487
PRIMARY KEY(f_int1,f_int2), UNIQUE INDEX (f_int2)
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
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;
509
f_charbig VARCHAR(1000)
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
518
#------------------------------------------------------------------------
524
f_charbig VARCHAR(1000),
527
PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
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
535
#========================================================================
537
#========================================================================
538
#------------------------------------------------------------------------
539
# 3.1 The constants in VALUES IN clauses must differ
540
#------------------------------------------------------------------------
546
f_charbig VARCHAR(1000)
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
#------------------------------------------------------------------------
576
f_charbig VARCHAR(1000)
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
#------------------------------------------------------------------------
591
f_charbig VARCHAR(1000)
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
608
f_charbig VARCHAR(1000)
610
PARTITION BY RANGE(f_int1)
611
( PARTITION part1 VALUES LESS THAN (NULL),
612
PARTITION part2 VALUES LESS THAN (1000));
613
ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '),
614
PARTITION part2 VALUES LESS THAN (1000))' at line 9
615
# 3.5.1.2 VALUE LESS THAN (NULL) is not allowed
621
f_charbig VARCHAR(1000)
623
PARTITION BY RANGE(f_int1)
624
( PARTITION part1 VALUES LESS THAN (NULL),
625
PARTITION part2 VALUES LESS THAN (1000));
626
ERROR 42000: Not allowed to use NULL value in VALUES LESS THAN near '),
627
PARTITION part2 VALUES LESS THAN (1000))' at line 9
628
# 3.5.2 NULL in LIST partitioning clause
629
# 3.5.2.1 VALUE IN (NULL)
635
f_charbig VARCHAR(1000)
637
PARTITION BY LIST(MOD(f_int1,2))
638
( PARTITION part1 VALUES IN (NULL),
639
PARTITION part2 VALUES IN (0),
640
PARTITION part3 VALUES IN (1));
642
# 3.5.2.2 VALUE IN (NULL)
648
f_charbig VARCHAR(1000)
650
PARTITION BY LIST(MOD(f_int1,2))
651
( PARTITION part1 VALUES IN (NULL),
652
PARTITION part3 VALUES IN (1));
654
SHOW CREATE TABLE t1;
656
t1 CREATE TABLE `t1` (
657
`f_int1` int(11) DEFAULT NULL,
658
`f_int2` int(11) DEFAULT NULL,
659
`f_char1` char(20) DEFAULT NULL,
660
`f_char2` char(20) DEFAULT NULL,
661
`f_charbig` varchar(1000) DEFAULT NULL
662
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (MOD(f_int1,2)) (PARTITION part1 VALUES IN (NULL) ENGINE = MyISAM, PARTITION part3 VALUES IN (1) ENGINE = MyISAM) */
665
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYD
666
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYI
667
$MYSQLTEST_VARDIR/master-data/test/t1#P#part3.MYD
668
$MYSQLTEST_VARDIR/master-data/test/t1#P#part3.MYI
669
$MYSQLTEST_VARDIR/master-data/test/t1.frm
670
$MYSQLTEST_VARDIR/master-data/test/t1.par
673
# 3.5.3 Reveal that IN (...NULL) is not mapped to IN(0)
679
f_charbig VARCHAR(1000)
681
PARTITION BY LIST(MOD(f_int1,2))
682
( PARTITION part1 VALUES IN (NULL),
683
PARTITION part2 VALUES IN (0),
684
PARTITION part3 VALUES IN (1));
686
SHOW CREATE TABLE t1;
688
t1 CREATE TABLE `t1` (
689
`f_int1` int(11) DEFAULT NULL,
690
`f_int2` int(11) DEFAULT NULL,
691
`f_char1` char(20) DEFAULT NULL,
692
`f_char2` char(20) DEFAULT NULL,
693
`f_charbig` varchar(1000) DEFAULT NULL
694
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (MOD(f_int1,2)) (PARTITION part1 VALUES IN (NULL) ENGINE = MyISAM, PARTITION part2 VALUES IN (0) ENGINE = MyISAM, PARTITION part3 VALUES IN (1) ENGINE = MyISAM) */
697
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYD
698
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYI
699
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYD
700
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYI
701
$MYSQLTEST_VARDIR/master-data/test/t1#P#part3.MYD
702
$MYSQLTEST_VARDIR/master-data/test/t1#P#part3.MYI
703
$MYSQLTEST_VARDIR/master-data/test/t1.frm
704
$MYSQLTEST_VARDIR/master-data/test/t1.par
708
#========================================================================
709
# 4. Check assigning the number of partitions and subpartitions
710
# with and without named partitions/subpartitions
711
#========================================================================
712
DROP TABLE IF EXISTS t1;
713
#------------------------------------------------------------------------
714
# 4.1 (positive) without partition/subpartition number assignment
715
#------------------------------------------------------------------------
716
# 4.1.1 no partition number, no named partitions
722
f_charbig VARCHAR(1000)
724
PARTITION BY HASH(f_int1);
726
SHOW CREATE TABLE t1;
728
t1 CREATE TABLE `t1` (
729
`f_int1` int(11) DEFAULT NULL,
730
`f_int2` int(11) DEFAULT NULL,
731
`f_char1` char(20) DEFAULT NULL,
732
`f_char2` char(20) DEFAULT NULL,
733
`f_charbig` varchar(1000) DEFAULT NULL
734
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) */
737
$MYSQLTEST_VARDIR/master-data/test/t1#P#p0.MYD
738
$MYSQLTEST_VARDIR/master-data/test/t1#P#p0.MYI
739
$MYSQLTEST_VARDIR/master-data/test/t1.frm
740
$MYSQLTEST_VARDIR/master-data/test/t1.par
743
# 4.1.2 no partition number, named partitions
749
f_charbig VARCHAR(1000)
751
PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part2);
753
SHOW CREATE TABLE t1;
755
t1 CREATE TABLE `t1` (
756
`f_int1` int(11) DEFAULT NULL,
757
`f_int2` int(11) DEFAULT NULL,
758
`f_char1` char(20) DEFAULT NULL,
759
`f_char2` char(20) DEFAULT NULL,
760
`f_charbig` varchar(1000) DEFAULT NULL
761
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
764
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYD
765
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYI
766
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYD
767
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYI
768
$MYSQLTEST_VARDIR/master-data/test/t1.frm
769
$MYSQLTEST_VARDIR/master-data/test/t1.par
772
# 4.1.3 variations on no partition/subpartition number, named partitions,
773
# different subpartitions are/are not named
774
CREATE TABLE t1 ( f_int1 INTEGER,
778
f_charbig VARCHAR(1000) )
779
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ;
781
CREATE TABLE t1 ( f_int1 INTEGER,
785
f_charbig VARCHAR(1000) )
786
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)
787
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
788
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '))' at line 7
789
CREATE TABLE t1 ( f_int1 INTEGER,
793
f_charbig VARCHAR(1000) )
794
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20)
795
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ;
796
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), PARTITION part3 VALUES LESS THAN (2147483646))' at line 7
797
CREATE TABLE t1 ( f_int1 INTEGER,
801
f_charbig VARCHAR(1000) )
802
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (20)
803
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)
804
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
805
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '), PARTITION part3 VALUES LESS THAN (2147483646)
806
(SUBPARTITION subpart31 , SUBPA' at line 7
807
CREATE TABLE t1 ( f_int1 INTEGER,
811
f_charbig VARCHAR(1000) )
812
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
813
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20), PARTITION part3 VALUES LESS THAN (2147483646)) ;
814
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION part3 VALUES LESS THAN (2147483646))' at line 7
815
CREATE TABLE t1 ( f_int1 INTEGER,
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
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
823
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ' PARTITION part3 VALUES LESS THAN (2147483646)
824
(SUBPARTITION subpart31 , SUBPART' at line 7
825
CREATE TABLE t1 ( f_int1 INTEGER,
829
f_charbig VARCHAR(1000) )
830
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
831
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20)
832
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)) ;
833
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')' at line 8
834
CREATE TABLE t1 ( f_int1 INTEGER,
838
f_charbig VARCHAR(1000) )
839
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) (PARTITION part1 VALUES LESS THAN (10)
840
(SUBPARTITION subpart11 , SUBPARTITION subpart12 ), PARTITION part2 VALUES LESS THAN (20)
841
(SUBPARTITION subpart21 , SUBPARTITION subpart22 ), PARTITION part3 VALUES LESS THAN (2147483646)
842
(SUBPARTITION subpart31 , SUBPARTITION subpart32 )) ;
844
SHOW CREATE TABLE t1;
846
t1 CREATE TABLE `t1` (
847
`f_int1` int(11) DEFAULT NULL,
848
`f_int2` int(11) DEFAULT NULL,
849
`f_char1` char(20) DEFAULT NULL,
850
`f_char2` char(20) DEFAULT NULL,
851
`f_charbig` varchar(1000) DEFAULT NULL
852
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (10) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (20) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM), PARTITION part3 VALUES LESS THAN (2147483646) (SUBPARTITION subpart31 ENGINE = MyISAM, SUBPARTITION subpart32 ENGINE = MyISAM)) */
855
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
856
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
857
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
858
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
859
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
860
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
861
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
862
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
863
$MYSQLTEST_VARDIR/master-data/test/t1#P#part3#SP#subpart31.MYD
864
$MYSQLTEST_VARDIR/master-data/test/t1#P#part3#SP#subpart31.MYI
865
$MYSQLTEST_VARDIR/master-data/test/t1#P#part3#SP#subpart32.MYD
866
$MYSQLTEST_VARDIR/master-data/test/t1#P#part3#SP#subpart32.MYI
867
$MYSQLTEST_VARDIR/master-data/test/t1.frm
868
$MYSQLTEST_VARDIR/master-data/test/t1.par
871
#------------------------------------------------------------------------
872
# 4.2 partition/subpartition numbers good and bad values and notations
873
#------------------------------------------------------------------------
874
DROP TABLE IF EXISTS t1;
875
# 4.2.1 partition/subpartition numbers INTEGER notation
881
f_charbig VARCHAR(1000)
883
PARTITION BY HASH(f_int1) PARTITIONS 2;
885
SHOW CREATE TABLE t1;
887
t1 CREATE TABLE `t1` (
888
`f_int1` int(11) DEFAULT NULL,
889
`f_int2` int(11) DEFAULT NULL,
890
`f_char1` char(20) DEFAULT NULL,
891
`f_char2` char(20) DEFAULT NULL,
892
`f_charbig` varchar(1000) DEFAULT NULL
893
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) PARTITIONS 2 */
896
$MYSQLTEST_VARDIR/master-data/test/t1#P#p0.MYD
897
$MYSQLTEST_VARDIR/master-data/test/t1#P#p0.MYI
898
$MYSQLTEST_VARDIR/master-data/test/t1#P#p1.MYD
899
$MYSQLTEST_VARDIR/master-data/test/t1#P#p1.MYI
900
$MYSQLTEST_VARDIR/master-data/test/t1.frm
901
$MYSQLTEST_VARDIR/master-data/test/t1.par
909
f_charbig VARCHAR(1000)
911
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
913
(PARTITION part1 VALUES LESS THAN (10),
914
PARTITION part2 VALUES LESS THAN (2147483646));
916
SHOW CREATE TABLE t1;
918
t1 CREATE TABLE `t1` (
919
`f_int1` int(11) DEFAULT NULL,
920
`f_int2` int(11) DEFAULT NULL,
921
`f_char1` char(20) DEFAULT NULL,
922
`f_char2` char(20) DEFAULT NULL,
923
`f_charbig` varchar(1000) DEFAULT NULL
924
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) SUBPARTITIONS 2 (PARTITION part1 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = MyISAM) */
927
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#part1sp0.MYD
928
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#part1sp0.MYI
929
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#part1sp1.MYD
930
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#part1sp1.MYI
931
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#part2sp0.MYD
932
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#part2sp0.MYI
933
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#part2sp1.MYD
934
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#part2sp1.MYI
935
$MYSQLTEST_VARDIR/master-data/test/t1.frm
936
$MYSQLTEST_VARDIR/master-data/test/t1.par
944
f_charbig VARCHAR(1000)
946
PARTITION BY HASH(f_int1) PARTITIONS 1;
948
SHOW CREATE TABLE t1;
950
t1 CREATE TABLE `t1` (
951
`f_int1` int(11) DEFAULT NULL,
952
`f_int2` int(11) DEFAULT NULL,
953
`f_char1` char(20) DEFAULT NULL,
954
`f_char2` char(20) DEFAULT NULL,
955
`f_charbig` varchar(1000) DEFAULT NULL
956
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) PARTITIONS 1 */
959
$MYSQLTEST_VARDIR/master-data/test/t1#P#p0.MYD
960
$MYSQLTEST_VARDIR/master-data/test/t1#P#p0.MYI
961
$MYSQLTEST_VARDIR/master-data/test/t1.frm
962
$MYSQLTEST_VARDIR/master-data/test/t1.par
970
f_charbig VARCHAR(1000)
972
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
974
(PARTITION part1 VALUES LESS THAN (10),
975
PARTITION part2 VALUES LESS THAN (2147483646));
977
SHOW CREATE TABLE t1;
979
t1 CREATE TABLE `t1` (
980
`f_int1` int(11) DEFAULT NULL,
981
`f_int2` int(11) DEFAULT NULL,
982
`f_char1` char(20) DEFAULT NULL,
983
`f_char2` char(20) DEFAULT NULL,
984
`f_charbig` varchar(1000) DEFAULT NULL
985
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION part2 VALUES LESS THAN (2147483646) ENGINE = MyISAM) */
988
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#part1sp0.MYD
989
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#part1sp0.MYI
990
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#part2sp0.MYD
991
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#part2sp0.MYI
992
$MYSQLTEST_VARDIR/master-data/test/t1.frm
993
$MYSQLTEST_VARDIR/master-data/test/t1.par
1001
f_charbig VARCHAR(1000)
1003
PARTITION BY HASH(f_int1) PARTITIONS 0;
1004
ERROR HY000: Number of partitions = 0 is not an allowed value
1010
f_charbig VARCHAR(1000)
1012
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1014
(PARTITION part1 VALUES LESS THAN (10),
1015
PARTITION part2 VALUES LESS THAN (2147483646));
1016
ERROR HY000: Number of subpartitions = 0 is not an allowed value
1022
f_charbig VARCHAR(1000)
1024
PARTITION BY HASH(f_int1) PARTITIONS -1;
1025
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 8
1031
f_charbig VARCHAR(1000)
1033
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1035
(PARTITION part1 VALUES LESS THAN (10),
1036
PARTITION part2 VALUES LESS THAN (2147483646));
1037
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1
1038
(PARTITION part1 VALUES LESS THAN (10),
1039
PARTITION part2 VALUES LESS THAN (214' at line 9
1045
f_charbig VARCHAR(1000)
1047
PARTITION BY HASH(f_int1) PARTITIONS 1000000;
1048
ERROR HY000: Too many partitions (including subpartitions) were defined
1054
f_charbig VARCHAR(1000)
1056
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1057
SUBPARTITIONS 1000000
1058
(PARTITION part1 VALUES LESS THAN (10),
1059
PARTITION part2 VALUES LESS THAN (2147483646));
1060
ERROR HY000: Too many partitions (including subpartitions) were defined
1061
# 4.2.2 partition/subpartition numbers DECIMAL notation
1067
f_charbig VARCHAR(1000)
1069
PARTITION BY HASH(f_int1) PARTITIONS 2.0;
1070
ERROR 42000: Only integers allowed as number here near '2.0' at line 8
1076
f_charbig VARCHAR(1000)
1078
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1080
(PARTITION part1 VALUES LESS THAN (10),
1081
PARTITION part2 VALUES LESS THAN (2147483646));
1082
ERROR 42000: Only integers allowed as number here near '2.0
1083
(PARTITION part1 VALUES LESS THAN (10),
1084
PARTITION part2 VALUES LESS THAN (21' at line 9
1090
f_charbig VARCHAR(1000)
1092
PARTITION BY HASH(f_int1) PARTITIONS -2.0;
1093
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0' at line 8
1099
f_charbig VARCHAR(1000)
1101
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1103
(PARTITION part1 VALUES LESS THAN (10),
1104
PARTITION part2 VALUES LESS THAN (2147483646));
1105
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0
1106
(PARTITION part1 VALUES LESS THAN (10),
1107
PARTITION part2 VALUES LESS THAN (2' at line 9
1113
f_charbig VARCHAR(1000)
1115
PARTITION BY HASH(f_int1) PARTITIONS 0.0;
1116
ERROR 42000: Only integers allowed as number here near '0.0' at line 8
1122
f_charbig VARCHAR(1000)
1124
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1126
(PARTITION part1 VALUES LESS THAN (10),
1127
PARTITION part2 VALUES LESS THAN (2147483646));
1128
ERROR 42000: Only integers allowed as number here near '0.0
1129
(PARTITION part1 VALUES LESS THAN (10),
1130
PARTITION part2 VALUES LESS THAN (21' at line 9
1136
f_charbig VARCHAR(1000)
1138
PARTITION BY HASH(f_int1) PARTITIONS 1.6;
1139
ERROR 42000: Only integers allowed as number here near '1.6' at line 8
1145
f_charbig VARCHAR(1000)
1147
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1149
(PARTITION part1 VALUES LESS THAN (10),
1150
PARTITION part2 VALUES LESS THAN (2147483646));
1151
ERROR 42000: Only integers allowed as number here near '1.6
1152
(PARTITION part1 VALUES LESS THAN (10),
1153
PARTITION part2 VALUES LESS THAN (21' at line 9
1159
f_charbig VARCHAR(1000)
1161
PARTITION BY HASH(f_int1) PARTITIONS 999999999999999999999999999999.999999999999999999999999999999;
1162
ERROR 42000: Only integers allowed as number here near '999999999999999999999999999999.999999999999999999999999999999' at line 8
1168
f_charbig VARCHAR(1000)
1170
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1171
SUBPARTITIONS 999999999999999999999999999999.999999999999999999999999999999
1172
(PARTITION part1 VALUES LESS THAN (10),
1173
PARTITION part2 VALUES LESS THAN (2147483646));
1174
ERROR 42000: Only integers allowed as number here near '999999999999999999999999999999.999999999999999999999999999999
1175
(PARTITION part1 V' at line 9
1181
f_charbig VARCHAR(1000)
1183
PARTITION BY HASH(f_int1) PARTITIONS 0.000000000000000000000000000001;
1184
ERROR 42000: Only integers allowed as number here near '0.000000000000000000000000000001' at line 8
1190
f_charbig VARCHAR(1000)
1192
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1193
SUBPARTITIONS 0.000000000000000000000000000001
1194
(PARTITION part1 VALUES LESS THAN (10),
1195
PARTITION part2 VALUES LESS THAN (2147483646));
1196
ERROR 42000: Only integers allowed as number here near '0.000000000000000000000000000001
1197
(PARTITION part1 VALUES LESS THAN (10),
1199
# 4.2.3 partition/subpartition numbers FLOAT notation
1205
f_charbig VARCHAR(1000)
1207
PARTITION BY HASH(f_int1) PARTITIONS 2.0E+0;
1208
ERROR 42000: Only integers allowed as number here near '2.0E+0' at line 8
1214
f_charbig VARCHAR(1000)
1216
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1217
SUBPARTITIONS 2.0E+0
1218
(PARTITION part1 VALUES LESS THAN (10),
1219
PARTITION part2 VALUES LESS THAN (2147483646));
1220
ERROR 42000: Only integers allowed as number here near '2.0E+0
1221
(PARTITION part1 VALUES LESS THAN (10),
1222
PARTITION part2 VALUES LESS THAN ' at line 9
1228
f_charbig VARCHAR(1000)
1230
PARTITION BY HASH(f_int1) PARTITIONS 0.2E+1;
1231
ERROR 42000: Only integers allowed as number here near '0.2E+1' at line 8
1237
f_charbig VARCHAR(1000)
1239
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1240
SUBPARTITIONS 0.2E+1
1241
(PARTITION part1 VALUES LESS THAN (10),
1242
PARTITION part2 VALUES LESS THAN (2147483646));
1243
ERROR 42000: Only integers allowed as number here near '0.2E+1
1244
(PARTITION part1 VALUES LESS THAN (10),
1245
PARTITION part2 VALUES LESS THAN ' at line 9
1251
f_charbig VARCHAR(1000)
1253
PARTITION BY HASH(f_int1) PARTITIONS -2.0E+0;
1254
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0E+0' at line 8
1260
f_charbig VARCHAR(1000)
1262
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1263
SUBPARTITIONS -2.0E+0
1264
(PARTITION part1 VALUES LESS THAN (10),
1265
PARTITION part2 VALUES LESS THAN (2147483646));
1266
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2.0E+0
1267
(PARTITION part1 VALUES LESS THAN (10),
1268
PARTITION part2 VALUES LESS THAN' at line 9
1274
f_charbig VARCHAR(1000)
1276
PARTITION BY HASH(f_int1) PARTITIONS 0.16E+1;
1277
ERROR 42000: Only integers allowed as number here near '0.16E+1' at line 8
1283
f_charbig VARCHAR(1000)
1285
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1286
SUBPARTITIONS 0.16E+1
1287
(PARTITION part1 VALUES LESS THAN (10),
1288
PARTITION part2 VALUES LESS THAN (2147483646));
1289
ERROR 42000: Only integers allowed as number here near '0.16E+1
1290
(PARTITION part1 VALUES LESS THAN (10),
1291
PARTITION part2 VALUES LESS THAN' at line 9
1297
f_charbig VARCHAR(1000)
1299
PARTITION BY HASH(f_int1) PARTITIONS 0.0E+300;
1300
ERROR 42000: Only integers allowed as number here near '0.0E+300' at line 8
1306
f_charbig VARCHAR(1000)
1308
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1309
SUBPARTITIONS 0.0E+300
1310
(PARTITION part1 VALUES LESS THAN (10),
1311
PARTITION part2 VALUES LESS THAN (2147483646));
1312
ERROR 42000: Only integers allowed as number here near '0.0E+300
1313
(PARTITION part1 VALUES LESS THAN (10),
1314
PARTITION part2 VALUES LESS THA' at line 9
1320
f_charbig VARCHAR(1000)
1322
PARTITION BY HASH(f_int1) PARTITIONS 1E+300;
1323
ERROR 42000: Only integers allowed as number here near '1E+300' at line 8
1329
f_charbig VARCHAR(1000)
1331
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1332
SUBPARTITIONS 1E+300
1333
(PARTITION part1 VALUES LESS THAN (10),
1334
PARTITION part2 VALUES LESS THAN (2147483646));
1335
ERROR 42000: Only integers allowed as number here near '1E+300
1336
(PARTITION part1 VALUES LESS THAN (10),
1337
PARTITION part2 VALUES LESS THAN ' at line 9
1343
f_charbig VARCHAR(1000)
1345
PARTITION BY HASH(f_int1) PARTITIONS 1E-300;
1346
ERROR 42000: Only integers allowed as number here near '1E-300' at line 8
1352
f_charbig VARCHAR(1000)
1354
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1355
SUBPARTITIONS 1E-300
1356
(PARTITION part1 VALUES LESS THAN (10),
1357
PARTITION part2 VALUES LESS THAN (2147483646));
1358
ERROR 42000: Only integers allowed as number here near '1E-300
1359
(PARTITION part1 VALUES LESS THAN (10),
1360
PARTITION part2 VALUES LESS THAN ' at line 9
1361
# 4.2.4 partition/subpartition numbers STRING notation
1367
f_charbig VARCHAR(1000)
1369
PARTITION BY HASH(f_int1) PARTITIONS '2';
1370
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 8
1376
f_charbig VARCHAR(1000)
1378
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1380
(PARTITION part1 VALUES LESS THAN (10),
1381
PARTITION part2 VALUES LESS THAN (2147483646));
1382
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'
1383
(PARTITION part1 VALUES LESS THAN (10),
1384
PARTITION part2 VALUES LESS THAN (21' at line 9
1390
f_charbig VARCHAR(1000)
1392
PARTITION BY HASH(f_int1) PARTITIONS '2.0';
1393
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 8
1399
f_charbig VARCHAR(1000)
1401
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1403
(PARTITION part1 VALUES LESS THAN (10),
1404
PARTITION part2 VALUES LESS THAN (2147483646));
1405
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'
1406
(PARTITION part1 VALUES LESS THAN (10),
1407
PARTITION part2 VALUES LESS THAN (' at line 9
1413
f_charbig VARCHAR(1000)
1415
PARTITION BY HASH(f_int1) PARTITIONS '0.2E+1';
1416
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 8
1422
f_charbig VARCHAR(1000)
1424
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1425
SUBPARTITIONS '0.2E+1'
1426
(PARTITION part1 VALUES LESS THAN (10),
1427
PARTITION part2 VALUES LESS THAN (2147483646));
1428
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'
1429
(PARTITION part1 VALUES LESS THAN (10),
1430
PARTITION part2 VALUES LESS THA' at line 9
1436
f_charbig VARCHAR(1000)
1438
PARTITION BY HASH(f_int1) PARTITIONS '2A';
1439
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 8
1445
f_charbig VARCHAR(1000)
1447
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1449
(PARTITION part1 VALUES LESS THAN (10),
1450
PARTITION part2 VALUES LESS THAN (2147483646));
1451
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'
1452
(PARTITION part1 VALUES LESS THAN (10),
1453
PARTITION part2 VALUES LESS THAN (2' at line 9
1459
f_charbig VARCHAR(1000)
1461
PARTITION BY HASH(f_int1) PARTITIONS 'A2';
1462
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 8
1468
f_charbig VARCHAR(1000)
1470
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1472
(PARTITION part1 VALUES LESS THAN (10),
1473
PARTITION part2 VALUES LESS THAN (2147483646));
1474
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'
1475
(PARTITION part1 VALUES LESS THAN (10),
1476
PARTITION part2 VALUES LESS THAN (2' at line 9
1482
f_charbig VARCHAR(1000)
1484
PARTITION BY HASH(f_int1) PARTITIONS '';
1485
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 8
1491
f_charbig VARCHAR(1000)
1493
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1495
(PARTITION part1 VALUES LESS THAN (10),
1496
PARTITION part2 VALUES LESS THAN (2147483646));
1497
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''
1498
(PARTITION part1 VALUES LESS THAN (10),
1499
PARTITION part2 VALUES LESS THAN (214' at line 9
1505
f_charbig VARCHAR(1000)
1507
PARTITION BY HASH(f_int1) PARTITIONS 'GARBAGE';
1508
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 8
1514
f_charbig VARCHAR(1000)
1516
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1517
SUBPARTITIONS 'GARBAGE'
1518
(PARTITION part1 VALUES LESS THAN (10),
1519
PARTITION part2 VALUES LESS THAN (2147483646));
1520
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'
1521
(PARTITION part1 VALUES LESS THAN (10),
1522
PARTITION part2 VALUES LESS TH' at line 9
1523
# 4.2.5 partition/subpartition numbers other notations
1529
f_charbig VARCHAR(1000)
1531
PARTITION BY HASH(f_int1) PARTITIONS 2A;
1532
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A' at line 8
1538
f_charbig VARCHAR(1000)
1540
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1542
(PARTITION part1 VALUES LESS THAN (10),
1543
PARTITION part2 VALUES LESS THAN (2147483646));
1544
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A
1545
(PARTITION part1 VALUES LESS THAN (10),
1546
PARTITION part2 VALUES LESS THAN (214' at line 9
1552
f_charbig VARCHAR(1000)
1554
PARTITION BY HASH(f_int1) PARTITIONS A2;
1555
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 8
1561
f_charbig VARCHAR(1000)
1563
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1565
(PARTITION part1 VALUES LESS THAN (10),
1566
PARTITION part2 VALUES LESS THAN (2147483646));
1567
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2
1568
(PARTITION part1 VALUES LESS THAN (10),
1569
PARTITION part2 VALUES LESS THAN (214' at line 9
1575
f_charbig VARCHAR(1000)
1577
PARTITION BY HASH(f_int1) PARTITIONS GARBAGE;
1578
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 8
1584
f_charbig VARCHAR(1000)
1586
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1587
SUBPARTITIONS GARBAGE
1588
(PARTITION part1 VALUES LESS THAN (10),
1589
PARTITION part2 VALUES LESS THAN (2147483646));
1590
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE
1591
(PARTITION part1 VALUES LESS THAN (10),
1592
PARTITION part2 VALUES LESS THAN' at line 9
1598
f_charbig VARCHAR(1000)
1600
PARTITION BY HASH(f_int1) PARTITIONS "2";
1601
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 8
1607
f_charbig VARCHAR(1000)
1609
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1611
(PARTITION part1 VALUES LESS THAN (10),
1612
PARTITION part2 VALUES LESS THAN (2147483646));
1613
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"
1614
(PARTITION part1 VALUES LESS THAN (10),
1615
PARTITION part2 VALUES LESS THAN (21' at line 9
1621
f_charbig VARCHAR(1000)
1623
PARTITION BY HASH(f_int1) PARTITIONS "2A";
1624
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 8
1630
f_charbig VARCHAR(1000)
1632
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1634
(PARTITION part1 VALUES LESS THAN (10),
1635
PARTITION part2 VALUES LESS THAN (2147483646));
1636
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"
1637
(PARTITION part1 VALUES LESS THAN (10),
1638
PARTITION part2 VALUES LESS THAN (2' at line 9
1644
f_charbig VARCHAR(1000)
1646
PARTITION BY HASH(f_int1) PARTITIONS "A2";
1647
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 8
1653
f_charbig VARCHAR(1000)
1655
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1657
(PARTITION part1 VALUES LESS THAN (10),
1658
PARTITION part2 VALUES LESS THAN (2147483646));
1659
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"
1660
(PARTITION part1 VALUES LESS THAN (10),
1661
PARTITION part2 VALUES LESS THAN (2' at line 9
1667
f_charbig VARCHAR(1000)
1669
PARTITION BY HASH(f_int1) PARTITIONS "GARBAGE";
1670
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 8
1676
f_charbig VARCHAR(1000)
1678
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1679
SUBPARTITIONS "GARBAGE"
1680
(PARTITION part1 VALUES LESS THAN (10),
1681
PARTITION part2 VALUES LESS THAN (2147483646));
1682
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"
1683
(PARTITION part1 VALUES LESS THAN (10),
1684
PARTITION part2 VALUES LESS TH' at line 9
1685
# 4.2.6 (negative) partition/subpartition numbers per @variables
1692
f_charbig VARCHAR(1000)
1694
PARTITION BY HASH(f_int1) PARTITIONS @aux;
1695
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aux' at line 8
1701
f_charbig VARCHAR(1000)
1703
PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1)
1704
SUBPARTITIONS @aux = 5
1705
(PARTITION part1 VALUES LESS THAN (10),
1706
PARTITION part2 VALUES LESS THAN (2147483646));
1707
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@aux = 5
1708
(PARTITION part1 VALUES LESS THAN (10),
1709
PARTITION part2 VALUES LESS THA' at line 9
1710
#------------------------------------------------------------------------
1711
# 4.3 Mixups of assigned partition/subpartition numbers and names
1712
#------------------------------------------------------------------------
1713
# 4.3.1 (positive) number of partition/subpartition
1714
# = number of named partition/subpartition
1720
f_charbig VARCHAR(1000)
1722
PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
1724
SHOW CREATE TABLE t1;
1726
t1 CREATE TABLE `t1` (
1727
`f_int1` int(11) DEFAULT NULL,
1728
`f_int2` int(11) DEFAULT NULL,
1729
`f_char1` char(20) DEFAULT NULL,
1730
`f_char2` char(20) DEFAULT NULL,
1731
`f_charbig` varchar(1000) DEFAULT NULL
1732
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (f_int1) (PARTITION part1 ENGINE = MyISAM, PARTITION part2 ENGINE = MyISAM) */
1735
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYD
1736
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1.MYI
1737
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYD
1738
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2.MYI
1739
$MYSQLTEST_VARDIR/master-data/test/t1.frm
1740
$MYSQLTEST_VARDIR/master-data/test/t1.par
1748
f_charbig VARCHAR(1000)
1750
PARTITION BY RANGE(f_int1) PARTITIONS 2
1751
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1752
( PARTITION part1 VALUES LESS THAN (1000)
1753
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1754
PARTITION part2 VALUES LESS THAN (2147483646)
1755
(SUBPARTITION subpart21, SUBPARTITION subpart22)
1758
SHOW CREATE TABLE t1;
1760
t1 CREATE TABLE `t1` (
1761
`f_int1` int(11) DEFAULT NULL,
1762
`f_int2` int(11) DEFAULT NULL,
1763
`f_char1` char(20) DEFAULT NULL,
1764
`f_char2` char(20) DEFAULT NULL,
1765
`f_charbig` varchar(1000) DEFAULT NULL
1766
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (f_int1) SUBPARTITION BY HASH (f_int1) (PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483646) (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM)) */
1769
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYD
1770
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart11.MYI
1771
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYD
1772
$MYSQLTEST_VARDIR/master-data/test/t1#P#part1#SP#subpart12.MYI
1773
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYD
1774
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart21.MYI
1775
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYD
1776
$MYSQLTEST_VARDIR/master-data/test/t1#P#part2#SP#subpart22.MYI
1777
$MYSQLTEST_VARDIR/master-data/test/t1.frm
1778
$MYSQLTEST_VARDIR/master-data/test/t1.par
1781
# 4.3.2 (positive) number of partition/subpartition ,
1782
# 0 (= no) named partition/subpartition
1783
# already checked above
1784
# 4.3.3 (negative) number of partitions/subpartitions
1785
# > number of named partitions/subpartitions
1791
f_charbig VARCHAR(1000)
1793
PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1 ) ;
1794
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8
1800
f_charbig VARCHAR(1000)
1802
PARTITION BY RANGE(f_int1)
1803
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1804
( PARTITION part1 VALUES LESS THAN (1000)
1805
(SUBPARTITION subpart11 ),
1806
PARTITION part2 VALUES LESS THAN (2147483646)
1807
(SUBPARTITION subpart21, SUBPARTITION subpart22)
1809
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1810
PARTITION part2 VALUES LESS THAN (2147483646)
1811
(SUBPARTITION subpart21, SUBPAR' at line 11
1817
f_charbig VARCHAR(1000)
1819
PARTITION BY RANGE(f_int1)
1820
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1821
( PARTITION part1 VALUES LESS THAN (1000)
1822
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1823
PARTITION part2 VALUES LESS THAN (2000)
1824
(SUBPARTITION subpart21 ),
1825
PARTITION part3 VALUES LESS THAN (2147483646)
1826
(SUBPARTITION subpart31, SUBPARTITION subpart32)
1828
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1829
PARTITION part3 VALUES LESS THAN (2147483646)
1830
(SUBPARTITION subpart31, SUBPAR' at line 13
1836
f_charbig VARCHAR(1000)
1838
PARTITION BY RANGE(f_int1) PARTITIONS 2
1839
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
1840
( PARTITION part1 VALUES LESS THAN (1000)
1841
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1842
PARTITION part2 VALUES LESS THAN (2147483646)
1843
(SUBPARTITION subpart21 )
1845
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')
1847
# 4.3.4 (negative) number of partitions < number of named partitions
1853
f_charbig VARCHAR(1000)
1855
PARTITION BY HASH(f_int1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
1856
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 8
1862
f_charbig VARCHAR(1000)
1864
PARTITION BY RANGE(f_int1)
1865
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1866
( PARTITION part1 VALUES LESS THAN (1000)
1867
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1868
PARTITION part2 VALUES LESS THAN (2147483646)
1869
(SUBPARTITION subpart21, SUBPARTITION subpart22)
1871
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1872
PARTITION part2 VALUES LESS THAN (2147483646)
1873
(SUBPARTITION subpart21, SUBPAR' at line 11
1879
f_charbig VARCHAR(1000)
1881
PARTITION BY RANGE(f_int1)
1882
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1883
( PARTITION part1 VALUES LESS THAN (1000)
1884
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1885
PARTITION part2 VALUES LESS THAN (2000)
1886
(SUBPARTITION subpart21 ),
1887
PARTITION part3 VALUES LESS THAN (2147483646)
1888
(SUBPARTITION subpart31, SUBPARTITION subpart32)
1890
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1891
PARTITION part2 VALUES LESS THAN (2000)
1892
(SUBPARTITION subpart21 ' at line 11
1898
f_charbig VARCHAR(1000)
1900
PARTITION BY RANGE(f_int1)
1901
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1
1902
( PARTITION part1 VALUES LESS THAN (1000)
1903
(SUBPARTITION subpart11, SUBPARTITION subpart12),
1904
PARTITION part2 VALUES LESS THAN (2147483646)
1905
(SUBPARTITION subpart21, SUBPARTITION subpart22)
1907
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
1908
PARTITION part2 VALUES LESS THAN (2147483646)
1909
(SUBPARTITION subpart21, SUBPAR' at line 11
1911
#========================================================================
1912
# 5. Checks of logical partition/subpartition name
1913
# file name clashes during CREATE TABLE
1914
#========================================================================
1915
DROP TABLE IF EXISTS t1;
1916
#------------------------------------------------------------------------
1917
# 5.1 (negative) A partition/subpartition name used more than once
1918
#------------------------------------------------------------------------
1919
# 5.1.1 duplicate partition name
1925
f_charbig VARCHAR(1000)
1927
PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part1);
1928
ERROR HY000: Duplicate partition name part1
1929
# 5.1.2 duplicate subpartition name
1935
f_charbig VARCHAR(1000)
1937
PARTITION BY RANGE(f_int1)
1938
SUBPARTITION BY HASH(f_int1)
1939
( PARTITION part1 VALUES LESS THAN (1000)
1940
(SUBPARTITION subpart11, SUBPARTITION subpart11)
1942
ERROR HY000: Duplicate partition name subpart11
1943
DROP VIEW IF EXISTS v1;
1944
DROP TABLE IF EXISTS t1;
1945
DROP TABLE IF EXISTS t0_aux;
1946
DROP TABLE IF EXISTS t0_definition;
1947
DROP TABLE IF EXISTS t0_template;