diff options
Diffstat (limited to 'mysql-test/suite/sys_vars/t/collation_database_func.test')
| -rw-r--r-- | mysql-test/suite/sys_vars/t/collation_database_func.test | 138 |
1 files changed, 138 insertions, 0 deletions
diff --git a/mysql-test/suite/sys_vars/t/collation_database_func.test b/mysql-test/suite/sys_vars/t/collation_database_func.test new file mode 100644 index 00000000000..ba8fc5a3d5e --- /dev/null +++ b/mysql-test/suite/sys_vars/t/collation_database_func.test @@ -0,0 +1,138 @@ +############## mysql-test\t\collation_database_func.test ##################### +# # +# Variable Name: collation_database # +# Scope: GLOBAL | SESSION # +# Access Type: Dynamic # +# Data Type: string # +# Default Value: # +# Range: # +# # +# # +# Creation Date: 2008-03-08 # +# Author: Rizwan # +# # +# Description: Test Cases of Dynamic System Variable collation_database # +# that checks the behavior of this variable # +# # +# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # +# server-system-variables.html # +# # +############################################################################### + + +--echo '#--------------------FN_DYNVARS_011_01-------------------------#' +#################################################################### +# Check if setting collation_database is changed in new connection # +#################################################################### +SET @global_collation_database = @@global.collation_database; +SET @session_collation_database = @@session.collation_database; +SET @session_collation_server = @@session.collation_server; + + +SET @@global.collation_database = latin1_danish_ci; +--echo 'connect (con1,localhost,root,,,,)' +connect (con1,localhost,root,,,,); +--echo 'connection con1' +connection con1; +SELECT @@global.collation_database; +SELECT @@session.collation_database; +disconnect con1; + +--echo 'Bug#35378: New session collation_database is not change by global collation_database' + +--echo '#--------------------FN_DYNVARS_011_02-------------------------#' +######################################################### +# Begin the functionality Testing of collation_database # +######################################################### + +--echo 'connection default' +connection default; + +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +--enable_warnings + + +SELECT @@character_set_server,@@collation_server; +#============================================================================== +--echo '--check if setting collation_database update character_set_database--' +#============================================================================== + +SET @@session.collation_database = utf8_spanish_ci; +SELECT @@collation_database, @@character_set_database; + +#============================================================================== +--echo '--check if collation_database effects database/tables charset/collation' +#============================================================================== +SET @@session.collation_server = utf8_roman_ci; +SET @@session.collation_database = latin2_croatian_ci; +CREATE DATABASE db1; +USE db1; +SHOW CREATE DATABASE db1; +--echo 'Bug#35380: collation_database does not effects CREATE DATABASE without characater set' + +CREATE TABLE t1(a CHAR(20)); +SHOW CREATE TABLE t1; + +#============================================================================== +--echo '---check if updating collation_database effects new table/column---' +#============================================================================== +SET @@session.collation_database = latin7_general_cs; +CREATE TABLE t2(a CHAR(10)); +SHOW CREATE TABLE t2; + +DROP TABLE t1,t2; + + +#============================================================================== +--echo '--check value of collation and character set when current db is dropped' +#============================================================================== +DROP DATABASE db1; +SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server; +USE test; +SELECT @@collation_database,@@collation_server,@@character_set_database,@@character_set_server; + + +--echo 'fill table with some test data'; +CREATE TABLE t1(a CHAR(20))CHARACTER SET=latin1; +INSERT INTO t1 VALUES('Muffler'),('Müller'),('MX Systems'); + +#============================================================================== +--echo '---check if collation_database effects results sort order---' +#============================================================================== + +SET @@session.collation_database = latin1_swedish_ci; +SELECT * FROM t1 ORDER BY a; + +SET @@session.collation_database = latin1_german1_ci; +SELECT * FROM t1 ORDER BY a; + +--echo 'explicit Collate clause should effects results sort order'; + +SELECT * FROM t1 ORDER BY a COLLATE latin1_swedish_ci; +SELECT * FROM t1 ORDER BY a COLLATE latin1_german1_ci; + + +#============================================================================== +--echo '----check if indexing is effected by collation_database---' +#============================================================================== +SET @@session.collation_database = latin1_swedish_ci; +ALTER TABLE t1 ADD PRIMARY KEY (a); +REPAIR TABLE t1; +SELECT * FROM t1 ORDER BY a; + +ALTER TABLE t1 DROP PRIMARY KEY; +SET @@session.collation_database = latin1_german1_ci; +ALTER TABLE t1 ADD PRIMARY KEY (a); +REPAIR TABLE t1; +SELECT * FROM t1 ORDER BY a; + +# clean up +DROP TABLE t1; +SET @@global.collation_database = @global_collation_database; +SET @@session.collation_database = @session_collation_database; +SET @@session.collation_server = @session_collation_server; + +####################################################### +# End of functionality Testing for collation_database # +####################################################### |