1
################################################################################
2
# inc/partition_alter2.inc #
5
# Tests where the columns used within the partitioning function are altered. #
6
# This routine is only useful for the partition_<feature>_<engine> tests. .#
8
#------------------------------------------------------------------------------#
9
# Original Author: mleich #
10
# Original Date: 2006-03-05 #
14
################################################################################
17
--echo #========================================================================
18
--echo # 1 Increase the size of the column used in the partitioning
19
--echo # function and/or PRIMARY KEY and/or UNIQUE INDEX
20
--echo #========================================================================
21
--echo #------------------------------------------------------------------------
22
--echo # 1.1 ALTER column f_int2 not used in partitioning function
23
--echo #------------------------------------------------------------------------
24
# Rule: Only f_int1 is used within the partitioning function
25
# ---> inc/partition_alter_11.inc
26
let $alter= ALTER TABLE t1 MODIFY f_int2 BIGINT;
27
--echo # 1.1.1 no PRIMARY KEY or UNIQUE INDEX exists
29
--source suite/parts/inc/partition_alter_11.inc
33
--echo # 1.1.2 PRIMARY KEY exists
34
# The value of the direct following test is maybe covered by the test with
35
# the PRIMARY KEY containing two columns.
36
if ($more_pk_ui_tests)
38
let $unique= , PRIMARY KEY (f_int1);
39
--source suite/parts/inc/partition_alter_11.inc
41
let $unique= , PRIMARY KEY (f_int1,f_int2);
42
--source suite/parts/inc/partition_alter_11.inc
43
let $unique= , PRIMARY KEY (f_int2,f_int1);
44
--source suite/parts/inc/partition_alter_11.inc
47
--echo # 1.1.3 UNIQUE INDEX exists
48
# The value of the direct following test is maybe covered by the test with
49
# the UNIQUE INDEX containing two columns
50
if ($more_pk_ui_tests)
52
let $unique= , UNIQUE INDEX uidx1 (f_int1);
53
--source suite/parts/inc/partition_alter_11.inc
55
let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2);
56
--source suite/parts/inc/partition_alter_11.inc
57
let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1);
58
--source suite/parts/inc/partition_alter_11.inc
60
if ($more_pk_ui_tests)
62
# The value of the tests 1.2 is maybe covered by the tests 1.3
63
--echo #------------------------------------------------------------------------
64
--echo # 1.2 ALTER column f_int1 used in partitioning function
65
--echo #------------------------------------------------------------------------
66
# Rule: Only f_int1 is used within the partitioning function
67
# ---> inc/partition_alter_11.inc
68
let $alter= ALTER TABLE t1 MODIFY f_int1 BIGINT;
69
--echo # 1.2.1 no PRIMARY KEY or UNIQUE INDEX exists
71
--source suite/parts/inc/partition_alter_11.inc
72
--source suite/parts/inc/partition_alter_13.inc
76
--echo # 1.2.2 PRIMARY KEY exists
77
let $unique= , PRIMARY KEY (f_int1);
78
--source suite/parts/inc/partition_alter_11.inc
79
let $unique= , PRIMARY KEY (f_int1,f_int2);
80
--source suite/parts/inc/partition_alter_11.inc
81
--source suite/parts/inc/partition_alter_13.inc
82
let $unique= , PRIMARY KEY (f_int2,f_int1);
83
--source suite/parts/inc/partition_alter_11.inc
84
--source suite/parts/inc/partition_alter_13.inc
87
--echo # 1.2.3 UNIQUE INDEX exists
88
let $unique= , UNIQUE INDEX uidx (f_int1);
89
--source suite/parts/inc/partition_alter_11.inc
90
let $unique= , UNIQUE INDEX uidx (f_int1,f_int2);
91
--source suite/parts/inc/partition_alter_11.inc
92
--source suite/parts/inc/partition_alter_13.inc
93
let $unique= , UNIQUE INDEX uidx (f_int2,f_int1);
94
--source suite/parts/inc/partition_alter_11.inc
95
--source suite/parts/inc/partition_alter_13.inc
98
--echo #------------------------------------------------------------------------
99
--echo # 1.3 ALTER column f_int1 and f_int2
100
--echo # f_int1 or (f_int1 and f_int2) used in partitioning function
101
--echo #------------------------------------------------------------------------
102
# Rule: f_int1 and f_int2 is used within the partitioning function
103
# ---> inc/partition_alter_13.inc
104
let $alter= ALTER TABLE t1 MODIFY f_int1 BIGINT, MODIFY f_int2 BIGINT;
105
--echo # 1.3.1 no PRIMARY KEY or UNIQUE INDEX exists
107
--source suite/parts/inc/partition_alter_11.inc
108
--source suite/parts/inc/partition_alter_13.inc
112
--echo # 1.3.2 PRIMARY KEY exists
113
# The value of the direct following test is maybe covered by the test with
114
# the PRIMARY KEY containing two columns.
115
if ($more_pk_ui_tests)
117
let $unique= , PRIMARY KEY (f_int1);
118
--source suite/parts/inc/partition_alter_11.inc
120
let $unique= , PRIMARY KEY (f_int1,f_int2);
121
--source suite/parts/inc/partition_alter_11.inc
122
--source suite/parts/inc/partition_alter_13.inc
123
let $unique= , PRIMARY KEY (f_int2,f_int1);
124
--source suite/parts/inc/partition_alter_11.inc
125
--source suite/parts/inc/partition_alter_13.inc
128
--echo # 1.3.3 UNIQUE INDEX exists
129
# The value of the direct following test is maybe covered by the test with
130
# the UNIQUE INDEX containing two columns.
131
if ($more_pk_ui_tests)
133
let $unique= , UNIQUE INDEX uidx (f_int1);
134
--source suite/parts/inc/partition_alter_11.inc
136
let $unique= , UNIQUE INDEX uidx (f_int1,f_int2);
137
--source suite/parts/inc/partition_alter_11.inc
138
--source suite/parts/inc/partition_alter_13.inc
139
let $unique= , UNIQUE INDEX uidx (f_int2,f_int1);
140
--source suite/parts/inc/partition_alter_11.inc
141
--source suite/parts/inc/partition_alter_13.inc
144
--echo #========================================================================
145
--echo # 2 Decrease the size of the column used in the partitioning
146
--echo # function and/or PRIMARY KEY and/or UNIQUE INDEX
147
--echo #========================================================================
148
--echo #------------------------------------------------------------------------
149
--echo # 2.1 ALTER column f_int2 not used in partitioning function
150
--echo #------------------------------------------------------------------------
151
# Rule: Only f_int1 is used within the partitioning function
152
# ---> inc/partition_alter_11.inc
153
let $alter= ALTER TABLE t1 MODIFY f_int2 MEDIUMINT;
154
--echo # 2.1.1 no PRIMARY KEY or UNIQUE INDEX exists
156
--source suite/parts/inc/partition_alter_11.inc
160
# The value of the direct following test is maybe covered by the test with
161
# the PRIMARY KEY containing two columns.
162
if ($more_pk_ui_tests)
164
--echo # 2.1.2 PRIMARY KEY exists
165
let $unique= , PRIMARY KEY (f_int1);
166
--source suite/parts/inc/partition_alter_11.inc
168
let $unique= , PRIMARY KEY (f_int1,f_int2);
169
--source suite/parts/inc/partition_alter_11.inc
170
let $unique= , PRIMARY KEY (f_int2,f_int1);
171
--source suite/parts/inc/partition_alter_11.inc
174
--echo # 2.1.3 UNIQUE INDEX exists
175
# The value of the direct following test is maybe covered by the test with
176
# the UNIQUE INDEX containing two columns.
177
if ($more_pk_ui_tests)
179
let $unique= , UNIQUE INDEX uidx1 (f_int1);
180
--source suite/parts/inc/partition_alter_11.inc
182
let $unique= , UNIQUE INDEX uidx1 (f_int1,f_int2);
183
--source suite/parts/inc/partition_alter_11.inc
184
let $unique= , UNIQUE INDEX uidx1 (f_int2,f_int1);
185
--source suite/parts/inc/partition_alter_11.inc
187
if ($more_pk_ui_tests)
189
# The value of the tests 2.2 is maybe covered by the tests 2.3
190
--echo #------------------------------------------------------------------------
191
--echo # 2.2 ALTER column f_int1 used in partitioning function
192
--echo #------------------------------------------------------------------------
193
# Rule: Only f_int1 is used within the partitioning function
194
# ---> inc/partition_alter_11.inc
195
let $alter= ALTER TABLE t1 MODIFY f_int1 MEDIUMINT;
196
--echo # 2.2.1 no PRIMARY KEY or UNIQUE INDEX exists
198
--source suite/parts/inc/partition_alter_11.inc
199
--source suite/parts/inc/partition_alter_13.inc
203
--echo # 2.2.2 PRIMARY KEY exists
204
let $unique= , PRIMARY KEY (f_int1);
205
--source suite/parts/inc/partition_alter_11.inc
206
let $unique= , PRIMARY KEY (f_int1,f_int2);
207
--source suite/parts/inc/partition_alter_11.inc
208
--source suite/parts/inc/partition_alter_13.inc
209
let $unique= , PRIMARY KEY (f_int2,f_int1);
210
--source suite/parts/inc/partition_alter_11.inc
211
--source suite/parts/inc/partition_alter_13.inc
214
--echo # 2.2.3 UNIQUE INDEX exists
215
let $unique= , UNIQUE INDEX uidx (f_int1);
216
--source suite/parts/inc/partition_alter_11.inc
217
let $unique= , UNIQUE INDEX uidx (f_int1,f_int2);
218
--source suite/parts/inc/partition_alter_11.inc
219
--source suite/parts/inc/partition_alter_13.inc
220
let $unique= , UNIQUE INDEX uidx (f_int2,f_int1);
221
--source suite/parts/inc/partition_alter_11.inc
222
--source suite/parts/inc/partition_alter_13.inc
225
--echo #------------------------------------------------------------------------
226
--echo # 2.3 ALTER column f_int1 and f_int2 used in partitioning function
227
--echo #------------------------------------------------------------------------
228
# Rule: f_int1 and f_int2 is used within the partitioning function
229
# ---> inc/partition_alter_13.inc
230
let $alter= ALTER TABLE t1 MODIFY f_int1 MEDIUMINT, MODIFY f_int2 MEDIUMINT;
231
--echo # 2.3.1 no PRIMARY KEY or UNIQUE INDEX exists
233
--source suite/parts/inc/partition_alter_11.inc
234
--source suite/parts/inc/partition_alter_13.inc
238
--echo # 2.3.2 PRIMARY KEY exists
239
# The value of the direct following test is maybe covered by the test with
240
# the PRIMARY KEY containing two columns.
241
if ($more_pk_ui_tests)
243
let $unique= , PRIMARY KEY (f_int1);
244
--source suite/parts/inc/partition_alter_11.inc
246
let $unique= , PRIMARY KEY (f_int1,f_int2);
247
--source suite/parts/inc/partition_alter_11.inc
248
--source suite/parts/inc/partition_alter_13.inc
249
let $unique= , PRIMARY KEY (f_int2,f_int1);
250
--source suite/parts/inc/partition_alter_11.inc
251
--source suite/parts/inc/partition_alter_13.inc
254
--echo # 2.3.3 UNIQUE INDEX exists
255
# The value of the direct following test is maybe covered by the test with
256
# the UNIQUE INDEX containing two columns.
257
if ($more_pk_ui_tests)
259
let $unique= , UNIQUE INDEX uidx (f_int1);
260
--source suite/parts/inc/partition_alter_11.inc
262
let $unique= , UNIQUE INDEX uidx (f_int1,f_int2);
263
--source suite/parts/inc/partition_alter_11.inc
264
--source suite/parts/inc/partition_alter_13.inc
265
let $unique= , UNIQUE INDEX uidx (f_int2,f_int1);
266
--source suite/parts/inc/partition_alter_11.inc
267
--source suite/parts/inc/partition_alter_13.inc
273
--echo #========================================================================
274
--echo # 3 ALTER the type of the column used in the partitioning
275
--echo # function and/or PRIMARY KEY and/or UNIQUE INDEX
276
--echo # INTEGER --> FLOAT
277
--echo # INTEGER --> DECIMAL
278
--echo # INTEGER --> VARCHAR
279
--echo # mleich: I assume that at least the first two variants are of
280
--echo # some interest. But I am unsure if the server allows such
281
--echo # conversions. I also think that such operations have a
282
--echo # conversions very small likelihood.
283
--echo # To be implemented.
284
--echo #========================================================================