1
################################################################################
2
# inc/partition_alter3.inc #
5
# Tests for partition management commands for HASH and KEY partitioning #
7
#------------------------------------------------------------------------------#
8
# Original Author: mleich #
9
# Original Date: 2006-04-11 #
13
################################################################################
16
--echo #========================================================================
17
--echo # 1. Partition management commands on HASH partitioned table
18
--echo # column in partitioning function is of type DATE
19
--echo #========================================================================
22
DROP TABLE IF EXISTS t1;
24
eval CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30));
25
# 2. Fill the table t1 with records
26
INSERT INTO t1 (f_date, f_varchar)
27
SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR)
29
WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
30
# 3. Calculate the number of inserted records.
31
SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
33
# DEBUG SELECT @exp_row_count;
34
# 4. Print the layout, check Readability
35
--source suite/parts/inc/partition_layout.inc
36
--source suite/parts/inc/partition_check_read1.inc
38
--echo #------------------------------------------------------------------------
39
--echo # 1.1 Increase number of PARTITIONS
40
--echo #------------------------------------------------------------------------
41
--echo # 1.1.1 ADD PARTITION to not partitioned table --> must fail
42
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
43
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
45
--echo # 1.1.2 Assign HASH partitioning
46
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
47
--source suite/parts/inc/partition_layout.inc
48
--source suite/parts/inc/partition_check_read1.inc
50
--echo # 1.1.3 Assign other HASH partitioning to already partitioned table
51
--echo # + test and switch back + test
52
ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
53
--source suite/parts/inc/partition_layout.inc
54
--source suite/parts/inc/partition_check_read1.inc
55
ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
56
--source suite/parts/inc/partition_layout.inc
57
--source suite/parts/inc/partition_check_read1.inc
59
--echo # 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
60
--error ER_PARTITION_WRONG_VALUES_ERROR
61
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
62
--error ER_PARTITION_WRONG_VALUES_ERROR
63
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
65
--echo # 1.1.5 Add two named partitions + test
66
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
67
--source suite/parts/inc/partition_layout.inc
68
--source suite/parts/inc/partition_check_read1.inc
70
--echo # 1.1.6 Add two named partitions, name clash --> must fail
71
--error ER_SAME_NAME_PARTITION
72
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
74
--echo # 1.1.7 Add one named partition + test
75
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
76
--source suite/parts/inc/partition_layout.inc
77
--source suite/parts/inc/partition_check_read1.inc
79
--echo # 1.1.8 Add four not named partitions + test
80
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
81
--source suite/parts/inc/partition_layout.inc
82
--source suite/parts/inc/partition_check_read1.inc
84
--echo #------------------------------------------------------------------------
85
--echo # 1.2 Decrease number of PARTITIONS
86
--echo #------------------------------------------------------------------------
87
--echo # 1.2.1 DROP PARTITION is not supported for HASH --> must fail
88
--error ER_ONLY_ON_RANGE_LIST_PARTITION
89
ALTER TABLE t1 DROP PARTITION part1;
91
--echo # 1.2.2 COALESCE PARTITION partitionname is not supported
92
--error ER_PARSE_ERROR
93
ALTER TABLE t1 COALESCE PARTITION part1;
95
--echo # 1.2.3 Decrease by 0 is non sense --> must fail
96
--error ER_COALESCE_PARTITION_NO_PARTITION
97
ALTER TABLE t1 COALESCE PARTITION 0;
99
--echo # 1.2.4 COALESCE one partition + test loop
103
ALTER TABLE t1 COALESCE PARTITION 1;
104
--source suite/parts/inc/partition_layout.inc
105
--source suite/parts/inc/partition_check_read1.inc
108
--echo # 1.2.5 COALESCE of last partition --> must fail
109
--error ER_DROP_LAST_PARTITION
110
ALTER TABLE t1 COALESCE PARTITION 1;
112
--echo # 1.2.6 Remove partitioning
113
ALTER TABLE t1 REMOVE PARTITIONING;
114
--source suite/parts/inc/partition_layout.inc
115
--source suite/parts/inc/partition_check_read1.inc
117
--echo # 1.2.7 Remove partitioning from not partitioned table --> ????
118
ALTER TABLE t1 REMOVE PARTITIONING;
120
--source suite/parts/inc/partition_check_drop.inc
123
--echo #========================================================================
124
--echo # 2. Partition management commands on KEY partitioned table
125
--echo #========================================================================
126
# 1. Create the table
128
DROP TABLE IF EXISTS t1;
130
eval CREATE TABLE t1 (
133
# 2. Fill the table t1 with some records
135
# 4. Print the layout, check Readability
136
--source suite/parts/inc/partition_layout.inc
137
--source suite/parts/inc/partition_check_read2.inc
139
--echo #------------------------------------------------------------------------
140
--echo # 2.1 Increase number of PARTITIONS
141
--echo # Some negative testcases are omitted (already checked with HASH).
142
--echo #------------------------------------------------------------------------
143
--echo # 2.1.1 Assign KEY partitioning
144
ALTER TABLE t1 PARTITION BY KEY(f_int1);
145
--source suite/parts/inc/partition_layout.inc
146
--source suite/parts/inc/partition_check_read2.inc
148
--echo # 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
149
--error ER_PARTITION_WRONG_VALUES_ERROR
150
ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
151
--error ER_PARTITION_WRONG_VALUES_ERROR
152
ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
154
--echo # 2.1.3 Add two named partitions + test
155
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
156
--source suite/parts/inc/partition_layout.inc
157
--source suite/parts/inc/partition_check_read2.inc
159
--echo # 2.1.4 Add one named partition + test
160
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
161
--source suite/parts/inc/partition_layout.inc
162
--source suite/parts/inc/partition_check_read2.inc
164
--echo # 2.1.5 Add four not named partitions + test
165
ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
166
--source suite/parts/inc/partition_layout.inc
167
--source suite/parts/inc/partition_check_read2.inc
169
--echo #------------------------------------------------------------------------
170
--echo # 2.2 Decrease number of PARTITIONS
171
--echo # Some negative testcases are omitted (already checked with HASH).
172
--echo #------------------------------------------------------------------------
173
--echo # 2.2.1 DROP PARTITION is not supported for KEY --> must fail
174
--error ER_ONLY_ON_RANGE_LIST_PARTITION
175
ALTER TABLE t1 DROP PARTITION part1;
177
--echo # 2.2.4 COALESCE one partition + test loop
181
ALTER TABLE t1 COALESCE PARTITION 1;
182
--source suite/parts/inc/partition_layout.inc
183
--source suite/parts/inc/partition_check_read2.inc
186
--echo # 2.2.5 COALESCE of last partition --> must fail
187
--error ER_DROP_LAST_PARTITION
188
ALTER TABLE t1 COALESCE PARTITION 1;
190
--echo # 2.2.6 Remove partitioning
191
ALTER TABLE t1 REMOVE PARTITIONING;
192
--source suite/parts/inc/partition_layout.inc
193
--source suite/parts/inc/partition_check_read2.inc
195
--echo # 2.2.7 Remove partitioning from not partitioned table --> ????
196
ALTER TABLE t1 REMOVE PARTITIONING;
198
--source suite/parts/inc/partition_check_drop.inc