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 = "+13: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 = "+13: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 = "+13:01";
165
166 --Error ER_UNKNOWN_TIME_ZONE
167 SET @@time_zone = "-13: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 = "+13:01";
179
180 --Error ER_UNKNOWN_TIME_ZONE
181 SET @@global.time_zone = "-13: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 SELECT @@time_zone = VARIABLE_VALUE
211 FROM INFORMATION_SCHEMA.SESSION_VARIABLES
212 WHERE VARIABLE_NAME='time_zone';
213
214
215 --echo '#----------------------FN_DYNVARS_001_07------------------------#'
216 #########################################################################
217 # Check if the value in GLOBAL Table matches value in variable #
218 #########################################################################
219
220 SELECT @@global.time_zone = VARIABLE_VALUE
221 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
222 WHERE VARIABLE_NAME='time_zone';
223
224
225 --echo '#---------------------FN_DYNVARS_001_08-------------------------#'
226 #############################################################################
227 # Check if ON, OFF, TRUE and FALSE values can be used on variable #
228 #############################################################################
229 # for session scope
230 --Error ER_UNKNOWN_TIME_ZONE
231 SET @@time_zone = OFF;
232
233 --Error ER_UNKNOWN_TIME_ZONE
234 SET @@time_zone = ON;
235
236 --Error ER_WRONG_TYPE_FOR_VAR
237 SET @@time_zone = TRUE;
238
239 --Error ER_WRONG_TYPE_FOR_VAR
240 SET @@time_zone = FALSE;
241
242 # for global scope
243 --Error ER_UNKNOWN_TIME_ZONE
244 SET @@global.time_zone = OFF;
245
246 --Error ER_UNKNOWN_TIME_ZONE
247 SET @@global.time_zone = ON;
248
249 --Error ER_WRONG_TYPE_FOR_VAR
250 SET @@global.time_zone = TRUE;
251
252 --Error ER_WRONG_TYPE_FOR_VAR
253 SET @@global.time_zone = FALSE;
254
255
256 --echo '#---------------------FN_DYNVARS_001_09----------------------#'
257 #####################################################################
258 # Check if Numbers and empty values can be used on variable #
259 #####################################################################
260 # for session scope
261 --Error ER_WRONG_TYPE_FOR_VAR
262 SET @@time_zone = 1;
263
264 --Error ER_WRONG_TYPE_FOR_VAR
265 SET @@time_zone = +0200;
266
267 --Error ER_PARSE_ERROR
268 SET @@time_zone = GMT+;
269
270 --Error ER_UNKNOWN_TIME_ZONE
271 SET @@time_zone = "";
272
273 # for global scope
274 --Error ER_WRONG_TYPE_FOR_VAR
275 SET @@global.time_zone = 1;
276
277 --Error ER_WRONG_TYPE_FOR_VAR
278 SET @@global.time_zone = +0200;
279
280 --Error ER_PARSE_ERROR
281 SET @@global.time_zone = GMT+;
282
283 --Error ER_UNKNOWN_TIME_ZONE
284 SET @@global.time_zone = "";
285
286
287 --echo '#---------------------FN_DYNVARS_001_10----------------------#'
288 ##############################################################################
289 # Check if accessing variable with SESSION,LOCAL and without SCOPE points
290 # to same session variable
291 ##############################################################################
292
293 SET @@time_zone = '+02:30';
294 SELECT @@time_zone = @@local.time_zone and @@time_zone = @@session.time_zone;
295 SET @@time_zone = '-01:30';
296 SELECT @@time_zone = @@local.time_zone and @@time_zone = @@session.time_zone;
297
298
299 --echo '#---------------------FN_DYNVARS_001_11----------------------#'
300 ####################################################################
301 # Check if time_zone can be accessed with and without @@ sign #
302 ####################################################################
303
304 SET time_zone = "+01:00";
305 SELECT @@time_zone;
306 --Error ER_UNKNOWN_TABLE
307 SELECT local.time_zone;
308 --Error ER_UNKNOWN_TABLE
309 SELECT session.time_zone;
310 --Error ER_BAD_FIELD_ERROR
311 SELECT time_zone = @@session.time_zone;
312
313 ##############################
314 # Restore initial value #
315 ##############################
316
317 SET @@time_zone = @session_start_value;
318 SELECT @@time_zone;
319
320 SET @@global.time_zone = @global_start_value;
321 SELECT @@global.time_zone;
322
323 #######################################################################
324 # END OF time_zone TESTS #
325 #######################################################################