1
################################################################################
2
# t/partition_supported_sql_funcs_main.inc #
5
# Tests which SQL functions are allowed in partinioning clauses. #
8
#------------------------------------------------------------------------------#
9
# Original Author: HH #
10
# Original Date: 2006-11-22 #
15
# This test uses a test frame (partition_supported_sql_funcs.inc) for every #
16
# SQL function allowed in the partitioning parts of CREATE and ALTE TABLE. #
17
# The variales represent the #
18
# - SQL function isself with a column (sqlfunc) and a literal (valsqlsunc), #
19
# - the type of the column (coltype), #
20
# - a file with test values of the coltype (infile) and #
21
# - single test values (val1 to val4). #
22
# The test frame includes CREATE/ALTER TABLE and some access statements. #
23
# Column types are int, float(7,4), char(1), date and time depending on the #
24
# SQL function. The test frame uses the include file #
25
# "part_supported_sql_funcs_delete.inc" testing the deletion of #
27
# The CREATE and ALTER TABLE statement do not cover the complete partitions #
28
# functions, but will ashure that the SQL functions are basically working. #
29
################################################################################
32
let $sqlfunc = abs(col1);
33
let $valsqlfunc = abs(15);
35
let $infile = part_supported_sql_funcs_int_int.inc;
40
--source suite/parts/inc/partition_supported_sql_funcs.inc
43
let $sqlfunc = ceiling(col1);
44
let $valsqlfunc = ceiling(15);
45
let $coltype = float(7,4);
46
let $infile = part_supported_sql_funcs_int_float.inc;
51
# DISABLED due to bug 30577
52
#--source suite/parts/inc/partition_supported_sql_funcs.inc
54
let $sqlfunc = floor(col1);
55
let $valsqlfunc = floor(15.123);
56
let $coltype = float(7,4);
57
let $infile = part_supported_sql_funcs_int_float.inc;
62
# DISABLED due to bug 30577
63
#--source suite/parts/inc/partition_supported_sql_funcs.inc
65
let $sqlfunc = mod(col1,10);
66
let $valsqlfunc = mod(15,10);
68
let $infile = part_supported_sql_funcs_int_int.inc;
73
--source suite/parts/inc/partition_supported_sql_funcs.inc
75
let $sqlfunc = day(col1);
76
let $valsqlfunc = day('2006-12-21');
78
let $infile = part_supported_sql_funcs_int_date.inc;
79
let $val1 = '2006-02-03';
80
let $val2 = '2006-01-17';
81
let $val3 = '2006-01-25';
82
let $val4 = '2006-02-05';
83
--source suite/parts/inc/partition_supported_sql_funcs.inc
85
let $sqlfunc = dayofmonth(col1);
86
let $valsqlfunc = dayofmonth('2006-12-24');
88
let $infile = part_supported_sql_funcs_int_date.inc;
89
let $val1 = '2006-02-03';
90
let $val2 = '2006-01-17';
91
let $val3 = '2006-01-25';
92
let $val4 = '2006-02-05';
93
--source suite/parts/inc/partition_supported_sql_funcs.inc
95
let $sqlfunc = dayofweek(col1);
96
let $valsqlfunc = dayofweek('2006-12-24');
98
let $infile = part_supported_sql_funcs_int_date.inc;
99
let $val1 = '2006-01-03';
100
let $val2 = '2006-02-17';
101
let $val3 = '2006-01-25';
102
let $val4 = '2006-02-05';
103
--source suite/parts/inc/partition_supported_sql_funcs.inc
105
let $sqlfunc = dayofyear(col1);
106
let $valsqlfunc = dayofyear('2006-12-25');
108
let $infile = part_supported_sql_funcs_int_date.inc;
109
let $val1 = '2006-01-03';
110
let $val2 = '2006-01-17';
111
let $val3 = '2006-02-25';
112
let $val4 = '2006-02-05';
113
--source suite/parts/inc/partition_supported_sql_funcs.inc
114
let $coltype = char(30);
115
--source suite/parts/inc/partition_supported_sql_funcs.inc
117
let $sqlfunc = extract(month from col1);
118
let $valsqlfunc = extract(year from '1998-11-23');
120
let $infile = part_supported_sql_funcs_int_date.inc;
121
let $val1 = '2006-01-03';
122
let $val2 = '2006-02-17';
123
let $val3 = '2006-01-25';
124
let $val4 = '2006-02-05';
125
--source suite/parts/inc/partition_supported_sql_funcs.inc
127
let $sqlfunc = hour(col1);
128
let $valsqlfunc = hour('18:30');
130
let $infile = part_supported_sql_funcs_int_time.inc;
135
--source suite/parts/inc/partition_supported_sql_funcs.inc
137
let $sqlfunc = microsecond(col1);
138
let $valsqlfunc = microsecond('10:30:10.000010');
140
let $infile = part_supported_sql_funcs_int_time.inc;
141
let $val1 = '09:09:15.000002';
142
let $val2 = '04:30:01.000018';
143
let $val3 = '00:59:22.000024';
144
let $val4 = '05:30:34.000037';
145
--source suite/parts/inc/partition_supported_sql_funcs.inc
147
let $sqlfunc = minute(col1);
148
let $valsqlfunc = minute('18:30');
150
let $val1 = '09:09:15';
151
let $val2 = '14:30:45';
152
let $val3 = '21:59:22';
153
let $val4 = '10:24:23';
154
--source suite/parts/inc/partition_supported_sql_funcs.inc
156
let $sqlfunc = second(col1);
157
let $valsqlfunc = second('18:30:14');
159
let $infile = part_supported_sql_funcs_int_time.inc;
160
let $val1 = '09:09:09';
161
let $val2 = '14:30:20';
162
let $val3 = '21:59:22';
163
let $val4 = '10:22:33';
164
--source suite/parts/inc/partition_supported_sql_funcs.inc
165
let $coltype = char(30);
166
--source suite/parts/inc/partition_supported_sql_funcs.inc
168
let $sqlfunc = month(col1);
169
let $valsqlfunc = month('2006-10-14');
171
let $infile = part_supported_sql_funcs_int_date.inc;
172
let $val1 = '2006-01-03';
173
let $val2 = '2006-12-17';
174
let $val3 = '2006-05-25';
175
let $val4 = '2006-11-06';
176
--source suite/parts/inc/partition_supported_sql_funcs.inc
178
let $sqlfunc = quarter(col1);
179
let $valsqlfunc = quarter('2006-10-14');
181
let $infile = part_supported_sql_funcs_int_date.inc;
182
let $val1 = '2006-01-03';
183
let $val2 = '2006-12-17';
184
let $val3 = '2006-09-25';
185
let $val4 = '2006-07-30';
186
--source suite/parts/inc/partition_supported_sql_funcs.inc
188
let $sqlfunc = time_to_sec(col1)-(time_to_sec(col1)-20);
189
let $valsqlfunc = time_to_sec('18:30:14')-(time_to_sec('17:59:59'));
191
let $infile = part_supported_sql_funcs_int_time.inc;
192
let $val1 = '09:09:15';
193
let $val2 = '14:30:45';
194
let $val3 = '21:59:22';
195
let $val4 = '10:33:11';
196
--source suite/parts/inc/partition_supported_sql_funcs.inc
198
let $sqlfunc = to_days(col1)-to_days('2006-01-01');
199
let $valsqlfunc = to_days('2006-02-02')-to_days('2006-01-01');
201
let $infile = part_supported_sql_funcs_int_date.inc;
202
let $val1 = '2006-02-03';
203
let $val2 = '2006-01-17';
204
let $val3 = '2006-01-25';
205
let $val4 = '2006-02-06';
206
--source suite/parts/inc/partition_supported_sql_funcs.inc
208
# DATEDIFF() is implemented as (TO_DAYS(d1) - TO_DAYS(d2))
209
let $sqlfunc = datediff(col1, '2006-01-01');
210
let $valsqlfunc = datediff('2006-02-02', '2006-01-01');
212
let $infile = part_supported_sql_funcs_int_date.inc;
213
let $val1 = '2006-02-03';
214
let $val2 = '2006-01-17';
215
let $val3 = '2006-01-25';
216
let $val4 = '2006-02-06';
217
--source suite/parts/inc/partition_supported_sql_funcs.inc
219
let $sqlfunc = weekday(col1);
220
let $valsqlfunc = weekday('2006-10-14');
222
let $infile = part_supported_sql_funcs_int_date.inc;
223
let $val1 = '2006-12-03';
224
let $val2 = '2006-11-17';
225
let $val3 = '2006-05-25';
226
let $val4 = '2006-02-06';
227
--source suite/parts/inc/partition_supported_sql_funcs.inc
229
let $sqlfunc = year(col1)-1990;
230
let $valsqlfunc = year('2005-10-14')-1990;
232
let $infile = part_supported_sql_funcs_int_date.inc;
233
let $val1 = '1996-01-03';
234
let $val2 = '2000-02-17';
235
let $val3 = '2004-05-25';
236
let $val4 = '2002-02-15';
237
--source suite/parts/inc/partition_supported_sql_funcs.inc
239
let $sqlfunc = yearweek(col1)-200600;
240
let $valsqlfunc = yearweek('2006-10-14')-200600;
242
let $infile = part_supported_sql_funcs_int_date.inc;
243
let $val1 = '2006-01-03';
244
let $val2 = '2006-08-17';
245
let $val3 = '2006-03-25';
246
let $val4 = '2006-11-15';
247
--source suite/parts/inc/partition_supported_sql_funcs.inc