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