1
############## mysql-test\t\collation_server_func.test #######################
3
# Variable Name: collation_server #
4
# Scope: GLOBAL | SESSION #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-03-08 #
14
# Description: Test Cases of Dynamic System Variable collation_server #
15
# that checks the behavior of this variable #
17
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
18
# server-system-variables.html #
20
###############################################################################
22
--echo '#--------------------FN_DYNVARS_014_01-------------------------#'
23
##################################################################
24
# Check if setting collation_server is changed in new connection #
25
##################################################################
27
SET @global_collation_server = @@global.collation_server;
28
SET @session_collation_server = @@session.collation_server;
30
SET @@global.collation_server = latin1_danish_ci;
31
--echo 'connect (con1,localhost,root,,,,)'
32
connect (con1,localhost,root,,,,);
33
--echo 'connection con1'
35
SELECT @@global.collation_server;
36
SELECT @@session.collation_server;
39
--echo '#--------------------FN_DYNVARS_014_02-------------------------#'
40
#######################################################
41
# Begin the functionality Testing of collation_server #
42
#######################################################
44
--echo 'connection default'
48
DROP TABLE IF EXISTS t1,t2;
51
#==============================================================================
52
--echo '---check if setting collation_server update character_set_server---'
53
#==============================================================================
55
SET @@session.collation_server = utf8_spanish_ci;
56
SELECT @@collation_server, @@character_set_server;
58
#==============================================================================
59
--echo '--check if collation_server effects database/table charset/collation--'
60
#==============================================================================
61
SET @@session.collation_server = latin1_german1_ci;
64
SHOW CREATE DATABASE db1;
65
CREATE TABLE t1(a CHAR(20));
67
#==============================================================================
68
--echo '---check if updating collation_server effects new table/column---'
69
#==============================================================================
70
SET @@session.collation_server = latin1_swedish_ci;
71
CREATE TABLE t2(a CHAR(10));
75
# fill table with some test data
76
INSERT INTO t1 VALUES('Muffler'),('M�ller'),('MX Systems');
78
#==============================================================================
79
--echo '---check if collation_server effects results sort order---'
80
#==============================================================================
81
SET @@session.collation_server = latin1_swedish_ci;
82
SELECT * FROM t1 ORDER BY a;
84
SET @@session.collation_server = latin1_german1_ci;
85
SELECT * FROM t1 ORDER BY a;
87
# explicit Collate clause should effects results sort order
89
SELECT * FROM t1 ORDER BY a COLLATE latin1_swedish_ci;
90
SELECT * FROM t1 ORDER BY a COLLATE latin1_german1_ci;
92
#==============================================================================
93
--echo '---check if indexing is effected by collation_server---'
94
#==============================================================================
95
SET @@session.collation_server = latin1_swedish_ci;
96
ALTER TABLE t1 ADD PRIMARY KEY (a);
98
SELECT * FROM t1 ORDER BY a;
100
ALTER TABLE t1 DROP PRIMARY KEY;
101
SET @@session.collation_server = latin1_german1_ci;
102
ALTER TABLE t1 ADD PRIMARY KEY (a);
104
SELECT * FROM t1 ORDER BY a;
111
SET @@global.collation_server = @global_collation_server;
112
SET @@session.collation_server = @session_collation_server;
113
#####################################################
114
# End of functionality Testing for collation_server #
115
#####################################################