1 ################################################################################
2 # inc/partition_alter_11.inc #
5 # Check ALTER partitioned table and the state of the table afterwards #
6 # The partitioning function use the column f_int1 #
8 # For all partitioning methods #
9 # PARTITION BY HASH/KEY/LIST/RANGE #
10 # PARTITION BY RANGE/LIST ... SUBPARTITION BY HASH/KEY ... #
12 # 1. Create the partitioned table #
13 # 2. Execute inc/partition_alter_1.inc, which will #
14 # - Insert the first half of the table t0_template into t1 #
15 # - Execute the ALTER TABLE statement #
16 # - Insert the second half of the table t0_template into t1 #
17 # - Execute the usability test inc/partition_check.inc #
18 # - Drop the table t1 #
22 # $unique -- PRIMARY KEY or UNIQUE INDEXes to be created within the #
23 # CREATE TABLE STATEMENT #
24 # $alter -- ALTER TABLE statement, which has to be executed #
25 # have to be set before sourcing this routine. #
27 # let $unique= , UNIQUE INDEX uidx1 (f_int1); #
28 # let $alter= ALTER TABLE t1 DROP UNIQUE INDEX uidx1; #
29 # inc/partition_alter1.inc #
31 # Attention: The routine inc/partition_alter_13.inc is very similar #
32 # to this one. So if something has to be changed here it #
33 # might be necessary to do it also there #
35 #------------------------------------------------------------------------------#
36 # Original Author: mleich #
37 # Original Date: 2006-03-05 #
41 ################################################################################
44 DROP
TABLE IF EXISTS t1;
48 #----------- PARTITION BY HASH
49 if ($with_partitioning)
51 let $partitioning= PARTITION BY
HASH(f_int1) PARTITIONS 2 (partition part_1, partition part_2);
53 eval CREATE
TABLE t1 (
58 --source suite/parts/inc/partition_alter_1.inc
60 #----------- PARTITION BY KEY
61 if ($with_partitioning)
63 let $partitioning= PARTITION BY
KEY(f_int1) PARTITIONS 5 (partition part_1, partition part_2, partition part_3, partition part_4, partition part_5);
65 eval CREATE
TABLE t1 (
70 --source suite/parts/inc/partition_alter_1.inc
72 #----------- PARTITION BY LIST
73 if ($with_partitioning)
75 let $partitioning= PARTITION BY
LIST(MOD(f_int1,4))
76 (PARTITION part_3 VALUES IN (-3),
77 PARTITION part_2 VALUES IN (-2),
78 PARTITION part_1 VALUES IN (-1),
79 PARTITION part_N VALUES IN (NULL),
80 PARTITION part0 VALUES IN (0),
81 PARTITION part1 VALUES IN (1),
82 PARTITION part2 VALUES IN (2),
83 PARTITION part3 VALUES IN (3));
85 eval CREATE
TABLE t1 (
90 --source suite/parts/inc/partition_alter_1.inc
92 #----------- PARTITION BY RANGE
93 if ($with_partitioning)
96 eval SET @aux =
'PARTITION BY RANGE(f_int1)
97 (PARTITION parta VALUES LESS THAN (0),
98 PARTITION part_1 VALUES LESS THAN ($max_row_div4),
99 PARTITION part_2 VALUES LESS THAN ($max_row_div2),
100 PARTITION part_3 VALUES LESS THAN ($max_row_div2 + $max_row_div4),
101 PARTITION part_4 VALUES LESS THAN ($max_row),
102 PARTITION part_5 VALUES LESS THAN $MAX_VALUE)';
103 let $partitioning= `SELECT @aux`;
106 eval CREATE
TABLE t1 (
111 --source suite/parts/inc/partition_alter_1.inc
113 #----------- PARTITION BY RANGE -- SUBPARTITION BY HASH
114 if ($with_partitioning)
118 'PARTITION BY RANGE(f_int1 DIV 2) SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
119 (PARTITION part_1 VALUES LESS THAN (0),
120 PARTITION part_2 VALUES LESS THAN ($max_row_div4),
121 PARTITION part_3 VALUES LESS THAN ($max_row_div2),
122 PARTITION part_4 VALUES LESS THAN $MAX_VALUE)';
123 let $partitioning= `SELECT @aux`;
126 eval CREATE
TABLE t1 (
131 --source suite/parts/inc/partition_alter_1.inc
133 #----------- PARTITION BY RANGE -- SUBPARTITION BY KEY
134 if ($with_partitioning)
137 eval SET @aux =
'PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
138 (PARTITION part_1 VALUES LESS THAN (0)
139 (SUBPARTITION subpart11, SUBPARTITION subpart12),
140 PARTITION part_2 VALUES LESS THAN ($max_row_div4)
141 (SUBPARTITION subpart21, SUBPARTITION subpart22),
142 PARTITION part_3 VALUES LESS THAN ($max_row_div2)
143 (SUBPARTITION subpart31, SUBPARTITION subpart32),
144 PARTITION part_4 VALUES LESS THAN $MAX_VALUE
145 (SUBPARTITION subpart41, SUBPARTITION subpart42))';
146 let $partitioning= `SELECT @aux`;
149 eval CREATE
TABLE t1 (
154 --source suite/parts/inc/partition_alter_1.inc
156 #----------- PARTITION BY LIST -- SUBPARTITION BY HASH
157 if ($with_partitioning)
159 let $partitioning= PARTITION BY
LIST(ABS(MOD(f_int1,3))) SUBPARTITION BY
HASH(f_int1 + 1)
160 (PARTITION part_1 VALUES IN (0)
161 (SUBPARTITION sp11, SUBPARTITION sp12),
162 PARTITION part_2 VALUES IN (1)
163 (SUBPARTITION sp21, SUBPARTITION sp22),
164 PARTITION part_3 VALUES IN (2)
165 (SUBPARTITION sp31, SUBPARTITION sp32),
166 PARTITION part_4 VALUES IN (NULL)
167 (SUBPARTITION sp41, SUBPARTITION sp42));
169 eval CREATE
TABLE t1 (
174 --source suite/parts/inc/partition_alter_1.inc
176 #----------- PARTITION BY LIST -- SUBPARTITION BY KEY
177 if ($with_partitioning)
181 'PARTITION BY LIST(ABS(MOD(f_int1,2)))
182 SUBPARTITION BY KEY(f_int1) SUBPARTITIONS $sub_part_no
183 (PARTITION part_1 VALUES IN (0),
184 PARTITION part_2 VALUES IN (1),
185 PARTITION part_3 VALUES IN (NULL))';
186 let $partitioning= `SELECT @aux`;
189 eval CREATE
TABLE t1 (
194 --source suite/parts/inc/partition_alter_1.inc