diff options
Diffstat (limited to 'mysql-test/t/collation_database_basic.test')
| -rw-r--r-- | mysql-test/t/collation_database_basic.test | 259 |
1 files changed, 259 insertions, 0 deletions
diff --git a/mysql-test/t/collation_database_basic.test b/mysql-test/t/collation_database_basic.test new file mode 100644 index 00000000000..478ecbdb9ab --- /dev/null +++ b/mysql-test/t/collation_database_basic.test @@ -0,0 +1,259 @@ +############## mysql-test\t\collation_database_basic.test ##################### +# # +# Variable Name: collation_database # +# Scope: GLOBAL | SESSION # +# Access Type: Dynamic # +# Data Type: string # +# Default Value: # +# Range: # +# # +# # +# Creation Date: 2008-02-07 # +# Author: Rizwan # +# # +# Description: Test Cases of Dynamic System Variable collation_database # +# that checks the behavior of this variable in the following ways# +# * Default Value # +# * Valid & Invalid values # +# * Scope & Access method # +# * Data Integrity # +# # +# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # +# server-system-variables.html # +# # +############################################################################### + +--source include/load_sysvars.inc + +################################################### +## START OF collation_database TESTS ## +################################################### + +############################################################# +# Save initial value # +############################################################# +SET @global_start_value = @@global.collation_database; +SELECT @global_start_value; +# Save initial session value +SET @session_start_value = @@collation_database; +SELECT @session_start_value; +# now save using local access +SET @session_start_value = @@local.collation_database; +SELECT @session_start_value; +# save using implicit session scope +SET @session_start_value = @@session.collation_database; +SELECT @session_start_value; + +--echo '#--------------------FN_DYNVARS_016_01------------------#' +############################################################################### +# Test Variable access and assignment with and without @@ # +############################################################################### +# select without @@ +--error ER_BAD_FIELD_ERROR +SELECT collation_database; +# assign value without @@ +SET collation_database=utf8_unicode_ci; +SELECT @@session.collation_database; +# assign global variable without @@ +--Error ER_PARSE_ERROR +SET global.collation_database=utf8_unicode_ci; +# using another syntax for accessing session variable +SET session collation_database=utf8_unicode_ci; +# accessing variable with scope the wrong way +--Error ER_BAD_FIELD_ERROR +SELECT session collation_database; +# using another syntax for accessing dynamic variable +SET global collation_database=utf8_unicode_ci; +--Error ER_BAD_FIELD_ERROR +SELECT global collation_database; + +--echo '#--------------------FN_DYNVARS_016_02-------------------------#' +############################################################################## +# Check the DEFAULT value of collation_database for session and global # +############################################################################## +SET @@collation_database = latin1_bin; +SET @@collation_database = DEFAULT; +SELECT @@collation_database AS DEFAULT_VALUE; + +SET @@global.collation_database = latin1_bin; +SET @@global.collation_database = DEFAULT; +SELECT @@global.collation_database; + +--echo '#--------------------FN_DYNVARS_016_03-------------------------#' +############################################################################## +# see if setting global value changes session value and vice versa # +############################################################################## +SET @@session.collation_database = utf8_polish_ci; +SELECT @@session.collation_database; +SET @@global.collation_database = latin7_general_ci; +SELECT @@global.collation_database; +SELECT @@session.collation_database AS res_is_utf8_polish_ci; + +SET @@session.collation_database = latin7_bin; +SELECT @@session.collation_database; +SELECT @@global.collation_database AS res_is_latin7_general_ci; +# composite check +SELECT @@global.collation_database=@@session.collation_database AS res_is_false; + +--echo '#--------------------FN_DYNVARS_016_04-------------------------#' +############################################################################### +# Check if accessing variable with and without session point to session # +# variable # +############################################################################### +SELECT @@collation_database = @@session.collation_database AS res; +SELECT @@collation_database = @@local.collation_database AS res; + +--echo '#--------------------FN_DYNVARS_016_05-------------------------#' +########################################################################### +# Check if combining character set works # +########################################################################### +--Error ER_BAD_FIELD_ERROR +SET @@collation_database = latin7_general_ci + latin7_general_cs; + +--echo '#--------------------FN_DYNVARS_016_06-------------------------#' +########################################################################### +# Change the value of collation_database to a valid value for session # +########################################################################### + +let collation_variable = @@session.collation_database; +--source include/collation_basic.inc + + +--echo '#--------------------FN_DYNVARS_016_07-------------------------#' +############################################################################### +# Change the value of collation_database to a valid value for global # +############################################################################### + +let collation_variable = @@global.collation_database; +--source include/collation_basic.inc + + +--echo '#--------------------FN_DYNVARS_016_08-------------------------#' +############################################################################# +# Change the value of collation_database to a valid value with uppercase, # +# lowercase and mixedcase # +############################################################################# +SET @@collation_database = LATIN7_GENERAL_CS; +SELECT @@collation_database; +SET @@collation_database = latin7_general_cs; +SELECT @@collation_database; + +SET @@global.collation_database = Latin7_GeneRal_cS; +SELECT @@global.collation_database; + +--echo '#--------------------FN_DYNVARS_016_09-------------------------#' +############################################################## +# Check if 1,2,3, ... values can be used on variable # +############################################################## +SET @@collation_database = 1; +SELECT @@collation_database; +SET @@collation_database = 2; +SELECT @@collation_database; +SET @@collation_database = 3; +SELECT @@collation_database; +SET @@collation_database = 99; +SELECT @@collation_database; +--Error ER_UNKNOWN_COLLATION +SET @@collation_database = 100; + +SET @@global.collation_database = 1; +SELECT @@global.collation_database; +SET @@global.collation_database = 2; +SELECT @@global.collation_database; +SET @@global.collation_database = 3; +SELECT @@global.collation_database; +SET @@global.collation_database = 99; +SELECT @@global.collation_database; +--Error ER_UNKNOWN_COLLATION +SET @@global.collation_database = 100; + +SET @total_collations = (SELECT count(*) FROM INFORMATION_SCHEMA.COLLATIONS); +SELECT @total_collations > 120; + + +--echo '#--------------------FN_DYNVARS_016_10-------------------------#' +############################################################################### +# Change the value of collation_database to an invalid value for session # +############################################################################### +--Error ER_UNKNOWN_COLLATION +SET @@collation_database = latin7_binary; +--Error ER_UNKNOWN_COLLATION +SET @@collation_database = 'eucjpms_japanese_cs'; +--Error ER_UNKNOWN_COLLATION +SET @@collation_database = 0; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@collation_database = 1.01; +--Error ER_UNKNOWN_COLLATION +SET @@collation_database = -1; +--Error ER_UNKNOWN_COLLATION +SET @@collation_database = ''; +--Error ER_UNKNOWN_COLLATION +SET @@collation_database = ' eucjpms_bin'; +SET @@collation_database = true; +SELECT @@collation_database AS res_with_true; + +--Error ER_UNKNOWN_COLLATION +SET @@collation_database = ON; + +--echo '#--------------------FN_DYNVARS_016_11-------------------------#' +############################################################################### +# Change the value of collation_database to an invalid value for global # +############################################################################### +--Error ER_UNKNOWN_COLLATION +SET @@global.collation_database = latin7_binary; +--Error ER_UNKNOWN_COLLATION +SET @@global.collation_database = 'eucjpms_japanese_cs'; +--Error ER_UNKNOWN_COLLATION +SET @@global.collation_database = 0; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.collation_database = 1.1; +--Error ER_UNKNOWN_COLLATION +SET @@global.collation_database = -1; +--Error ER_UNKNOWN_COLLATION +SET @@global.collation_database = ""; +--Error ER_UNKNOWN_COLLATION +SET @@global.collation_database = ' eucjpms_bin'; + +SET @@global.collation_database = true; + +--Error ER_UNKNOWN_COLLATION +SET @@global.collation_database = ON; + +--echo '#--------------------FN_DYNVARS_016_12-------------------------#' +############################################################################### +# Check if the value in GLOBAL Table matches value in variable # +############################################################################### +SELECT @@global.collation_database = + (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES + WHERE VARIABLE_NAME='collation_database') AS res; +SET @@global.collation_database = 1; +SELECT @@global.collation_database; +SELECT @@global.collation_database = + (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES + WHERE VARIABLE_NAME='collation_database') AS res; + +--echo '#--------------------FN_DYNVARS_016_13-------------------------#' +############################################################################## +# Check if the value in SESSION Table matches value in variable # +############################################################################## +SELECT @@collation_database = + (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES + WHERE VARIABLE_NAME='collation_database') AS res; +SELECT @@local.collation_database = + (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES + WHERE VARIABLE_NAME='collation_database') AS res; +SELECT @@session.collation_database = + (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES + WHERE VARIABLE_NAME='collation_database') AS res; + +#################################### +# Restore initial value # +#################################### +SET @@global.collation_database = @global_start_value; +SELECT @@global.collation_database; +SET @@session.collation_database = @session_start_value; +SELECT @@session.collation_database; + +########################################################### +# END OF collation_database TESTS # +########################################################### |