summaryrefslogtreecommitdiff
path: root/mysql-test/t/sql_mode_basic.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/sql_mode_basic.test')
-rw-r--r--mysql-test/t/sql_mode_basic.test413
1 files changed, 0 insertions, 413 deletions
diff --git a/mysql-test/t/sql_mode_basic.test b/mysql-test/t/sql_mode_basic.test
deleted file mode 100644
index 24f1e8aefcb..00000000000
--- a/mysql-test/t/sql_mode_basic.test
+++ /dev/null
@@ -1,413 +0,0 @@
-#################### 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 #
-################################################################
-
View Lorry Controller Status