SET @start_value= @@global.max_sort_length; SET @session_max_sort_length = @@Session.max_sort_length; DROP TABLE IF EXISTS t; ** 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) ); '#--------------------FN_DYNVARS_098_01-------------------------#' ** Connecting test_con1 using username 'root' ** ** Connection test_con1 ** SELECT @@global.max_sort_length = 10; @@global.max_sort_length = 10 0 SELECT @@session.max_sort_length = 10; @@session.max_sort_length = 10 0 ** Setting value to 30 and inserting data ** SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; @@global.max_sort_length 30 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** SET @@session.max_sort_length = 29; SELECT @@session.max_sort_length; @@session.max_sort_length 29 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** '#--------------------FN_DYNVARS_098_02-------------------------#' ** Connecting test_con2 using username 'root' ** ** Connection test_con2 ** SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; @@global.max_sort_length 30 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** SET @@session.max_sort_length = 20; SELECT @@session.max_sort_length; @@session.max_sort_length 20 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** '#--------------------FN_DYNVARS_098_03-------------------------#' SET max_sort_length=20; 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** RESET QUERY CACHE; '#--------------------FN_DYNVARS_098_04-------------------------#' SET max_sort_length=29; SELECT c from t ORDER BY c, id; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** '#--------------------FN_DYNVARS_098_05-------------------------#' SET max_sort_length=30; SELECT c from t ORDER BY c, id; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** '#--------------------FN_DYNVARS_098_06-------------------------#' SET max_sort_length=default; SELECT c from t ORDER BY c, id; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** '#--------------------FN_DYNVARS_098_07-------------------------#' Testing type BLOB SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; @@global.max_sort_length 30 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxx ** Results should be sorted ** SET @@session.max_sort_length = 20; SELECT @@session.max_sort_length; @@session.max_sort_length 20 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** '#--------------------FN_DYNVARS_098_08-------------------------#' Testing type CHAR SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; @@global.max_sort_length 30 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** SET @@session.max_sort_length = 20; SELECT @@session.max_sort_length; @@session.max_sort_length 20 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; c xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw xxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw ** Results should not be sorted ** ** Connection default ** ** Disconnecting test_con1, test_con2 ** SET @@SESSION.max_sort_length = @session_max_sort_length; DROP TABLE IF EXISTS t; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; SET @@global.max_sort_length= @start_value;