1
############## mysql-test\t\collation_database_basic.test #####################
3
# Variable Name: collation_database #
4
# Scope: GLOBAL | SESSION #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-02-07 #
14
# Description: Test Cases of Dynamic System Variable collation_database #
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_database TESTS ##
35
###################################################
37
#############################################################
38
# Save initial value #
39
#############################################################
40
SET @global_start_value = @@global.collation_database;
41
SELECT @global_start_value;
42
# Save initial session value
43
SET @session_start_value = @@collation_database;
44
SELECT @session_start_value;
45
# now save using local access
46
SET @session_start_value = @@local.collation_database;
47
SELECT @session_start_value;
48
# save using implicit session scope
49
SET @session_start_value = @@session.collation_database;
50
SELECT @session_start_value;
52
--echo '#--------------------FN_DYNVARS_016_01------------------#'
53
###############################################################################
54
# Test Variable access and assignment with and without @@ #
55
###############################################################################
57
--error ER_BAD_FIELD_ERROR
58
SELECT collation_database;
59
# assign value without @@
60
SET collation_database=utf8_unicode_ci;
61
SELECT @@session.collation_database;
62
# assign global variable without @@
63
--Error ER_PARSE_ERROR
64
SET global.collation_database=utf8_unicode_ci;
65
# using another syntax for accessing session variable
66
SET session collation_database=utf8_unicode_ci;
67
# accessing variable with scope the wrong way
68
--Error ER_BAD_FIELD_ERROR
69
SELECT session collation_database;
70
# using another syntax for accessing dynamic variable
71
SET global collation_database=utf8_unicode_ci;
72
--Error ER_BAD_FIELD_ERROR
73
SELECT global collation_database;
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 @@collation_database AS DEFAULT_VALUE;
83
SET @@global.collation_database = latin1_bin;
84
SET @@global.collation_database = DEFAULT;
85
SELECT @@global.collation_database;
87
--echo '#--------------------FN_DYNVARS_016_03-------------------------#'
88
##############################################################################
89
# see if setting global value changes session value and vice versa #
90
##############################################################################
91
SET @@session.collation_database = utf8_polish_ci;
92
SELECT @@session.collation_database;
93
SET @@global.collation_database = latin7_general_ci;
94
SELECT @@global.collation_database;
95
SELECT @@session.collation_database AS res_is_utf8_polish_ci;
97
SET @@session.collation_database = latin7_bin;
98
SELECT @@session.collation_database;
99
SELECT @@global.collation_database AS res_is_latin7_general_ci;
101
SELECT @@global.collation_database=@@session.collation_database AS res_is_false;
103
--echo '#--------------------FN_DYNVARS_016_04-------------------------#'
104
###############################################################################
105
# Check if accessing variable with and without session point to session #
107
###############################################################################
108
SELECT @@collation_database = @@session.collation_database AS res;
109
SELECT @@collation_database = @@local.collation_database AS res;
111
--echo '#--------------------FN_DYNVARS_016_05-------------------------#'
112
###########################################################################
113
# Check if combining character set works #
114
###########################################################################
115
--Error ER_BAD_FIELD_ERROR
116
SET @@collation_database = latin7_general_ci + latin7_general_cs;
118
--echo '#--------------------FN_DYNVARS_016_06-------------------------#'
119
###########################################################################
120
# Change the value of collation_database to a valid value for session #
121
###########################################################################
123
let collation_variable = @@session.collation_database;
124
--source include/collation_basic.inc
127
--echo '#--------------------FN_DYNVARS_016_07-------------------------#'
128
###############################################################################
129
# Change the value of collation_database to a valid value for global #
130
###############################################################################
132
let collation_variable = @@global.collation_database;
133
--source include/collation_basic.inc
136
--echo '#--------------------FN_DYNVARS_016_08-------------------------#'
137
#############################################################################
138
# Change the value of collation_database to a valid value with uppercase, #
139
# lowercase and mixedcase #
140
#############################################################################
141
SET @@collation_database = LATIN7_GENERAL_CS;
142
SELECT @@collation_database;
143
SET @@collation_database = latin7_general_cs;
144
SELECT @@collation_database;
146
SET @@global.collation_database = Latin7_GeneRal_cS;
147
SELECT @@global.collation_database;
149
--echo '#--------------------FN_DYNVARS_016_09-------------------------#'
150
##############################################################
151
# Check if 1,2,3, ... values can be used on variable #
152
##############################################################
153
SET @@collation_database = 1;
154
SELECT @@collation_database;
155
SET @@collation_database = 2;
156
SELECT @@collation_database;
157
SET @@collation_database = 3;
158
SELECT @@collation_database;
159
SET @@collation_database = 99;
160
SELECT @@collation_database;
161
--Error ER_UNKNOWN_COLLATION
162
SET @@collation_database = 100;
164
SET @@global.collation_database = 1;
165
SELECT @@global.collation_database;
166
SET @@global.collation_database = 2;
167
SELECT @@global.collation_database;
168
SET @@global.collation_database = 3;
169
SELECT @@global.collation_database;
170
SET @@global.collation_database = 99;
171
SELECT @@global.collation_database;
172
--Error ER_UNKNOWN_COLLATION
173
SET @@global.collation_database = 100;
175
SET @total_collations = (SELECT count(*) FROM INFORMATION_SCHEMA.COLLATIONS);
176
SELECT @total_collations > 120;
179
--echo '#--------------------FN_DYNVARS_016_10-------------------------#'
180
###############################################################################
181
# Change the value of collation_database to an invalid value for session #
182
###############################################################################
183
--Error ER_UNKNOWN_COLLATION
184
SET @@collation_database = latin7_binary;
185
--Error ER_UNKNOWN_COLLATION
186
SET @@collation_database = 'eucjpms_japanese_cs';
187
--Error ER_UNKNOWN_COLLATION
188
SET @@collation_database = 0;
189
--Error ER_WRONG_TYPE_FOR_VAR
190
SET @@collation_database = 1.01;
191
--Error ER_UNKNOWN_COLLATION
192
SET @@collation_database = -1;
193
--Error ER_UNKNOWN_COLLATION
194
SET @@collation_database = '';
195
--Error ER_UNKNOWN_COLLATION
196
SET @@collation_database = ' eucjpms_bin';
197
SET @@collation_database = true;
198
SELECT @@collation_database AS res_with_true;
200
--Error ER_UNKNOWN_COLLATION
201
SET @@collation_database = ON;
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';
222
SET @@global.collation_database = true;
224
--Error ER_UNKNOWN_COLLATION
225
SET @@global.collation_database = ON;
227
--echo '#--------------------FN_DYNVARS_016_12-------------------------#'
228
###############################################################################
229
# Check if the value in GLOBAL Table matches value in variable #
230
###############################################################################
231
SELECT @@global.collation_database =
232
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
233
WHERE VARIABLE_NAME='collation_database') AS res;
234
SET @@global.collation_database = 1;
235
SELECT @@global.collation_database;
236
SELECT @@global.collation_database =
237
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
238
WHERE VARIABLE_NAME='collation_database') AS res;
240
--echo '#--------------------FN_DYNVARS_016_13-------------------------#'
241
##############################################################################
242
# Check if the value in SESSION Table matches value in variable #
243
##############################################################################
244
SELECT @@collation_database =
245
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES
246
WHERE VARIABLE_NAME='collation_database') AS res;
247
SELECT @@local.collation_database =
248
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES
249
WHERE VARIABLE_NAME='collation_database') AS res;
250
SELECT @@session.collation_database =
251
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES
252
WHERE VARIABLE_NAME='collation_database') AS res;
254
####################################
255
# Restore initial value #
256
####################################
257
SET @@global.collation_database = @global_start_value;
258
SELECT @@global.collation_database;
259
SET @@session.collation_database = @session_start_value;
260
SELECT @@session.collation_database;
262
###########################################################
263
# END OF collation_database TESTS #
264
###########################################################