1
################################################################################
2
# t/partition_supported_sql_funcs.inc # # #
4
# Tests frame for allowed sql functions #
7
#------------------------------------------------------------------------------#
8
# Original Author: HH #
9
# Original Date: 2006-11-22 #
13
################################################################################
14
--echo -------------------------------------------------------------------------
15
--echo --- $sqlfunc in partition with coltype $coltype
16
--echo -------------------------------------------------------------------------
17
--disable_abort_on_error
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 ;
26
--enable_abort_on_error
28
--echo -------------------------------------------------------------------------
29
--echo --- Create tables with $sqlfunc
30
--echo -------------------------------------------------------------------------
31
eval create table t1 (col1 $coltype) engine=$engine
32
partition by range($sqlfunc)
33
(partition p0 values less than (15),
34
partition p1 values less than maxvalue);
36
eval create table t2 (col1 $coltype) engine=$engine
37
partition by list($sqlfunc)
38
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
39
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
40
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
41
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
42
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
43
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
46
eval create table t3 (col1 $coltype) engine=$engine
47
partition by hash($sqlfunc);
49
eval create table t4 (colint int, col1 $coltype) engine=$engine
50
partition by range(colint)
51
subpartition by hash($sqlfunc) subpartitions 2
52
(partition p0 values less than (15),
53
partition p1 values less than maxvalue);
55
eval create table t5 (colint int, col1 $coltype) engine=$engine
56
partition by list(colint)
57
subpartition by hash($sqlfunc) subpartitions 2
58
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
59
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
60
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
61
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
62
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
63
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
66
eval create table t6 (colint int, col1 $coltype) engine=$engine
67
partition by range(colint)
68
(partition p0 values less than ($valsqlfunc),
69
partition p1 values less than maxvalue);
71
--echo -------------------------------------------------------------------------
72
--echo --- Access tables with $sqlfunc
73
--echo -------------------------------------------------------------------------
75
eval insert into t1 values ($val1);
76
eval insert into t1 values ($val2);
78
eval insert into t2 values ($val1);
79
eval insert into t2 values ($val2);
80
eval insert into t2 values ($val3);
82
eval insert into t3 values ($val1);
83
eval insert into t3 values ($val2);
84
eval insert into t3 values ($val3);
86
eval load data infile '../std_data_ln/parts/$infile' into table t4;
87
eval load data infile '../std_data_ln/parts/$infile' into table t5;
88
eval load data infile '../std_data_ln/parts/$infile' into table t6;
90
eval select $sqlfunc from t1 order by col1;
92
select * from t1 order by col1;
93
select * from t2 order by col1;
94
select * from t3 order by col1;
95
select * from t4 order by colint;
96
select * from t5 order by colint;
97
select * from t6 order by colint;
101
eval update t1 set col1=$val4 where col1=$val1;
102
eval update t2 set col1=$val4 where col1=$val1;
103
eval update t3 set col1=$val4 where col1=$val1;
104
eval update t4 set col1=$val4 where col1=$val1;
105
eval update t5 set col1=$val4 where col1=$val1;
106
eval update t6 set col1=$val4 where col1=$val1;
108
select * from t1 order by col1;
109
select * from t2 order by col1;
110
select * from t3 order by col1;
111
select * from t4 order by colint;
112
select * from t5 order by colint;
113
select * from t6 order by colint;
116
--echo -------------------------------------------------------------------------
117
--echo --- Alter tables with $sqlfunc
118
--echo -------------------------------------------------------------------------
120
--disable_abort_on_error
122
drop table if exists t11 ;
123
drop table if exists t22 ;
124
drop table if exists t33 ;
125
drop table if exists t44 ;
126
drop table if exists t55 ;
127
drop table if exists t66 ;
129
--enable_abort_on_error
131
eval create table t11 engine=$engine as select * from t1;
132
eval create table t22 engine=$engine as select * from t2;
133
eval create table t33 engine=$engine as select * from t3;
134
eval create table t44 engine=$engine as select * from t4;
135
eval create table t55 engine=$engine as select * from t5;
136
eval create table t66 engine=$engine as select * from t6;
138
partition by range($sqlfunc)
139
(partition p0 values less than (15),
140
partition p1 values less than maxvalue);
142
partition by list($sqlfunc)
143
(partition p0 values in (0,1,2,3,4,5,6,7,8,9,10),
144
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
145
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
146
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
147
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
148
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
151
partition by hash($sqlfunc);
153
partition by range(colint)
154
subpartition by hash($sqlfunc) subpartitions 2
155
(partition p0 values less than (15),
156
partition p1 values less than maxvalue);
158
partition by list(colint)
159
subpartition by hash($sqlfunc) subpartitions 2
160
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
161
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
162
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
163
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
164
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
165
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
168
partition by range(colint)
169
(partition p0 values less than ($valsqlfunc),
170
partition p1 values less than maxvalue);
172
select * from t11 order by col1;
173
select * from t22 order by col1;
174
select * from t33 order by col1;
175
select * from t44 order by colint;
176
select * from t55 order by colint;
177
select * from t66 order by colint;
181
--echo ---------------------------
182
--echo ---- some alter table begin
183
--echo ---------------------------
185
reorganize partition p0,p1 into
186
(partition s1 values less than maxvalue);
187
select * from t11 order by col1;
190
reorganize partition s1 into
191
(partition p0 values less than (15),
192
partition p1 values less than maxvalue);
193
select * from t11 order by col1;
196
partition by list(colint)
197
subpartition by hash($sqlfunc) subpartitions 5
198
(partition p0 values in (1,2,3,4,5,6,7,8,9,10),
199
partition p1 values in (11,12,13,14,15,16,17,18,19,20),
200
partition p2 values in (21,22,23,24,25,26,27,28,29,30),
201
partition p3 values in (31,32,33,34,35,36,37,38,39,40),
202
partition p4 values in (41,42,43,44,45,46,47,48,49,50),
203
partition p5 values in (51,52,53,54,55,56,57,58,59,60)
205
show create table t55;
206
select * from t55 order by colint;
209
reorganize partition p0,p1 into
210
(partition s1 values less than maxvalue);
211
select * from t66 order by colint;
214
reorganize partition s1 into
215
(partition p0 values less than ($valsqlfunc),
216
partition p1 values less than maxvalue);
217
select * from t66 order by colint;
220
reorganize partition p0,p1 into
221
(partition s1 values less than maxvalue);
222
select * from t66 order by colint;
225
reorganize partition s1 into
226
(partition p0 values less than ($valsqlfunc),
227
partition p1 values less than maxvalue);
228
select * from t66 order by colint;
236
--source suite/parts/inc/part_supported_sql_funcs_delete.inc
244
--source suite/parts/inc/part_supported_sql_funcs_delete.inc
245
--echo -------------------------
246
--echo ---- some alter table end
247
--echo -------------------------
250
drop table if exists t1 ;
251
drop table if exists t2 ;
252
drop table if exists t3 ;
253
drop table if exists t4 ;
254
drop table if exists t5 ;
255
drop table if exists t6 ;
256
drop table if exists t11 ;
257
drop table if exists t22 ;
258
drop table if exists t33 ;
259
drop table if exists t44 ;
260
drop table if exists t55 ;
261
drop table if exists t66 ;