Debian Codegraph @

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

raw
1 #################### mysql-test\t\sql_mode_basic.test #########################
2 # #
3 # Variable Name: sql_mode #
4 # Scope: GLOBAL | SESSION #
5 # Access Type: Dynamic #
6 # Data Type: enumeration #
7 # Default Value: YES #
8 # Valid Values : ANSI, STRICT_TRANS_TABLES, TRADITIONAL, #
9 # ERROR_FOR_DIVISION_BY_ZERO, ANSI_QUOTES, HIGH_NOT_PRECEDENCE,#
10 # IGNORE_SPACE, NO_AUTO_CREATE_USER, ONLY_FULL_GROUP_BY, #
11 # NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES, #
12 # NO_UNSIGNED_SUBTRACTION, OFF #
13 # #
14 # #
15 # Creation Date: 2008-02-07 #
16 # Author: Rizwan #
17 # #
18 # Description: Test Cases of Dynamic System Variable sql_mode #
19 # that checks the behavior of this variable in the following ways#
20 # * Default Value #
21 # * Valid & Invalid values #
22 # * Scope & Access method #
23 # * Data Integrity #
24 # #
25 # Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
26 # server-system-variables.html #
27 # #
28 ###############################################################################
29
30 --source include/load_sysvars.inc
31
32 ################################################################
33 # START OF sql_mode TESTS #
34 ################################################################
35
36
37 ###################################################################
38 # Saving initial value of sql_mode in a temporary variable #
39 ###################################################################
40
41 SET @global_start_value = @@global.sql_mode;
42 SELECT @global_start_value;
43
44 SET @session_start_value = @@session.sql_mode;
45 SELECT @session_start_value;
46
47 --echo '#--------------------FN_DYNVARS_152_01------------------------#'
48 ################################################################
49 # Display the DEFAULT value of sql_mode #
50 ################################################################
51
52 SET @@global.sql_mode = ANSI;
53 SET @@global.sql_mode = DEFAULT;
54 SELECT @@global.sql_mode;
55
56 SET @@session.sql_mode = ANSI;
57 SET @@session.sql_mode = DEFAULT;
58 SELECT @@session.sql_mode;
59
60 --echo '#---------------------FN_DYNVARS_152_02-------------------------#'
61 #########################################################
62 # Check if NULL or empty value is accepeted #
63 #########################################################
64
65 --Error ER_WRONG_VALUE_FOR_VAR
66 SET @@global.sql_mode = NULL;
67
68 # resets sql mode to nothing
69 SET @@global.sql_mode = '';
70 SELECT @@global.sql_mode;
71
72 SET @@global.sql_mode = ' ';
73 SELECT @@global.sql_mode;
74
75 --Error ER_WRONG_VALUE_FOR_VAR
76 SET @@session.sql_mode = NULL;
77
78 SET @@session.sql_mode = '';
79 SELECT @@session.sql_mode;
80
81 SET @@session.sql_mode = ' ';
82 SELECT @@session.sql_mode;
83
84
85 --echo '#--------------------FN_DYNVARS_152_03------------------------#'
86 ########################################################################
87 # Change the value of sql_mode to a valid value #
88 ########################################################################
89
90 # sql modes ref: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
91
92 # check valid values for global
93
94 SET @@global.sql_mode = ANSI;
95 SELECT @@global.sql_mode;
96 SET @@global.sql_mode = STRICT_TRANS_TABLES;
97 SELECT @@global.sql_mode;
98 SET @@global.sql_mode = TRADITIONAL;
99 SELECT @@global.sql_mode;
100 SET @@global.sql_mode = ALLOW_INVALID_DATES;
101 SELECT @@global.sql_mode;
102 SET @@global.sql_mode = ANSI_QUOTES;
103 SELECT @@global.sql_mode;
104 SET @@global.sql_mode = ERROR_FOR_DIVISION_BY_ZERO;
105 SELECT @@global.sql_mode;
106 SET @@global.sql_mode = HIGH_NOT_PRECEDENCE;
107 SELECT @@global.sql_mode;
108 SET @@global.sql_mode = IGNORE_SPACE;
109 SELECT @@global.sql_mode;
110 SET @@global.sql_mode = NO_AUTO_CREATE_USER;
111 SELECT @@global.sql_mode;
112 SET @@global.sql_mode = NO_AUTO_VALUE_ON_ZERO;
113 SELECT @@global.sql_mode;
114 SET @@global.sql_mode = NO_BACKSLASH_ESCAPES;
115 SELECT @@global.sql_mode;
116 SET @@global.sql_mode = NO_DIR_IN_CREATE;
117 SELECT @@global.sql_mode;
118 SET @@global.sql_mode = NO_ENGINE_SUBSTITUTION;
119 SELECT @@global.sql_mode;
120 SET @@global.sql_mode = NO_FIELD_OPTIONS;
121 SELECT @@global.sql_mode;
122 SET @@global.sql_mode = NO_KEY_OPTIONS;
123 SELECT @@global.sql_mode;
124 SET @@global.sql_mode = NO_TABLE_OPTIONS;
125 SELECT @@global.sql_mode;
126 SET @@global.sql_mode = NO_UNSIGNED_SUBTRACTION;
127 SELECT @@global.sql_mode;
128 SET @@global.sql_mode = NO_ZERO_DATE;
129 SELECT @@global.sql_mode;
130 SET @@global.sql_mode = NO_ZERO_IN_DATE;
131 SELECT @@global.sql_mode;
132 SET @@global.sql_mode = ONLY_FULL_GROUP_BY;
133 SELECT @@global.sql_mode;
134 SET @@global.sql_mode = PIPES_AS_CONCAT;
135 SELECT @@global.sql_mode;
136 SET @@global.sql_mode = REAL_AS_FLOAT;
137 SELECT @@global.sql_mode;
138 SET @@global.sql_mode = STRICT_ALL_TABLES;
139 SELECT @@global.sql_mode;
140 SET @@global.sql_mode = STRICT_TRANS_TABLES;
141 SELECT @@global.sql_mode;
142 SET @@global.sql_mode = DB2;
143 SELECT @@global.sql_mode;
144 SET @@global.sql_mode = MAXDB;
145 SELECT @@global.sql_mode;
146 SET @@global.sql_mode = MSSQL;
147 SELECT @@global.sql_mode;
148 SET @@global.sql_mode = MYSQL323;
149 SELECT @@global.sql_mode;
150 SET @@global.sql_mode = MYSQL40;
151 SELECT @@global.sql_mode;
152 SET @@global.sql_mode = ORACLE;
153 SELECT @@global.sql_mode;
154 SET @@global.sql_mode = POSTGRESQL;
155 SELECT @@global.sql_mode;
156 SET @@global.sql_mode = TRADITIONAL;
157 SELECT @@global.sql_mode;
158
159 --Error ER_WRONG_VALUE_FOR_VAR
160 SET @@global.sql_mode = OFF;
161
162 #check valid values for session
163 SET @@session.sql_mode = ANSI;
164 SELECT @@session.sql_mode;
165 SET @@session.sql_mode = STRICT_TRANS_TABLES;
166 SELECT @@session.sql_mode;
167 SET @@session.sql_mode = TRADITIONAL;
168 SELECT @@session.sql_mode;
169 SET @@session.sql_mode = ALLOW_INVALID_DATES;
170 SELECT @@session.sql_mode;
171 SET @@session.sql_mode = ANSI_QUOTES;
172 SELECT @@session.sql_mode;
173 SET @@session.sql_mode = ERROR_FOR_DIVISION_BY_ZERO;
174 SELECT @@session.sql_mode;
175 SET @@session.sql_mode = HIGH_NOT_PRECEDENCE;
176 SELECT @@session.sql_mode;
177 SET @@session.sql_mode = IGNORE_SPACE;
178 SELECT @@session.sql_mode;
179 SET @@session.sql_mode = NO_AUTO_CREATE_USER;
180 SELECT @@session.sql_mode;
181 SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO;
182 SELECT @@session.sql_mode;
183 SET @@session.sql_mode = NO_BACKSLASH_ESCAPES;
184 SELECT @@session.sql_mode;
185 SET @@session.sql_mode = NO_DIR_IN_CREATE;
186 SELECT @@session.sql_mode;
187 SET @@session.sql_mode = NO_ENGINE_SUBSTITUTION;
188 SELECT @@session.sql_mode;
189 SET @@session.sql_mode = NO_FIELD_OPTIONS;
190 SELECT @@session.sql_mode;
191 SET @@session.sql_mode = NO_KEY_OPTIONS;
192 SELECT @@session.sql_mode;
193 SET @@session.sql_mode = NO_TABLE_OPTIONS;
194 SELECT @@session.sql_mode;
195 SET @@session.sql_mode = NO_UNSIGNED_SUBTRACTION;
196 SELECT @@session.sql_mode;
197 SET @@session.sql_mode = NO_ZERO_DATE;
198 SELECT @@session.sql_mode;
199 SET @@session.sql_mode = NO_ZERO_IN_DATE;
200 SELECT @@session.sql_mode;
201 SET @@session.sql_mode = ONLY_FULL_GROUP_BY;
202 SELECT @@session.sql_mode;
203 SET @@session.sql_mode = PIPES_AS_CONCAT;
204 SELECT @@session.sql_mode;
205 SET @@session.sql_mode = REAL_AS_FLOAT;
206 SELECT @@session.sql_mode;
207 SET @@session.sql_mode = STRICT_ALL_TABLES;
208 SELECT @@session.sql_mode;
209 SET @@session.sql_mode = STRICT_TRANS_TABLES;
210 SELECT @@session.sql_mode;
211 SET @@session.sql_mode = DB2;
212 SELECT @@session.sql_mode;
213 SET @@session.sql_mode = MAXDB;
214 SELECT @@session.sql_mode;
215 SET @@session.sql_mode = MSSQL;
216 SELECT @@session.sql_mode;
217 SET @@session.sql_mode = MYSQL323;
218 SELECT @@session.sql_mode;
219 SET @@session.sql_mode = MYSQL40;
220 SELECT @@session.sql_mode;
221 SET @@session.sql_mode = ORACLE;
222 SELECT @@session.sql_mode;
223 SET @@session.sql_mode = POSTGRESQL;
224 SELECT @@session.sql_mode;
225 SET @@session.sql_mode = TRADITIONAL;
226 SELECT @@session.sql_mode;
227
228 --Error ER_WRONG_VALUE_FOR_VAR
229 SET @@session.sql_mode = OFF;
230
231 --Error ER_WRONG_VALUE_FOR_VAR
232 SET @@global.sql_mode = '?';
233 SELECT @@global.sql_mode;
234
235 --echo '#--------------------FN_DYNVARS_152_04-------------------------#'
236 ###########################################################################
237 # Change the value of sql_mode to invalid value #
238 ###########################################################################
239
240 # invalid values for global
241 --Error ER_WRONG_VALUE_FOR_VAR
242 SET @@global.sql_mode = -1;
243 --Error ER_WRONG_VALUE_FOR_VAR
244 SET @@global.sql_mode = ASCII;
245 --Error ER_WRONG_VALUE_FOR_VAR
246 SET @@global.sql_mode = NON_TRADITIONAL;
247 --Error ER_WRONG_VALUE_FOR_VAR
248 SET @@global.sql_mode = 'OF';
249 --Error ER_WRONG_VALUE_FOR_VAR
250 SET @@global.sql_mode = NONE;
251 --Error ER_WRONG_VALUE_FOR_VAR
252
253 #invalid values for session
254
255 --Error ER_WRONG_VALUE_FOR_VAR
256 SET @@session.sql_mode = -1;
257 --Error ER_WRONG_VALUE_FOR_VAR
258 SET @@session.sql_mode = ANSI_SINGLE_QUOTES;
259 --Error ER_WRONG_VALUE_FOR_VAR
260 SET @@session.sql_mode = 'ON';
261 --Error ER_WRONG_VALUE_FOR_VAR
262 SET @@session.sql_mode = 'OF';
263 --Error ER_WRONG_VALUE_FOR_VAR
264 SET @@session.sql_mode = DISABLE;
265
266
267 --echo '#-------------------FN_DYNVARS_152_05----------------------------#'
268 #########################################################################
269 # Check if the value in session Table matches value in variable #
270 #########################################################################
271
272 SELECT @@session.sql_mode = VARIABLE_VALUE
273 FROM INFORMATION_SCHEMA.SESSION_VARIABLES
274 WHERE VARIABLE_NAME='sql_mode';
275
276 --echo '#----------------------FN_DYNVARS_152_06------------------------#'
277 #########################################################################
278 # Check if the value in GLOBAL Table matches value in variable #
279 #########################################################################
280
281 SELECT @@global.sql_mode = VARIABLE_VALUE
282 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
283 WHERE VARIABLE_NAME='sql_mode';
284
285
286 --echo '#---------------------FN_DYNVARS_152_07-------------------------#'
287 ###################################################################
288 # Check if numbers can be used on variable #
289 ###################################################################
290
291 # test if variable accepts 0,1,2
292 SET @@global.sql_mode = 0;
293 SELECT @@global.sql_mode;
294
295 SET @@global.sql_mode = 1;
296 SELECT @@global.sql_mode;
297
298 SET @@global.sql_mode = 2;
299 SELECT @@global.sql_mode;
300
301 SET @@global.sql_mode = 3;
302 SELECT @@global.sql_mode;
303
304 SET @@global.sql_mode = 50000;
305 SELECT @@global.sql_mode;
306
307 SET @@global.sql_mode = 500000;
308 SELECT @@global.sql_mode;
309
310 SET @@global.sql_mode = 17179869183;
311 SELECT @@global.sql_mode;
312
313 SET @@global.sql_mode = 17179869184;
314 SELECT @@global.sql_mode;
315
316 SET @@global.sql_mode = 34359738367;
317 SELECT @@global.sql_mode;
318
319 --Error ER_WRONG_VALUE_FOR_VAR
320 SET @@global.sql_mode = 34359738368;
321
322 # use of decimal values
323
324 --Error ER_WRONG_TYPE_FOR_VAR
325 SET @@global.sql_mode = 0.4;
326
327 --echo '#---------------------FN_DYNVARS_152_08----------------------#'
328 ###################################################################
329 # Check if TRUE and FALSE values can be used on variable #
330 ###################################################################
331
332 SET @@global.sql_mode = TRUE;
333 SELECT @@global.sql_mode;
334 SET @@global.sql_mode = FALSE;
335 SELECT @@global.sql_mode;
336
337 --echo '#---------------------FN_DYNVARS_152_09----------------------#'
338 #########################################################################
339 # Check if sql_mode can be accessed with and without @@ sign #
340 #########################################################################
341
342
343 SET sql_mode = 'ANSI';
344
345 SET session sql_mode = 1;
346 SELECT @@sql_mode;
347
348 SET global sql_mode = 0;
349 SELECT @@global.sql_mode;
350
351 --echo '#---------------------FN_DYNVARS_152_10----------------------#'
352 #######################################################################
353 # Check if sql_mode values can be combined as specified #
354 #######################################################################
355
356 SET @@session.sql_mode = 'TRADITIONAL,ALLOW_INVALID_DATES,ANSI_QUOTES';
357 SELECT @@session.sql_mode;
358
359 SET @@global.sql_mode = 'ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT,REAL_AS_FLOAT,ORACLE,POSTGRESQL';
360 SELECT @@global.sql_mode;
361
362 #try combining invalid mode with correct mode
363 --Error ER_WRONG_VALUE_FOR_VAR
364 SET @@session.sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,FOOBAR,IGNORE_SPACE';
365
366 #zero-length values are ok
367 SET @@sql_mode=',';
368 SELECT @@sql_mode;
369 SET @@sql_mode=',,,,ANSI_QUOTES,,,';
370 SELECT @@sql_mode;
371 --Error ER_WRONG_VALUE_FOR_VAR
372 SET @@sql_mode=',,,,FOOBAR,,,,,';
373 SELECT @@sql_mode;
374
375 ##############################
376 # Restore initial value #
377 ##############################
378
379 SET @@global.sql_mode = @global_start_value;
380 SELECT @@global.sql_mode;
381
382 SET @@session.sql_mode = @session_start_value;
383 SELECT @@session.sql_mode;
384
385 ################################################################
386 # END OF sql_mode TESTS #
387 ################################################################
388