1
################################################################################
2
# inc/partition_methods1.inc #
5
# Create and check partitioned tables #
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 Insert the content of the table t0_template into t1 #
14
# 3. Execute inc/partition_check.inc #
15
# 4. Drop the table t1 #
19
# $unique -- PRIMARY KEY or UNIQUE INDEXes to be created within the #
20
# CREATE TABLE STATEMENT #
21
# has to be set before sourcing this routine. #
23
# let $unique= , UNIQUE INDEX uidx1 (f_int1); #
24
# inc/partition_method1s.inc #
26
# Attention: The routine inc/partition_methods2.inc is very similar #
27
# to this one. So if something has to be changed here it #
28
# might be necessary to do it also there #
30
#------------------------------------------------------------------------------#
31
# Original Author: mleich #
32
# Original Date: 2006-03-05 #
34
# Change Date: 2006-05-12 #
35
# Change: Introduced DATA/INDEX DIRECTORY #
36
################################################################################
39
DROP TABLE IF EXISTS t1;
43
#----------- PARTITION BY HASH
44
if ($with_partitioning)
46
let $partitioning= PARTITION BY HASH(f_int1) PARTITIONS 2;
47
if ($with_directories)
50
PARTITION BY HASH(f_int1) PARTITIONS 2
59
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
60
eval CREATE TABLE t1 (
66
--source suite/parts/inc/partition_check.inc
68
--source suite/parts/inc/partition_check_drop.inc
70
#----------- PARTITION BY KEY
71
if ($with_partitioning)
74
PARTITION BY KEY(f_int1) PARTITIONS 5;
75
if ($with_directories)
78
PARTITION BY KEY(f_int1) PARTITIONS 5
96
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
97
eval CREATE TABLE t1 (
103
--source suite/parts/inc/partition_check.inc
105
--source suite/parts/inc/partition_check_drop.inc
107
#----------- PARTITION BY LIST
108
if ($with_partitioning)
111
PARTITION BY LIST(MOD(f_int1,4))
112
(PARTITION part_3 VALUES IN (-3),
113
PARTITION part_2 VALUES IN (-2),
114
PARTITION part_1 VALUES IN (-1),
115
PARTITION part_N VALUES IN (NULL),
116
PARTITION part0 VALUES IN (0),
117
PARTITION part1 VALUES IN (1),
118
PARTITION part2 VALUES IN (2),
119
PARTITION part3 VALUES IN (3));
120
if ($with_directories)
123
PARTITION BY LIST(MOD(f_int1,4))
124
(PARTITION part_3 VALUES IN (-3)
125
$data_directory $index_directory,
126
PARTITION part_2 VALUES IN (-2)
127
$data_directory $index_directory,
128
PARTITION part_1 VALUES IN (-1)
129
$data_directory $index_directory,
130
PARTITION part_N VALUES IN (NULL)
131
$data_directory $index_directory,
132
PARTITION part0 VALUES IN (0)
133
$data_directory $index_directory,
134
PARTITION part1 VALUES IN (1)
135
$data_directory $index_directory,
136
PARTITION part2 VALUES IN (2)
137
$data_directory $index_directory,
138
PARTITION part3 VALUES IN (3)
139
$data_directory $index_directory);
142
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
143
eval CREATE TABLE t1 (
149
--source suite/parts/inc/partition_check.inc
151
--source suite/parts/inc/partition_check_drop.inc
153
#----------- PARTITION BY RANGE
154
if ($with_partitioning)
156
let $partitioning= PARTITION BY RANGE(f_int1)
157
(PARTITION parta VALUES LESS THAN (0),
158
PARTITION partb VALUES LESS THAN ($max_row_div4),
159
PARTITION partc VALUES LESS THAN ($max_row_div2),
160
PARTITION partd VALUES LESS THAN ($max_row_div2 + $max_row_div4),
161
PARTITION parte VALUES LESS THAN ($max_row),
162
PARTITION partf VALUES LESS THAN $MAX_VALUE);
163
if ($with_directories)
165
let $partitioning= PARTITION BY RANGE(f_int1)
166
(PARTITION parta VALUES LESS THAN (0)
169
PARTITION partb VALUES LESS THAN ($max_row_div4)
172
PARTITION partc VALUES LESS THAN ($max_row_div2)
175
PARTITION partd VALUES LESS THAN ($max_row_div2 + $max_row_div4)
178
PARTITION parte VALUES LESS THAN ($max_row)
181
PARTITION partf VALUES LESS THAN $MAX_VALUE
186
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
187
eval CREATE TABLE t1 (
193
--source suite/parts/inc/partition_check.inc
195
--source suite/parts/inc/partition_check_drop.inc
197
#----------- PARTITION BY RANGE -- SUBPARTITION BY HASH
198
if ($with_partitioning)
201
PARTITION BY RANGE(f_int1 DIV 2) SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
202
(PARTITION parta VALUES LESS THAN (0),
203
PARTITION partb VALUES LESS THAN ($max_row_div4),
204
PARTITION partc VALUES LESS THAN ($max_row_div2),
205
PARTITION partd VALUES LESS THAN $MAX_VALUE);
206
if ($with_directories)
209
PARTITION BY RANGE(f_int1 DIV 2) SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
210
(PARTITION parta VALUES LESS THAN (0)
213
PARTITION partb VALUES LESS THAN ($max_row_div4)
216
PARTITION partc VALUES LESS THAN ($max_row_div2)
219
PARTITION partd VALUES LESS THAN $MAX_VALUE
224
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
225
eval CREATE TABLE t1 (
231
--source suite/parts/inc/partition_check.inc
233
--source suite/parts/inc/partition_check_drop.inc
235
#----------- PARTITION BY RANGE -- SUBPARTITION BY KEY
236
if ($with_partitioning)
238
let $partitioning= PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
239
(PARTITION part1 VALUES LESS THAN (0)
240
(SUBPARTITION subpart11, SUBPARTITION subpart12),
241
PARTITION part2 VALUES LESS THAN ($max_row_div4)
242
(SUBPARTITION subpart21, SUBPARTITION subpart22),
243
PARTITION part3 VALUES LESS THAN ($max_row_div2)
244
(SUBPARTITION subpart31, SUBPARTITION subpart32),
245
PARTITION part4 VALUES LESS THAN $MAX_VALUE
246
(SUBPARTITION subpart41, SUBPARTITION subpart42));
247
if ($with_directories)
249
let $partitioning= PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
250
(PARTITION part1 VALUES LESS THAN (0)
251
(SUBPARTITION subpart11 $data_directory $index_directory,
252
SUBPARTITION subpart12 $data_directory $index_directory),
253
PARTITION part2 VALUES LESS THAN ($max_row_div4)
254
(SUBPARTITION subpart21 $data_directory $index_directory,
255
SUBPARTITION subpart22 $data_directory $index_directory),
256
PARTITION part3 VALUES LESS THAN ($max_row_div2)
257
(SUBPARTITION subpart31 $data_directory $index_directory,
258
SUBPARTITION subpart32 $data_directory $index_directory),
259
PARTITION part4 VALUES LESS THAN $MAX_VALUE
260
(SUBPARTITION subpart41 $data_directory $index_directory,
261
SUBPARTITION subpart42 $data_directory $index_directory));
264
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
265
eval CREATE TABLE t1 (
271
--source suite/parts/inc/partition_check.inc
273
--source suite/parts/inc/partition_check_drop.inc
275
#----------- PARTITION BY LIST -- SUBPARTITION BY HASH
276
if ($with_partitioning)
279
PARTITION BY LIST(ABS(MOD(f_int1,3))) SUBPARTITION BY HASH(f_int1 + 1)
280
(PARTITION part1 VALUES IN (0)
283
PARTITION part2 VALUES IN (1)
286
PARTITION part3 VALUES IN (2)
289
PARTITION part4 VALUES IN (NULL)
292
if ($with_directories)
295
PARTITION BY LIST(ABS(MOD(f_int1,3))) SUBPARTITION BY HASH(f_int1 + 1)
296
(PARTITION part1 VALUES IN (0)
305
PARTITION part2 VALUES IN (1)
314
PARTITION part3 VALUES IN (2)
319
PARTITION part4 VALUES IN (NULL)
330
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
331
eval CREATE TABLE t1 (
337
--source suite/parts/inc/partition_check.inc
339
--source suite/parts/inc/partition_check_drop.inc
341
#----------- PARTITION BY LIST -- SUBPARTITION BY KEY
342
if ($with_partitioning)
345
PARTITION BY LIST(ABS(MOD(f_int1,2)))
346
SUBPARTITION BY KEY(f_int1) SUBPARTITIONS $sub_part_no
347
(PARTITION part1 VALUES IN (0),
348
PARTITION part2 VALUES IN (1),
349
PARTITION part3 VALUES IN (NULL));
350
if ($with_directories)
353
PARTITION BY LIST(ABS(MOD(f_int1,2)))
354
SUBPARTITION BY KEY(f_int1) SUBPARTITIONS $sub_part_no
355
(PARTITION part1 VALUES IN (0)
358
PARTITION part2 VALUES IN (1)
361
PARTITION part3 VALUES IN (NULL)
366
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
367
eval CREATE TABLE t1 (
373
--source suite/parts/inc/partition_check.inc
375
--source suite/parts/inc/partition_check_drop.inc