1
############## mysql-test\t\collation_connection_basic.test ###################
3
# Variable Name: collation_connection #
4
# Scope: GLOBAL | SESSION #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-02-07 #
14
# Description: Test Cases of Dynamic System Variable collation_connection #
15
# that checks the behavior of this variable in the following ways#
17
# * Valid & Invalid values #
18
# * Scope & Access method #
21
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
22
# server-system-variables.html #
24
###############################################################################
25
--source include/have_big5.inc
26
--source include/have_ujis.inc
27
--source include/have_sjis.inc
28
--source include/have_utf8.inc
29
--source include/have_ucs2.inc
31
--source include/load_sysvars.inc
33
###################################################
34
## START OF collation_connection TESTS ##
35
###################################################
37
#############################################################
38
# Save initial value #
39
#############################################################
40
SET @global_start_value = @@global.collation_connection;
41
SELECT @global_start_value;
42
# Save initial session value
43
SET @session_start_value = @@collation_connection;
44
SELECT @session_start_value;
45
# now save using local access
46
SET @session_start_value = @@local.collation_connection;
47
SELECT @session_start_value;
48
# save using implicit session scope
49
SET @session_start_value = @@session.collation_connection;
50
SELECT @session_start_value;
52
--echo '#--------------------FN_DYNVARS_015_01------------------#'
53
###############################################################################
54
# Test Variable access and assignment with and without @@ #
55
###############################################################################
57
--error ER_BAD_FIELD_ERROR
58
SELECT collation_connection;
59
# assign value without @@
60
SET collation_connection=utf8_unicode_ci;
61
SELECT @@session.collation_connection;
62
# assign global variable without @@
63
--Error ER_PARSE_ERROR
64
SET global.collation_connection=utf8_unicode_ci;
65
# using another syntax for accessing session variable
66
SET session collation_connection=utf8_unicode_ci;
67
# accessing variable with scope the wrong way
68
--Error ER_BAD_FIELD_ERROR
69
SELECT session collation_connection;
70
# using another syntax for accessing dynamic variable
71
SET global collation_connection=utf8_unicode_ci;
72
--Error ER_BAD_FIELD_ERROR
73
SELECT global collation_connection;
75
--echo '#--------------------FN_DYNVARS_015_02-------------------------#'
76
###############################################################################
77
# Check the DEFAULT value of collation_connection for session and global #
78
###############################################################################
79
SET @@collation_connection = latin1_bin;
80
SET @@collation_connection = DEFAULT;
81
SELECT @@collation_connection AS DEFAULT_VALUE;
83
SET @@global.collation_connection = latin1_bin;
84
SET @@global.collation_connection = DEFAULT;
85
SELECT @@global.collation_connection;
87
--echo '#--------------------FN_DYNVARS_015_03-------------------------#'
88
##############################################################################
89
# see if setting global value changes session value and vice versa #
90
##############################################################################
91
SET @@session.collation_connection = utf8_polish_ci;
92
SELECT @@session.collation_connection;
93
SET @@global.collation_connection = latin7_general_ci;
94
SELECT @@global.collation_connection;
95
SELECT @@session.collation_connection AS res_is_utf8_polish_ci;
97
SET @@session.collation_connection = latin7_bin;
98
SELECT @@session.collation_connection;
99
SELECT @@global.collation_connection AS res_is_latin7_general_ci;
101
SELECT @@global.collation_connection=@@session.collation_connection
104
--echo '#--------------------FN_DYNVARS_015_04-------------------------#'
105
###############################################################################
106
# Check if accessing variable with and without session point to session #
108
###############################################################################
109
SELECT @@collation_connection = @@session.collation_connection AS res;
110
SELECT @@collation_connection = @@local.collation_connection AS res;
112
--echo '#--------------------FN_DYNVARS_015_05-------------------------#'
113
###########################################################################
114
# Check if combining character set works #
115
###########################################################################
116
--Error ER_BAD_FIELD_ERROR
117
SET @@collation_connection = latin7_general_ci + latin7_general_cs;
119
--echo '#--------------------FN_DYNVARS_015_06-------------------------#'
120
###############################################################################
121
# Change the value of collation_connection to a valid value for session #
122
###############################################################################
124
let collation_variable = @@session.collation_connection;
125
--source include/collation_basic.inc
128
--echo '#--------------------FN_DYNVARS_015_07-------------------------#'
129
###############################################################################
130
# Change the value of collation_connection to a valid value for global #
131
###############################################################################
133
let collation_variable = @@global.collation_connection;
134
--source include/collation_basic.inc
137
--echo '#--------------------FN_DYNVARS_015_08-------------------------#'
138
##############################################################################
139
# Change the value of collation_connection to a valid value with uppercase, #
140
# lowercase and mixedcase #
141
##############################################################################
142
SET @@collation_connection = LATIN7_GENERAL_CS;
143
SELECT @@collation_connection;
144
SET @@collation_connection = latin7_general_cs;
145
SELECT @@collation_connection;
147
SET @@global.collation_connection = Latin7_GeneRal_cS;
148
SELECT @@global.collation_connection;
151
--echo '#--------------------FN_DYNVARS_015_09-------------------------#'
152
##############################################################
153
# Check if 1,2,3, ... values can be used on variable #
154
##############################################################
155
SET @@collation_connection = 1;
156
SELECT @@collation_connection;
157
SET @@collation_connection = 2;
158
SELECT @@collation_connection;
159
SET @@collation_connection = 3;
160
SELECT @@collation_connection;
161
SET @@collation_connection = 99;
162
SELECT @@collation_connection;
163
--Error ER_UNKNOWN_COLLATION
164
SET @@collation_connection = 100;
166
SET @@global.collation_connection = 1;
167
SELECT @@global.collation_connection;
168
SET @@global.collation_connection = 2;
169
SELECT @@global.collation_connection;
170
SET @@global.collation_connection = 3;
171
SELECT @@global.collation_connection;
172
SET @@global.collation_connection = 99;
173
SELECT @@global.collation_connection;
174
--Error ER_UNKNOWN_COLLATION
175
SET @@global.collation_connection = 100;
177
SET @total_collations = (SELECT count(*) FROM INFORMATION_SCHEMA.COLLATIONS);
178
SELECT @total_collations > 120;
180
--echo '#--------------------FN_DYNVARS_015_10-------------------------#'
181
###############################################################################
182
# Change the value of collation_connection to an invalid value for session #
183
###############################################################################
184
--Error ER_UNKNOWN_COLLATION
185
SET @@collation_connection = latin7_binary;
186
--Error ER_UNKNOWN_COLLATION
187
SET @@collation_connection = 'eucjpms_japanese_cs';
188
--Error ER_UNKNOWN_COLLATION
189
SET @@collation_connection = 0;
190
--Error ER_WRONG_TYPE_FOR_VAR
191
SET @@collation_connection = 1.01;
192
--Error ER_UNKNOWN_COLLATION
193
SET @@collation_connection = -1;
194
--Error ER_UNKNOWN_COLLATION
195
SET @@collation_connection = '';
196
--Error ER_UNKNOWN_COLLATION
197
SET @@collation_connection = ' eucjpms_bin';
198
SET @@collation_connection = true;
199
SELECT @@collation_connection AS res_with_true;
201
--Error ER_UNKNOWN_COLLATION
202
SET @@collation_connection = ON;
204
--echo '#--------------------FN_DYNVARS_015_11-------------------------#'
205
###############################################################################
206
# Change the value of collation_connection to an invalid value for global #
207
###############################################################################
208
--Error ER_UNKNOWN_COLLATION
209
SET @@global.collation_connection = latin7_binary;
210
--Error ER_UNKNOWN_COLLATION
211
SET @@global.collation_connection = 'eucjpms_japanese_cs';
212
--Error ER_UNKNOWN_COLLATION
213
SET @@global.collation_connection = 0;
214
--Error ER_WRONG_TYPE_FOR_VAR
215
SET @@global.collation_connection = 1.1;
216
--Error ER_UNKNOWN_COLLATION
217
SET @@global.collation_connection = -1;
218
--Error ER_UNKNOWN_COLLATION
219
SET @@global.collation_connection = "";
220
--Error ER_UNKNOWN_COLLATION
221
SET @@global.collation_connection = ' eucjpms_bin';
223
SET @@global.collation_connection = true;
225
--Error ER_UNKNOWN_COLLATION
226
SET @@global.collation_connection = ON;
228
--echo '#--------------------FN_DYNVARS_015_12-------------------------#'
229
##############################################################################
230
# Check if the value in GLOBAL Table matches value in variable #
231
##############################################################################
232
SELECT @@global.collation_connection =
233
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
234
WHERE VARIABLE_NAME='collation_connection') AS res;
235
SET @@global.collation_connection = 1;
236
SELECT @@global.collation_connection;
237
SELECT @@global.collation_connection =
238
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
239
WHERE VARIABLE_NAME='collation_connection') AS res;
241
--echo '#--------------------FN_DYNVARS_015_13-------------------------#'
242
###############################################################################
243
# Check if the value in SESSION Table matches value in variable #
244
###############################################################################
245
SELECT @@collation_connection =
246
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES
247
WHERE VARIABLE_NAME='collation_connection') AS res;
248
SELECT @@local.collation_connection =
249
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES
250
WHERE VARIABLE_NAME='collation_connection') AS res;
251
SELECT @@session.collation_connection =
252
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES
253
WHERE VARIABLE_NAME='collation_connection') AS res;
255
####################################
256
# Restore initial value #
257
####################################
258
SET @@global.collation_connection = @global_start_value;
259
SELECT @@global.collation_connection;
260
SET @@session.collation_connection = @session_start_value;
261
SELECT @@session.collation_connection;
263
#############################################################
264
# END OF collation_connection TESTS #
265
#############################################################