Debian Codegraph @

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

raw
1 ############## mysql-test\t\collation_database_basic.test #####################
2 # #
3 # Variable Name: collation_database #
4 # Scope: GLOBAL | SESSION #
5 # Access Type: Dynamic #
6 # Data Type: string #
7 # Default Value: #
8 # Range: #
9 # #
10 # #
11 # Creation Date: 2008-02-07 #
12 # Author: Rizwan #
13 # #
14 # Description: Test Cases of Dynamic System Variable collation_database #
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 collation_database TESTS ##
30 ###################################################
31
32 #############################################################
33 # Save initial value #
34 #############################################################
35 SET @global_start_value = @@global.collation_database;
36 SELECT @global_start_value;
37 # Save initial session value
38 SET @session_start_value = @@collation_database;
39 SELECT @session_start_value;
40 # now save using local access
41 SET @session_start_value = @@local.collation_database;
42 SELECT @session_start_value;
43 # save using implicit session scope
44 SET @session_start_value = @@session.collation_database;
45 SELECT @session_start_value;
46
47 --echo '#--------------------FN_DYNVARS_016_01------------------#'
48 ###############################################################################
49 # Test Variable access and assignment with and without @@ #
50 ###############################################################################
51 # select without @@
52 --error ER_BAD_FIELD_ERROR
53 SELECT collation_database;
54 # assign value without @@
55 SET collation_database=utf8_unicode_ci;
56 SELECT @@session.collation_database;
57 # assign global variable without @@
58 --Error ER_PARSE_ERROR
59 SET global.collation_database=utf8_unicode_ci;
60 # using another syntax for accessing session variable
61 SET session collation_database=utf8_unicode_ci;
62 # accessing variable with scope the wrong way
63 --Error ER_BAD_FIELD_ERROR
64 SELECT session collation_database;
65 # using another syntax for accessing dynamic variable
66 SET global collation_database=utf8_unicode_ci;
67 --Error ER_BAD_FIELD_ERROR
68 SELECT global collation_database;
69
70 --echo '#--------------------FN_DYNVARS_016_02-------------------------#'
71 ##############################################################################
72 # Check the DEFAULT value of collation_database for session and global #
73 ##############################################################################
74 SET @@collation_database = latin1_bin;
75 SET @@collation_database = DEFAULT;
76 SELECT DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=DATABASE();
77 SELECT @@collation_database AS DEFAULT_VALUE;
78
79 SET @@global.collation_database = latin1_bin;
80 SET @@global.collation_database = DEFAULT;
81 SELECT @@global.collation_database;
82
83 --echo '#--------------------FN_DYNVARS_016_03-------------------------#'
84 ##############################################################################
85 # see if setting global value changes session value and vice versa #
86 ##############################################################################
87 SET @@session.collation_database = utf8_polish_ci;
88 SELECT @@session.collation_database;
89 SET @@global.collation_database = latin7_general_ci;
90 SELECT @@global.collation_database;
91 SELECT @@session.collation_database AS res_is_utf8_polish_ci;
92
93 SET @@session.collation_database = latin7_bin;
94 SELECT @@session.collation_database;
95 SELECT @@global.collation_database AS res_is_latin7_general_ci;
96 # composite check
97 SELECT @@global.collation_database=@@session.collation_database AS res_is_false;
98
99 --echo '#--------------------FN_DYNVARS_016_04-------------------------#'
100 ###############################################################################
101 # Check if accessing variable with and without session point to session #
102 # variable #
103 ###############################################################################
104 SELECT @@collation_database = @@session.collation_database AS res;
105 SELECT @@collation_database = @@local.collation_database AS res;
106
107 --echo '#--------------------FN_DYNVARS_016_05-------------------------#'
108 ###########################################################################
109 # Check if combining character set works #
110 ###########################################################################
111 --Error ER_BAD_FIELD_ERROR
112 SET @@collation_database = latin7_general_ci + latin7_general_cs;
113
114 --echo '#--------------------FN_DYNVARS_016_06-------------------------#'
115 ###########################################################################
116 # Change the value of collation_database to a valid value for session #
117 ###########################################################################
118
119 let $collation_variable = @@session.collation_database;
120 --source suite/sys_vars/inc/collation_basic.inc
121
122
123 --echo '#--------------------FN_DYNVARS_016_07-------------------------#'
124 ###############################################################################
125 # Change the value of collation_database to a valid value for global #
126 ###############################################################################
127
128 let $collation_variable = @@global.collation_database;
129 --source suite/sys_vars/inc/collation_basic.inc
130
131
132 --echo '#--------------------FN_DYNVARS_016_08-------------------------#'
133 #############################################################################
134 # Change the value of collation_database to a valid value with uppercase, #
135 # lowercase and mixedcase #
136 #############################################################################
137 SET @@collation_database = LATIN7_GENERAL_CS;
138 SELECT @@collation_database;
139 SET @@collation_database = latin7_general_cs;
140 SELECT @@collation_database;
141
142 SET @@global.collation_database = Latin7_GeneRal_cS;
143 SELECT @@global.collation_database;
144
145 --echo '#--------------------FN_DYNVARS_016_09-------------------------#'
146 ##############################################################
147 # Check if 1,2,3, ... values can be used on variable #
148 ##############################################################
149 SET @@collation_database = 1;
150 SELECT @@collation_database;
151 SET @@collation_database = 2;
152 SELECT @@collation_database;
153 SET @@collation_database = 3;
154 SELECT @@collation_database;
155 SET @@collation_database = 99;
156 SELECT @@collation_database;
157 --Error ER_UNKNOWN_COLLATION
158 SET @@collation_database = 100;
159
160 SET @@global.collation_database = 1;
161 SELECT @@global.collation_database;
162 SET @@global.collation_database = 2;
163 SELECT @@global.collation_database;
164 SET @@global.collation_database = 3;
165 SELECT @@global.collation_database;
166 SET @@global.collation_database = 99;
167 SELECT @@global.collation_database;
168 --Error ER_UNKNOWN_COLLATION
169 SET @@global.collation_database = 100;
170
171 SET @total_collations = (SELECT count(*) FROM INFORMATION_SCHEMA.COLLATIONS);
172 SELECT @total_collations > 120;
173
174
175 --echo '#--------------------FN_DYNVARS_016_10-------------------------#'
176 ###############################################################################
177 # Change the value of collation_database to an invalid value for session #
178 ###############################################################################
179 --Error ER_UNKNOWN_COLLATION
180 SET @@collation_database = latin7_binary;
181 --Error ER_UNKNOWN_COLLATION
182 SET @@collation_database = 'eucjpms_japanese_cs';
183 --Error ER_UNKNOWN_COLLATION
184 SET @@collation_database = 0;
185 --Error ER_WRONG_TYPE_FOR_VAR
186 SET @@collation_database = 1.01;
187 --Error ER_UNKNOWN_COLLATION
188 SET @@collation_database = -1;
189 --Error ER_UNKNOWN_COLLATION
190 SET @@collation_database = '';
191 --Error ER_UNKNOWN_COLLATION
192 SET @@collation_database = ' eucjpms_bin';
193 SET @@collation_database = true;
194 SELECT @@collation_database AS res_with_true;
195
196 --Error ER_UNKNOWN_COLLATION
197 SET @@collation_database = ON;
198 --Error ER_UNKNOWN_COLLATION
199 SET @@collation_database = repeat('e', 256);
200 --Error ER_UNKNOWN_COLLATION
201 SET @@collation_database = repeat('e', 1024);
202
203 --echo '#--------------------FN_DYNVARS_016_11-------------------------#'
204 ###############################################################################
205 # Change the value of collation_database to an invalid value for global #
206 ###############################################################################
207 --Error ER_UNKNOWN_COLLATION
208 SET @@global.collation_database = latin7_binary;
209 --Error ER_UNKNOWN_COLLATION
210 SET @@global.collation_database = 'eucjpms_japanese_cs';
211 --Error ER_UNKNOWN_COLLATION
212 SET @@global.collation_database = 0;
213 --Error ER_WRONG_TYPE_FOR_VAR
214 SET @@global.collation_database = 1.1;
215 --Error ER_UNKNOWN_COLLATION
216 SET @@global.collation_database = -1;
217 --Error ER_UNKNOWN_COLLATION
218 SET @@global.collation_database = "";
219 --Error ER_UNKNOWN_COLLATION
220 SET @@global.collation_database = ' eucjpms_bin';
221
222 SET @@global.collation_database = true;
223
224 --Error ER_UNKNOWN_COLLATION
225 SET @@global.collation_database = ON;
226 --Error ER_UNKNOWN_COLLATION
227 SET @@global.collation_database = repeat('e', 256);
228 --Error ER_UNKNOWN_COLLATION
229 SET @@global.collation_database = repeat('e', 1024);
230
231 --echo '#--------------------FN_DYNVARS_016_12-------------------------#'
232 ###############################################################################
233 # Check if the value in GLOBAL Table matches value in variable #
234 ###############################################################################
235 --disable_warnings
236 SELECT @@global.collation_database =
237 (SELECT VARIABLE_VALUE FROM performance_schema.global_variables
238 WHERE VARIABLE_NAME='collation_database') AS res;
239 --enable_warnings
240 SET @@global.collation_database = 1;
241 SELECT @@global.collation_database;
242 --disable_warnings
243 SELECT @@global.collation_database =
244 (SELECT VARIABLE_VALUE FROM performance_schema.global_variables
245 WHERE VARIABLE_NAME='collation_database') AS res;
246 --enable_warnings
247
248 --echo '#--------------------FN_DYNVARS_016_13-------------------------#'
249 ##############################################################################
250 # Check if the value in SESSION Table matches value in variable #
251 ##############################################################################
252 --disable_warnings
253 SELECT @@collation_database =
254 (SELECT VARIABLE_VALUE FROM performance_schema.session_variables
255 WHERE VARIABLE_NAME='collation_database') AS res;
256 SELECT @@local.collation_database =
257 (SELECT VARIABLE_VALUE FROM performance_schema.session_variables
258 WHERE VARIABLE_NAME='collation_database') AS res;
259 SELECT @@session.collation_database =
260 (SELECT VARIABLE_VALUE FROM performance_schema.session_variables
261 WHERE VARIABLE_NAME='collation_database') AS res;
262 --enable_warnings
263
264 ####################################
265 # Restore initial value #
266 ####################################
267 SET @@global.collation_database = @global_start_value;
268 SELECT @@global.collation_database;
269 SET @@session.collation_database = @session_start_value;
270 SELECT @@session.collation_database;
271
272 ###########################################################
273 # END OF collation_database TESTS #
274 ###########################################################