mirror of
https://github.com/MariaDB/server.git
synced 2025-07-10 21:38:12 +02:00
2fdb556e04
This patch changes the main name of 3 byte character set from utf8 to utf8mb3. New old_mode UTF8_IS_UTF8MB3 is added and set TRUE by default, so that utf8 would mean utf8mb3. If not set, utf8 would mean utf8mb4.
319 lines
12 KiB
Text
319 lines
12 KiB
Text
#################### mysql-test\t\sql_mode_basic.test #########################
|
|
# #
|
|
# Variable Name: old_mode #
|
|
# Scope: GLOBAL | SESSION #
|
|
# Access Type: Dynamic #
|
|
# Data Type: enumeration #
|
|
# Default Value: YES #
|
|
# Valid Values : NO_DUP_KEY_WARNINGS_WITH_IGNORE, NO_PROGRESS_INFO #
|
|
# #
|
|
# #
|
|
# Description: Test Cases of Dynamic System Variable old_mode #
|
|
# that checks the behavior of this variable in the following ways#
|
|
# * Default Value #
|
|
# * Valid & Invalid values #
|
|
# * Scope & Access method #
|
|
# * Data Integrity #
|
|
# #
|
|
# #
|
|
###############################################################################
|
|
|
|
--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.old_mode;
|
|
SELECT @global_start_value;
|
|
|
|
SET @session_start_value = @@session.old_mode;
|
|
SELECT @session_start_value;
|
|
|
|
--echo '#--------------------FN_DYNVARS_152_01------------------------#'
|
|
################################################################
|
|
# Display the DEFAULT value of old_mode #
|
|
################################################################
|
|
|
|
SET @@global.old_mode = "NO_PROGRESS_INFO";
|
|
SET @@global.old_mode = DEFAULT;
|
|
SELECT @@global.old_mode;
|
|
|
|
SET @@session.old_mode = "NO_PROGRESS_INFO";
|
|
SET @@session.old_mode = DEFAULT;
|
|
SELECT @@session.old_mode;
|
|
|
|
--echo '#---------------------FN_DYNVARS_152_02-------------------------#'
|
|
#########################################################
|
|
# Check if NULL or empty value is accepeted #
|
|
#########################################################
|
|
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = NULL;
|
|
|
|
# resets sql mode to nothing
|
|
SET @@global.old_mode = '';
|
|
SELECT @@global.old_mode;
|
|
|
|
SET @@global.old_mode = ' ';
|
|
SELECT @@global.old_mode;
|
|
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@session.old_mode = NULL;
|
|
|
|
SET @@session.old_mode = '';
|
|
SELECT @@session.old_mode;
|
|
|
|
SET @@session.old_mode = ' ';
|
|
SELECT @@session.old_mode;
|
|
|
|
|
|
--echo '#--------------------FN_DYNVARS_152_03------------------------#'
|
|
########################################################################
|
|
# Change the value of old_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.old_mode = NO_PROGRESS_INFO;
|
|
SELECT @@global.old_mode;
|
|
SET @@global.old_mode = NO_DUP_KEY_WARNINGS_WITH_IGNORE;
|
|
SELECT @@global.old_mode;
|
|
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = OFF;
|
|
|
|
#check valid values for session
|
|
SET @@session.old_mode = NO_PROGRESS_INFO;
|
|
SELECT @@session.old_mode;
|
|
SET @@session.old_mode = NO_DUP_KEY_WARNINGS_WITH_IGNORE;
|
|
SELECT @@session.old_mode;
|
|
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@session.old_mode = OFF;
|
|
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = '?';
|
|
SELECT @@global.old_mode;
|
|
|
|
--echo '#--------------------FN_DYNVARS_152_04-------------------------#'
|
|
###########################################################################
|
|
# Change the value of old_mode to invalid value #
|
|
###########################################################################
|
|
|
|
# invalid values for global
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = -1;
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = ASCII;
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = NON_TRADITIONAL;
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = 'OF';
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = NONE;
|
|
|
|
#invalid values for session
|
|
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@session.old_mode = -1;
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@session.old_mode = ANSI_SINGLE_QUOTES;
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@session.old_mode = 'ON';
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@session.old_mode = 'OF';
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@session.old_mode = DISABLE;
|
|
|
|
|
|
--echo '#-------------------FN_DYNVARS_152_05----------------------------#'
|
|
#########################################################################
|
|
# Check if the value in session Table matches value in variable #
|
|
#########################################################################
|
|
|
|
SELECT @@session.old_mode = VARIABLE_VALUE
|
|
FROM INFORMATION_SCHEMA.SESSION_VARIABLES
|
|
WHERE VARIABLE_NAME='old_mode';
|
|
|
|
--echo '#----------------------FN_DYNVARS_152_06------------------------#'
|
|
#########################################################################
|
|
# Check if the value in GLOBAL Table matches value in variable #
|
|
#########################################################################
|
|
|
|
SELECT @@global.old_mode = VARIABLE_VALUE
|
|
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
|
|
WHERE VARIABLE_NAME='old_mode';
|
|
|
|
|
|
--echo '#---------------------FN_DYNVARS_152_07-------------------------#'
|
|
###################################################################
|
|
# Check if numbers can be used on variable #
|
|
###################################################################
|
|
|
|
# test if variable accepts 0,1,2
|
|
SET @@global.old_mode = 0;
|
|
SELECT @@global.old_mode;
|
|
|
|
SET @@global.old_mode = 1;
|
|
SELECT @@global.old_mode;
|
|
|
|
SET @@global.old_mode = 2;
|
|
SELECT @@global.old_mode;
|
|
|
|
SET @@global.old_mode = 4;
|
|
SELECT @@global.old_mode;
|
|
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@global.old_mode = 16;
|
|
SELECT @@global.old_mode;
|
|
|
|
# use of decimal values
|
|
|
|
--Error ER_WRONG_TYPE_FOR_VAR
|
|
SET @@global.old_mode = 0.4;
|
|
|
|
--echo '#---------------------FN_DYNVARS_152_08----------------------#'
|
|
###################################################################
|
|
# Check if TRUE and FALSE values can be used on variable #
|
|
###################################################################
|
|
|
|
SET @@global.old_mode = TRUE;
|
|
SELECT @@global.old_mode;
|
|
SET @@global.old_mode = FALSE;
|
|
SELECT @@global.old_mode;
|
|
|
|
--echo '#---------------------FN_DYNVARS_152_09----------------------#'
|
|
#########################################################################
|
|
# Check if old_mode can be accessed with and without @@ sign #
|
|
#########################################################################
|
|
|
|
SET old_mode = 'NO_PROGRESS_INFO';
|
|
|
|
SET session old_mode = 1;
|
|
SELECT @@old_mode;
|
|
|
|
SET global old_mode = 0;
|
|
SELECT @@global.old_mode;
|
|
|
|
--echo '#---------------------FN_DYNVARS_152_10----------------------#'
|
|
#######################################################################
|
|
# Check if old_mode values can be combined as specified #
|
|
#######################################################################
|
|
|
|
SET @@session.old_mode = 'NO_PROGRESS_INFO,NO_DUP_KEY_WARNINGS_WITH_IGNORE';
|
|
SELECT @@session.old_mode;
|
|
|
|
SET @@global.old_mode = 'NO_DUP_KEY_WARNINGS_WITH_IGNORE,NO_PROGRESS_INFO';
|
|
SELECT @@global.old_mode;
|
|
|
|
#try combining invalid mode with correct mode
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@session.old_mode = 'NO_PROGRESS_INFO,NO_SUCH_MODE';
|
|
|
|
#zero-length values are ok
|
|
SET @@old_mode=',';
|
|
SELECT @@old_mode;
|
|
SET @@old_mode=',,,,NO_PROGRESS_INFO,,,';
|
|
SELECT @@old_mode;
|
|
--Error ER_WRONG_VALUE_FOR_VAR
|
|
SET @@old_mode=',,,,FOOBAR,,,,,';
|
|
SELECT @@old_mode;
|
|
|
|
##############################
|
|
# Restore initial value #
|
|
##############################
|
|
|
|
SET @@global.old_mode = @global_start_value;
|
|
SELECT @@global.old_mode;
|
|
|
|
SET @@session.old_mode = @session_start_value;
|
|
SELECT @@session.old_mode;
|
|
|
|
--echo #
|
|
--echo # Beginning of 10.6 test
|
|
--echo #
|
|
--echo # MDEV-8334: Rename utf8 to utf8mb3
|
|
--echo #
|
|
--echo # Save and display old values
|
|
SET @save_old_mode = @@OLD_MODE;
|
|
SET @save_character_set_server = @@character_set_server;
|
|
SET @save_character_set_client = @@character_set_client;
|
|
SET @save_character_set_results = @@character_set_results;
|
|
SET @save_character_set_connection = @@character_set_connection;
|
|
SET @save_character_set_filesystem = @@character_set_filesystem;
|
|
SET @save_character_set_database = @@character_set_database;
|
|
SET @save_collation_connection = @@collation_connection;
|
|
SET @save_collation_server = @@collation_server;
|
|
SET @save_collation_database = @@collation_database;
|
|
SELECT @@OLD_MODE;
|
|
SELECT @@character_set_server,@@character_set_client,@@character_set_results,
|
|
@@character_set_connection, @@character_set_filesystem, @@character_set_database,
|
|
@@collation_connection, @@collation_server, @@collation_database;
|
|
--echo #
|
|
--echo # UTF8MB3 alias for UTF8
|
|
--echo #
|
|
SET @@character_set_server = utf8;
|
|
SET @@character_set_client = utf8;
|
|
SET @@character_set_results = utf8;
|
|
SET @@character_set_connection = utf8;
|
|
SET @@character_set_filesystem = utf8;
|
|
SET @@character_set_database = utf8;
|
|
SET @@collation_connection = utf8_general_ci;
|
|
SET @@collation_server = utf8_unicode_ci;
|
|
SET @@collation_database = utf8_bin;
|
|
SELECT @@character_set_server, @@character_set_client, @@character_set_results,
|
|
@@character_set_connection, @@character_set_filesystem, @@character_set_database,
|
|
@@collation_connection, @@collation_server, @@collation_database;
|
|
CREATE DATABASE db1 CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
|
|
ALTER DATABASE db1 CHARACTER SET = 'utf8' COLLATE = 'utf8_unicode_ci';
|
|
CREATE TABLE tb1 (id1 INT) CHARACTER SET 'utf8' COLLATE 'utf8_bin';
|
|
SHOW CREATE TABLE tb1;
|
|
DROP TABLE tb1;
|
|
DROP DATABASE db1;
|
|
--echo #
|
|
--echo # UTF8MB4 is alias for UTF8
|
|
--echo #
|
|
SET @@OLD_MODE=0;
|
|
SET @@character_set_server = utf8;
|
|
SET @@character_set_client = utf8;
|
|
SET @@character_set_results = utf8;
|
|
SET @@character_set_connection = utf8;
|
|
SET @@character_set_filesystem = utf8;
|
|
SET @@character_set_database = utf8;
|
|
SET @@collation_connection = utf8_general_ci;
|
|
SET @@collation_server = utf8_unicode_ci;
|
|
SET @@collation_database = utf8_bin;
|
|
SELECT @@character_set_server, @@character_set_client, @@character_set_results,
|
|
@@character_set_connection, @@character_set_filesystem, @@character_set_database,
|
|
@@collation_connection, @@collation_server, @@collation_database;
|
|
CREATE DATABASE db1 CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
|
|
ALTER DATABASE db1 CHARACTER SET = 'utf8' COLLATE = 'utf8_unicode_ci';
|
|
CREATE TABLE tb1 (id1 INT) CHARACTER SET 'utf8' COLLATE 'utf8_bin';
|
|
SHOW CREATE TABLE tb1;
|
|
DROP TABLE tb1;
|
|
DROP DATABASE db1;
|
|
SET @@OLD_MODE = @save_old_mode;
|
|
SET @@character_set_server = @save_character_set_server;
|
|
SET @@character_set_client = @save_character_set_client;
|
|
SET @@character_set_results = @save_character_set_results;
|
|
SET @@character_set_connection = @save_character_set_connection;
|
|
SET @@character_set_filesystem = @save_character_set_filesystem;
|
|
SET @@character_set_database = @save_character_set_database;
|
|
SET @@collation_connection = @save_collation_connection;
|
|
SET @@collation_server = @save_collation_server;
|
|
SET @@collation_database = @save_collation_database;
|
|
--echo #
|
|
--echo # End of 10.6 test
|
|
--echo #
|
|
|
|
################################################################
|
|
# END OF old_mode TESTS #
|
|
################################################################
|