1
#################### mysql-test\t\sql_mode_basic.test #########################
3
# Variable Name: sql_mode #
4
# Scope: GLOBAL | SESSION #
5
# Access Type: Dynamic #
6
# Data Type: enumeration #
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 #
15
# Creation Date: 2008-02-07 #
18
# Description: Test Cases of Dynamic System Variable sql_mode #
19
# that checks the behavior of this variable in the following ways#
21
# * Valid & Invalid values #
22
# * Scope & Access method #
25
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
26
# server-system-variables.html #
28
###############################################################################
30
--source include/load_sysvars.inc
32
################################################################
33
# START OF sql_mode TESTS #
34
################################################################
37
###################################################################
38
# Saving initial value of sql_mode in a temporary variable #
39
###################################################################
41
SET @global_start_value = @@global.sql_mode;
42
SELECT @global_start_value;
44
SET @session_start_value = @@session.sql_mode;
45
SELECT @session_start_value;
47
--echo '#--------------------FN_DYNVARS_152_01------------------------#'
48
################################################################
49
# Display the DEFAULT value of sql_mode #
50
################################################################
52
SET @@global.sql_mode = ANSI;
53
SET @@global.sql_mode = DEFAULT;
54
SELECT @@global.sql_mode;
56
SET @@session.sql_mode = ANSI;
57
SET @@session.sql_mode = DEFAULT;
58
SELECT @@session.sql_mode;
60
--echo 'Bug# 34876: Default values for both session and global sql_mode is ""';
61
--echo 'and not OFF.';
63
--echo '#---------------------FN_DYNVARS_152_02-------------------------#'
64
#########################################################
65
# Check if NULL or empty value is accepeted #
66
#########################################################
68
--Error ER_WRONG_VALUE_FOR_VAR
69
SET @@global.sql_mode = NULL;
71
# resets sql mode to nothing
72
SET @@global.sql_mode = '';
73
SELECT @@global.sql_mode;
75
SET @@global.sql_mode = ' ';
76
SELECT @@global.sql_mode;
78
--Error ER_WRONG_VALUE_FOR_VAR
79
SET @@session.sql_mode = NULL;
81
SET @@session.sql_mode = '';
82
SELECT @@session.sql_mode;
84
SET @@session.sql_mode = ' ';
85
SELECT @@session.sql_mode;
88
--echo '#--------------------FN_DYNVARS_152_03------------------------#'
89
########################################################################
90
# Change the value of sql_mode to a valid value #
91
########################################################################
93
# sql modes ref: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
95
--echo 'Bug: Incomplete sql modes valid values at:';
96
--echo 'http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html';
98
# check valid values for global
100
SET @@global.sql_mode = ANSI;
101
SELECT @@global.sql_mode;
102
SET @@global.sql_mode = STRICT_TRANS_TABLES;
103
SELECT @@global.sql_mode;
104
SET @@global.sql_mode = TRADITIONAL;
105
SELECT @@global.sql_mode;
106
SET @@global.sql_mode = ALLOW_INVALID_DATES;
107
SELECT @@global.sql_mode;
108
SET @@global.sql_mode = ANSI_QUOTES;
109
SELECT @@global.sql_mode;
110
SET @@global.sql_mode = ERROR_FOR_DIVISION_BY_ZERO;
111
SELECT @@global.sql_mode;
112
SET @@global.sql_mode = HIGH_NOT_PRECEDENCE;
113
SELECT @@global.sql_mode;
114
SET @@global.sql_mode = IGNORE_SPACE;
115
SELECT @@global.sql_mode;
116
SET @@global.sql_mode = NO_AUTO_CREATE_USER;
117
SELECT @@global.sql_mode;
118
SET @@global.sql_mode = NO_AUTO_VALUE_ON_ZERO;
119
SELECT @@global.sql_mode;
120
SET @@global.sql_mode = NO_BACKSLASH_ESCAPES;
121
SELECT @@global.sql_mode;
122
SET @@global.sql_mode = NO_DIR_IN_CREATE;
123
SELECT @@global.sql_mode;
124
SET @@global.sql_mode = NO_ENGINE_SUBSTITUTION;
125
SELECT @@global.sql_mode;
126
SET @@global.sql_mode = NO_FIELD_OPTIONS;
127
SELECT @@global.sql_mode;
128
SET @@global.sql_mode = NO_KEY_OPTIONS;
129
SELECT @@global.sql_mode;
130
SET @@global.sql_mode = NO_TABLE_OPTIONS;
131
SELECT @@global.sql_mode;
132
SET @@global.sql_mode = NO_UNSIGNED_SUBTRACTION;
133
SELECT @@global.sql_mode;
134
SET @@global.sql_mode = NO_ZERO_DATE;
135
SELECT @@global.sql_mode;
136
SET @@global.sql_mode = NO_ZERO_IN_DATE;
137
SELECT @@global.sql_mode;
138
SET @@global.sql_mode = ONLY_FULL_GROUP_BY;
139
SELECT @@global.sql_mode;
140
SET @@global.sql_mode = PIPES_AS_CONCAT;
141
SELECT @@global.sql_mode;
142
SET @@global.sql_mode = REAL_AS_FLOAT;
143
SELECT @@global.sql_mode;
144
SET @@global.sql_mode = STRICT_ALL_TABLES;
145
SELECT @@global.sql_mode;
146
SET @@global.sql_mode = STRICT_TRANS_TABLES;
147
SELECT @@global.sql_mode;
148
SET @@global.sql_mode = DB2;
149
SELECT @@global.sql_mode;
150
SET @@global.sql_mode = MAXDB;
151
SELECT @@global.sql_mode;
152
SET @@global.sql_mode = MSSQL;
153
SELECT @@global.sql_mode;
154
SET @@global.sql_mode = MYSQL323;
155
SELECT @@global.sql_mode;
156
SET @@global.sql_mode = MYSQL40;
157
SELECT @@global.sql_mode;
158
SET @@global.sql_mode = ORACLE;
159
SELECT @@global.sql_mode;
160
SET @@global.sql_mode = POSTGRESQL;
161
SELECT @@global.sql_mode;
162
SET @@global.sql_mode = TRADITIONAL;
163
SELECT @@global.sql_mode;
165
--Error ER_WRONG_VALUE_FOR_VAR
166
SET @@global.sql_mode = OFF;
167
--echo 'Bug: OFF is documented as an sql mode but infact it is not';
169
#check valid values for session
170
SET @@session.sql_mode = ANSI;
171
SELECT @@session.sql_mode;
172
SET @@session.sql_mode = STRICT_TRANS_TABLES;
173
SELECT @@session.sql_mode;
174
SET @@session.sql_mode = TRADITIONAL;
175
SELECT @@session.sql_mode;
176
SET @@session.sql_mode = ALLOW_INVALID_DATES;
177
SELECT @@session.sql_mode;
178
SET @@session.sql_mode = ANSI_QUOTES;
179
SELECT @@session.sql_mode;
180
SET @@session.sql_mode = ERROR_FOR_DIVISION_BY_ZERO;
181
SELECT @@session.sql_mode;
182
SET @@session.sql_mode = HIGH_NOT_PRECEDENCE;
183
SELECT @@session.sql_mode;
184
SET @@session.sql_mode = IGNORE_SPACE;
185
SELECT @@session.sql_mode;
186
SET @@session.sql_mode = NO_AUTO_CREATE_USER;
187
SELECT @@session.sql_mode;
188
SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO;
189
SELECT @@session.sql_mode;
190
SET @@session.sql_mode = NO_BACKSLASH_ESCAPES;
191
SELECT @@session.sql_mode;
192
SET @@session.sql_mode = NO_DIR_IN_CREATE;
193
SELECT @@session.sql_mode;
194
SET @@session.sql_mode = NO_ENGINE_SUBSTITUTION;
195
SELECT @@session.sql_mode;
196
SET @@session.sql_mode = NO_FIELD_OPTIONS;
197
SELECT @@session.sql_mode;
198
SET @@session.sql_mode = NO_KEY_OPTIONS;
199
SELECT @@session.sql_mode;
200
SET @@session.sql_mode = NO_TABLE_OPTIONS;
201
SELECT @@session.sql_mode;
202
SET @@session.sql_mode = NO_UNSIGNED_SUBTRACTION;
203
SELECT @@session.sql_mode;
204
SET @@session.sql_mode = NO_ZERO_DATE;
205
SELECT @@session.sql_mode;
206
SET @@session.sql_mode = NO_ZERO_IN_DATE;
207
SELECT @@session.sql_mode;
208
SET @@session.sql_mode = ONLY_FULL_GROUP_BY;
209
SELECT @@session.sql_mode;
210
SET @@session.sql_mode = PIPES_AS_CONCAT;
211
SELECT @@session.sql_mode;
212
SET @@session.sql_mode = REAL_AS_FLOAT;
213
SELECT @@session.sql_mode;
214
SET @@session.sql_mode = STRICT_ALL_TABLES;
215
SELECT @@session.sql_mode;
216
SET @@session.sql_mode = STRICT_TRANS_TABLES;
217
SELECT @@session.sql_mode;
218
SET @@session.sql_mode = DB2;
219
SELECT @@session.sql_mode;
220
SET @@session.sql_mode = MAXDB;
221
SELECT @@session.sql_mode;
222
SET @@session.sql_mode = MSSQL;
223
SELECT @@session.sql_mode;
224
SET @@session.sql_mode = MYSQL323;
225
SELECT @@session.sql_mode;
226
SET @@session.sql_mode = MYSQL40;
227
SELECT @@session.sql_mode;
228
SET @@session.sql_mode = ORACLE;
229
SELECT @@session.sql_mode;
230
SET @@session.sql_mode = POSTGRESQL;
231
SELECT @@session.sql_mode;
232
SET @@session.sql_mode = TRADITIONAL;
233
SELECT @@session.sql_mode;
235
--Error ER_WRONG_VALUE_FOR_VAR
236
SET @@session.sql_mode = OFF;
238
SET @@global.sql_mode = '?';
239
SELECT @@global.sql_mode;
241
SET @@session.sql_mode = '?';
242
SELECT @@session.sql_mode;
244
--echo 'Bug# 34834: ? is acceptable as a valid sql mode.'
247
--echo '#--------------------FN_DYNVARS_152_04-------------------------#'
248
###########################################################################
249
# Change the value of sql_mode to invalid value #
250
###########################################################################
252
# invalid values for global
253
--Error ER_WRONG_VALUE_FOR_VAR
254
SET @@global.sql_mode = -1;
255
--Error ER_WRONG_VALUE_FOR_VAR
256
SET @@global.sql_mode = ASCII;
257
--Error ER_WRONG_VALUE_FOR_VAR
258
SET @@global.sql_mode = NON_TRADITIONAL;
259
--Error ER_WRONG_VALUE_FOR_VAR
260
SET @@global.sql_mode = 'OF';
261
--Error ER_WRONG_VALUE_FOR_VAR
262
SET @@global.sql_mode = NONE;
263
--Error ER_WRONG_VALUE_FOR_VAR
265
#invalid values for session
267
--Error ER_WRONG_VALUE_FOR_VAR
268
SET @@session.sql_mode = -1;
269
--Error ER_WRONG_VALUE_FOR_VAR
270
SET @@session.sql_mode = ANSI_SINGLE_QUOTES;
271
--Error ER_WRONG_VALUE_FOR_VAR
272
SET @@session.sql_mode = 'ON';
273
--Error ER_WRONG_VALUE_FOR_VAR
274
SET @@session.sql_mode = 'OF';
275
--Error ER_WRONG_VALUE_FOR_VAR
276
SET @@session.sql_mode = DISABLE;
279
--echo '#-------------------FN_DYNVARS_152_05----------------------------#'
280
#########################################################################
281
# Check if the value in session Table matches value in variable #
282
#########################################################################
284
SELECT @@session.sql_mode = VARIABLE_VALUE
285
FROM INFORMATION_SCHEMA.SESSION_VARIABLES
286
WHERE VARIABLE_NAME='sql_mode';
288
--echo '#----------------------FN_DYNVARS_152_06------------------------#'
289
#########################################################################
290
# Check if the value in GLOBAL Table matches value in variable #
291
#########################################################################
293
SELECT @@global.sql_mode = VARIABLE_VALUE
294
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
295
WHERE VARIABLE_NAME='sql_mode';
298
--echo '#---------------------FN_DYNVARS_152_07-------------------------#'
299
###################################################################
300
# Check if numbers can be used on variable #
301
###################################################################
303
# test if variable accepts 0,1,2
304
SET @@global.sql_mode = 0;
305
SELECT @@global.sql_mode;
307
SET @@global.sql_mode = 1;
308
SELECT @@global.sql_mode;
310
SET @@global.sql_mode = 2;
311
SELECT @@global.sql_mode;
313
SET @@global.sql_mode = 3;
314
SELECT @@global.sql_mode;
316
SET @@global.sql_mode = 50000;
317
SELECT @@global.sql_mode;
319
SET @@global.sql_mode = 500000;
320
SELECT @@global.sql_mode;
322
SET @@global.sql_mode = 4294967295;
323
SELECT @@global.sql_mode;
325
--Error ER_WRONG_VALUE_FOR_VAR
326
SET @@global.sql_mode = 4294967296;
328
# use of decimal values
330
SET @@global.sql_mode = 0.4;
331
SELECT @@global.sql_mode;
333
SET @@global.sql_mode = 1.0;
334
SELECT @@global.sql_mode;
336
SET @@global.sql_mode = 40000.1;
337
SELECT @@global.sql_mode;
339
SET @@global.sql_mode = 1.5;
340
SELECT @@global.sql_mode;
342
SET @@global.sql_mode = 124567.49;
343
SELECT @@global.sql_mode;
345
SET @@session.sql_mode = 50000000.5;
346
SELECT @@session.sql_mode;
348
SET @@session.sql_mode = 4294967295.4;
349
SELECT @@session.sql_mode;
351
--echo 'Bug: Decimal values can be used within the range [0.0-4294967295.5).';
352
--echo 'Values are rounded to numeric values as evident from outcome.';
355
--echo '#---------------------FN_DYNVARS_152_08----------------------#'
356
###################################################################
357
# Check if TRUE and FALSE values can be used on variable #
358
###################################################################
360
SET @@global.sql_mode = TRUE;
361
SELECT @@global.sql_mode;
362
SET @@global.sql_mode = FALSE;
363
SELECT @@global.sql_mode;
365
--echo '#---------------------FN_DYNVARS_152_09----------------------#'
366
#########################################################################
367
# Check if sql_mode can be accessed with and without @@ sign #
368
#########################################################################
371
SET sql_mode = 'ANSI';
373
--Error ER_PARSE_ERROR
374
SET session.sql_mode = 'ANSI';
375
--Error ER_PARSE_ERROR
376
SET global.sql_mode = 'ANSI';
378
SET session sql_mode = 1;
381
SET global sql_mode = 0;
382
SELECT @@global.sql_mode;
384
--echo '#---------------------FN_DYNVARS_152_10----------------------#'
385
#######################################################################
386
# Check if sql_mode values can be combined as specified #
387
#######################################################################
389
SET @@session.sql_mode = 'TRADITIONAL,ALLOW_INVALID_DATES,ANSI_QUOTES';
390
SELECT @@session.sql_mode;
392
SET @@global.sql_mode = 'ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT,REAL_AS_FLOAT,ORACLE,POSTGRESQL';
393
SELECT @@global.sql_mode;
395
#try combining invalid mode with correct mode
396
--Error ER_WRONG_VALUE_FOR_VAR
397
SET @@session.sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,HIGH_OR_PRECEDENCE,
400
##############################
401
# Restore initial value #
402
##############################
404
SET @@global.sql_mode = @global_start_value;
405
SELECT @@global.sql_mode;
407
SET @@session.sql_mode = @session_start_value;
408
SELECT @@session.sql_mode;
410
################################################################
411
# END OF sql_mode TESTS #
412
################################################################