Edit File: max_sort_length_func.test
############# mysql-test\t\max_sort_length_func.test ########################### # # # Variable Name: sql_warnings # # Scope: SESSION # # Access Type: Dynamic # # Data Type: NUMERIC # # Default Value: 1024 # # Values: 4-8388608 # # # # # # Creation Date: 2008-03-02 # # Author: Sharique Abdullah # # # # Description: Test Cases of Dynamic System Variable max_sort_length # # that checks behavior of this variable in the following ways # # * Functionality based on different values # # # # Reference: # # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ################################################################################ # # Setup # SET @start_value= @@global.max_sort_length; SET @session_max_sort_length = @@Session.max_sort_length; --disable_warnings DROP TABLE IF EXISTS t; --enable_warnings ######################### # Creating new table # ######################### --echo ** creating tables ** CREATE TABLE t ( id INT AUTO_INCREMENT PRIMARY KEY, c TEXT(30) ); CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, c BLOB(30) ); CREATE TABLE t2 ( id INT AUTO_INCREMENT PRIMARY KEY, c TEXT(30) ); --echo '#--------------------FN_DYNVARS_098_01-------------------------#' ########################################################## # Test behavior of variable on new connection # 01 # ########################################################## --echo ** Connecting test_con1 using username 'root' ** connect (test_con1,localhost,root,,); --echo ** Connection test_con1 ** connection test_con1; # Value of session & global vairable here should be 10 SELECT @@global.max_sort_length = 10; SELECT @@session.max_sort_length = 10; # Setting global value of variable and inserting data in table --echo ** Setting value to 30 and inserting data ** SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; INSERT INTO t set c = repeat('x',29); INSERT INTO t set c = concat(repeat('x',28),'r','x'); INSERT INTO t set c = concat(repeat('x',28),'s','y'); INSERT INTO t set c = concat(repeat('x',28),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** # Setting session value of variable and inserting data in table SET @@session.max_sort_length = 29; SELECT @@session.max_sort_length; INSERT INTO t set c = repeat('x',29); INSERT INTO t set c = concat(repeat('x',28),'r','x'); INSERT INTO t set c = concat(repeat('x',28),'s','y'); INSERT INTO t set c = concat(repeat('x',28),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** --echo '#--------------------FN_DYNVARS_098_02-------------------------#' ########################################################## # Test behavior of variable on new connection # 02 # ########################################################## --echo ** Connecting test_con2 using username 'root' ** connect (test_con2,localhost,root,,); --echo ** Connection test_con2 ** connection test_con2; ## Setting global value of variable and inserting data in table SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; INSERT INTO t set c = repeat('x',29); INSERT INTO t set c = concat(repeat('x',28),'r','x'); INSERT INTO t set c = concat(repeat('x',28),'s','y'); INSERT INTO t set c = concat(repeat('x',28),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** # Setting session value of variable and inserting data in table SET @@session.max_sort_length = 20; SELECT @@session.max_sort_length; INSERT INTO t set c = repeat('x',29); INSERT INTO t set c = concat(repeat('x',28),'r','x'); INSERT INTO t set c = concat(repeat('x',28),'s','y'); INSERT INTO t set c = concat(repeat('x',28),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should not be sorted ** --echo '#--------------------FN_DYNVARS_098_03-------------------------#' ######################################################### #Check if sorting is applied with the max_sort_length=20# ######################################################### ########################################### # Setting new value for max_sort_length # ########################################### SET max_sort_length=20; ################################### # Inserting values in table t # ################################### INSERT INTO t set c = repeat('x',29); INSERT INTO t set c = concat(repeat('x',28),'r','x'); INSERT INTO t set c = concat(repeat('x',28),'s','y'); INSERT INTO t set c = concat(repeat('x',28),'g','w'); SELECT c from t ORDER BY c, id; --echo ** Results should not be sorted ** RESET QUERY CACHE; --echo '#--------------------FN_DYNVARS_098_04-------------------------#' ######################################################### #Check if sorting is applied with the max_sort_length=29# ######################################################### SET max_sort_length=29; SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** --echo '#--------------------FN_DYNVARS_098_05-------------------------#' ######################################################### #Check if sorting is applied with the max_sort_length=30# ######################################################### SET max_sort_length=30; SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** --echo '#--------------------FN_DYNVARS_098_06-------------------------#' ############################################################## #Check if sorting is applied with the max_sort_length=Default# ############################################################## SET max_sort_length=default; SELECT c from t ORDER BY c, id; --echo ** Results should be sorted ** --echo '#--------------------FN_DYNVARS_098_07-------------------------#' ########################################### #Check if sorting is applied on BLOB type # ########################################### --echo Testing type BLOB # Setting global value of variable and inserting data in table SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; INSERT INTO t1 set c = repeat('x',29); INSERT INTO t1 set c = concat(repeat('x',28),'r','x'); INSERT INTO t1 set c = concat(repeat('x',28),'s','y'); INSERT INTO t1 set c = concat(repeat('x',28),'g','w'); SELECT c from t1 ORDER BY c, id; --echo ** Results should be sorted ** # Setting session value of variable and inserting data in table SET @@session.max_sort_length = 20; SELECT @@session.max_sort_length; INSERT INTO t1 set c = repeat('x',29); INSERT INTO t1 set c = concat(repeat('x',28),'r','x'); INSERT INTO t1 set c = concat(repeat('x',28),'s','y'); INSERT INTO t1 set c = concat(repeat('x',28),'g','w'); SELECT c from t1 ORDER BY c, id; --echo ** Results should not be sorted ** --echo '#--------------------FN_DYNVARS_098_08-------------------------#' ########################################### #Check if sorting is applied on CHAR type # ########################################### --echo Testing type CHAR # Setting global value of variable and inserting data in table SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; INSERT INTO t2 set c = repeat('x',29); INSERT INTO t2 set c = concat(repeat('x',28),'r','x'); INSERT INTO t2 set c = concat(repeat('x',28),'s','y'); INSERT INTO t2 set c = concat(repeat('x',28),'g','w'); SELECT c from t2 ORDER BY c, id; --echo ** Results should not be sorted ** # Setting session value of variable and inserting data in table SET @@session.max_sort_length = 20; SELECT @@session.max_sort_length; INSERT INTO t2 set c = repeat('x',29); INSERT INTO t2 set c = concat(repeat('x',28),'r','x'); INSERT INTO t2 set c = concat(repeat('x',28),'s','y'); INSERT INTO t2 set c = concat(repeat('x',28),'g','w'); SELECT c from t2 ORDER BY c, id; --echo ** Results should not be sorted ** # # Cleanup # --echo ** Connection default ** connection default; --echo ** Disconnecting test_con1, test_con2 ** disconnect test_con1; disconnect test_con2; SET @@SESSION.max_sort_length = @session_max_sort_length; --disable_warnings DROP TABLE IF EXISTS t; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; --enable_warnings SET @@global.max_sort_length= @start_value;
Back to File Manager