Debian Codegraph @

mysql-test/suite/parts/inc/partition_alter3.inc

raw
1 ################################################################################
2 # inc/partition_alter3.inc #
3 # #
4 # Purpose: #
5 # Tests for partition management commands for HASH and KEY partitioning #
6 # #
7 #------------------------------------------------------------------------------#
8 # Original Author: mleich #
9 # Original Date: 2006-04-11 #
10 # Change Author: #
11 # Change Date: #
12 # Change: #
13 ################################################################################
14
15 --echo
16 --echo #========================================================================
17 --echo # 1. Partition management commands on HASH partitioned table
18 --echo # column in partitioning function is of type DATE
19 --echo #========================================================================
20 # 1. Create the table
21 --disable_warnings
22 DROP TABLE IF EXISTS t1;
23 --enable_warnings
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)
28 FROM t0_template
29 WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
30 # 3. Calculate the number of inserted records.
31 --disable_cursor_protocol
32 SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
33 INTO @exp_row_count;
34 --enable_cursor_protocol
35 # DEBUG SELECT @exp_row_count;
36 # 4. Print the layout, check Readability
37 --source suite/parts/inc/partition_layout.inc
38 --source suite/parts/inc/partition_check_read1.inc
39 #
40 --echo #------------------------------------------------------------------------
41 --echo # 1.1 Increase number of PARTITIONS
42 --echo #------------------------------------------------------------------------
43 --echo # 1.1.1 ADD PARTITION to not partitioned table --> must fail
44 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
45 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
46 #
47 --echo # 1.1.2 Assign HASH partitioning
48 ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
49 --source suite/parts/inc/partition_layout.inc
50 --source suite/parts/inc/partition_check_read1.inc
51 #
52 --echo # 1.1.3 Assign other HASH partitioning to already partitioned table
53 --echo # + test and switch back + test
54 ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
55 --source suite/parts/inc/partition_layout.inc
56 --source suite/parts/inc/partition_check_read1.inc
57 ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
58 --source suite/parts/inc/partition_layout.inc
59 --source suite/parts/inc/partition_check_read1.inc
60 #
61 --echo # 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
62 --error ER_PARTITION_WRONG_VALUES_ERROR
63 ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
64 --error ER_PARTITION_WRONG_VALUES_ERROR
65 ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
66 #
67 --echo # 1.1.5 Add two named partitions + test
68 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
69 --source suite/parts/inc/partition_layout.inc
70 --source suite/parts/inc/partition_check_read1.inc
71 #
72 --echo # 1.1.6 Add two named partitions, name clash --> must fail
73 --error ER_SAME_NAME_PARTITION
74 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
75 #
76 --echo # 1.1.7 Add one named partition + test
77 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
78 --source suite/parts/inc/partition_layout.inc
79 --source suite/parts/inc/partition_check_read1.inc
80 #
81 --echo # 1.1.8 Add four not named partitions + test
82 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
83 --source suite/parts/inc/partition_layout.inc
84 --source suite/parts/inc/partition_check_read1.inc
85
86 --echo #------------------------------------------------------------------------
87 --echo # 1.2 Decrease number of PARTITIONS
88 --echo #------------------------------------------------------------------------
89 --echo # 1.2.1 DROP PARTITION is not supported for HASH --> must fail
90 --error ER_ONLY_ON_RANGE_LIST_PARTITION
91 ALTER TABLE t1 DROP PARTITION part1;
92 #
93 --echo # 1.2.2 COALESCE PARTITION partitionname is not supported
94 --error ER_PARSE_ERROR
95 ALTER TABLE t1 COALESCE PARTITION part1;
96 #
97 --echo # 1.2.3 Decrease by 0 is non sense --> must fail
98 --error ER_COALESCE_PARTITION_NO_PARTITION
99 ALTER TABLE t1 COALESCE PARTITION 0;
100 #
101 --echo # 1.2.4 COALESCE one partition + test loop
102 let $loop= 7;
103 while ($loop)
104 {
105 ALTER TABLE t1 COALESCE PARTITION 1;
106 --source suite/parts/inc/partition_layout.inc
107 --source suite/parts/inc/partition_check_read1.inc
108 dec $loop;
109 }
110 --echo # 1.2.5 COALESCE of last partition --> must fail
111 --error ER_DROP_LAST_PARTITION
112 ALTER TABLE t1 COALESCE PARTITION 1;
113 #
114 --echo # 1.2.6 Remove partitioning
115 ALTER TABLE t1 REMOVE PARTITIONING;
116 --source suite/parts/inc/partition_layout.inc
117 --source suite/parts/inc/partition_check_read1.inc
118 #
119 --echo # 1.2.7 Remove partitioning from not partitioned table --> ????
120 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
121 ALTER TABLE t1 REMOVE PARTITIONING;
122 DROP TABLE t1;
123 --source suite/parts/inc/partition_check_drop.inc
124
125 --echo
126 --echo #========================================================================
127 --echo # 2. Partition management commands on KEY partitioned table
128 --echo #========================================================================
129 # 1. Create the table
130 --disable_warnings
131 DROP TABLE IF EXISTS t1;
132 --enable_warnings
133 eval CREATE TABLE t1 (
134 $column_list
135 );
136 # 2. Fill the table t1 with some records
137 eval $insert_all;
138 # 4. Print the layout, check Readability
139 --source suite/parts/inc/partition_layout.inc
140 --source suite/parts/inc/partition_check_read2.inc
141 #
142 --echo #------------------------------------------------------------------------
143 --echo # 2.1 Increase number of PARTITIONS
144 --echo # Some negative testcases are omitted (already checked with HASH).
145 --echo #------------------------------------------------------------------------
146 --echo # 2.1.1 Assign KEY partitioning
147 ALTER TABLE t1 PARTITION BY KEY(f_int1);
148 --source suite/parts/inc/partition_layout.inc
149 --source suite/parts/inc/partition_check_read2.inc
150 #
151 --echo # 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
152 --error ER_PARTITION_WRONG_VALUES_ERROR
153 ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
154 --error ER_PARTITION_WRONG_VALUES_ERROR
155 ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
156 #
157 --echo # 2.1.3 Add two named partitions + test
158 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
159 --source suite/parts/inc/partition_layout.inc
160 --source suite/parts/inc/partition_check_read2.inc
161 #
162 --echo # 2.1.4 Add one named partition + test
163 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
164 --source suite/parts/inc/partition_layout.inc
165 --source suite/parts/inc/partition_check_read2.inc
166 #
167 --echo # 2.1.5 Add four not named partitions + test
168 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
169 --source suite/parts/inc/partition_layout.inc
170 --source suite/parts/inc/partition_check_read2.inc
171
172 --echo #------------------------------------------------------------------------
173 --echo # 2.2 Decrease number of PARTITIONS
174 --echo # Some negative testcases are omitted (already checked with HASH).
175 --echo #------------------------------------------------------------------------
176 --echo # 2.2.1 DROP PARTITION is not supported for KEY --> must fail
177 --error ER_ONLY_ON_RANGE_LIST_PARTITION
178 ALTER TABLE t1 DROP PARTITION part1;
179 #
180 --echo # 2.2.4 COALESCE one partition + test loop
181 let $loop= 7;
182 while ($loop)
183 {
184 ALTER TABLE t1 COALESCE PARTITION 1;
185 --source suite/parts/inc/partition_layout.inc
186 --source suite/parts/inc/partition_check_read2.inc
187 dec $loop;
188 }
189 --echo # 2.2.5 COALESCE of last partition --> must fail
190 --error ER_DROP_LAST_PARTITION
191 ALTER TABLE t1 COALESCE PARTITION 1;
192 #
193 --echo # 2.2.6 Remove partitioning
194 ALTER TABLE t1 REMOVE PARTITIONING;
195 --source suite/parts/inc/partition_layout.inc
196 --source suite/parts/inc/partition_check_read2.inc
197 #
198 --echo # 2.2.7 Remove partitioning from not partitioned table --> ????
199 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
200 ALTER TABLE t1 REMOVE PARTITIONING;
201 DROP TABLE t1;
202 --source suite/parts/inc/partition_check_drop.inc
203