Debian Codegraph @

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

raw
1 ################################################################################
2 # t/partition_supported_sql_funcs.inc #
3 # #
4 # Purpose: #
5 # Tests frame for allowed sql functions #
6 # #
7 # #
8 #------------------------------------------------------------------------------#
9 # Original Author: HH #
10 # Original Date: 2006-11-22 #
11 # Change Author: MattiasJ #
12 # Change Date: 2008-05-15 #
13 ################################################################################
14 --echo -------------------------------------------------------------------------
15 --echo --- $sqlfunc in partition with coltype $coltype
16 --echo -------------------------------------------------------------------------
17 --disable_abort_on_error
18 --disable_warnings
19 drop table if exists t1 ;
20 drop table if exists t2 ;
21 drop table if exists t3 ;
22 drop table if exists t4 ;
23 drop table if exists t5 ;
24 drop table if exists t6 ;
25 --enable_warnings
26 --enable_abort_on_error
27
28 let $part_t1= partition by range($sqlfunc)
29 (partition p0 values less than (15),
30 partition p1 values less than maxvalue);
31
32 let $part_t2= partition by list($sqlfunc)
33 (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
34 partition p1 values in (11,12,13,14,15,16,17,18,19,20),
35 partition p2 values in (21,22,23,24,25,26,27,28,29,30),
36 partition p3 values in (31,32,33,34,35,36,37,38,39,40),
37 partition p4 values in (41,42,43,44,45,46,47,48,49,50),
38 partition p5 values in (51,52,53,54,55,56,57,58,59,60)
39 );
40
41 let $part_t3= partition by hash($sqlfunc);
42
43 let $part_t4= partition by range(colint)
44 subpartition by hash($sqlfunc) subpartitions 2
45 (partition p0 values less than (15),
46 partition p1 values less than maxvalue);
47
48 let $part_t5= partition by list(colint)
49 subpartition by hash($sqlfunc) subpartitions 2
50 (partition p0 values in (1,2,3,4,5,6,7,8,9,10),
51 partition p1 values in (11,12,13,14,15,16,17,18,19,20),
52 partition p2 values in (21,22,23,24,25,26,27,28,29,30),
53 partition p3 values in (31,32,33,34,35,36,37,38,39,40),
54 partition p4 values in (41,42,43,44,45,46,47,48,49,50),
55 partition p5 values in (51,52,53,54,55,56,57,58,59,60)
56 );
57 if ($max_8_partitions)
58 {
59 let $part_t5= partition by list(colint)
60 subpartition by hash($sqlfunc) subpartitions 2
61 (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
62 partition p1 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
63 partition p2 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
64 partition p3 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
65 );
66 }
67
68 let $part_t6= partition by range(colint)
69 (partition p0 values less than ($valsqlfunc),
70 partition p1 values less than maxvalue);
71
72 let $part_t55_altered= partition by list(colint)
73 subpartition by hash($sqlfunc) subpartitions 5
74 (partition p0 values in (1,2,3,4,5,6,7,8,9,10),
75 partition p1 values in (11,12,13,14,15,16,17,18,19,20),
76 partition p2 values in (21,22,23,24,25,26,27,28,29,30),
77 partition p3 values in (31,32,33,34,35,36,37,38,39,40),
78 partition p4 values in (41,42,43,44,45,46,47,48,49,50),
79 partition p5 values in (51,52,53,54,55,56,57,58,59,60)
80 );
81 if ($max_8_partitions)
82 {
83 let $part_t55_altered= partition by list(colint)
84 subpartition by hash($sqlfunc) subpartitions 4
85 (partition p0 values in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
86 partition p1 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
87 );
88 }
89 --echo -------------------------------------------------------------------------
90 --echo --- Create tables with $sqlfunc
91 --echo -------------------------------------------------------------------------
92 eval create table t1 (col1 $coltype) engine=$engine
93 $part_t1;
94
95 eval create table t2 (col1 $coltype) engine=$engine
96 $part_t2;
97
98 eval create table t3 (col1 $coltype) engine=$engine
99 $part_t3;
100
101 eval create table t4 (colint int, col1 $coltype) engine=$engine
102 $part_t4;
103
104 eval create table t5 (colint int, col1 $coltype) engine=$engine
105 $part_t5;
106
107 eval create table t6 (colint int, col1 $coltype) engine=$engine
108 $part_t6;
109
110 --echo -------------------------------------------------------------------------
111 --echo --- Access tables with $sqlfunc
112 --echo -------------------------------------------------------------------------
113
114 begin;
115 eval insert into t1 values ($val1);
116 eval insert into t1 values ($val2);
117
118 eval insert into t2 values ($val1);
119 eval insert into t2 values ($val2);
120 eval insert into t2 values ($val3);
121
122 eval insert into t3 values ($val1);
123 eval insert into t3 values ($val2);
124 eval insert into t3 values ($val3);
125 commit;
126
127 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
128 eval load data infile '$MYSQLTEST_VARDIR/std_data/parts/$infile' into table t4;
129 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
130 eval load data infile '$MYSQLTEST_VARDIR/std_data/parts/$infile' into table t5;
131 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
132 eval load data infile '$MYSQLTEST_VARDIR/std_data/parts/$infile' into table t6;
133
134 eval select $sqlfunc from t1 order by col1;
135
136 select * from t1 order by col1;
137 select * from t2 order by col1;
138 select * from t3 order by col1;
139 select * from t4 order by colint;
140 select * from t5 order by colint;
141 select * from t6 order by colint;
142
143 if ($do_long_tests)
144 {
145 begin;
146 eval update t1 set col1=$val4 where col1=$val1;
147 eval update t2 set col1=$val4 where col1=$val1;
148 eval update t3 set col1=$val4 where col1=$val1;
149 eval update t4 set col1=$val4 where col1=$val1;
150 eval update t5 set col1=$val4 where col1=$val1;
151 eval update t6 set col1=$val4 where col1=$val1;
152 commit;
153
154 select * from t1 order by col1;
155 select * from t2 order by col1;
156 select * from t3 order by col1;
157 select * from t4 order by colint;
158 select * from t5 order by colint;
159 select * from t6 order by colint;
160 }
161
162 --echo -------------------------------------------------------------------------
163 --echo --- Alter tables with $sqlfunc
164 --echo -------------------------------------------------------------------------
165
166 --disable_abort_on_error
167 --disable_warnings
168 drop table if exists t11 ;
169 drop table if exists t22 ;
170 drop table if exists t33 ;
171 drop table if exists t44 ;
172 drop table if exists t55 ;
173 drop table if exists t66 ;
174 --enable_warnings
175 --enable_abort_on_error
176
177 eval create table t11 engine=$engine as select * from t1;
178 eval create table t22 engine=$engine as select * from t2;
179 eval create table t33 engine=$engine as select * from t3;
180 eval create table t44 engine=$engine as select * from t4;
181 eval create table t55 engine=$engine as select * from t5;
182 eval create table t66 engine=$engine as select * from t6;
183 eval alter table t11
184 $part_t1;
185 eval alter table t22
186 $part_t2;
187 eval alter table t33
188 $part_t3;
189 eval alter table t44
190 $part_t4;
191 eval alter table t55
192 $part_t5;
193 eval alter table t66
194 $part_t6;
195
196 select * from t11 order by col1;
197 select * from t22 order by col1;
198 select * from t33 order by col1;
199 select * from t44 order by colint;
200 select * from t55 order by colint;
201 select * from t66 order by colint;
202
203 if ($do_long_tests)
204 {
205 --echo ---------------------------
206 --echo ---- some alter table begin
207 --echo ---------------------------
208 if (!$no_reorg_partition)
209 {
210 eval alter table t11
211 reorganize partition p0,p1 into
212 (partition s1 values less than maxvalue);
213 select * from t11 order by col1;
214
215 eval alter table t11
216 reorganize partition s1 into
217 (partition p0 values less than (15),
218 partition p1 values less than maxvalue);
219 select * from t11 order by col1;
220 }
221
222 eval alter table t55
223 $part_t55_altered;
224 show create table t55;
225 select * from t55 order by colint;
226
227 if (!$no_reorg_partition)
228 {
229 eval alter table t66
230 reorganize partition p0,p1 into
231 (partition s1 values less than maxvalue);
232 select * from t66 order by colint;
233
234 eval alter table t66
235 reorganize partition s1 into
236 (partition p0 values less than ($valsqlfunc),
237 partition p1 values less than maxvalue);
238 select * from t66 order by colint;
239
240 eval alter table t66
241 reorganize partition p0,p1 into
242 (partition s1 values less than maxvalue);
243 select * from t66 order by colint;
244
245 eval alter table t66
246 reorganize partition s1 into
247 (partition p0 values less than ($valsqlfunc),
248 partition p1 values less than maxvalue);
249 select * from t66 order by colint;
250 }
251
252 let $t1=t1;
253 let $t2=t2;
254 let $t3=t3;
255 let $t4=t4;
256 let $t5=t5;
257 let $t6=t6;
258 --source suite/parts/inc/part_supported_sql_funcs_delete.inc
259
260 let $t1=t11;
261 let $t2=t22;
262 let $t3=t33;
263 let $t4=t44;
264 let $t5=t55;
265 let $t6=t66;
266 --source suite/parts/inc/part_supported_sql_funcs_delete.inc
267 --echo -------------------------
268 --echo ---- some alter table end
269 --echo -------------------------
270 }
271 --disable_warnings
272 drop table if exists t1 ;
273 drop table if exists t2 ;
274 drop table if exists t3 ;
275 drop table if exists t4 ;
276 drop table if exists t5 ;
277 drop table if exists t6 ;
278 drop table if exists t11 ;
279 drop table if exists t22 ;
280 drop table if exists t33 ;
281 drop table if exists t44 ;
282 drop table if exists t55 ;
283 drop table if exists t66 ;
284 --enable_warnings
285