diff options
Diffstat (limited to 'mysql-test/suite/sys_vars/t/sql_mode_basic.test')
| -rw-r--r-- | mysql-test/suite/sys_vars/t/sql_mode_basic.test | 413 |
1 files changed, 413 insertions, 0 deletions
diff --git a/mysql-test/suite/sys_vars/t/sql_mode_basic.test b/mysql-test/suite/sys_vars/t/sql_mode_basic.test new file mode 100644 index 00000000000..24f1e8aefcb --- /dev/null +++ b/mysql-test/suite/sys_vars/t/sql_mode_basic.test @@ -0,0 +1,413 @@ +#################### 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 'Bug# 34876: Default values for both session and global sql_mode is ""'; +--echo 'and not OFF.'; + +--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 + +--echo 'Bug: Incomplete sql modes valid values at:'; +--echo 'http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.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; +--echo 'Bug: OFF is documented as an sql mode but infact it is not'; + +#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; + +SET @@global.sql_mode = '?'; +SELECT @@global.sql_mode; + +SET @@session.sql_mode = '?'; +SELECT @@session.sql_mode; + +--echo 'Bug# 34834: ? is acceptable as a valid 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 + +SET @@global.sql_mode = 0.4; +SELECT @@global.sql_mode; + +SET @@global.sql_mode = 1.0; +SELECT @@global.sql_mode; + +SET @@global.sql_mode = 40000.1; +SELECT @@global.sql_mode; + +SET @@global.sql_mode = 1.5; +SELECT @@global.sql_mode; + +SET @@global.sql_mode = 124567.49; +SELECT @@global.sql_mode; + +SET @@session.sql_mode = 50000000.5; +SELECT @@session.sql_mode; + +SET @@session.sql_mode = 4294967295.4; +SELECT @@session.sql_mode; + +--echo 'Bug: Decimal values can be used within the range [0.0-4294967295.5).'; +--echo 'Values are rounded to numeric values as evident from outcome.'; + + +--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,HIGH_OR_PRECEDENCE, +IGNORE_SPACE'; + +############################## +# 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 # +################################################################ + |