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