Debian Codegraph @

mysql-test/suite/sys_vars/t/time_zone_basic.test

raw
1 ################### mysql-test\t\time_zone_basic.test #########################
2 # #
3 # Variable Name: time_zone #
4 # Scope: GLOBAL | SESSION #
5 # Access Type: Dynamic #
6 # Data Type: string #
7 # Default Value: SYSTEM #
8 # Range: #
9 # #
10 # #
11 # Creation Date: 2008-02-07 #
12 # Author: Rizwan #
13 # #
14 # Description: Test Cases of Dynamic System Variable time_zone #
15 # that checks the behavior of this variable in the following ways#
16 # * Default Value #
17 # * Valid & Invalid values #
18 # * Scope & Access method #
19 # * Data Integrity #
20 # #
21 # Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
22 # server-system-variables.html #
23 # #
24 ###############################################################################
25
26 --source include/load_sysvars.inc
27
28 #######################################################################
29 # START OF time_zone TESTS #
30 #######################################################################
31
32
33 #######################################################################
34 # Saving initial value of time_zone in a temporary variable #
35 #######################################################################
36
37 SET @global_start_value = @@global.time_zone;
38 SELECT @global_start_value;
39
40 SET @session_start_value = @@session.time_zone;
41 SELECT @session_start_value;
42
43
44 --echo '#--------------------FN_DYNVARS_001_01------------------------#'
45 ########################################################################
46 # Display the DEFAULT value of time_zone #
47 ########################################################################
48
49 SET @@time_zone = "+02:00";
50 SET @@time_zone = DEFAULT;
51 SELECT @@time_zone;
52
53 SET @@global.time_zone = "-00:30";
54 SET @@global.time_zone = DEFAULT;
55 SELECT @@global.time_zone;
56
57
58 --echo '#---------------------FN_DYNVARS_001_02-------------------------#'
59 ##############################################################################
60 # see if setting global value changes session value and vice versa #
61 ##############################################################################
62
63 SET @@session.time_zone = "+02:00";
64 SELECT @@session.time_zone;
65 SET @@global.time_zone = "+05:00";
66 SELECT @@global.time_zone;
67 SELECT @@session.time_zone AS res_is_02_00;
68
69 SET @@session.time_zone = "-02:00";
70 SELECT @@session.time_zone;
71 SELECT @@global.time_zone AS res_is_05_00;
72 # composite check
73 SELECT @@global.time_zone=@@session.time_zone AS res_is_false;
74
75
76 --echo '#--------------------FN_DYNVARS_001_03------------------------#'
77 ########################################################################
78 # Change the value of time_zone to a valid value #
79 ########################################################################
80
81 # for session scope
82
83 SET @@time_zone = "+14:00";
84 SELECT @@time_zone;
85 SET @@time_zone = "-12:00";
86 SELECT @@time_zone;
87 SET @@time_zone = "+12:59";
88 SELECT @@time_zone;
89 SET @@time_zone = "-11:30";
90 SELECT @@time_zone;
91 SET @@time_zone = "+00:00";
92 SELECT @@time_zone;
93 SET @@time_zone = "-12:59";
94 SELECT @@time_zone;
95 SET @@time_zone = "-00:00";
96 SELECT @@time_zone;
97 SET @@time_zone = "+10:00";
98 SELECT @@time_zone;
99 SET @@time_zone = "+0:0";
100 SELECT @@time_zone;
101 SET @@time_zone = "-10:00";
102 SELECT @@time_zone;
103 SET @@time_zone = "+2:00";
104 SELECT @@time_zone;
105
106 # for global scope
107
108 SET @@global.time_zone = "+14:00";
109 SELECT @@global.time_zone;
110 SET @@global.time_zone = "-12:00";
111 SELECT @@global.time_zone;
112 SET @@global.time_zone = "+12:59";
113 SELECT @@global.time_zone;
114 SET @@global.time_zone = "-11:30";
115 SELECT @@global.time_zone;
116 SET @@global.time_zone = "+00:00";
117 SELECT @@global.time_zone;
118 SET @@global.time_zone = "-12:59";
119 SELECT @@global.time_zone;
120 SET @@global.time_zone = "-00:00";
121 SELECT @@global.time_zone;
122 SET @@global.time_zone = "+10:00";
123 SELECT @@global.time_zone;
124 SET @@global.time_zone = "+0:0";
125 SELECT @@global.time_zone;
126 SET @@global.time_zone = "-10:00";
127 SELECT @@global.time_zone;
128 SET @@global.time_zone = "+2:00";
129 SELECT @@global.time_zone;
130
131
132 # these test are dependant on values stored in mysql.time_zone_name
133
134 #SET @@time_zone = "africa/cairo";
135 #SELECT @@time_zone;
136 #SET @@time_zone = "America/Aruba";
137 #SELECT @@time_zone;
138 #
139 #SET @@time_zone = "Etc/GMT-14";
140 #SELECT @@time_zone;
141 #
142 #SET @@time_zone = "GMT-0";
143 #SELECT @@time_zone;
144 #
145 #SET @@time_zone = "UTC";
146 #SELECT @@time_zone;
147
148 #--echo 'Bug: Region and zone values cant be set in time_zone. Although values are loaded in mysql.time_zone_name'
149
150 --echo '#--------------------FN_DYNVARS_001_04-------------------------#'
151 ###########################################################################
152 # Change the value of time_zone to invalid value #
153 ###########################################################################
154
155 # for session scope
156
157 --Error ER_UNKNOWN_TIME_ZONE
158 SET @@time_zone = "+24:00";
159
160 --Error ER_UNKNOWN_TIME_ZONE
161 SET @@time_zone = "+23:59";
162
163 --Error ER_UNKNOWN_TIME_ZONE
164 SET @@time_zone = "+14:01";
165
166 --Error ER_UNKNOWN_TIME_ZONE
167 SET @@time_zone = "-14:01";
168
169 # for global scope
170
171 --Error ER_UNKNOWN_TIME_ZONE
172 SET @@global.time_zone = "+24:00";
173
174 --Error ER_UNKNOWN_TIME_ZONE
175 SET @@global.time_zone = "+23:59";
176
177 --Error ER_UNKNOWN_TIME_ZONE
178 SET @@global.time_zone = "+14:01";
179
180 --Error ER_UNKNOWN_TIME_ZONE
181 SET @@global.time_zone = "-14:01";
182
183
184 #checking time zone names not in mysql.time_zone_name. These test may succeed
185 #depending on timezone stored
186
187 #SET @@time_zone = "london";
188 #SELECT @@time_zone;
189 #
190 #SET @@time_zone = "GMT+14";
191 #SELECT @@time_zone;
192
193
194 --echo '#-------------------FN_DYNVARS_001_05----------------------------#'
195 ###########################################################################
196 # Assign System time zone to variable for session and global #
197 ###########################################################################
198
199 SET @@global.time_zone = 'SYSTEM';
200 SELECT @@global.time_zone;
201
202 SET @@time_zone = 'SYSTEM';
203 SELECT @@time_zone;
204
205 --echo '#----------------------FN_DYNVARS_001_06------------------------#'
206 #########################################################################
207 # Check if the value in SESSION Table matches value in variable #
208 #########################################################################
209
210 --disable_warnings
211 SELECT @@time_zone = VARIABLE_VALUE
212 FROM performance_schema.session_variables
213 WHERE VARIABLE_NAME='time_zone';
214 --enable_warnings
215
216
217 --echo '#----------------------FN_DYNVARS_001_07------------------------#'
218 #########################################################################
219 # Check if the value in GLOBAL Table matches value in variable #
220 #########################################################################
221
222 --disable_warnings
223 SELECT @@global.time_zone = VARIABLE_VALUE
224 FROM performance_schema.global_variables
225 WHERE VARIABLE_NAME='time_zone';
226 --enable_warnings
227
228
229 --echo '#---------------------FN_DYNVARS_001_08-------------------------#'
230 #############################################################################
231 # Check if ON, OFF, TRUE and FALSE values can be used on variable #
232 #############################################################################
233 # for session scope
234 --Error ER_UNKNOWN_TIME_ZONE
235 SET @@time_zone = OFF;
236
237 --Error ER_UNKNOWN_TIME_ZONE
238 SET @@time_zone = ON;
239
240 --Error ER_WRONG_TYPE_FOR_VAR
241 SET @@time_zone = TRUE;
242
243 --Error ER_WRONG_TYPE_FOR_VAR
244 SET @@time_zone = FALSE;
245
246 # for global scope
247 --Error ER_UNKNOWN_TIME_ZONE
248 SET @@global.time_zone = OFF;
249
250 --Error ER_UNKNOWN_TIME_ZONE
251 SET @@global.time_zone = ON;
252
253 --Error ER_WRONG_TYPE_FOR_VAR
254 SET @@global.time_zone = TRUE;
255
256 --Error ER_WRONG_TYPE_FOR_VAR
257 SET @@global.time_zone = FALSE;
258
259
260 --echo '#---------------------FN_DYNVARS_001_09----------------------#'
261 #####################################################################
262 # Check if Numbers and empty values can be used on variable #
263 #####################################################################
264 # for session scope
265 --Error ER_WRONG_TYPE_FOR_VAR
266 SET @@time_zone = 1;
267
268 --Error ER_WRONG_TYPE_FOR_VAR
269 SET @@time_zone = +0200;
270
271 --Error ER_PARSE_ERROR
272 SET @@time_zone = GMT+;
273
274 --Error ER_UNKNOWN_TIME_ZONE
275 SET @@time_zone = "";
276
277 # for global scope
278 --Error ER_WRONG_TYPE_FOR_VAR
279 SET @@global.time_zone = 1;
280
281 --Error ER_WRONG_TYPE_FOR_VAR
282 SET @@global.time_zone = +0200;
283
284 --Error ER_PARSE_ERROR
285 SET @@global.time_zone = GMT+;
286
287 --Error ER_UNKNOWN_TIME_ZONE
288 SET @@global.time_zone = "";
289
290
291 --echo '#---------------------FN_DYNVARS_001_10----------------------#'
292 ##############################################################################
293 # Check if accessing variable with SESSION,LOCAL and without SCOPE points
294 # to same session variable
295 ##############################################################################
296
297 SET @@time_zone = '+02:30';
298 SELECT @@time_zone = @@local.time_zone and @@time_zone = @@session.time_zone;
299 SET @@time_zone = '-01:30';
300 SELECT @@time_zone = @@local.time_zone and @@time_zone = @@session.time_zone;
301
302
303 --echo '#---------------------FN_DYNVARS_001_11----------------------#'
304 ####################################################################
305 # Check if time_zone can be accessed with and without @@ sign #
306 ####################################################################
307
308 SET time_zone = "+01:00";
309 SELECT @@time_zone;
310 --Error ER_UNKNOWN_TABLE
311 SELECT local.time_zone;
312 --Error ER_UNKNOWN_TABLE
313 SELECT session.time_zone;
314 --Error ER_BAD_FIELD_ERROR
315 SELECT time_zone = @@session.time_zone;
316
317 ##############################
318 # Restore initial value #
319 ##############################
320
321 SET @@time_zone = @session_start_value;
322 SELECT @@time_zone;
323
324 SET @@global.time_zone = @global_start_value;
325 SELECT @@global.time_zone;
326
327 #######################################################################
328 # END OF time_zone TESTS #
329 #######################################################################