Edit File: sql_mode_basic.test
#################### mysql-test\t\sql_mode_basic.test ######################### # # # Variable Name: sql_mode # # Scope: GLOBAL | SESSION # # Access Type: Dynamic # # Data Type: enumeration # # Default Value: YES # # Valid Values : ANSI, STRICT_TRANS_TABLES, TRADITIONAL, # # ERROR_FOR_DIVISION_BY_ZERO, ANSI_QUOTES, HIGH_NOT_PRECEDENCE,# # IGNORE_SPACE, NO_AUTO_CREATE_USER, ONLY_FULL_GROUP_BY, # # NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES, # # NO_UNSIGNED_SUBTRACTION, OFF # # # # # # Creation Date: 2008-02-07 # # Author: Rizwan # # # # Description: Test Cases of Dynamic System Variable sql_mode # # 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 sql_mode TESTS # ################################################################ ################################################################### # Saving initial value of sql_mode in a temporary variable # ################################################################### SET @global_start_value = @@global.sql_mode; SELECT @global_start_value; SET @session_start_value = @@session.sql_mode; SELECT @session_start_value; --echo '#--------------------FN_DYNVARS_152_01------------------------#' ################################################################ # Display the DEFAULT value of sql_mode # ################################################################ SET @@global.sql_mode = ANSI; SET @@global.sql_mode = DEFAULT; SELECT @@global.sql_mode; SET @@session.sql_mode = ANSI; SET @@session.sql_mode = DEFAULT; SELECT @@session.sql_mode; --echo '#---------------------FN_DYNVARS_152_02-------------------------#' ######################################################### # Check if NULL or empty value is accepeted # ######################################################### --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = NULL; # resets sql mode to nothing SET @@global.sql_mode = ''; SELECT @@global.sql_mode; SET @@global.sql_mode = ' '; SELECT @@global.sql_mode; --Error ER_WRONG_VALUE_FOR_VAR SET @@session.sql_mode = NULL; SET @@session.sql_mode = ''; SELECT @@session.sql_mode; SET @@session.sql_mode = ' '; SELECT @@session.sql_mode; --echo '#--------------------FN_DYNVARS_152_03------------------------#' ######################################################################## # Change the value of sql_mode to a valid value # ######################################################################## # sql modes ref: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html # check valid values for global SET @@global.sql_mode = ANSI; SELECT @@global.sql_mode; SET @@global.sql_mode = STRICT_TRANS_TABLES; SELECT @@global.sql_mode; SET @@global.sql_mode = TRADITIONAL; SELECT @@global.sql_mode; SET @@global.sql_mode = ALLOW_INVALID_DATES; SELECT @@global.sql_mode; SET @@global.sql_mode = ANSI_QUOTES; SELECT @@global.sql_mode; SET @@global.sql_mode = ERROR_FOR_DIVISION_BY_ZERO; SELECT @@global.sql_mode; SET @@global.sql_mode = HIGH_NOT_PRECEDENCE; SELECT @@global.sql_mode; SET @@global.sql_mode = IGNORE_SPACE; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_AUTO_CREATE_USER; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_AUTO_VALUE_ON_ZERO; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_BACKSLASH_ESCAPES; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_DIR_IN_CREATE; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_ENGINE_SUBSTITUTION; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_FIELD_OPTIONS; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_KEY_OPTIONS; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_TABLE_OPTIONS; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_UNSIGNED_SUBTRACTION; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_ZERO_DATE; SELECT @@global.sql_mode; SET @@global.sql_mode = NO_ZERO_IN_DATE; SELECT @@global.sql_mode; SET @@global.sql_mode = ONLY_FULL_GROUP_BY; SELECT @@global.sql_mode; SET @@global.sql_mode = PIPES_AS_CONCAT; SELECT @@global.sql_mode; SET @@global.sql_mode = REAL_AS_FLOAT; SELECT @@global.sql_mode; SET @@global.sql_mode = STRICT_ALL_TABLES; SELECT @@global.sql_mode; SET @@global.sql_mode = STRICT_TRANS_TABLES; SELECT @@global.sql_mode; SET @@global.sql_mode = DB2; SELECT @@global.sql_mode; SET @@global.sql_mode = MAXDB; SELECT @@global.sql_mode; SET @@global.sql_mode = MSSQL; SELECT @@global.sql_mode; SET @@global.sql_mode = MYSQL323; SELECT @@global.sql_mode; SET @@global.sql_mode = MYSQL40; SELECT @@global.sql_mode; SET @@global.sql_mode = ORACLE; SELECT @@global.sql_mode; SET @@global.sql_mode = POSTGRESQL; SELECT @@global.sql_mode; SET @@global.sql_mode = TRADITIONAL; SELECT @@global.sql_mode; --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = OFF; #check valid values for session SET @@session.sql_mode = ANSI; SELECT @@session.sql_mode; SET @@session.sql_mode = STRICT_TRANS_TABLES; SELECT @@session.sql_mode; SET @@session.sql_mode = TRADITIONAL; SELECT @@session.sql_mode; SET @@session.sql_mode = ALLOW_INVALID_DATES; SELECT @@session.sql_mode; SET @@session.sql_mode = ANSI_QUOTES; SELECT @@session.sql_mode; SET @@session.sql_mode = ERROR_FOR_DIVISION_BY_ZERO; SELECT @@session.sql_mode; SET @@session.sql_mode = HIGH_NOT_PRECEDENCE; SELECT @@session.sql_mode; SET @@session.sql_mode = IGNORE_SPACE; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_AUTO_CREATE_USER; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_BACKSLASH_ESCAPES; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_DIR_IN_CREATE; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_ENGINE_SUBSTITUTION; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_FIELD_OPTIONS; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_KEY_OPTIONS; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_TABLE_OPTIONS; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_UNSIGNED_SUBTRACTION; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_ZERO_DATE; SELECT @@session.sql_mode; SET @@session.sql_mode = NO_ZERO_IN_DATE; SELECT @@session.sql_mode; SET @@session.sql_mode = ONLY_FULL_GROUP_BY; SELECT @@session.sql_mode; SET @@session.sql_mode = PIPES_AS_CONCAT; SELECT @@session.sql_mode; SET @@session.sql_mode = REAL_AS_FLOAT; SELECT @@session.sql_mode; SET @@session.sql_mode = STRICT_ALL_TABLES; SELECT @@session.sql_mode; SET @@session.sql_mode = STRICT_TRANS_TABLES; SELECT @@session.sql_mode; SET @@session.sql_mode = DB2; SELECT @@session.sql_mode; SET @@session.sql_mode = MAXDB; SELECT @@session.sql_mode; SET @@session.sql_mode = MSSQL; SELECT @@session.sql_mode; SET @@session.sql_mode = MYSQL323; SELECT @@session.sql_mode; SET @@session.sql_mode = MYSQL40; SELECT @@session.sql_mode; SET @@session.sql_mode = ORACLE; SELECT @@session.sql_mode; SET @@session.sql_mode = POSTGRESQL; SELECT @@session.sql_mode; SET @@session.sql_mode = TRADITIONAL; SELECT @@session.sql_mode; --Error ER_WRONG_VALUE_FOR_VAR SET @@session.sql_mode = OFF; --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = '?'; SELECT @@global.sql_mode; --echo '#--------------------FN_DYNVARS_152_04-------------------------#' ########################################################################### # Change the value of sql_mode to invalid value # ########################################################################### # invalid values for global --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = -1; --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = ASCII; --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = NON_TRADITIONAL; --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = 'OF'; --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = NONE; --Error ER_WRONG_VALUE_FOR_VAR #invalid values for session --Error ER_WRONG_VALUE_FOR_VAR SET @@session.sql_mode = -1; --Error ER_WRONG_VALUE_FOR_VAR SET @@session.sql_mode = ANSI_SINGLE_QUOTES; --Error ER_WRONG_VALUE_FOR_VAR SET @@session.sql_mode = 'ON'; --Error ER_WRONG_VALUE_FOR_VAR SET @@session.sql_mode = 'OF'; --Error ER_WRONG_VALUE_FOR_VAR SET @@session.sql_mode = DISABLE; --echo '#-------------------FN_DYNVARS_152_05----------------------------#' ######################################################################### # Check if the value in session Table matches value in variable # ######################################################################### SELECT @@session.sql_mode = VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='sql_mode'; --echo '#----------------------FN_DYNVARS_152_06------------------------#' ######################################################################### # Check if the value in GLOBAL Table matches value in variable # ######################################################################### SELECT @@global.sql_mode = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='sql_mode'; --echo '#---------------------FN_DYNVARS_152_07-------------------------#' ################################################################### # Check if numbers can be used on variable # ################################################################### # test if variable accepts 0,1,2 SET @@global.sql_mode = 0; SELECT @@global.sql_mode; SET @@global.sql_mode = 1; SELECT @@global.sql_mode; SET @@global.sql_mode = 2; SELECT @@global.sql_mode; SET @@global.sql_mode = 3; SELECT @@global.sql_mode; SET @@global.sql_mode = 50000; SELECT @@global.sql_mode; SET @@global.sql_mode = 500000; SELECT @@global.sql_mode; SET @@global.sql_mode = 4294967295; SELECT @@global.sql_mode; --Error ER_WRONG_VALUE_FOR_VAR SET @@global.sql_mode = 4294967296; # use of decimal values --Error ER_WRONG_TYPE_FOR_VAR SET @@global.sql_mode = 0.4; --echo '#---------------------FN_DYNVARS_152_08----------------------#' ################################################################### # Check if TRUE and FALSE values can be used on variable # ################################################################### SET @@global.sql_mode = TRUE; SELECT @@global.sql_mode; SET @@global.sql_mode = FALSE; SELECT @@global.sql_mode; --echo '#---------------------FN_DYNVARS_152_09----------------------#' ######################################################################### # Check if sql_mode can be accessed with and without @@ sign # ######################################################################### SET sql_mode = 'ANSI'; --Error ER_PARSE_ERROR SET session.sql_mode = 'ANSI'; --Error ER_PARSE_ERROR SET global.sql_mode = 'ANSI'; SET session sql_mode = 1; SELECT @@sql_mode; SET global sql_mode = 0; SELECT @@global.sql_mode; --echo '#---------------------FN_DYNVARS_152_10----------------------#' ####################################################################### # Check if sql_mode values can be combined as specified # ####################################################################### SET @@session.sql_mode = 'TRADITIONAL,ALLOW_INVALID_DATES,ANSI_QUOTES'; SELECT @@session.sql_mode; SET @@global.sql_mode = 'ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT,REAL_AS_FLOAT,ORACLE,POSTGRESQL'; SELECT @@global.sql_mode; #try combining invalid mode with correct mode --Error ER_WRONG_VALUE_FOR_VAR SET @@session.sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,FOOBAR,IGNORE_SPACE'; #zero-length values are ok SET @@sql_mode=','; SELECT @@sql_mode; SET @@sql_mode=',,,,ANSI_QUOTES,,,'; SELECT @@sql_mode; --Error ER_WRONG_VALUE_FOR_VAR SET @@sql_mode=',,,,FOOBAR,,,,,'; SELECT @@sql_mode; ############################## # Restore initial value # ############################## SET @@global.sql_mode = @global_start_value; SELECT @@global.sql_mode; SET @@session.sql_mode = @session_start_value; SELECT @@session.sql_mode; ################################################################ # END OF sql_mode TESTS # ################################################################
Back to File Manager