Debian Codegraph @

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

raw
1 #################### mysql-test\t\sql_mode_basic.test #########################
2 # #
3 # Variable Name: old_mode #
4 # Scope: GLOBAL | SESSION #
5 # Access Type: Dynamic #
6 # Data Type: enumeration #
7 # Default Value: YES #
8 # Valid Values : NO_DUP_KEY_WARNINGS_WITH_IGNORE, NO_PROGRESS_INFO #
9 # #
10 # #
11 # Description: Test Cases of Dynamic System Variable old_mode #
12 # that checks the behavior of this variable in the following ways#
13 # * Default Value #
14 # * Valid & Invalid values #
15 # * Scope & Access method #
16 # * Data Integrity #
17 # #
18 # #
19 ###############################################################################
20
21 --source include/load_sysvars.inc
22
23 ################################################################
24 # START OF sql_mode TESTS #
25 ################################################################
26
27
28 ###################################################################
29 # Saving initial value of sql_mode in a temporary variable #
30 ###################################################################
31
32 SET @global_start_value = @@global.old_mode;
33 SELECT @global_start_value;
34
35 SET @session_start_value = @@session.old_mode;
36 SELECT @session_start_value;
37
38 --echo '#--------------------FN_DYNVARS_152_01------------------------#'
39 ################################################################
40 # Display the DEFAULT value of old_mode #
41 ################################################################
42
43 SET @@global.old_mode = "NO_PROGRESS_INFO";
44 SET @@global.old_mode = DEFAULT;
45 SELECT @@global.old_mode;
46
47 SET @@session.old_mode = "NO_PROGRESS_INFO";
48 SET @@session.old_mode = DEFAULT;
49 SELECT @@session.old_mode;
50
51 --echo '#---------------------FN_DYNVARS_152_02-------------------------#'
52 #########################################################
53 # Check if NULL or empty value is accepeted #
54 #########################################################
55
56 --Error ER_WRONG_VALUE_FOR_VAR
57 SET @@global.old_mode = NULL;
58
59 # resets sql mode to nothing
60 SET @@global.old_mode = '';
61 SELECT @@global.old_mode;
62
63 SET @@global.old_mode = ' ';
64 SELECT @@global.old_mode;
65
66 --Error ER_WRONG_VALUE_FOR_VAR
67 SET @@session.old_mode = NULL;
68
69 SET @@session.old_mode = '';
70 SELECT @@session.old_mode;
71
72 SET @@session.old_mode = ' ';
73 SELECT @@session.old_mode;
74
75
76 --echo '#--------------------FN_DYNVARS_152_03------------------------#'
77 ########################################################################
78 # Change the value of old_mode to a valid value #
79 ########################################################################
80
81 # sql modes ref: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
82
83 # check valid values for global
84
85 SET @@global.old_mode = NO_PROGRESS_INFO;
86 SELECT @@global.old_mode;
87 SET @@global.old_mode = NO_DUP_KEY_WARNINGS_WITH_IGNORE;
88 SELECT @@global.old_mode;
89
90 --Error ER_WRONG_VALUE_FOR_VAR
91 SET @@global.old_mode = OFF;
92
93 #check valid values for session
94 SET @@session.old_mode = NO_PROGRESS_INFO;
95 SELECT @@session.old_mode;
96 SET @@session.old_mode = NO_DUP_KEY_WARNINGS_WITH_IGNORE;
97 SELECT @@session.old_mode;
98
99 --Error ER_WRONG_VALUE_FOR_VAR
100 SET @@session.old_mode = OFF;
101
102 --Error ER_WRONG_VALUE_FOR_VAR
103 SET @@global.old_mode = '?';
104 SELECT @@global.old_mode;
105
106 --echo '#--------------------FN_DYNVARS_152_04-------------------------#'
107 ###########################################################################
108 # Change the value of old_mode to invalid value #
109 ###########################################################################
110
111 # invalid values for global
112 --Error ER_WRONG_VALUE_FOR_VAR
113 SET @@global.old_mode = -1;
114 --Error ER_WRONG_VALUE_FOR_VAR
115 SET @@global.old_mode = ASCII;
116 --Error ER_WRONG_VALUE_FOR_VAR
117 SET @@global.old_mode = NON_TRADITIONAL;
118 --Error ER_WRONG_VALUE_FOR_VAR
119 SET @@global.old_mode = 'OF';
120 --Error ER_WRONG_VALUE_FOR_VAR
121 SET @@global.old_mode = NONE;
122
123 #invalid values for session
124
125 --Error ER_WRONG_VALUE_FOR_VAR
126 SET @@session.old_mode = -1;
127 --Error ER_WRONG_VALUE_FOR_VAR
128 SET @@session.old_mode = ANSI_SINGLE_QUOTES;
129 --Error ER_WRONG_VALUE_FOR_VAR
130 SET @@session.old_mode = 'ON';
131 --Error ER_WRONG_VALUE_FOR_VAR
132 SET @@session.old_mode = 'OF';
133 --Error ER_WRONG_VALUE_FOR_VAR
134 SET @@session.old_mode = DISABLE;
135
136
137 --echo '#-------------------FN_DYNVARS_152_05----------------------------#'
138 #########################################################################
139 # Check if the value in session Table matches value in variable #
140 #########################################################################
141
142 SELECT @@session.old_mode = VARIABLE_VALUE
143 FROM INFORMATION_SCHEMA.SESSION_VARIABLES
144 WHERE VARIABLE_NAME='old_mode';
145
146 --echo '#----------------------FN_DYNVARS_152_06------------------------#'
147 #########################################################################
148 # Check if the value in GLOBAL Table matches value in variable #
149 #########################################################################
150
151 SELECT @@global.old_mode = VARIABLE_VALUE
152 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
153 WHERE VARIABLE_NAME='old_mode';
154
155
156 --echo '#---------------------FN_DYNVARS_152_07-------------------------#'
157 ###################################################################
158 # Check if numbers can be used on variable #
159 ###################################################################
160
161 # test if variable accepts 0,1,2
162 SET @@global.old_mode = 0;
163 SELECT @@global.old_mode;
164
165 SET @@global.old_mode = 1;
166 SELECT @@global.old_mode;
167
168 SET @@global.old_mode = 2;
169 SELECT @@global.old_mode;
170
171 SET @@global.old_mode = 4;
172 SELECT @@global.old_mode;
173
174 --Error ER_WRONG_VALUE_FOR_VAR
175 SET @@global.old_mode = 6536;
176 SELECT @@global.old_mode;
177
178 # use of decimal values
179
180 --Error ER_WRONG_TYPE_FOR_VAR
181 SET @@global.old_mode = 0.4;
182
183 --echo '#---------------------FN_DYNVARS_152_08----------------------#'
184 ###################################################################
185 # Check if TRUE and FALSE values can be used on variable #
186 ###################################################################
187
188 SET @@global.old_mode = TRUE;
189 SELECT @@global.old_mode;
190 SET @@global.old_mode = FALSE;
191 SELECT @@global.old_mode;
192
193 --echo '#---------------------FN_DYNVARS_152_09----------------------#'
194 #########################################################################
195 # Check if old_mode can be accessed with and without @@ sign #
196 #########################################################################
197
198 SET old_mode = 'NO_PROGRESS_INFO';
199
200 SET session old_mode = 1;
201 SELECT @@old_mode;
202
203 SET global old_mode = 0;
204 SELECT @@global.old_mode;
205
206 --echo '#---------------------FN_DYNVARS_152_10----------------------#'
207 #######################################################################
208 # Check if old_mode values can be combined as specified #
209 #######################################################################
210
211 SET @@session.old_mode = 'NO_PROGRESS_INFO,NO_DUP_KEY_WARNINGS_WITH_IGNORE';
212 SELECT @@session.old_mode;
213
214 SET @@global.old_mode = 'NO_DUP_KEY_WARNINGS_WITH_IGNORE,NO_PROGRESS_INFO';
215 SELECT @@global.old_mode;
216
217 #try combining invalid mode with correct mode
218 --Error ER_WRONG_VALUE_FOR_VAR
219 SET @@session.old_mode = 'NO_PROGRESS_INFO,NO_SUCH_MODE';
220
221 #zero-length values are ok
222 SET @@old_mode=',';
223 SELECT @@old_mode;
224 SET @@old_mode=',,,,NO_PROGRESS_INFO,,,';
225 SELECT @@old_mode;
226 --Error ER_WRONG_VALUE_FOR_VAR
227 SET @@old_mode=',,,,FOOBAR,,,,,';
228 SELECT @@old_mode;
229
230 ##############################
231 # Restore initial value #
232 ##############################
233
234 SET @@global.old_mode = @global_start_value;
235 SELECT @@global.old_mode;
236
237 SET @@session.old_mode = @session_start_value;
238 SELECT @@session.old_mode;
239
240 --echo #
241 --echo # Beginning of 10.6 test
242 --echo #
243 --echo # MDEV-8334: Rename utf8 to utf8mb3
244 --echo #
245 --echo # Save and display old values
246 SET @save_old_mode = @@OLD_MODE;
247 SET @save_character_set_server = @@character_set_server;
248 SET @save_character_set_client = @@character_set_client;
249 SET @save_character_set_results = @@character_set_results;
250 SET @save_character_set_connection = @@character_set_connection;
251 SET @save_character_set_filesystem = @@character_set_filesystem;
252 SET @save_character_set_database = @@character_set_database;
253 SET @save_collation_connection = @@collation_connection;
254 SET @save_collation_server = @@collation_server;
255 SET @save_collation_database = @@collation_database;
256 SELECT @@OLD_MODE;
257 SELECT @@character_set_server,@@character_set_client,@@character_set_results,
258 @@character_set_connection, @@character_set_filesystem, @@character_set_database,
259 @@collation_connection, @@collation_server, @@collation_database;
260 --echo #
261 --echo # UTF8MB3 alias for UTF8
262 --echo #
263 SET @@character_set_server = utf8;
264 SET @@character_set_client = utf8;
265 SET @@character_set_results = utf8;
266 SET @@character_set_connection = utf8;
267 SET @@character_set_filesystem = utf8;
268 SET @@character_set_database = utf8;
269 SET @@collation_connection = utf8_general_ci;
270 SET @@collation_server = utf8_unicode_ci;
271 SET @@collation_database = utf8_bin;
272 SELECT @@character_set_server, @@character_set_client, @@character_set_results,
273 @@character_set_connection, @@character_set_filesystem, @@character_set_database,
274 @@collation_connection, @@collation_server, @@collation_database;
275 CREATE DATABASE db1 CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
276 ALTER DATABASE db1 CHARACTER SET = 'utf8' COLLATE = 'utf8_unicode_ci';
277 CREATE TABLE tb1 (id1 INT) CHARACTER SET 'utf8' COLLATE 'utf8_bin';
278 SHOW CREATE TABLE tb1;
279 DROP TABLE tb1;
280 DROP DATABASE db1;
281 --echo #
282 --echo # UTF8MB4 is alias for UTF8
283 --echo #
284 SET @@OLD_MODE=0;
285 SET @@character_set_server = utf8;
286 SET @@character_set_client = utf8;
287 SET @@character_set_results = utf8;
288 SET @@character_set_connection = utf8;
289 SET @@character_set_filesystem = utf8;
290 SET @@character_set_database = utf8;
291 SET @@collation_connection = utf8_general_ci;
292 SET @@collation_server = utf8_unicode_ci;
293 SET @@collation_database = utf8_bin;
294 SELECT @@character_set_server, @@character_set_client, @@character_set_results,
295 @@character_set_connection, @@character_set_filesystem, @@character_set_database,
296 @@collation_connection, @@collation_server, @@collation_database;
297 CREATE DATABASE db1 CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
298 ALTER DATABASE db1 CHARACTER SET = 'utf8' COLLATE = 'utf8_unicode_ci';
299 CREATE TABLE tb1 (id1 INT) CHARACTER SET 'utf8' COLLATE 'utf8_bin';
300 SHOW CREATE TABLE tb1;
301 DROP TABLE tb1;
302 DROP DATABASE db1;
303 SET @@OLD_MODE = @save_old_mode;
304 SET @@character_set_server = @save_character_set_server;
305 SET @@character_set_client = @save_character_set_client;
306 SET @@character_set_results = @save_character_set_results;
307 SET @@character_set_connection = @save_character_set_connection;
308 SET @@character_set_filesystem = @save_character_set_filesystem;
309 SET @@character_set_database = @save_character_set_database;
310 SET @@collation_connection = @save_collation_connection;
311 SET @@collation_server = @save_collation_server;
312 SET @@collation_database = @save_collation_database;
313 --echo #
314 --echo # End of 10.6 test
315 --echo #
316
317 --echo #
318 --echo # Beginning of 10.9 test
319 --echo #
320 --echo #
321 --echo # MDEV-24920: Merge "old" SQL variable to "old_mode" sql variable
322 --echo #
323
324 --echo # Checking that setting old will also set old_mode
325 SELECT @@OLD_MODE;
326
327 SET old= 1;
328 SELECT @@OLD_MODE;
329
330 SET old= DEFAULT;
331 SELECT @@OLD_MODE;
332
333 --echo # Checking that old_mode can be set independently as well
334
335 SELECT @@OLD_MODE;
336
337 SET @save_old_mode = @@OLD_MODE;
338
339 SET @@OLD_MODE= IGNORE_INDEX_ONLY_FOR_JOIN;
340 SELECT @@OLD_MODE;
341
342 SET @@OLD_MODE= COMPAT_5_1_CHECKSUM;
343 SELECT @@OLD_MODE;
344
345 SET @@OLD_MODE= @save_old_mode;
346
347 --echo #
348 --echo # End of 10.9 test
349 --echo #
350 ################################################################
351 # END OF old_mode TESTS #
352 ################################################################