Debian Codegraph @

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

raw
1 ################################################################################
2 # t/partition_supported_sql_funcs_main.inc #
3 # #
4 # Purpose: #
5 # Tests which SQL functions are allowed in partinioning clauses. #
6 # #
7 # #
8 #------------------------------------------------------------------------------#
9 # Original Author: HH #
10 # Original Date: 2006-11-22 #
11 # Change Author: #
12 # Change Date: #
13 # Change: #
14 # #
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 #
26 # partitions. #
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 ################################################################################
30
31
32 let $sqlfunc = abs(col1);
33 let $valsqlfunc = abs(15);
34 let $coltype = int;
35 let $infile = part_supported_sql_funcs_int_int.inc;
36 let $val1 = 5 ;
37 let $val2 = 13 ;
38 let $val3 = 17 ;
39 let $val4 = 15 ;
40 --source suite/parts/inc/partition_supported_sql_funcs.inc
41
42 let $sqlfunc = mod(col1,10);
43 let $valsqlfunc = mod(15,10);
44 let $coltype = int;
45 let $infile = part_supported_sql_funcs_int_int.inc;
46 let $val1 = 5;
47 let $val2 = 19;
48 let $val3 = 17;
49 let $val4 = 15 ;
50 --source suite/parts/inc/partition_supported_sql_funcs.inc
51
52 let $sqlfunc = day(col1);
53 let $valsqlfunc = day('2006-12-21');
54 let $coltype = date;
55 let $infile = part_supported_sql_funcs_int_date.inc;
56 let $val1 = '2006-02-03';
57 let $val2 = '2006-01-17';
58 let $val3 = '2006-01-25';
59 let $val4 = '2006-02-05';
60 --source suite/parts/inc/partition_supported_sql_funcs.inc
61
62 let $sqlfunc = dayofmonth(col1);
63 let $valsqlfunc = dayofmonth('2006-12-24');
64 let $coltype = date;
65 let $infile = part_supported_sql_funcs_int_date.inc;
66 let $val1 = '2006-02-03';
67 let $val2 = '2006-01-17';
68 let $val3 = '2006-01-25';
69 let $val4 = '2006-02-05';
70 --source suite/parts/inc/partition_supported_sql_funcs.inc
71
72 let $sqlfunc = dayofweek(col1);
73 let $valsqlfunc = dayofweek('2006-12-24');
74 let $coltype = date;
75 let $infile = part_supported_sql_funcs_int_date.inc;
76 let $val1 = '2006-01-03';
77 let $val2 = '2006-02-17';
78 let $val3 = '2006-01-25';
79 let $val4 = '2006-02-05';
80 --source suite/parts/inc/partition_supported_sql_funcs.inc
81
82 let $sqlfunc = dayofyear(col1);
83 let $valsqlfunc = dayofyear('2006-12-25');
84 let $coltype = date;
85 let $infile = part_supported_sql_funcs_int_date.inc;
86 let $val1 = '2006-01-03';
87 let $val2 = '2006-01-17';
88 let $val3 = '2006-02-25';
89 let $val4 = '2006-02-05';
90 --source suite/parts/inc/partition_supported_sql_funcs.inc
91 # Disabled after fixing bug#54483.
92 #let $coltype = char(30);
93 #--source suite/parts/inc/partition_supported_sql_funcs.inc
94
95 let $sqlfunc = extract(month from col1);
96 let $valsqlfunc = extract(year from '1998-11-23');
97 let $coltype = date;
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
104
105 let $sqlfunc = hour(col1);
106 let $valsqlfunc = hour('18:30');
107 let $coltype = time;
108 let $infile = part_supported_sql_funcs_int_time.inc;
109 let $val1 = '09:09';
110 let $val2 = '14:30';
111 let $val3 = '21:59';
112 let $val4 = '10:30';
113 --source suite/parts/inc/partition_supported_sql_funcs.inc
114
115 let $sqlfunc = microsecond(col1);
116 let $valsqlfunc = microsecond('10:30:10.000010');
117 let $coltype = time(6);
118 let $infile = part_supported_sql_funcs_int_time.inc;
119 let $val1 = '09:09:15.000002';
120 let $val2 = '04:30:01.000018';
121 let $val3 = '00:59:22.000024';
122 let $val4 = '05:30:34.000037';
123 --source suite/parts/inc/partition_supported_sql_funcs.inc
124
125 let $sqlfunc = minute(col1);
126 let $valsqlfunc = minute('18:30');
127 let $coltype = time;
128 let $val1 = '09:09:15';
129 let $val2 = '14:30:45';
130 let $val3 = '21:59:22';
131 let $val4 = '10:24:23';
132 --source suite/parts/inc/partition_supported_sql_funcs.inc
133
134 let $sqlfunc = second(col1);
135 let $valsqlfunc = second('18:30:14');
136 let $coltype = time;
137 let $infile = part_supported_sql_funcs_int_time.inc;
138 let $val1 = '09:09:09';
139 let $val2 = '14:30:20';
140 let $val3 = '21:59:22';
141 let $val4 = '10:22:33';
142 --source suite/parts/inc/partition_supported_sql_funcs.inc
143 # second(non_time_col) is disabled after bug#54483.
144 #let $coltype = char(30);
145 #--source suite/parts/inc/partition_supported_sql_funcs.inc
146
147 let $sqlfunc = month(col1);
148 let $valsqlfunc = month('2006-10-14');
149 let $coltype = date;
150 let $infile = part_supported_sql_funcs_int_date.inc;
151 let $val1 = '2006-01-03';
152 let $val2 = '2006-12-17';
153 let $val3 = '2006-05-25';
154 let $val4 = '2006-11-06';
155 --source suite/parts/inc/partition_supported_sql_funcs.inc
156
157 let $sqlfunc = quarter(col1);
158 let $valsqlfunc = quarter('2006-10-14');
159 let $coltype = date;
160 let $infile = part_supported_sql_funcs_int_date.inc;
161 let $val1 = '2006-01-03';
162 let $val2 = '2006-12-17';
163 let $val3 = '2006-09-25';
164 let $val4 = '2006-07-30';
165 --source suite/parts/inc/partition_supported_sql_funcs.inc
166
167 # time_to_sec() is now a double function, so it can't be portioned upon
168 --disable_parsing
169 let $sqlfunc = time_to_sec(col1)-(time_to_sec(col1)-20);
170 let $valsqlfunc = time_to_sec('18:30:14')-(time_to_sec('17:59:59'));
171 let $coltype = time;
172 let $infile = part_supported_sql_funcs_int_time.inc;
173 let $val1 = '09:09:15';
174 let $val2 = '14:30:45';
175 let $val3 = '21:59:22';
176 let $val4 = '10:33:11';
177 --source suite/parts/inc/partition_supported_sql_funcs.inc
178 --enable_parsing
179
180 # to_days(non_date_col) is disabled after bug#54483.
181 #let $sqlfunc = to_days(col1)-to_days('2006-01-01');
182 #let $valsqlfunc = to_days('2006-02-02')-to_days('2006-01-01');
183 #let $coltype = date;
184 #let $infile = part_supported_sql_funcs_int_date.inc;
185 #let $val1 = '2006-02-03';
186 #let $val2 = '2006-01-17';
187 #let $val3 = '2006-01-25';
188 #let $val4 = '2006-02-06';
189 #--source suite/parts/inc/partition_supported_sql_funcs.inc
190
191 # to_days(non_date_col) is disabled after bug#54483.
192 # DATEDIFF() is implemented as (TO_DAYS(d1) - TO_DAYS(d2))
193 #let $sqlfunc = datediff(col1, '2006-01-01');
194 #let $valsqlfunc = datediff('2006-02-02', '2006-01-01');
195 #let $coltype = date;
196 #let $infile = part_supported_sql_funcs_int_date.inc;
197 #let $val1 = '2006-02-03';
198 #let $val2 = '2006-01-17';
199 #let $val3 = '2006-01-25';
200 #let $val4 = '2006-02-06';
201 #--source suite/parts/inc/partition_supported_sql_funcs.inc
202
203 let $sqlfunc = weekday(col1);
204 let $valsqlfunc = weekday('2006-10-14');
205 let $coltype = date;
206 let $infile = part_supported_sql_funcs_int_date.inc;
207 let $val1 = '2006-12-03';
208 let $val2 = '2006-11-17';
209 let $val3 = '2006-05-25';
210 let $val4 = '2006-02-06';
211 --source suite/parts/inc/partition_supported_sql_funcs.inc
212
213 let $sqlfunc = year(col1)-1990;
214 let $valsqlfunc = year('2005-10-14')-1990;
215 let $coltype = date;
216 let $infile = part_supported_sql_funcs_int_date.inc;
217 let $val1 = '1996-01-03';
218 let $val2 = '2000-02-17';
219 let $val3 = '2004-05-25';
220 let $val4 = '2002-02-15';
221 --source suite/parts/inc/partition_supported_sql_funcs.inc
222
223 let $sqlfunc = yearweek(col1)-200600;
224 let $valsqlfunc = yearweek('2006-10-14')-200600;
225 let $coltype = date;
226 let $infile = part_supported_sql_funcs_int_date.inc;
227 let $val1 = '2006-01-03';
228 let $val2 = '2006-08-17';
229 let $val3 = '2006-03-25';
230 let $val4 = '2006-11-15';
231 --source suite/parts/inc/partition_supported_sql_funcs.inc