MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
partition_supported_sql_funcs.inc
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