diff options
Diffstat (limited to 'mysql-test/r')
119 files changed, 667 insertions, 615 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index f243d244486..b4379f49c7e 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1,5 +1,6 @@ drop table if exists t1,t2; drop database if exists mysqltest; +set @save_max_allowed_packet=@@global.max_allowed_packet; create table t1 ( col1 int not null auto_increment primary key, col2 varchar(30) not null, @@ -1463,7 +1464,7 @@ disconnect con1; connection default; DROP TABLE t1, t2, t3; SET SQL_MODE=default; -SET GLOBAL max_allowed_packet=default; +SET GLOBAL max_allowed_packet=@save_max_allowed_packet; CREATE TABLE t1 ( id INT(11) NOT NULL, x_param INT(11) DEFAULT NULL, diff --git a/mysql-test/r/analyze_stmt.result b/mysql-test/r/analyze_stmt.result index 3ae49c5eeb6..c5d35759c9c 100644 --- a/mysql-test/r/analyze_stmt.result +++ b/mysql-test/r/analyze_stmt.result @@ -1,3 +1,4 @@ +set join_cache_level=2; drop table if exists t0,t1,t2,t3; create table t0 (a int) engine=myisam; INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/r/bootstrap.result b/mysql-test/r/bootstrap.result index 5cefc281996..a7365d4f462 100644 --- a/mysql-test/r/bootstrap.result +++ b/mysql-test/r/bootstrap.result @@ -3,7 +3,8 @@ drop table t1; drop table t1; ERROR 42S02: Unknown table 'test.t1' set @my_max_allowed_packet= @@max_allowed_packet; -set global max_allowed_packet=64*@@max_allowed_packet; +set @@global.max_allowed_packet= greatest(1073741824, @@max_allowed_packet); +set @max_allowed_packed=@@global.max_allowed_packet; set global max_allowed_packet=@my_max_allowed_packet; drop table t1; End of 5.1 tests diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 13ce2e53367..43f3a2ac28c 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -716,6 +716,7 @@ DROP TABLE t1; # # Bug #11765023: 57934: DOS POSSIBLE SINCE BINARY CASTING # DOESN'T ADHERE TO MAX_ALLOWED_PACKET +set @save_max_allowed_packet=@@global.max_allowed_packet; SET @@GLOBAL.max_allowed_packet=2048; Warnings: Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length' @@ -737,7 +738,7 @@ Warnings: Warning 1301 Result of cast_as_char() was larger than max_allowed_packet (2048) - truncated connection default; disconnect newconn; -SET @@GLOBAL.max_allowed_packet=default; +SET @@GLOBAL.max_allowed_packet=@save_max_allowed_packet; # # Bug#13519724 63793: CRASH IN DTCOLLATION::SET(DTCOLLATION &SET) # diff --git a/mysql-test/r/compound.result b/mysql-test/r/compound.result index 49ad567a055..9ac1ca83e7d 100644 --- a/mysql-test/r/compound.result +++ b/mysql-test/r/compound.result @@ -153,6 +153,7 @@ master-bin.000001 # Query # # use `test`; insert t1 values( NAME_CONST('a',4)+3) master-bin.000001 # Query # # COMMIT drop function fn| drop table t1| +set @@sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"; begin not atomic select @@sql_mode; end| @@sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result index 762ab6630d8..8fbbb324b16 100644 --- a/mysql-test/r/compress.result +++ b/mysql-test/r/compress.result @@ -1354,8 +1354,10 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 -SET @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='outer_join_with_cache=off'; +SET @local_optimizer_switch=@@optimizer_switch; +set @local_join_cache_level=@@join_cache_level; +set @@join_cache_level=2; +set optimizer_switch='outer_join_with_cache=off,join_cache_hashed=off'; insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname @@ -1431,7 +1433,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@local_optimizer_switch; select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -1440,6 +1442,7 @@ explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr= id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) +SET @@join_cache_level=@local_join_cache_level; select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result index 85eb8289ebe..b1bc1be3594 100644 --- a/mysql-test/r/connect.result +++ b/mysql-test/r/connect.result @@ -254,7 +254,7 @@ mysqltest_u1 root # -- Resetting variables... -SET GLOBAL max_connections = 151; +SET GLOBAL max_connections = #max_connections#; # -- Stopping Event Scheduler... SET GLOBAL event_scheduler = OFF; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 2ff4bac546c..ab9a13143d5 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1031,6 +1031,8 @@ USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' +set @save_character_set_client=@@character_set_client; +set @save_collation_connection=@@collation_connection; set names utf8; create database имÑ_базы_в_кодировке_утф8_длиной_больше_чем_45; use имÑ_базы_в_кодировке_утф8_длиной_больше_чем_45; @@ -1119,7 +1121,9 @@ return 0; ERROR 42000: Identifier name 'очень_очень_очень_очень_очень_очень_очень_очень_длинна' is too long drop view имÑ_вью_кодировке_утф8_длиной_больше_чем_42; drop table имÑ_таблицы_в_кодировке_утф8_длиной_больше_чем_48; -set names default; +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; +set @@session.collation_connection=@save_collation_connection; drop table if exists t1,t2,t3; drop function if exists f1; create function f1() returns int diff --git a/mysql-test/r/ctype_create.result b/mysql-test/r/ctype_create.result index 4128be82c23..9a76802579e 100644 --- a/mysql-test/r/ctype_create.result +++ b/mysql-test/r/ctype_create.result @@ -83,6 +83,8 @@ USE test; # # MDEV-7387 Alter table xxx CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1 should fail # +CREATE DATABASE tmp DEFAULT CHARACTER SET latin5; +USE tmp; CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET DEFAULT, CHARACTER SET utf8; ERROR HY000: Conflicting declarations: 'CHARACTER SET DEFAULT' and 'CHARACTER SET utf8' CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8, CHARACTER SET DEFAULT; @@ -105,6 +107,8 @@ ERROR HY000: Conflicting declarations: 'CHARACTER SET latin5' and 'CHARACTER SET ALTER TABLE t1 CONVERT TO CHARACTER SET DEFAULT, CHARACTER SET latin1; ERROR HY000: Conflicting declarations: 'CHARACTER SET latin5' and 'CHARACTER SET latin1' DROP TABLE t1; +DROP DATABASE tmp; +USE test; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 2d3803fba18..4fd366b2e8e 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -1,3 +1,5 @@ +create database latin1_german2_ci default character set latin1 collate latin1_german2_ci; +use latin1_german2_ci; set names latin1; set @@collation_connection=latin1_german2_ci; select @@collation_connection; @@ -139,7 +141,7 @@ a a check table t1; Table Op Msg_type Msg_text -test.t1 check status OK +latin1_german2_ci.t1 check status OK select * from t1 where a like "ö%"; a b ö 1 @@ -835,3 +837,5 @@ hex(weight_string('xß' as char(2))) # # End of 5.6 tests # +drop database latin1_german2_ci; +use test; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 9c7fbbfdf06..602ec8a1fc7 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -205,6 +205,7 @@ DROP TABLE t1; # and reverse() function # # Problem # 1 (original report): wrong parsing of ucs2 data +SET character_set_connection=ucs2; SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp.txt'; CREATE TABLE t1(a INT); LOAD DATA INFILE 'tmpp.txt' INTO TABLE t1 CHARACTER SET ucs2 diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result index 8d01212c9b1..66175344615 100644 --- a/mysql-test/r/ctype_ujis.result +++ b/mysql-test/r/ctype_ujis.result @@ -2504,6 +2504,8 @@ Warning 1977 Cannot convert 'ujis' character 0x8FABF8 to 'ucs2' DROP TABLE IF EXISTS t1, t2; DROP PROCEDURE IF EXISTS sp1; set names ujis; +SET @save_character_set_server= @@character_set_server; +SET @save_character_set_database= @@character_set_database; set character_set_database = ujis; set character_set_server = ujis; CREATE TABLE t1(c1 char(2)) default charset = ujis; @@ -2539,8 +2541,8 @@ Warnings: Warning 1292 Truncated incorrect INTEGER value: 'a' Warning 1292 Truncated incorrect INTEGER value: 'a' set names default; -set character_set_database=default; -set character_set_server=default; +set character_set_database=@save_character_set_server; +set character_set_server=@save_character_set_database; End of 5.1 tests # # Start of 5.5 tests diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result index 3ad7baadae3..371ccc01a22 100644 --- a/mysql-test/r/ctype_utf16.result +++ b/mysql-test/r/ctype_utf16.result @@ -1567,7 +1567,7 @@ SELECT space(date_add(101, INTERVAL CHAR('1' USING utf16) hour_second)); space(date_add(101, INTERVAL CHAR('1' USING utf16) hour_second)) NULL Warnings: -Warning 1301 Result of space() was larger than max_allowed_packet (16777216) - truncated +Warning 1301 Result of space() was larger than max_allowed_packet (XXX) - truncated # # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT # diff --git a/mysql-test/r/ctype_utf8_def_upgrade.result b/mysql-test/r/ctype_utf8_def_upgrade.result index 1b921289af6..921b5200aca 100644 --- a/mysql-test/r/ctype_utf8_def_upgrade.result +++ b/mysql-test/r/ctype_utf8_def_upgrade.result @@ -7,7 +7,7 @@ # Test with a saved table from 3.23 SELECT @@character_set_database; @@character_set_database -utf8 +latin1 SET @@character_set_database="latin1"; SELECT COUNT(*) FROM t1; ERROR HY000: Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result index 911108bf74a..b0777a0b9f0 100644 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@ -1850,6 +1850,7 @@ character_set_filesystem binary character_set_results utf8mb4 character_set_server latin1 character_set_system utf8 +SET @@character_set_server=@save_character_set_server; CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; USE crashtest; CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8mb4; diff --git a/mysql-test/r/ctype_utf8mb4_heap.result b/mysql-test/r/ctype_utf8mb4_heap.result index 18213e65166..127b72674ac 100644 --- a/mysql-test/r/ctype_utf8mb4_heap.result +++ b/mysql-test/r/ctype_utf8mb4_heap.result @@ -1682,6 +1682,7 @@ character_set_filesystem binary character_set_results utf8mb4 character_set_server latin1 character_set_system utf8 +SET @@character_set_server=@save_character_set_server; CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; USE crashtest; CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8mb4 ENGINE heap; diff --git a/mysql-test/r/ctype_utf8mb4_innodb.result b/mysql-test/r/ctype_utf8mb4_innodb.result index bf7a54c5bf6..bce039d436f 100644 --- a/mysql-test/r/ctype_utf8mb4_innodb.result +++ b/mysql-test/r/ctype_utf8mb4_innodb.result @@ -1810,6 +1810,7 @@ character_set_filesystem binary character_set_results utf8mb4 character_set_server latin1 character_set_system utf8 +SET @@character_set_server=@save_character_set_server; CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; USE crashtest; CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8mb4 ENGINE InnoDB; diff --git a/mysql-test/r/ctype_utf8mb4_myisam.result b/mysql-test/r/ctype_utf8mb4_myisam.result index c4bbd4ef344..b834448b2fd 100644 --- a/mysql-test/r/ctype_utf8mb4_myisam.result +++ b/mysql-test/r/ctype_utf8mb4_myisam.result @@ -1810,6 +1810,7 @@ character_set_filesystem binary character_set_results utf8mb4 character_set_server latin1 character_set_system utf8 +SET @@character_set_server=@save_character_set_server; CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; USE crashtest; CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8mb4 ENGINE MyISAM; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 8a93d5db7e6..da580158181 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1,3 +1,4 @@ +drop table if exists t1,t2,t3; set @save_derived_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; select * from (select 2 from DUAL) b; diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index d040aaf609d..ac16dac73de 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -1,3 +1,4 @@ +set @@join_buffer_size=256*1024; create table t1 (a int, b int, c int); create table t2 (a int, b int, c int, d decimal); insert into t1 values diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 07cd69b3ff9..fe25feb5a38 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2,6 +2,7 @@ drop table if exists t1,t2; drop view if exists v1,v2,v3,v4; set @exit_optimizer_switch=@@optimizer_switch; set @exit_join_cache_level=@@join_cache_level; +set @exit_join_buffer_size=@@join_buffer_size; set optimizer_switch='derived_merge=on,derived_with_keys=on'; set @save_optimizer_switch=@@optimizer_switch; set join_cache_level=1; @@ -1995,8 +1996,8 @@ e e e e -SET SESSION join_cache_level = DEFAULT; -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_cache_level = @exit_join_cache_level; +SET SESSION join_buffer_size = @exit_join_buffer_size; DROP VIEW v3; DROP TABLE t1,t2,t3,t4,t5; # @@ -2111,7 +2112,7 @@ a b 8 x 5 r 9 y -SET SESSION join_cache_level = default; +SET SESSION join_cache_level = @exit_join_cache_level; SET optimizer_switch=@save_optimizer_switch; DROP VIEW v2; DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/drop_bad_db_type.result b/mysql-test/r/drop_bad_db_type.result index de22373e0fd..d44af1e8808 100644 --- a/mysql-test/r/drop_bad_db_type.result +++ b/mysql-test/r/drop_bad_db_type.result @@ -26,8 +26,10 @@ Level Warning Code 1286 Message Unknown storage engine 'ARCHIVE' install soname 'ha_archive'; +db.opt t1.ARZ t1.frm drop table t1; +db.opt uninstall soname 'ha_archive'; set debug_dbug='-d,unstable_db_type'; diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result index 7a5eeac67cc..9e6c5a7747a 100644 --- a/mysql-test/r/dyncol.result +++ b/mysql-test/r/dyncol.result @@ -1483,7 +1483,7 @@ hex(column_create("1212", 2, 3, 3)) select hex(column_create("1212", 2, "адын", 1, 3, 3)); hex(column_create("1212", 2, "адын", 1, 3, 3)) 0403000D000000000001001000050020003331323132D0B0D0B4D18BD0BD060402 -set names default; +set names latin1; # fetching column test (names) set names utf8; select column_get(column_create("адын", 1212), "адын" as int); @@ -1507,7 +1507,7 @@ NULL select column_get(column_create("1212", 2, "адын", 1, 3, 3), "4" as int); column_get(column_create("1212", 2, "адын", 1, 3, 3), "4" as int) NULL -set names default; +set names latin1; # column existance test (names) set names utf8; select column_exists(column_create("адын", 1212), "адын"); @@ -1534,7 +1534,7 @@ column_exists(column_create("1212", 2, "адын", 1, 3, 3), 4) select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "4"); column_exists(column_create("1212", 2, "адын", 1, 3, 3), "4") 0 -set names default; +set names latin1; # column changing test (names) select hex(column_add(column_create(1, "AAA"), "b", "BBB")); hex(column_add(column_create(1, "AAA"), "b", "BBB")) diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index 008d4db7dae..cb8850112a5 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -2,6 +2,7 @@ SET SQL_MODE=""; drop database if exists events_test; drop database if exists mysqltest_db1; drop database if exists mysqltest_db2; +set collation_server=latin1_swedish_ci; create database events_test; use events_test; set @concurrent_insert= @@global.concurrent_insert; diff --git a/mysql-test/r/events_logs_tests.result b/mysql-test/r/events_logs_tests.result index 9cc6e44511c..ac4a43118da 100644 --- a/mysql-test/r/events_logs_tests.result +++ b/mysql-test/r/events_logs_tests.result @@ -1,3 +1,4 @@ +set @save_long_query_time=@@long_query_time; drop database if exists events_test; create database if not exists events_test; use events_test; @@ -68,5 +69,5 @@ user_host db sql_text USER_HOST events_test select 'events_logs_test' as inside_event, sleep(1.5) drop database events_test; set global event_scheduler=off; -set @@global.long_query_time=default; -set @@session.long_query_time=default; +set @@global.long_query_time=@save_long_query_time; +set @@session.long_query_time=@save_long_query_time; diff --git a/mysql-test/r/flush2.result b/mysql-test/r/flush2.result index a66b0d5c688..513715b1aee 100644 --- a/mysql-test/r/flush2.result +++ b/mysql-test/r/flush2.result @@ -1,3 +1,4 @@ +set @save_expire_logs_days=@@global.expire_logs_days; flush logs; set global expire_logs_days = 3; show variables like 'log_bin%'; @@ -10,9 +11,9 @@ log_bin_index log_bin_trust_function_creators ON show variables like 'relay_log%'; Variable_name Value -relay_log mysqld-relay-bin -relay_log_basename MYSQLTEST_VARDIR/mysqld.1/data/mysqld-relay-bin -relay_log_index MYSQLTEST_VARDIR/mysqld.1/data/mysqld-relay-bin.index +relay_log XXX-relay-bin +relay_log_basename MYSQLTEST_VARDIR/mysqld.1/data/XXX-relay-bin +relay_log_index MYSQLTEST_VARDIR/mysqld.1/data/XXX-relay-bin.index relay_log_info_file relay-log.info relay_log_purge ON relay_log_recovery OFF @@ -28,11 +29,11 @@ log_bin_index log_bin_trust_function_creators ON show variables like 'relay_log%'; Variable_name Value -relay_log mysqld-relay-bin -relay_log_basename MYSQLTEST_VARDIR/mysqld.1/data/mysqld-relay-bin -relay_log_index MYSQLTEST_VARDIR/mysqld.1/data/mysqld-relay-bin.index +relay_log XXX-relay-bin +relay_log_basename MYSQLTEST_VARDIR/mysqld.1/data/XXX-relay-bin +relay_log_index MYSQLTEST_VARDIR/mysqld.1/data/XXX-relay-bin.index relay_log_info_file relay-log.info relay_log_purge ON relay_log_recovery OFF relay_log_space_limit 0 -set global expire_logs_days = 0; +set global expire_logs_days=@save_expire_logs_days; diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index 6857813559f..a3f70ed7228 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -1,3 +1,4 @@ +set @save_max_allowed_packet=@@max_allowed_packet; set global max_allowed_packet=1048576; connect conn1,localhost,root,,; connection conn1; @@ -81,7 +82,7 @@ Warning 1292 Truncated incorrect DOUBLE value: XXX Warning 1292 Truncated incorrect DOUBLE value: XXX disconnect newconn; connection default; -set @@global.max_allowed_packet=default; +set @@global.max_allowed_packet=@save_max_allowed_packet; create table t1(a blob); insert into t1 values(NULL), (compress('a')); select uncompress(a), uncompressed_length(a) from t1; @@ -163,7 +164,7 @@ Warnings: Warning 1259 ZLIB: Input data corrupted disconnect conn1; connection default; -set global max_allowed_packet=default; +set global max_allowed_packet=@save_max_allowed_packet; # # End of 5.5 tests # diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index b5f450f2baa..94adf310fec 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -619,6 +619,8 @@ JSON_search( '{"x": "\\""}', "one", '"') SELECT JSON_search( '{"x": "\\""}', "one", '\\"'); JSON_search( '{"x": "\\""}', "one", '\\"') "$.x" +set @save_max_allowed_packet=@@max_allowed_packet; +set @save_net_buffer_length=@@net_buffer_length; set @@global.net_buffer_length=1024; set @@global.max_allowed_packet=2048; connect newconn, localhost, root,,; @@ -639,8 +641,8 @@ NULL Warnings: Warning 1301 Result of json_object() was larger than max_allowed_packet (2048) - truncated connection default; -set @@global.max_allowed_packet = default; -set @@global.net_buffer_length = default; +set @@global.max_allowed_packet = @save_max_allowed_packet; +set @@global.net_buffer_length = @save_net_buffer_length; disconnect newconn; create table t1(j longtext, p longtext); insert into t1 values diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 255a218ea52..b199b43037a 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -296,7 +296,7 @@ format(t2.f2-t2.f1+1,0) 10,000 10,000 drop table t1, t2; -set names default; +set names latin1; select cast(-2 as unsigned), 18446744073709551614, -2; cast(-2 as unsigned) 18446744073709551614 -2 18446744073709551614 18446744073709551614 -2 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 86ea749fc15..0907adf460f 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1,4 +1,5 @@ drop table if exists t1,t2; +set @save_max_allowed_packet=@@global.max_allowed_packet; set global max_allowed_packet=1048576; connect conn1,localhost,root,,; connection conn1; @@ -4622,7 +4623,7 @@ COLLATION(space(2)) latin2_general_ci disconnect conn1; connection default; -set global max_allowed_packet=default; +set global max_allowed_packet=@save_max_allowed_packet; # # End of 5.6 tests # diff --git a/mysql-test/r/func_weight_string.result b/mysql-test/r/func_weight_string.result index 04fd9962218..241ce3b0bab 100644 --- a/mysql-test/r/func_weight_string.result +++ b/mysql-test/r/func_weight_string.result @@ -1,4 +1,5 @@ drop table if exists t1; +set @save_max_allowed_packet=@@max_allowed_packet; set global max_allowed_packet=1048576; connect conn1,localhost,root,,; connection conn1; @@ -95,7 +96,7 @@ Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (1048 Warning 1301 Result of weight_string() was larger than max_allowed_packet (1048576) - truncated disconnect conn1; connection default; -set global max_allowed_packet=default; +set global max_allowed_packet=@save_max_allowed_packet; # # Start of 10.1 tests # diff --git a/mysql-test/r/grant_cache_no_prot.result b/mysql-test/r/grant_cache_no_prot.result index f1adb3ae839..04eb2469732 100644 --- a/mysql-test/r/grant_cache_no_prot.result +++ b/mysql-test/r/grant_cache_no_prot.result @@ -1,3 +1,6 @@ +set @save_query_cache_size=@@global.query_cache_size; +set @save_sql_mode=@@global.sql_mode; +set @save_query_cache_type=@@global.query_cache_type; set GLOBAL sql_mode=""; set LOCAL sql_mode=""; drop table if exists test.t1,mysqltest.t1,mysqltest.t2; @@ -242,8 +245,8 @@ delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysql flush privileges; drop table test.t1,mysqltest.t1,mysqltest.t2; drop database mysqltest; -set GLOBAL query_cache_size=default; set GLOBAL query_cache_type=ON; set LOCAL query_cache_type=ON; -set GLOBAL sql_mode=default; -set GLOBAL query_cache_type=default; +set GLOBAL query_cache_size=@save_query_cache_size; +set GLOBAL sql_mode=@save_sql_mode; +set GLOBAL query_cache_type=@save_query_cache_type; diff --git a/mysql-test/r/grant_cache_ps_prot.result b/mysql-test/r/grant_cache_ps_prot.result index 56a98cc076d..3bc51430fd0 100644 --- a/mysql-test/r/grant_cache_ps_prot.result +++ b/mysql-test/r/grant_cache_ps_prot.result @@ -1,3 +1,6 @@ +set @save_query_cache_size=@@global.query_cache_size; +set @save_sql_mode=@@global.sql_mode; +set @save_query_cache_type=@@global.query_cache_type; set GLOBAL sql_mode=""; set LOCAL sql_mode=""; drop table if exists test.t1,mysqltest.t1,mysqltest.t2; @@ -242,8 +245,8 @@ delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysql flush privileges; drop table test.t1,mysqltest.t1,mysqltest.t2; drop database mysqltest; -set GLOBAL query_cache_size=default; set GLOBAL query_cache_type=ON; set LOCAL query_cache_type=ON; -set GLOBAL sql_mode=default; -set GLOBAL query_cache_type=default; +set GLOBAL query_cache_size=@save_query_cache_size; +set GLOBAL sql_mode=@save_sql_mode; +set GLOBAL query_cache_type=@save_query_cache_type; diff --git a/mysql-test/r/greedy_optimizer.result b/mysql-test/r/greedy_optimizer.result index de9db45cb15..d7c7e603c68 100644 --- a/mysql-test/r/greedy_optimizer.result +++ b/mysql-test/r/greedy_optimizer.result @@ -110,13 +110,7 @@ insert into t7 values (18,2,3,4,5,6); insert into t7 values (19,2,3,4,5,6); insert into t7 values (20,2,3,4,5,6); insert into t7 values (21,2,3,4,5,6); -select @@optimizer_search_depth; -@@optimizer_search_depth -62 -select @@optimizer_prune_level; -@@optimizer_prune_level -1 -set optimizer_search_depth=63; +set optimizer_search_depth=63, optimizer_prune_level=1; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '63' select @@optimizer_search_depth; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 9da6fa5ae8f..aaf0a3e5288 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -523,6 +523,8 @@ NULL 9 b 1 drop table t1; set big_tables=0; +SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off',@@optimizer_use_condition_selectivity=1; create table t1 (a int not null, b int not null); insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1); create table t2 (a int not null, b int not null, key(a)); @@ -535,10 +537,10 @@ a b 3 1 select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; a b +1 1 1 3 -3 1 2 2 -1 1 +3 1 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort @@ -548,6 +550,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary 1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) drop table t1,t2; +SET @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity,@@optimizer_switch=@save_optimizer_switch; create table t1 (a int, b int); insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a; diff --git a/mysql-test/r/host_cache_size_functionality.result b/mysql-test/r/host_cache_size_functionality.result index 069cf61bcad..f5d7e7ee109 100644 --- a/mysql-test/r/host_cache_size_functionality.result +++ b/mysql-test/r/host_cache_size_functionality.result @@ -4,12 +4,7 @@ echo '##' #################################################################### # Checking default value # #################################################################### -SELECT COUNT(@@GLOBAL.Host_Cache_Size) -1 Expected -set @Default_host_cache_size=279; -select @@global.Host_Cache_Size=@Default_host_cache_size; -@@global.Host_Cache_Size=@Default_host_cache_size -1 +select @@global.Host_Cache_Size > 0 1 Expected '#---------------------WL6372_VAR_6_02----------------------#' # Restart server with Host_Cache_Size 1 diff --git a/mysql-test/r/huge_frm-6224.result b/mysql-test/r/huge_frm-6224.result index bbb650bc5b4..4c97bf3837c 100644 --- a/mysql-test/r/huge_frm-6224.result +++ b/mysql-test/r/huge_frm-6224.result @@ -1,6 +1,7 @@ +set @save_max_allowed_packet=@@max_allowed_packet; set global max_allowed_packet=1024*1024*10; connect con1,localhost,root; ERROR HY000: The definition for table `t1` is too big connection default; disconnect con1; -set global max_allowed_packet=default; +set global max_allowed_packet=@save_max_allowed_packet; diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index f27bb263c6f..549c920e857 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -704,8 +704,8 @@ SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX(primary,idx) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6145 -2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 6145 Using sort_union(idx,PRIMARY); Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL # +2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL # Using sort_union(idx,PRIMARY); Using where SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX(primary,idx) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; diff --git a/mysql-test/r/information_schema-big.result b/mysql-test/r/information_schema-big.result index d4aa6deb2e2..0ed74d113ea 100644 --- a/mysql-test/r/information_schema-big.result +++ b/mysql-test/r/information_schema-big.result @@ -15,7 +15,7 @@ WHERE c2.table_schema = t.table_schema AND c2.table_name = t.table_name AND c2.column_name LIKE '%SCHEMA%' ) -AND t.table_name NOT LIKE 'innodb%'; +AND t.table_name NOT LIKE 'innodb%' and t.table_name NOT LIKE "OPTIMIZER_TRACE%"; table_name column_name ALL_PLUGINS PLUGIN_NAME APPLICABLE_ROLES GRANTEE @@ -73,7 +73,7 @@ WHERE c2.table_schema = 'information_schema' AND c2.table_name = t.table_name AND c2.column_name LIKE '%SCHEMA%' ) -AND t.table_name NOT LIKE 'innodb%'; +AND t.table_name NOT LIKE 'innodb%' and t.table_name NOT LIKE "OPTIMIZER_TRACE%"; table_name column_name ALL_PLUGINS PLUGIN_NAME APPLICABLE_ROLES GRANTEE diff --git a/mysql-test/r/information_schema_stats.result b/mysql-test/r/information_schema_stats.result index bc2ce9f0294..8e044bc9f96 100644 --- a/mysql-test/r/information_schema_stats.result +++ b/mysql-test/r/information_schema_stats.result @@ -1,3 +1,4 @@ +set @save_userstat=@@global.userstat; set global userstat=1; create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30)); insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'), @@ -67,4 +68,4 @@ select * from information_schema.index_statistics where table_schema='test' and TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES -set global userstat=0; +set global userstat=@save_userstat; diff --git a/mysql-test/r/init_connection_query_cache.result b/mysql-test/r/init_connection_query_cache.result index eacc06c05f6..df27327e93b 100644 --- a/mysql-test/r/init_connection_query_cache.result +++ b/mysql-test/r/init_connection_query_cache.result @@ -2,6 +2,7 @@ # MDEV-4520: Assertion `0' fails in Query_cache::end_of_result on # concurrent drop event and event execution # +set @save_query_cache_size=@@query_cache_size; set GLOBAL query_cache_size=1355776; create user mysqltest1@localhost; grant SELECT on test.* to mysqltest1@localhost; @@ -20,4 +21,4 @@ disconnect con1; revoke all privileges, grant option from mysqltest1@localhost; drop user mysqltest1@localhost; drop table t1; -set GLOBAL query_cache_size=default; +set GLOBAL query_cache_size=@save_query_cache_size; diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index 7a994730738..1ab44135f90 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -1137,6 +1137,7 @@ drop table t0,t1,t2; # # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' # was corrupted, server crashes in opt_sum_query +SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@@optimizer_use_condition_selectivity=1; CREATE TABLE t1 ( pk INT, f1 VARCHAR(3), @@ -1209,3 +1210,4 @@ EXPLAIN } } drop table t1; +SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 43e659e9b46..e41c79a59f9 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -1,12 +1,16 @@ DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; DROP DATABASE IF EXISTS world; -set @save_optimizer_switch=@@optimizer_switch; +set @org_optimizer_switch=@@optimizer_switch; +set @save_join_cache_level=@@join_cache_level; +set @save_join_buffer_space_limit=@@join_buffer_space_limit; +set @save_join_buffer_size=@@join_buffer_size; +set @save_expensive_subquery_limit=@@expensive_subquery_limit; set @@optimizer_switch='optimize_join_buffer_size=on'; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set @@optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; -set @local_join_cache_test_optimizer_switch_default=@@optimizer_switch; +set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @local_optimizer_switch=@@optimizer_switch; set names utf8; CREATE DATABASE world; use world; @@ -770,14 +774,8 @@ Long Beach United States English Lexington-Fayette United States English Louisville United States English Little Rock United States English -set join_cache_level=default; -set join_buffer_size=default; -show variables like 'join_buffer_size'; -Variable_name Value -join_buffer_size 262144 -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 2 +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; DROP DATABASE world; CREATE DATABASE world; use world; @@ -2684,14 +2682,8 @@ Kaunas Klaipeda ?iauliai Panevezys -set join_cache_level=default; -set join_buffer_size=default; -show variables like 'join_buffer_size'; -Variable_name Value -join_buffer_size 262144 -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 2 +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; set join_cache_level=1; SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; @@ -2799,7 +2791,7 @@ Teheran Iran Tianjin China Tokyo Japan Wuhan China -set join_buffer_size=default; +set join_buffer_size=@save_join_buffer_size; set join_cache_level=6; ALTER TABLE Country MODIFY Name varchar(52) NOT NULL default ''; SELECT City.Name, Country.Name FROM City,Country @@ -2883,8 +2875,8 @@ Kaunas Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Klaipeda Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ?iauliai Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Panevezys Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -set join_cache_level=default; -set join_buffer_size=default; +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; # # MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults # @@ -2915,7 +2907,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables=@save_use_stat_tables; -set join_cache_level=default; +set @@join_cache_level=@save_join_cache_level; DROP DATABASE world; use test; CREATE TABLE t1( @@ -3204,8 +3196,8 @@ DROP TABLE t1,t2; # # Bug #40134: outer join with not exists optimization and join buffer # -set join_cache_level=default; -set join_buffer_size=default; +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; CREATE TABLE t1 (a int NOT NULL); INSERT INTO t1 VALUES (2), (4), (3), (5), (1); CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a)); @@ -3233,8 +3225,8 @@ a a b 5 NULL NULL 1 NULL NULL DROP TABLE t1, t2; -set join_cache_level=default; -set join_buffer_size=default; +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; # # BUG#40136: Group by is ignored when join buffer is used for an outer join # @@ -3286,7 +3278,7 @@ a b a b a b 4 2 1 2 4 2 3 3 NULL NULL NULL NULL 5 3 NULL NULL NULL NULL -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; DROP TABLE t2, t3, t4; # # Bug #40192: outer join with where clause when using BNL @@ -3310,7 +3302,7 @@ select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; a b a c 3 30 3 102 2 20 NULL NULL -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1, t2; # # Bug #40317: outer join with with constant on expression equal to FALSE @@ -3337,7 +3329,7 @@ explain select * from t1 left join t2 on (1=0); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1, t2; # # Bug #41204: small buffer with big rec_per_key for ref access @@ -3376,8 +3368,8 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; AVG(c) 5.0000 -set join_buffer_size=default; -set join_cache_level=default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1, t2; # # Bug #41894: big join buffer of level 7 used to join records @@ -3419,8 +3411,8 @@ WHERE t1.a=t2.a AND t2.a=t3.a AND t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; COUNT(*) 16384 -set join_buffer_size=default; -set join_cache_level=default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; # # Bug #42020: join buffer is used for outer join with fields of @@ -3519,8 +3511,8 @@ a a a b b val 2 2 2 60 60 0 2 2 2 70 70 0 2 2 2 80 80 0 -set join_buffer_size=default; -set join_cache_level=default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; create table t1(f1 int, f2 int); insert into t1 values (1,1),(2,2),(3,3); @@ -3607,7 +3599,7 @@ Variable_name Value Handler_icp_attempts 80 Handler_icp_match 16 drop table t1,t2; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; # # Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled # @@ -3651,7 +3643,7 @@ id1 20 30 30 -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1,t2; # # Bug #44019: star-like multi-join query executed join_cache_level=6 @@ -3715,7 +3707,7 @@ select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; a b c d e f g 3 30 300 3000 bbb ddd ccc -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1,t2,t3,t4; # # Bug #44250: Corruption of linked join buffers when using BKA @@ -3799,8 +3791,8 @@ id1 num3 text1 id4 id3 dummy 228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 228808822 28 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 228808822 62 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 -set join_buffer_size=default; -set join_cache_level=default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4,t5; # # Bug#45267: Incomplete check caused wrong result. @@ -3862,7 +3854,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL int_key 4 NULL 2 Using index; Using join buffer (flat, BNL join) DROP TABLE t1,t2; -SET join_cache_level=default; +SET join_cache_level=@save_join_cache_level; # # Regression test for # Bug#46733 - NULL value not returned for aggregate on empty result @@ -3915,7 +3907,7 @@ SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); c1 c2 c1 c2 LENGTH(t2.c1) LENGTH(t2.c2) 2 2 tt uu 2 2 -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # Bug #51092: linked join buffer is used for a 3-way cross join query @@ -3959,7 +3951,7 @@ a b 2 2 1 1 2 2 -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1,t2,t3; # # Bug #52394: using join buffer for 3 table join with ref access @@ -3988,7 +3980,7 @@ a 27 27 DROP TABLE t1,t2,t3; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; # # Bug #51084: Batched key access crashes for SELECT with # derived table and LEFT JOIN @@ -4028,7 +4020,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.carrier 1 Using where 1 SIMPLE t4 ref carrier_id carrier_id 5 test.t3.id 2 Using index -SET join_cache_level=default; +SET join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; # # Bug #52636: allowing JOINs on NULL values w/ join_cache_level = 5-8 @@ -4092,7 +4084,7 @@ SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; a NULL NULL -set join_cache_level = default; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # Bug #54359: Extra rows with join_cache_level=7,8 and two joins @@ -4119,7 +4111,7 @@ WHERE s.pk AND s.a >= t.pk AND s.b = t.c; a 9 9 -set join_cache_level = default; +set join_cache_level=@save_join_cache_level; DROP TABLE t1; # # Bug #54235: Extra rows with join_cache_level=6,8 and two LEFT JOINs @@ -4159,7 +4151,7 @@ WHERE t1.a OR t4.a; a 2 1 -set join_cache_level = default; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; # # Bug #663840: Memory overwrite causing crash with hash join @@ -4294,8 +4286,8 @@ i d v i d t v 8 2009-01-13 z 5 1900-01-01 22:45:53 a 8 2009-01-13 z 5 1900-01-01 22:45:53 a DROP TABLE t1,t2,t3; -SET SESSION join_cache_level=DEFAULT; -SET SESSION join_buffer_size=DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; # # Bug #664508: 'Simple' GROUP BY + ORDER BY # when join buffers are used @@ -4440,7 +4432,7 @@ n v p DROP TABLE t1,t2,t3; -SET SESSION join_cache_level=DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; # # Bug #668290: hash join with non-binary collations # @@ -4486,7 +4478,7 @@ SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; i 6 6 -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # Bug #669382: hash join using a ref with constant key parts @@ -4511,8 +4503,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t2.a 36 Using where; Using join buffer (flat, BNLH join) SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; a c -SET SESSION join_cache_level = DEFAULT; -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; DROP TABLE t1,t2; # # Bug #671901: hash join using a ref to a varchar field @@ -4580,7 +4572,7 @@ SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); v i f 5 f 5 -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # Bug #672497: 3 way join with tiny incremental join buffer with # and a ref access from the first table @@ -4649,8 +4641,8 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT t3.i FROM t1,t2,t3 WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; i -SET SESSION join_cache_level = DEFAULT; -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; DROP TABLE t1,t2,t3; # # Bug #672551: hash join over a long varchar field @@ -4704,7 +4696,7 @@ pk a pk a 2 aa 131 aa 3 bb 132 bb 2 aa 142 aa -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # Bug #674431: nested outer join when join_cache_level is set to 7 @@ -4725,8 +4717,8 @@ a Warnings: Warning 1292 Truncated incorrect INTEGER value: 'h' Warning 1292 Truncated incorrect INTEGER value: 'j' -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; # # Bug #52540: nested outer join when join_cache_level is set to 3 @@ -4746,8 +4738,8 @@ FROM t2 LEFT JOIN a a pk a f 2 2 NULL x 2 2 NULL -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; # # Bug #674423: outer join with ON expression over only outer tables @@ -4775,8 +4767,8 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; pk a a 1 NULL NULL -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # Bug #675095: nested outer join using join buffer @@ -4861,8 +4853,8 @@ pk a1 pk a2 c2 d2 pk a3 c3 d3 pk a4 pk a5 2 NULL 9 0 0 2 9 0 0 2 NULL NULL 8 0 2 NULL 9 0 0 2 1 0 0 7 NULL NULL 8 0 8 0 NULL NULL NULL NULL NULL NULL NULL NULL 8 0 NULL NULL -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4,t5; # # Bug #675516: nested outer join with 3 tables in the nest @@ -4909,8 +4901,8 @@ t1 LEFT JOIN ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 WHERE t3.a3 IS NULL; a1 b1 c1 a2 a3 b3 c3 a4 b4 c4 -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; # # Bug #660963: nested outer join with join_cache_level set to 5 @@ -4944,8 +4936,8 @@ SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; a1 a2 b2 a3 b3 0 2 1 2 1 0 2 1 2 1 -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; # # Bug #675922: incremental buffer for BKA with access from previous @@ -4996,8 +4988,8 @@ LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; a4 b5 9 0 9 NULL -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4,t5; # # Bug #670380: hash join for non-binary collation @@ -5041,7 +5033,7 @@ pk a pk a 40 DD 112 dd 50 ee 113 EE 50 ee 114 ee -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # Bug #694092: incorrect detection of index only pushdown conditions @@ -5075,8 +5067,8 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1,t2 WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); f1 f3 f3 f2 f4 -SET SESSION join_cache_level = DEFAULT; -SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2; CREATE TABLE t1 (f1 int, f2 varchar(10), KEY (f1), KEY (f2)) ; INSERT INTO t1 VALUES @@ -5098,7 +5090,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; f1 f2 f3 -SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; +SET SESSION optimizer_switch=@local_optimizer_switch; SET SESSION optimizer_switch = 'index_condition_pushdown=on'; EXPLAIN SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; @@ -5108,7 +5100,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; f1 f2 f3 -SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; +SET SESSION optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2; # # Bug #694443: hash join using IS NULL the an equi-join condition @@ -5154,7 +5146,7 @@ a a b 2 NULL 41 1 NULL 42 2 NULL 42 -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # Bug #697557: hash join on a varchar field @@ -5176,7 +5168,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 hash_ALL f1 #hash#f1 13 test.t1.f1 20 Using join buffer (flat, BNLH join) SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; f1 f2 f1 f2 -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # Bug #707827: hash join on varchar column with NULLs @@ -5210,8 +5202,8 @@ SELECT a FROM t1,t2 WHERE t2.v = t1.v ; a 11 18 -SET SESSION join_cache_level = DEFAULT; -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; DROP TABLE t1,t2; # # Bug #802860: crash on join cache + derived + duplicate_weedout @@ -5246,8 +5238,8 @@ SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); a 0 -SET SESSION join_cache_level = DEFAULT; DROP TABLE t1, t2; +SET SESSION join_cache_level=@save_join_cache_level; # # Bug #887479: join_cache_level=3 + semijoin=on # @@ -5255,7 +5247,7 @@ CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20); CREATE TABLE t2 (c int, KEY (c)); INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2); -SET @tmp887479_optimizer_switch=@@optimizer_switch; +SET @tmp_optimizer_switch=@@optimizer_switch; SET SESSION optimizer_switch='semijoin=on'; SET SESSION optimizer_switch='semijoin_with_cache=on'; SET SESSION join_cache_level=1; @@ -5278,8 +5270,8 @@ SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); a b 3914 17 3888 20 -SET SESSION join_cache_level = DEFAULT; -SET optimizer_switch=@tmp887479_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2; # # Bug #899777: join_cache_level=4 + semijoin=on @@ -5325,8 +5317,8 @@ WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) AND t1.a = 1; a b c 1 8 6 -SET SESSION join_cache_level = DEFAULT; -SET optimizer_switch=@tmp_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3,t4; # # Bug #899509: an attempt to use hash join with join_cache_level=0 @@ -5337,11 +5329,9 @@ CREATE TABLE t2 (a int); INSERT INTO t2 VALUES (8), (7); CREATE TABLE t3 (a int); INSERT INTO t3 VALUES (8), (7); -SET @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch=default; set @@optimizer_switch='semijoin_with_cache=off'; set @@optimizer_switch='outer_join_with_cache=off'; -SET optimizer_switch='derived_merge=off,derived_with_keys=off'; +set @@optimizer_switch='derived_merge=off,derived_with_keys=off'; SET join_cache_level=0; EXPLAIN SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); @@ -5363,8 +5353,8 @@ a 8 7 7 -SET SESSION join_cache_level = DEFAULT; -SET optimizer_switch=@tmp_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; # # Bug #900469: semijoin + BNLH + ORDER BY @@ -5377,8 +5367,8 @@ INSERT INTO t2 VALUES (9,11); CREATE TABLE t3 (c int, d int); INSERT INTO t3 VALUES (8,10); INSERT INTO t3 VALUES (9,11); -SET @tmp_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='semijoin_with_cache=on'; +set @@optimizer_switch='firstmatch=off'; SET join_cache_level=1; EXPLAIN SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; @@ -5412,8 +5402,8 @@ SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; a b c d 8 10 8 10 8 10 9 11 -SET SESSION join_cache_level = DEFAULT; -SET optimizer_switch=@tmp_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; # # Bug #901478: semijoin + ORDER BY + join_cache_level=4|6 @@ -5431,7 +5421,6 @@ INSERT INTO t4 VALUES INSERT INTO t4 VALUES (19,11,10), (17,12,18), (12,13,15), (14,12,19), (18,13,18), (13,14,11), (15,15,14); -SET @tmp_optimizer_switch=@@optimizer_switch; SET @@optimizer_switch='semijoin=on'; SET @@optimizer_switch='firstmatch=off'; SET @@optimizer_switch='mrr=off'; @@ -5491,8 +5480,8 @@ ORDER BY t2.b; a a b c x 4 2 9 x 5 5 4 -SET join_cache_level = DEFAULT; -SET optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3,t4; # # Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8) @@ -5511,11 +5500,10 @@ insert into t3 values (1,1), (1,2), (1,3),(1,4), (2,5), (2,6), (2,7), (2,8), (3,1), (3,2), (3,9); -set @tmp_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='semijoin=on'; -set optimizer_switch='materialization=off'; -set optimizer_switch='loosescan=off,firstmatch=off'; -set optimizer_switch='mrr_sort_keys=off'; +set @@optimizer_switch='semijoin=on'; +set @@optimizer_switch='materialization=off'; +set @@optimizer_switch='loosescan=off,firstmatch=off'; +set @@optimizer_switch='mrr_sort_keys=off'; set join_cache_level=7; create table t4 (uid int primary key, name varchar(128), index(name)); insert into t4 values @@ -5544,8 +5532,8 @@ F G H I -set join_cache_level = default; -set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; drop table t1,t2,t3,t4; # # Bug#50358 - semijoin execution of subquery with outerjoin @@ -5557,9 +5545,8 @@ CREATE TABLE t3 (i int); INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (6); INSERT INTO t3 VALUES (1), (2); -set @tmp_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='semijoin=on'; -set optimizer_switch='materialization=on'; +set @@optimizer_switch='semijoin=on'; +set @@optimizer_switch='materialization=on'; set join_cache_level=0; EXPLAIN SELECT * FROM t1 WHERE t1.i IN @@ -5588,8 +5575,8 @@ SELECT * FROM t1 WHERE t1.i IN i 1 2 -set join_cache_level = default; -set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; # # Bug #12546542: missing row with semijoin=off + join cache @@ -5603,9 +5590,8 @@ a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6); CREATE TABLE t3 (b int, c int); INSERT INTO t3 VALUES (4,4); -set @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch='semijoin=off'; -set optimizer_switch='materialization=off'; +set @@optimizer_switch='semijoin=off'; +set @@optimizer_switch='materialization=off'; set join_cache_level=0; EXPLAIN SELECT * FROM t1 @@ -5636,8 +5622,8 @@ WHERE t2.c < 10 OR t3.c > 1); a v we -set join_cache_level = default; -set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; # # lp:925985 LEFT JOIN with optimize_join_buffer_size=off + @@ -5652,12 +5638,11 @@ INSERT INTO t2 VALUES (3,32), (1,12), (7,72), (2,22); CREATE TABLE t3 (b int, c int); INSERT INTO t3 VALUES (32, 302), (42,400), (30,300); -set @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch='optimize_join_buffer_size=off'; +set @@optimizer_switch='optimize_join_buffer_size=off'; +set @@optimizer_switch='outer_join_with_cache=on'; set join_buffer_space_limit=4096; set join_buffer_size=4096*2; set join_cache_level=2; -set optimizer_switch='outer_join_with_cache=on'; EXPLAIN SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra @@ -5669,10 +5654,10 @@ a a b b c 3 3 30 30 300 3 3 31 NULL NULL 3 3 32 32 302 -set join_buffer_space_limit=default; -set join_buffer_size=default; -set join_cache_level=default; -set optimizer_switch=@tmp_optimizer_switch; +set join_buffer_space_limit=@save_join_buffer_space_limit; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; # # Bug #1058071: LEFT JOIN using blobs @@ -5705,8 +5690,7 @@ CREATE TABLE t2 (b int) ENGINE=Aria; INSERT INTO t2 VALUES (NULL); CREATE TABLE t3 (c int) ENGINE=Aria; INSERT INTO t3 VALUES (NULL); -set @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on'; +set @@optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on'; set join_buffer_size=128; EXPLAIN SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 @@ -5719,11 +5703,9 @@ SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 GROUP BY elt(t1.col282,1,t1.col280); c 1 -set join_buffer_size=default; -set optimizer_switch=@tmp_optimizer_switch; DROP table t1,t2,t3; -set join_buffer_size= default; -set @@optimizer_switch=@save_optimizer_switch; +set join_buffer_size=@save_join_buffer_size; +set @@optimizer_switch=@org_optimizer_switch,@local_optimizer_switch= @org_optimizer_switch; # # MDEV-5293: outer join, join buffering, and order by - invalid query plan # @@ -5894,8 +5876,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE c25 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE c26 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE c27 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) -set join_buffer_size=default; -set join_cache_level = default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # # MDEV-14960: BNLH used for materialized semi-join @@ -5913,11 +5895,10 @@ INSERT INTO t1 SELECT i1+64 FROM t1; INSERT INTO t2 SELECT * FROM t1; INSERT INTO t4 SELECT * FROM t1; INSERT INTO t5 SELECT * FROM t1; -set @save_optimizer_switch= @@optimizer_switch; SET join_cache_level = 6; SET join_buffer_size=4096; SET join_buffer_space_limit=4096; -SET optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on'; +set @@optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on'; EXPLAIN SELECT * FROM t1 WHERE i1 < 10 AND @@ -5950,10 +5931,10 @@ i1 7 8 9 -SET join_cache_level = default; -SET join_buffer_size = default; -SET join_buffer_space_limit= default; -set optimizer_switch=@save_optimizer_switch; +set join_cache_level=@save_join_cache_level; +SET join_buffer_size=@save_join_buffer_size; +SET join_buffer_space_limit=@save_join_buffer_space_limit; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t4,t5,t2; # # MDEV-16603: BNLH for query with materialized semi-join @@ -5987,11 +5968,10 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where `test`.`temp`.`f1` = `test`.`t1`.`i1` and `test`.`t2`.`v1` = `test`.`t1`.`v1` and `test`.`temp`.`f2` = `test`.`t1`.`v1` DROP TABLE t1,t2,temp; -SET join_cache_level = default; +set join_cache_level=@save_join_cache_level; # # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. # -set join_cache_level=default; set expensive_subquery_limit=0; create table t1 (c1 int); create table t2 (c2 int); @@ -6034,7 +6014,7 @@ select @counter; @counter 2 drop table t1,t2,t3; -set expensive_subquery_limit=default; +set expensive_subquery_limit=@save_expensive_subquery_limit; # # MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down # @@ -6046,17 +6026,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 11 1 SIMPLE t2 ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) set join_buffer_space_limit=1024*8; -select @@join_buffer_space_limit; -@@join_buffer_space_limit -8192 -select @@join_buffer_size; -@@join_buffer_size -262144 explain select count(*) from t1, t1 t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 11 1 SIMPLE t2 ALL NULL NULL NULL NULL 11 Using where -set join_buffer_space_limit=default; +set join_buffer_space_limit=@save_join_buffer_space_limit; drop table t1; # # MDEV-6687: Assertion `0' failed in Protocol::end_statement on query @@ -6070,7 +6044,7 @@ SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL Using where 1 SIMPLE user hash_ALL NULL #hash#$hj 1 information_schema.PROFILING.PAGE_FAULTS_MINOR 4 Using where; Using join buffer (flat, BNLH join) -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; create table t1 (c1 date not null, key (c1)) engine=innodb; insert t1 values ('2017-12-27'); create table t2 (pk int, f1 int, f2 int); @@ -6079,5 +6053,5 @@ set join_buffer_size = 222222208; select f2 from t2,t1 where f2 = 0; f2 drop table t1, t2; -set join_buffer_size = default; +set join_buffer_size=@save_join_buffer_size; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index f9e1c49b03c..b0f924b0069 100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -4,9 +4,7 @@ set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 6 +set optimizer_search_depth=62; set @optimizer_switch_for_join_nested_test=@@optimizer_switch; set @join_cache_level_for_join_nested_test=@@join_cache_level; DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; @@ -2116,9 +2114,6 @@ a b a b a b a b 3 2 NULL NULL NULL NULL NULL NULL DROP TABLE t5,t6,t7,t8; set join_cache_level=default; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 2 set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_join_nested_test=NULL; set @join_cache_level_for_join_nested_test=NULL; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index f9217997a6a..fdc36b74f63 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1,5 +1,5 @@ drop table if exists t0,t1,t2,t3,t4,t5; -SET @save_optimizer_switch=@@optimizer_switch; +SET @org_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off'); set join_cache_level=1; CREATE TABLE t1 ( @@ -2018,7 +2018,7 @@ EXECUTE stmt; b b a b 10 10 0 10 DEALLOCATE PREPARE stmt; -SET SESSION join_cache_level=default; +SET SESSION join_cache_level=2; DROP TABLE t1,t2,t3; # # LP bug #943543: LEFT JOIN converted to JOIN with @@ -2682,4 +2682,4 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 DROP TABLE t1,t2,t3,t4; # end of 10.1 tests -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=@org_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 4a42becbb02..6a6b1007866 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2,15 +2,12 @@ set @save_optimizer_switch_jcl6=@@optimizer_switch; set @@optimizer_switch='optimize_join_buffer_size=on'; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; -set join_cache_level=6; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 6 +set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @@join_cache_level=6; set @optimizer_switch_for_join_outer_test=@@optimizer_switch; set @join_cache_level_for_join_outer_test=@@join_cache_level; drop table if exists t0,t1,t2,t3,t4,t5; -SET @save_optimizer_switch=@@optimizer_switch; +SET @org_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off'); set join_cache_level=@join_cache_level_for_join_outer_test; CREATE TABLE t1 ( @@ -2029,7 +2026,7 @@ EXECUTE stmt; b b a b 10 10 0 10 DEALLOCATE PREPARE stmt; -SET SESSION join_cache_level=default; +SET SESSION join_cache_level=2; DROP TABLE t1,t2,t3; # # LP bug #943543: LEFT JOIN converted to JOIN with @@ -2693,11 +2690,8 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 DROP TABLE t1,t2,t3,t4; # end of 10.1 tests -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=@org_optimizer_switch; set join_cache_level=default; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 2 set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_join_outer_test=NULL; set @join_cache_level_for_join_outer_test=NULL; diff --git a/mysql-test/r/log_slow.result b/mysql-test/r/log_slow.result index 485b1fd9826..79cb9d2352b 100644 --- a/mysql-test/r/log_slow.result +++ b/mysql-test/r/log_slow.result @@ -1,3 +1,4 @@ +set @@log_slow_verbosity=""; select @@log_slow_filter; @@log_slow_filter admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index d4f98a6bde9..5e7aac81b0c 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -3,6 +3,7 @@ SET @old_general_log= @@global.general_log; SET @old_general_log_file= @@global.general_log_file; SET @old_slow_query_log= @@global.slow_query_log; SET @old_slow_query_log_file= @@global.slow_query_log_file; +set @save_long_query_time=@@long_query_time; set global general_log= OFF; truncate table mysql.general_log; truncate table mysql.slow_log; @@ -276,7 +277,7 @@ SELECT @@general_log_file = @my_glf; SET GLOBAL general_log_file = @old_general_log_file; disconnect con1; connection default; -SET GLOBAL long_query_time = DEFAULT; +SET GLOBAL long_query_time = @save_long_query_time; SET GLOBAL log_output = @old_log_output; SET global general_log = @old_general_log; SET global general_log_file = @old_general_log_file; diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 126230a8765..aed5dbfba3d 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -9,9 +9,6 @@ SET @saved_long_query_time = @@long_query_time; SET @saved_log_output = @@log_output; SET @saved_general_log = @@GLOBAL.general_log; SET @saved_slow_query_log = @@GLOBAL.slow_query_log; -SELECT @saved_long_query_time, @saved_log_output, @saved_general_log, @saved_slow_query_log; -@saved_long_query_time @saved_log_output @saved_general_log @saved_slow_query_log -10 FILE,TABLE 1 1 truncate table general_log; select * from general_log; event_time user_host thread_id server_id command_type argument @@ -436,9 +433,9 @@ My own slow query sleep(2) My own slow query 0 SELECT * FROM mysql.slow_log WHERE seq >= 2 LIMIT 3; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id rows_affected seq -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 0 2 -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 0 3 -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 0 4 +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) THREAD_ID 0 2 +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) THREAD_ID 0 3 +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) THREAD_ID 0 4 SET GLOBAL slow_query_log = 0; SET SESSION long_query_time =@saved_long_query_time; FLUSH LOGS; diff --git a/mysql-test/r/lowercase_table_qcache.result b/mysql-test/r/lowercase_table_qcache.result index 9d7e1007e4c..9f8c89a41e8 100644 --- a/mysql-test/r/lowercase_table_qcache.result +++ b/mysql-test/r/lowercase_table_qcache.result @@ -1,3 +1,4 @@ +set @save_query_cache_size=@@query_cache_size; set GLOBAL query_cache_size=1355776; set GLOBAL query_cache_type=ON; set LOCAL query_cache_type=ON; @@ -23,5 +24,5 @@ select * from MySQL.db; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 -set GLOBAL query_cache_size=default; +set GLOBAL query_cache_size=@save_query_cache_size; set GLOBAL query_cache_type=default; diff --git a/mysql-test/r/mdev375.result b/mysql-test/r/mdev375.result index 0ae8375ff24..3fdf97f5c24 100644 --- a/mysql-test/r/mdev375.result +++ b/mysql-test/r/mdev375.result @@ -1,3 +1,4 @@ +set @save_log_warnings=@@log_warnings, @save_max_connections=@@max_connections; SET GLOBAL log_warnings=4; SET GLOBAL max_connections=10; connect tmp_con1,localhost,root,,; @@ -24,5 +25,5 @@ SELECT 0; show status like "Threads_connected"; Variable_name Value Threads_connected 11 -SET GLOBAL log_warnings=default; -SET GLOBAL max_connections=default; +SET GLOBAL log_warnings=@save_log_warnings; +SET GLOBAL max_connections=@save_max_connections; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 9922089bf7d..b6e622ce5fc 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -1,5 +1,6 @@ -set global storage_engine=myisam; -set session storage_engine=myisam; +set @save_default_storage_engine=@@global.storage_engine; +set global default_storage_engine=myisam; +set session default_storage_engine=myisam; drop table if exists t1,t2,t3,t4,t5,t6; drop database if exists mysqltest; create table t1 (a int not null primary key auto_increment, message char(20)); @@ -2583,11 +2584,8 @@ prepare stmt from @a; execute stmt; prepare stmt from "select * from t_parent"; execute stmt; -ERROR HY000: Prepared statement needs to be re-prepared execute stmt; -ERROR HY000: Prepared statement needs to be re-prepared execute stmt; -ERROR HY000: Prepared statement needs to be re-prepared deallocate prepare stmt; # # Create merge parent diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index bd50df7c40e..2fbfe148ef4 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -1,6 +1,7 @@ drop table if exists t0, t1, t2, t3; set @myisam_mrr_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set optimizer_switch='optimize_join_buffer_size=off'; set @mrr_buffer_size_save= @@mrr_buffer_size; set mrr_buffer_size=79; Warnings: diff --git a/mysql-test/r/myisam_recover.result b/mysql-test/r/myisam_recover.result index 1af3dbf9ad5..8c0f9169cf1 100644 --- a/mysql-test/r/myisam_recover.result +++ b/mysql-test/r/myisam_recover.result @@ -1,4 +1,5 @@ call mtr.add_suppression("Table 't1' is marked as crashed and should be repaired"); +set @save_table_definition_cache=@@table_definition_cache; # # Tests for corrupted MyISAM tables and MyISAMMRG tables with corrupted # children.. @@ -98,10 +99,10 @@ unlock tables; prepare stmt from @drop_table_stmt; execute stmt; deallocate prepare stmt; -set @@global.table_definition_cache=default; set @@global.table_open_cache=@save_table_open_cache; disconnect con1; connection default; +set @@global.table_definition_cache=@save_table_definition_cache; # # 18075170 - sql node restart required to avoid deadlock after # restore diff --git a/mysql-test/r/mysql_client_test.result b/mysql-test/r/mysql_client_test.result index 4ad07b20ab5..9538475fb86 100644 --- a/mysql-test/r/mysql_client_test.result +++ b/mysql-test/r/mysql_client_test.result @@ -1,3 +1,9 @@ +SET @save_character_set_client= @@global.character_set_client; +SET @save_collation_connection= @@global.collation_connection; +SET @@global.character_set_server= @@character_set_server; +SET @@global.collation_server= @@collation_server; +SET @@global.character_set_client= @@character_set_server; +SET @@global.collation_connection= @@collation_server; SET @old_general_log= @@global.general_log; SET @old_slow_query_log= @@global.slow_query_log; call mtr.add_suppression(" Error reading file './client_test_db/test_frm_bug.frm'"); @@ -123,3 +129,7 @@ mysql_stmt_next_result(): 0; field_count: 0 SET @@global.general_log= @old_general_log; SET @@global.slow_query_log= @old_slow_query_log; +SET @@global.character_set_server= @save_character_set_server; +SET @@global.collation_server= @save_collation_server; +SET @@global.character_set_client= @save_character_set_client; +SET @@global.collation_connection= @save_collation_connection; diff --git a/mysql-test/r/mysqlbinlog-innodb.result b/mysql-test/r/mysqlbinlog-innodb.result index 2971a69fb4d..f9f48299f41 100644 --- a/mysql-test/r/mysqlbinlog-innodb.result +++ b/mysql-test/r/mysqlbinlog-innodb.result @@ -30,10 +30,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; INSERT INTO t1 VALUES (1) @@ -61,10 +61,10 @@ use `foo`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; INSERT INTO t1 VALUES (1) diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 35ab4a5b54f..19d4cde7f80 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -23,10 +23,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; DROP TABLE IF EXISTS `t1`,`t2`,`t3`,`t4`,`t5`,`t03`,`t04` /* generated by server */ @@ -103,10 +103,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`word`) @@ -139,10 +139,10 @@ BEGIN SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; COMMIT @@ -190,10 +190,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values ("Alas") @@ -217,10 +217,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; DROP TABLE IF EXISTS `t1`,`t2`,`t3`,`t4`,`t5`,`t03`,`t04` /* generated by server */ @@ -297,10 +297,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`word`) @@ -333,10 +333,10 @@ BEGIN SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; COMMIT @@ -384,10 +384,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values ("Alas") @@ -493,10 +493,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() @@ -545,10 +545,10 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a varchar(64) character set utf8) diff --git a/mysql-test/r/mysqlbinlog_row_big.result b/mysql-test/r/mysqlbinlog_row_big.result index a90ba9d79b6..ecbb9df2be1 100644 --- a/mysql-test/r/mysqlbinlog_row_big.result +++ b/mysql-test/r/mysqlbinlog_row_big.result @@ -96,6 +96,5 @@ FLUSH LOGS; # Cleanup. # # reset variable value to pass testcase checks -SET @@global.max_allowed_packet = 16777216; DROP TABLE t1; remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_big_1.out diff --git a/mysql-test/r/mysqlbinlog_row_compressed.result b/mysql-test/r/mysqlbinlog_row_compressed.result index fc44934ef3f..35d89416042 100644 --- a/mysql-test/r/mysqlbinlog_row_compressed.result +++ b/mysql-test/r/mysqlbinlog_row_compressed.result @@ -34,10 +34,10 @@ use `test`/*!*/; SET TIMESTAMP=X/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 TINYINT, f4 MEDIUMINT, f5 BIGINT, f6 INT, f7 INT, f8 char(1)) diff --git a/mysql-test/r/mysqlbinlog_row_minimal.result b/mysql-test/r/mysqlbinlog_row_minimal.result index 8a0a22e1195..e78c3ca3a40 100644 --- a/mysql-test/r/mysqlbinlog_row_minimal.result +++ b/mysql-test/r/mysqlbinlog_row_minimal.result @@ -32,10 +32,10 @@ use `test`/*!*/; SET TIMESTAMP=X/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 TINYINT, f4 MEDIUMINT, f5 BIGINT, f6 INT, f7 INT, f8 char(1)) @@ -371,10 +371,10 @@ BEGIN SET TIMESTAMP=X/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; COMMIT diff --git a/mysql-test/r/mysqlbinlog_stmt_compressed.result b/mysql-test/r/mysqlbinlog_stmt_compressed.result index cd8e98c1ee3..42717bccd01 100644 --- a/mysql-test/r/mysqlbinlog_stmt_compressed.result +++ b/mysql-test/r/mysqlbinlog_stmt_compressed.result @@ -34,10 +34,10 @@ use `test`/*!*/; SET TIMESTAMP=X/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1411383296/*!*/; +SET @@session.sql_mode=#/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 TINYINT, f4 MEDIUMINT, f5 BIGINT, f6 INT, f7 INT, f8 char(1)) diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index ff9cc5d5c81..a11b0fa62f4 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -1,4 +1,6 @@ call mtr.add_suppression("Invalid .old.. table or database name"); +set @save_character_set_client=@@character_set_client; +set @save_collation_connection=@@collation_connection; DROP TABLE IF EXISTS t1, `t``1`, `t 1`, test.`t.1`, v1; drop view if exists t1, `t``1`, `t 1`, test.`t.1`, v1; drop database if exists client_test_db; @@ -214,7 +216,9 @@ CREATE TABLE `#mysql50#@` (a INT) engine=myisam; SHOW TABLES; Tables_in_test #mysql50#@ -SET NAMES DEFAULT; +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; +set @@collation_connection=@save_collation_connection; mysqlcheck --fix-table-names --databases test SET NAMES utf8; SHOW TABLES; @@ -222,7 +226,9 @@ Tables_in_test @ DROP TABLE `@`; CREATE TABLE `Ñ` (a INT) engine=myisam; -SET NAMES DEFAULT; +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; +set @@collation_connection=@save_collation_connection; call mtr.add_suppression("@003f.frm' \\(errno: 22\\)"); mysqlcheck --default-character-set="latin1" --databases test call mtr.add_suppression("Can't find file: '..test.@003f.frm'"); @@ -233,7 +239,9 @@ mysqlcheck --default-character-set="utf8" --databases test test.Ñ OK SET NAMES utf8; DROP TABLE `Ñ`; -SET NAMES DEFAULT; +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; +set @@collation_connection=@save_collation_connection; CREATE DATABASE `#mysql50#a@b`; USE `#mysql50#a@b`; CREATE TABLE `#mysql50#c@d` (a INT) engine=myisam; @@ -470,5 +478,5 @@ test.t1 Engine-independent statistics status : OK select * from mysql.column_stats where db_name = 'test' and table_name = 't1'; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -test t1 a 1 3 0.0000 4.0000 1.0000 0 NULL NULL +test t1 a 1 3 0.0000 4.0000 1.0000 # # # drop table t1; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index d402c355bb1..e83edb8c121 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -91,7 +91,7 @@ INSERT INTO `t1` VALUES (1.23450,2.3456),(1.23450,2.3456),(1.23450,2.3456),(1.23 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -202,7 +202,7 @@ INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'), (NULL); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -305,7 +305,7 @@ create table t1(a int); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -364,7 +364,7 @@ set global sql_mode='ANSI_QUOTES'; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -429,7 +429,7 @@ insert into t1 values (1),(2),(3); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; @@ -461,7 +461,7 @@ drop table t1; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -487,7 +487,7 @@ create database mysqldump_test_db character set latin2 collate latin2_bin; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -521,7 +521,7 @@ INSERT INTO t1 VALUES (_latin1 'ÄÖÜß'); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -639,7 +639,7 @@ INSERT INTO t2 VALUES (4),(5),(6); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -680,7 +680,7 @@ INSERT INTO `t1` VALUES (0x602010000280100005E71A); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -721,7 +721,7 @@ INSERT INTO t1 VALUES (4),(5),(6); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -755,7 +755,7 @@ UNLOCK TABLES; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -1124,7 +1124,7 @@ insert into t1 (F_8d3bba7425e7c98c50f52ca1b52d3735) values (1); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -1493,7 +1493,7 @@ INSERT INTO t1 VALUES (1),(2),(3); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -1543,7 +1543,7 @@ INSERT INTO t2 VALUES (1), (2); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -1578,7 +1578,7 @@ CREATE TABLE `t2` ( /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -1726,7 +1726,7 @@ insert into t2 (a, b) values (NULL, NULL),(10, NULL),(NULL, "twenty"),(30, "thir /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; @@ -1757,7 +1757,7 @@ UNLOCK TABLES; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -1874,7 +1874,7 @@ create table t3(a int); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -1923,7 +1923,7 @@ mysqldump: Got error: 1064: "You have an error in your SQL syntax; check the man /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -1993,7 +1993,7 @@ UNLOCK TABLES; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2047,7 +2047,7 @@ create view v2 as select * from t2 where a like 'a%' with check option; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2143,7 +2143,7 @@ create view v1 as select * from t1; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2215,7 +2215,7 @@ create view v2 as select * from t2 where a like 'a%' with check option; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2282,7 +2282,7 @@ INSERT INTO t1 VALUES ('\''); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2328,7 +2328,7 @@ select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2483,7 +2483,7 @@ update t1 set a = 4 where a=3; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2612,7 +2612,7 @@ DELIMITER ; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2757,7 +2757,7 @@ set sql_mode=''; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2908,7 +2908,7 @@ set global time_zone='Europe/Moscow'; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -2947,7 +2947,7 @@ UNLOCK TABLES; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; @@ -3081,7 +3081,7 @@ a b c /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -3212,7 +3212,7 @@ SET SQL_MODE = @old_sql_mode; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -3275,7 +3275,7 @@ insert into t1 values ('',''); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -3310,7 +3310,7 @@ UNLOCK TABLES; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -3483,7 +3483,7 @@ insert into t1 values (0815); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -3765,7 +3765,7 @@ CREATE TABLE t1 (a INT) ENGINE=merge UNION=(t2, t3); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -3957,7 +3957,7 @@ create view db42635.v2 (c) as select * from db42635.t1; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -4103,7 +4103,7 @@ INSERT INTO t1 VALUES (3,4), (4,5); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -4372,7 +4372,7 @@ insert into t1 values (0815); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -5658,7 +5658,7 @@ INSERT INTO t1 (a) VALUES (1),(2),(3); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; diff --git a/mysql-test/r/named_pipe.result b/mysql-test/r/named_pipe.result index 66da9a874b4..f8c2acf98a0 100644 --- a/mysql-test/r/named_pipe.result +++ b/mysql-test/r/named_pipe.result @@ -1348,8 +1348,10 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 -SET @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='outer_join_with_cache=off'; +SET @local_optimizer_switch=@@optimizer_switch; +set @local_join_cache_level=@@join_cache_level; +set @@join_cache_level=2; +set optimizer_switch='outer_join_with_cache=off,join_cache_hashed=off'; insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname @@ -1425,7 +1427,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@local_optimizer_switch; select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -1434,6 +1436,7 @@ explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr= id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) +SET @@join_cache_level=@local_join_cache_level; select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result index 24c4cc69f5d..bc06c6859f0 100644 --- a/mysql-test/r/openssl_1.result +++ b/mysql-test/r/openssl_1.result @@ -81,7 +81,7 @@ INSERT INTO t1 VALUES (1), (2); /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -115,7 +115,7 @@ UNLOCK TABLES; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; @@ -149,7 +149,7 @@ UNLOCK TABLES; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; +/*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; diff --git a/mysql-test/r/partition_alter.result b/mysql-test/r/partition_alter.result index d272381bc08..1dced95b17a 100644 --- a/mysql-test/r/partition_alter.result +++ b/mysql-test/r/partition_alter.result @@ -76,6 +76,7 @@ t1 CREATE TABLE `t1` ( insert t1 values (2, '2020-01-03', 20); ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` drop table t1; +db.opt create table t1(id int, d date not null, b bool not null default 0, primary key(id,d)) partition by range columns (d) ( partition p1 values less than ('2016-10-18'), @@ -100,6 +101,7 @@ t1 CREATE TABLE `t1` ( insert t1 values (2, '2020-01-03', 20); ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` drop table t1; +db.opt create table t1 (id_1 int auto_increment, id_2 int, id_3 int, d1 date, dt1 datetime default current_timestamp, dt2 datetime default current_timestamp on update current_timestamp, primary key (id_2, id_3), key(id_1)) partition by hash(id_2) partitions 3 (partition p01, partition p02, partition p03); insert into t1 values(0, 1, 1, NULL, now(), now()); alter online table t1 delay_key_write=1; diff --git a/mysql-test/r/partition_key_cache.result b/mysql-test/r/partition_key_cache.result index f1b174ca249..fae7639d9bb 100644 --- a/mysql-test/r/partition_key_cache.result +++ b/mysql-test/r/partition_key_cache.result @@ -1,9 +1,7 @@ DROP TABLE IF EXISTS t1, t2, v, x; # Actual test of key caches # Verifing that reads/writes use the key cache correctly -SELECT @org_key_cache_buffer_size:= @@global.default.key_buffer_size; -@org_key_cache_buffer_size:= @@global.default.key_buffer_size -1048576 +SET @org_key_cache_buffer_size= @@global.default.key_buffer_size; # Minimize default key cache (almost disabled). SET @@global.default.key_buffer_size = 4096; CREATE TABLE t1 ( diff --git a/mysql-test/r/pool_of_threads.result b/mysql-test/r/pool_of_threads.result index b25d8d1e50e..9c02dfac9cd 100644 --- a/mysql-test/r/pool_of_threads.result +++ b/mysql-test/r/pool_of_threads.result @@ -1,4 +1,3 @@ -SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; drop table if exists t1,t2,t3,t4; CREATE TABLE t1 ( @@ -1349,8 +1348,10 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 -SET @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='outer_join_with_cache=off'; +SET @local_optimizer_switch=@@optimizer_switch; +set @local_join_cache_level=@@join_cache_level; +set @@join_cache_level=2; +set optimizer_switch='outer_join_with_cache=off,join_cache_hashed=off'; insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname @@ -1426,7 +1427,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@local_optimizer_switch; select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -1435,6 +1436,7 @@ explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr= id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) +SET @@join_cache_level=@local_join_cache_level; select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 @@ -2156,7 +2158,7 @@ Privat (Private Nutzung) Mobilfunk Warnings: Warning 1052 Column 'kundentyp' in group statement is ambiguous drop table t1; -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=default; connection default; SELECT sleep(50); connect con2,localhost,root,,; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index ee734845a9e..96cf08dd6c7 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1,3 +1,4 @@ +set @save_query_cache_size=@@query_cache_size; SET GLOBAL query_cache_type= Off; SET LOCAL query_cache_type= Off; SET LOCAL query_cache_type= 0; @@ -1634,7 +1635,7 @@ show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 0 drop database db2; -set global query_cache_size=default; +set global query_cache_size=@save_query_cache_size; drop database if exists db1; drop database if exists db3; set GLOBAL query_cache_size=15*1024*1024; @@ -1669,7 +1670,7 @@ drop database db3; set GLOBAL query_cache_type=default; set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; -set GLOBAL query_cache_size=default; +set GLOBAL query_cache_size=@save_query_cache_size; set local query_cache_type=default; set GLOBAL query_cache_type=on; set local query_cache_type=on; @@ -1949,6 +1950,7 @@ select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type; # MDEV-4981: Account for queries handled by query-cache in # USER_STATISTICS (and in HOST_STATISTICS) # +set @save_userstat=@@global.userstat; SET GLOBAL userstat=1; set GLOBAL query_cache_size=1355776; FLUSH USER_STATISTICS; @@ -2089,7 +2091,7 @@ Qcache_hits 0 drop procedure p1; drop table t1; set GLOBAL query_cache_size=1355776; -SET GLOBAL userstat=default; +SET GLOBAL userstat=@save_userstat; End of 5.5 tests # # Bug#12977203: SERVER CRASH ON A DERIVED TABLE WITH QUERY CACHE ENABLED @@ -2189,5 +2191,4 @@ DROP FUNCTION foo; drop table t1; restore defaults SET GLOBAL query_cache_type= default; -SET GLOBAL query_cache_size= default; -SET LOCAL query_cache_type= default; +SET GLOBAL query_cache_size=@save_query_cache_size; diff --git a/mysql-test/r/query_cache_debug.result b/mysql-test/r/query_cache_debug.result index 2a54f073974..e365984986a 100644 --- a/mysql-test/r/query_cache_debug.result +++ b/mysql-test/r/query_cache_debug.result @@ -1,4 +1,6 @@ set global query_cache_type= ON; +set @save_query_cache_size=@@global.query_cache_size; +SET @save_concurrent_insert= @@GLOBAL.concurrent_insert; flush status; set query_cache_type=DEMAND; set global query_cache_size= 1024*768; @@ -37,8 +39,6 @@ set debug_sync= 'RESET'; set global query_cache_size= 0; use test; drop table t1; -SET @old_concurrent_insert= @@GLOBAL.concurrent_insert; -SET @old_query_cache_size= @@GLOBAL.query_cache_size; DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); @@ -214,7 +214,6 @@ SET GLOBAL query_cache_type= DEFAULT; # # Bug#56822: Add a thread state for sessions waiting on the query cache lock # -SET @old_query_cache_size= @@GLOBAL.query_cache_size; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); @@ -246,7 +245,7 @@ connection default; SET DEBUG_SYNC= 'RESET'; RESET QUERY CACHE; DROP TABLE t1; -SET GLOBAL query_cache_size= DEFAULT; +SET GLOBAL query_cache_size= @save_query_cache_size; SET GLOBAL query_cache_type= DEFAULT; # # MDEV-14526: MariaDB keeps crashing under load when @@ -270,7 +269,7 @@ SELECT DISTINCT id FROM t1 WHERE id BETWEEN 5603 AND 16218 ORDER BY k; connection default; set debug_sync="now WAIT_FOR parked"; connection con2; -SET GLOBAL query_cache_type= 0; +SET GLOBAL query_cache_type= 0;; connection default; set debug_sync="now SIGNAL go"; connection con1; @@ -281,6 +280,7 @@ disconnect con2; connection default; set debug_sync= 'RESET'; DROP TABLE t1; -SEt GLOBAL query_cache_size= DEFAULT; -SEt GLOBAL query_cache_type= DEFAULT; +SET GLOBAL query_cache_size=@save_query_cache_size; +SET GLOBAL query_cache_type= DEFAULT; +SET @@GLOBAL.concurrent_insert=@save_concurrent_insert; # End of 5.5 tests diff --git a/mysql-test/r/query_cache_innodb.result b/mysql-test/r/query_cache_innodb.result index 146a6fbc289..62424ac362b 100644 --- a/mysql-test/r/query_cache_innodb.result +++ b/mysql-test/r/query_cache_innodb.result @@ -1,3 +1,4 @@ +set @save_query_cache_size=@@query_cache_size; # # MDEV-12485: foreign key on delete cascade stale entries with # query cache enabled @@ -86,5 +87,5 @@ drop database `#mysql50#-`; SET NAMES default; FOUND 8 /\[ERROR\] Invalid \(old\?\) table or database name/ in mysqld.1.err set global query_cache_type=DEFAULT; -set global query_cache_size=DEFAULT; +set global query_cache_size=@save_query_cache_size; End of 10.2 tests diff --git a/mysql-test/r/query_cache_merge.result b/mysql-test/r/query_cache_merge.result index e31ab05b396..39e70abb51f 100644 --- a/mysql-test/r/query_cache_merge.result +++ b/mysql-test/r/query_cache_merge.result @@ -1,3 +1,5 @@ +set @save_query_cache_size=@@global.query_cache_size; +set @save_query_cache_type=@@global.query_cache_type; SET GLOBAL query_cache_type=ON; SET LOCAL query_cache_type=ON; SET @@global.query_cache_size=1355776; @@ -1692,7 +1694,7 @@ DROP TABLE t4; DROP TABLE t3; DROP TABLE t2; DROP TABLE t1; -SET @@global.query_cache_size = default; +SET @@global.query_cache_size = @save_query_cache_size; SET @@global.table_definition_cache = @save_table_definition_cache; -SET GLOBAL query_cache_type=default; +SET @@global.query_cache_type = @save_query_cache_type; End of 5.1 tests diff --git a/mysql-test/r/query_cache_notembedded.result b/mysql-test/r/query_cache_notembedded.result index bfb8f4d1ff7..29a091b68c1 100644 --- a/mysql-test/r/query_cache_notembedded.result +++ b/mysql-test/r/query_cache_notembedded.result @@ -409,9 +409,7 @@ disconnect bug30269; DROP USER 'bug30269'@'localhost'; set GLOBAL query_cache_type=ON; set LOCAL query_cache_type=ON; -set GLOBAL query_cache_limit=default; -set GLOBAL query_cache_min_res_unit=default; -set GLOBAL query_cache_size=default; +set GLOBAL query_cache_size=@query_cache_size_save; drop table if exists t1| create table t1 ( id char(16) not null default '', diff --git a/mysql-test/r/query_cache_with_views.result b/mysql-test/r/query_cache_with_views.result index 763113dbac3..8cb3d8cb297 100644 --- a/mysql-test/r/query_cache_with_views.result +++ b/mysql-test/r/query_cache_with_views.result @@ -1,5 +1,7 @@ drop table if exists t1,t2,v1,v2,v3; drop view if exists t1,t2,v1,v2,v3; +set @save_query_cache_size=@@global.query_cache_size; +set @save_query_cache_type=@@global.query_cache_type; set GLOBAL query_cache_type=ON; set LOCAL query_cache_type=ON; set GLOBAL query_cache_size=1355776; @@ -208,5 +210,5 @@ DROP TABLE t1; DROP TABLE t2; DROP VIEW t1_view; SET AUTOCOMMIT=DEFAULT; -set GLOBAL query_cache_size=default; -set GLOBAL query_cache_type=default; +set GLOBAL query_cache_size=@save_query_cache_size; +set GLOBAL query_cache_type=@save_query_cache_type; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index c23e92d798e..406e5c774cf 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1,3 +1,4 @@ +SET optimizer_use_condition_selectivity=1; drop table if exists t1, t2, t3, t10, t100; CREATE TABLE t1 ( event_date date DEFAULT '0000-00-00' NOT NULL, diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 138b5166680..e31901fdb26 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -1,5 +1,6 @@ set @mrr_icp_extra_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_use_condition_selectivity=1; drop table if exists t1, t2, t3, t10, t100; CREATE TABLE t1 ( event_date date DEFAULT '0000-00-00' NOT NULL, diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index 8d48d2f8848..171ef836bda 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -1,3 +1,4 @@ +call mtr.add_suppression("character set is multi-byte"); drop table if exists t1; create table t1 SELECT 1,"table 1"; repair table t1 use_frm; @@ -188,6 +189,8 @@ drop tables t1, t2; # .frm file from MySQL 3.23 # # Test with a saved table from 3.23 +SET @save_global_character_set_server= @@global.character_set_server; +set @@global.character_set_server=@@character_set_server; select count(*) from t1; ERROR HY000: Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM check table t1; @@ -208,6 +211,7 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +set @@global.character_set_server=@save_global_character_set_server; create table t1 (a blob); create view v1 as select * from t1; repair view v1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 010f37757e0..f4a6d17dc9c 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1,7 +1,6 @@ drop table if exists t1,t2,t3,t4,t11; drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; drop view if exists v1; -SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off'); set join_cache_level=1; CREATE TABLE t1 ( @@ -4894,7 +4893,7 @@ CREATE TABLE t5 (f1 int) ; INSERT INTO t5 VALUES (20),(5); CREATE TABLE t6(f1 int); INSERT INTO t6 VALUES (9),(7); -SET SESSION join_buffer_size = 2176; +SET @save_join_buffer_size=@@join_buffer_size,@@join_buffer_size = 2176; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra @@ -4954,7 +4953,7 @@ f1 f1 f1 f1 f2 f1 f1 3 9 NULL NULL NULL 5 7 7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 5 7 -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_buffer_size = @save_join_buffer_size; DROP TABLE t1,t2,t3,t4,t5,t6; # # Bug #698882: best equality substitution not applied to ref @@ -5336,7 +5335,7 @@ SELECT a, COUNT(*) FROM t1 WHERE 0; a COUNT(*) NULL 0 DROP TABLE t1; -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@save_optimizer_switch; # # LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed # in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 6eb0520b0ff..63c0968dc73 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -12,7 +12,6 @@ set @join_cache_level_for_select_test=@@join_cache_level; drop table if exists t1,t2,t3,t4,t11; drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; drop view if exists v1; -SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off'); set join_cache_level=@join_cache_level_for_select_test; CREATE TABLE t1 ( @@ -4905,7 +4904,7 @@ CREATE TABLE t5 (f1 int) ; INSERT INTO t5 VALUES (20),(5); CREATE TABLE t6(f1 int); INSERT INTO t6 VALUES (9),(7); -SET SESSION join_buffer_size = 2176; +SET @save_join_buffer_size=@@join_buffer_size,@@join_buffer_size = 2176; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra @@ -4965,7 +4964,7 @@ f1 f1 f1 f1 f2 f1 f1 3 9 NULL NULL NULL 5 7 7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 5 7 -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_buffer_size = @save_join_buffer_size; DROP TABLE t1,t2,t3,t4,t5,t6; # # Bug #698882: best equality substitution not applied to ref @@ -5347,7 +5346,7 @@ SELECT a, COUNT(*) FROM t1 WHERE 0; a COUNT(*) NULL 0 DROP TABLE t1; -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@save_optimizer_switch; # # LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed # in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK @@ -5562,9 +5561,6 @@ NULL drop table t1; End of 10.0 tests set join_cache_level=default; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 2 set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_select_test=NULL; set @join_cache_level_for_select_test=NULL; diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 010f37757e0..f4a6d17dc9c 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -1,7 +1,6 @@ drop table if exists t1,t2,t3,t4,t11; drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; drop view if exists v1; -SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off'); set join_cache_level=1; CREATE TABLE t1 ( @@ -4894,7 +4893,7 @@ CREATE TABLE t5 (f1 int) ; INSERT INTO t5 VALUES (20),(5); CREATE TABLE t6(f1 int); INSERT INTO t6 VALUES (9),(7); -SET SESSION join_buffer_size = 2176; +SET @save_join_buffer_size=@@join_buffer_size,@@join_buffer_size = 2176; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra @@ -4954,7 +4953,7 @@ f1 f1 f1 f1 f2 f1 f1 3 9 NULL NULL NULL 5 7 7 9 NULL NULL NULL 5 7 18 9 NULL NULL NULL 5 7 -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_buffer_size = @save_join_buffer_size; DROP TABLE t1,t2,t3,t4,t5,t6; # # Bug #698882: best equality substitution not applied to ref @@ -5336,7 +5335,7 @@ SELECT a, COUNT(*) FROM t1 WHERE 0; a COUNT(*) NULL 0 DROP TABLE t1; -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@save_optimizer_switch; # # LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed # in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 47efc8ba868..eb0ae313231 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -10,6 +10,8 @@ set use_stat_tables='preferably'; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; set @save_histogram_size=@@histogram_size; set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; set optimizer_use_condition_selectivity=3; create table t1 (a int); insert into t1 values @@ -1640,6 +1642,8 @@ drop function f1; # set @@optimizer_use_condition_selectivity=4; set @@use_stat_tables='preferably'; +set @save_histogram_size=@@histogram_size; +set @@histogram_size=0; create table t1 (a int, b int, key (b), key (a)); insert into t1 select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; @@ -1667,5 +1671,7 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) drop table t1; set use_stat_tables= @save_use_stat_tables; +set @@histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; # End of 10.1 tests +set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index b243dbbce5e..9773b122e75 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -13,6 +13,8 @@ set use_stat_tables='preferably'; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; set @save_histogram_size=@@histogram_size; set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; set optimizer_use_condition_selectivity=3; create table t1 (a int); insert into t1 values @@ -1650,6 +1652,8 @@ drop function f1; # set @@optimizer_use_condition_selectivity=4; set @@use_stat_tables='preferably'; +set @save_histogram_size=@@histogram_size; +set @@histogram_size=0; create table t1 (a int, b int, key (b), key (a)); insert into t1 select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; @@ -1677,8 +1681,10 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) drop table t1; set use_stat_tables= @save_use_stat_tables; +set @@histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; # End of 10.1 tests +set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; set @tmp_oucs= @@optimizer_use_condition_selectivity; @@ -1828,4 +1834,5 @@ drop view v1; # set use_stat_tables= @tmp_ust; set optimizer_use_condition_selectivity= @tmp_oucs; +set @@global.histogram_size=@save_histogram_size; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result index 406ca99a5ea..845c0f21e3e 100644 --- a/mysql-test/r/set_statement.result +++ b/mysql-test/r/set_statement.result @@ -1044,19 +1044,11 @@ set @save_slow_query_log= @@slow_query_log; set @save_log_output= @@log_output; set statement long_query_time=default for select @@long_query_time; @@long_query_time -10.000000 +DEFAULT set statement log_slow_filter=default for select @@log_slow_filter; -@@log_slow_filter -admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk set statement log_slow_verbosity=default for select @@log_slow_verbosity; -@@log_slow_verbosity - set statement log_slow_rate_limit=default for select @@log_slow_rate_limit; -@@log_slow_rate_limit -1 set statement slow_query_log=default for select @@slow_query_log; -@@slow_query_log -1 truncate table mysql.slow_log; set slow_query_log= 1; set global log_output='TABLE'; diff --git a/mysql-test/r/shm.result b/mysql-test/r/shm.result index 65187b6b19b..beaa2ecd3a7 100644 --- a/mysql-test/r/shm.result +++ b/mysql-test/r/shm.result @@ -1348,8 +1348,10 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 -SET @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='outer_join_with_cache=off'; +SET @local_optimizer_switch=@@optimizer_switch; +set @local_join_cache_level=@@join_cache_level; +set @@join_cache_level=2; +set optimizer_switch='outer_join_with_cache=off,join_cache_hashed=off'; insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname @@ -1425,7 +1427,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@local_optimizer_switch; select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -1434,6 +1436,7 @@ explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr= id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) +SET @@join_cache_level=@local_join_cache_level; select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index d75970f7b23..19228d6a2f6 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1,3 +1,5 @@ +SET @@global.character_set_server= @@session.character_set_server; +set @@sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"; drop table if exists t1,t2; drop table if exists t1aa,t2aa; drop database if exists mysqltest; @@ -438,9 +440,7 @@ table CREATE TABLE `table` ( DROP TABLE `table`; SET sql_quote_show_create= @old_sql_quote_show_create; SET sql_mode= @old_sql_mode; -select @@max_heap_table_size; -@@max_heap_table_size -1048576 +set @@max_heap_table_size=1048576; CREATE TABLE t1 ( a int(11) default NULL, KEY a USING BTREE (a) @@ -1597,3 +1597,4 @@ Level Code Message SHOW CONTRIBUTORS; SHOW WARNINGS; Level Code Message +SET @@global.character_set_server=@save_character_set_server; diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 4487528210f..de33c11a28f 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -1,3 +1,4 @@ +set @@global.character_set_server=@@session.character_set_server; connect con1root,localhost,root,,; connection con1root; use test; @@ -800,3 +801,4 @@ disconnect u1; connection default; DROP DATABASE u1; DROP USER u1@localhost; +set @@global.character_set_server=@save_character_set_server; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 310413f4924..d0a5a68732b 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1,3 +1,5 @@ +set @save_character_set_client=@@character_set_client; +set @save_userstat=@@global.userstat, @@global.userstat= 0; use test; drop table if exists t1,t2,t3,t4; drop view if exists v1; @@ -3342,7 +3344,8 @@ läbel1: begin end| call bug6063()| call bug7088_1()| call bug7088_2()| -set character set default| +set @@character_set_client=@save_character_set_client| +set @@character_set_results=@save_character_set_client| show create procedure bug6063| Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation bug6063 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug6063`() @@ -6250,7 +6253,8 @@ END| CALL p1(); áâä NULL -SET NAMES default; +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; DROP PROCEDURE p1; drop procedure if exists proc_25411_a; drop procedure if exists proc_25411_b; @@ -7643,6 +7647,7 @@ c1 c2 count(c3) 2012-03-01 02:00:00 3 1 DROP PROCEDURE p1; # End of 5.5 test +FLUSH USER_STATISTICS; CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; CALL sp; Table Op Msg_type Msg_text diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 8de36f55ecf..00faea58fd2 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -1357,8 +1357,10 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 -SET @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='outer_join_with_cache=off'; +SET @local_optimizer_switch=@@optimizer_switch; +set @local_join_cache_level=@@join_cache_level; +set @@join_cache_level=2; +set optimizer_switch='outer_join_with_cache=off,join_cache_hashed=off'; insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname @@ -1434,7 +1436,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@local_optimizer_switch; select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -1443,6 +1445,7 @@ explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr= id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) +SET @@join_cache_level=@local_join_cache_level; select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result index 4e37cc68a24..d298f8ec6e4 100644 --- a/mysql-test/r/ssl_compress.result +++ b/mysql-test/r/ssl_compress.result @@ -1354,8 +1354,10 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 -SET @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='outer_join_with_cache=off'; +SET @local_optimizer_switch=@@optimizer_switch; +set @local_join_cache_level=@@join_cache_level; +set @@join_cache_level=2; +set optimizer_switch='outer_join_with_cache=off,join_cache_hashed=off'; insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname @@ -1431,7 +1433,7 @@ explain select companynr,companyname from t4 left join t2 using (companynr) wher id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where -SET optimizer_switch=@save_optimizer_switch; +SET @@optimizer_switch=@local_optimizer_switch; select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -1440,6 +1442,7 @@ explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr= id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) +SET @@join_cache_level=@local_join_cache_level; select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 diff --git a/mysql-test/r/stat_tables-enospc.result b/mysql-test/r/stat_tables-enospc.result index f0d76f04eee..943cbca6bc5 100644 --- a/mysql-test/r/stat_tables-enospc.result +++ b/mysql-test/r/stat_tables-enospc.result @@ -2,6 +2,7 @@ call mtr.add_suppression("No space left on device"); create table t1 (a varchar(255), b varchar(255), c varchar(255)); set use_stat_tables=PREFERABLY, optimizer_use_condition_selectivity=3; set debug_dbug='+d,simulate_file_write_error'; +set @@max_heap_table_size=128*1024; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze Error Error writing file 'tmp-file' (Errcode: 28 "No space left on device") diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index a7e9256e39d..50767d7e3dc 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -5,6 +5,9 @@ select @@session.use_stat_tables; @@session.use_stat_tables COMPLEMENTARY set @save_use_stat_tables=@@use_stat_tables; +set @save_histogram_size=@@global.histogram_size; +set @@global.histogram_size=0,@@local.histogram_size=0; +set optimizer_use_condition_selectivity=1; set use_stat_tables='preferably'; DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; @@ -749,7 +752,6 @@ set names utf8; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; set optimizer_use_condition_selectivity=4; set use_stat_tables=preferably; -set @save_histogram_size= @@histogram_size; set histogram_size=255; create table t1 ( a varchar(255) character set utf8); insert into t1 values (REPEAT('Ó¥',255)), (REPEAT('ç',255)); @@ -800,3 +802,4 @@ set @@sql_mode= @save_sql_mode; set use_stat_tables=@save_use_stat_tables; set @@histogram_size= @save_histogram_size; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 7341ef84bca..70c8808efbb 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -8,6 +8,9 @@ select @@session.use_stat_tables; @@session.use_stat_tables COMPLEMENTARY set @save_use_stat_tables=@@use_stat_tables; +set @save_histogram_size=@@global.histogram_size; +set @@global.histogram_size=0,@@local.histogram_size=0; +set optimizer_use_condition_selectivity=1; set use_stat_tables='preferably'; DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; @@ -776,7 +779,6 @@ set names utf8; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; set optimizer_use_condition_selectivity=4; set use_stat_tables=preferably; -set @save_histogram_size= @@histogram_size; set histogram_size=255; create table t1 ( a varchar(255) character set utf8); insert into t1 values (REPEAT('Ó¥',255)), (REPEAT('ç',255)); @@ -827,5 +829,6 @@ set @@sql_mode= @save_sql_mode; set use_stat_tables=@save_use_stat_tables; set @@histogram_size= @save_histogram_size; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index 267d9da965e..e800c327db6 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -1,5 +1,7 @@ drop table if exists t1,t2; set @save_use_stat_tables=@@use_stat_tables; +set @save_histogram_size=@@global.histogram_size; +set @@global.histogram_size=0,@@local.histogram_size=0; DELETE FROM mysql.table_stats; DELETE FROM mysql.column_stats; DELETE FROM mysql.index_stats; @@ -1771,3 +1773,4 @@ DROP TABLE t1; # # End of 10.2 tests # +set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ea9d96f960f..d4d61eb4247 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5,7 +5,8 @@ set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set join_cache_level=1; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_use_condition_selectivity=1; select (select 2); (select 2) 2 diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 9de9d34a2f9..155d5ab2a50 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -383,7 +383,7 @@ key_code value_string DEALLOCATE PREPARE stmt; DROP VIEW v1; DROP TABLE t1,t2,t3; -set optimizer_switch=@subselect2_test_tmp; +set optimizer_switch=@save_optimizer_switch; create table t1 (a int); create table t2 (a int); create table t3(a int); diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 9ed067c0bdc..8d33e4e1606 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1,7 +1,7 @@ drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; -set @subselect3_tmp= @@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; set join_cache_level=1; +drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), @@ -1544,4 +1544,4 @@ Note 1276 Field or reference 'test.t2.v' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`i` AS `i`,`test`.`t2`.`v` AS `v`,<expr_cache><`test`.`t2`.`v`>((select count(distinct `test`.`t1`.`i`) from `test`.`t1` where `test`.`t1`.`v` = `test`.`t2`.`v`)) AS `subsel` from `test`.`t2` DROP TABLE t1,t2; End of 5.6 tests -set @@optimizer_switch=@subselect3_tmp; +# end of 10.2 test diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 2a1fc0df957..6d1e305bc49 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1,17 +1,10 @@ -set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='optimize_join_buffer_size=on'; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 6 -set @join_cache_level_for_subselect3_test=@@join_cache_level; drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; -set @subselect3_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; -set join_cache_level=@join_cache_level_for_subselect3_test; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), @@ -1554,10 +1547,4 @@ Note 1276 Field or reference 'test.t2.v' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`i` AS `i`,`test`.`t2`.`v` AS `v`,<expr_cache><`test`.`t2`.`v`>((select count(distinct `test`.`t1`.`i`) from `test`.`t1` where `test`.`t1`.`v` = `test`.`t2`.`v`)) AS `subsel` from `test`.`t2` DROP TABLE t1,t2; End of 5.6 tests -set @@optimizer_switch=@subselect3_tmp; -set join_cache_level=default; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 2 -set @@optimizer_switch=@save_optimizer_switch; -set @join_cache_level_for_subselect3_test=NULL; +# end of 10.2 test diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 158ec74ccf2..b97d26fb026 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1,5 +1,4 @@ set @subselect_mat_test_optimizer_switch_value='materialization=on,in_to_exists=off,semijoin=off'; -set @subselect_sj_mat_tmp= @@optimizer_switch; set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @optimizer_switch_local_default= @@optimizer_switch; @@ -185,7 +184,7 @@ a1 a2 1 - 02 2 - 02 select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='semijoin=off'; prepare st1 from @@ -218,7 +217,7 @@ execute st1; a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 -set @@optimizer_switch=@save_optimizer_switch; +set @@optimizer_switch=@local_optimizer_switch; explain extended select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -1186,7 +1185,7 @@ id select_type table type possible_keys key key_len ref rows Extra select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='materialization=off,in_to_exists=on'; explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); @@ -1223,7 +1222,7 @@ id select_type table type possible_keys key key_len ref rows Extra select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; min(a1) NULL -set @@optimizer_switch= @save_optimizer_switch; +set @@optimizer_switch= @local_optimizer_switch; drop table t1,t2; create table t1 (a char(2), b varchar(10)); insert into t1 values ('a', 'aaa'); @@ -1472,7 +1471,7 @@ INSERT INTO t1 (f1, f2) VALUES (10, 1.668); CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (1, 1.789); INSERT INTO t2 VALUES (13, 1.454); -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); @@ -1483,7 +1482,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) 2 -set @@optimizer_switch= @save_optimizer_switch; +set @@optimizer_switch= @local_optimizer_switch; DROP TABLE t1, t2; CREATE TABLE t1 ( pk int, @@ -1496,7 +1495,7 @@ PRIMARY KEY (pk) INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff'); -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); @@ -1511,7 +1510,7 @@ SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); pk 2 DROP TABLE t1, t2; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@local_optimizer_switch; # # BUG#50019: Wrong result for IN-subquery with materialization # @@ -1527,7 +1526,7 @@ i 2 3 4 -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set session optimizer_switch='materialization=off,in_to_exists=on'; select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5); i @@ -1535,7 +1534,7 @@ i 2 3 4 -set session optimizer_switch=@save_optimizer_switch; +set session optimizer_switch=@local_optimizer_switch; drop table t1, t2, t3; create table t0 (a int); insert into t0 values (0),(1),(2); @@ -1911,7 +1910,7 @@ DROP TABLE t1,t2,t3; # # BUG#939009: Crash with aggregate function in IN subquery # -SET @save_optimizer_switch=@@optimizer_switch; +SET @local_optimizer_switch=@@optimizer_switch; SET optimizer_switch='materialization=on,semijoin=on'; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (7,1), (4,2), (7,7); @@ -1940,7 +1939,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); a b -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2; # # BUG#946055: Crash with semijoin IN subquery when hash join is used @@ -1951,8 +1950,7 @@ CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c)); INSERT INTO t2 VALUES (4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'), (7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j'); -SET @save_optimizer_switch=@@optimizer_switch; -SET @save_join_cache_level=@@join_cache_level; +SET @local_optimizer_switch=@@optimizer_switch; SET join_cache_level=2; EXPLAIN SELECT a, c FROM t1, t2 @@ -1990,7 +1988,7 @@ a c 7 1 7 1 7 1 -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=@local_optimizer_switch; SET join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # @@ -2043,21 +2041,21 @@ insert into t2 values (1, 1), (2, 1), (3, 1), (4, 2), (5, 3), (6, 3), (7, 3); delete t2 from t2 where a_id in (select * from (select t1.id from t1 limit 2) as x); drop table t1,t2; # This must be at the end: -set optimizer_switch=@subselect_sj_mat_tmp; +set optimizer_switch=@save_optimizer_switch; set join_cache_level=@save_join_cache_level; # # MDEV-4908: Assertion `((Item_cond *) cond)->functype() == # ((Item_cond *) new_item)->functype()' fails on a query with # IN and equal conditions, AND/OR, materialization+semijoin # -SET @save_optimizer_switch=@@optimizer_switch; +SET @local_optimizer_switch=@@optimizer_switch; SET optimizer_switch = 'materialization=on,semijoin=on'; CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,3,5),(2,4,6); SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b ); pk a b drop table t1; -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=@local_optimizer_switch; # # MDEV-5011: ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries # @@ -2203,7 +2201,7 @@ WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); f2 foo -set @save_optimizer_switch= @@optimizer_switch; +set @local_optimizer_switch= @@optimizer_switch; set optimizer_switch='exists_to_in=off'; EXPLAIN SELECT sq1.f2 FROM t1 AS sq1 @@ -2221,7 +2219,7 @@ WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); f2 foo -set optimizer_switch= @save_optimizer_switch; +set optimizer_switch= @local_optimizer_switch; DROP TABLE t1; # # MDEV-12145: IN subquery used in WHERE of EXISTS subquery @@ -2232,7 +2230,7 @@ CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; INSERT INTO t2 VALUES (8),(7),(1); CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); -set @save_optimizer_switch= @@optimizer_switch; +set @local_optimizer_switch= @@optimizer_switch; set optimizer_switch='exists_to_in=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 @@ -2259,7 +2257,7 @@ WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); f1 6 -set optimizer_switch= @save_optimizer_switch; +set optimizer_switch= @local_optimizer_switch; DROP TABLE t1,t2,t3; # # MDEV-9686: IN subquery used in WHERE of a subquery from select list @@ -2291,7 +2289,7 @@ Warnings: Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1` # this checks the result set above -set @save_optimizer_switch= @@optimizer_switch; +set @local_optimizer_switch= @@optimizer_switch; set optimizer_switch= 'materialization=off,semijoin=off'; SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; @@ -2301,12 +2299,12 @@ pk f1 sq 3 3 5 4 6 5 5 3 5 -set optimizer_switch= @save_optimizer_switch; +set optimizer_switch= @local_optimizer_switch; DROP TABLE t1,t2; # # mdev-12838: scan of materialized of semi-join subquery in join # -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; CREATE TABLE t1 ( dispatch_group varchar(32), assignment_group varchar(32), @@ -2390,7 +2388,7 @@ ec70316637232000158bbfc8bcbe5d60 7b10fd2637232000158bbfc8bcbe5d30 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@local_optimizer_switch; # # MDEV-16751: Server crashes in st_join_table::cleanup or # TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 @@ -2453,7 +2451,7 @@ a 4189 8732 5 -set @save_optimizer_switch= @@optimizer_switch; +set @local_optimizer_switch= @@optimizer_switch; SET optimizer_switch='materialization=off'; SELECT t1.a FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; @@ -2465,7 +2463,7 @@ WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c a 5 drop procedure prepare_data; -set @@optimizer_switch= @save_optimizer_switch; +set @@optimizer_switch= @local_optimizer_switch; drop table t1,t2,t3; CREATE TABLE t1 ( id int NOT NULL, key(id)); INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result index a9f980fff5d..d7b2efd3580 100644 --- a/mysql-test/r/subselect_mat_cost.result +++ b/mysql-test/r/subselect_mat_cost.result @@ -1,4 +1,3 @@ -set @subselect_mat_cost=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set long_query_time=0.1; TEST GROUP 1: @@ -562,4 +561,4 @@ TEST GROUP 2: Tests of various combinations of optimizer switches, types of queries, available indexes, column nullability, constness of tables/predicates. ===================================================================== -set optimizer_switch=@subselect_mat_cost; +set optimizer_switch=default; diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 83ca3deaf28..fa9ab8bec1d 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -9,7 +9,8 @@ set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set join_cache_level=1; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_use_condition_selectivity=1; select (select 2); (select 2) 2 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 94388ac602e..c4d5b2813fd 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -12,7 +12,8 @@ set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set join_cache_level=@join_cache_level_for_subselect_test; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_use_condition_selectivity=1; select (select 2); (select 2) 2 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index bb4e1fde39a..fd1c5279f4d 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -8,7 +8,8 @@ set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set join_cache_level=@join_cache_level_for_subselect_test; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_use_condition_selectivity=1; select (select 2); (select 2) 2 diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 2476db25553..48d5bc974a7 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -11,7 +11,8 @@ set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set join_cache_level=@join_cache_level_for_subselect_test; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_use_condition_selectivity=1; select (select 2); (select 2) 2 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 15b4a9f3867..2df8073d8bf 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -8,7 +8,8 @@ set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set join_cache_level=@join_cache_level_for_subselect_test; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_use_condition_selectivity=1; select (select 2); (select 2) 2 diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 8466e37300a..262b4acdc89 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -7,7 +7,7 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'outer_join_with_cache=off'); SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'semijoin_with_cache=off'); set join_cache_level=1; -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); @@ -94,6 +94,7 @@ id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA 2 MATERIALIZED t10 index PRIMARY PRIMARY 4 NULL 10 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where `test`.`t1`.`A` = `<suBquery2>`.`pk`)))) where 1 +set @save_join_buffer_size=@@join_buffer_size; set join_buffer_size=8*1024; we shouldn't flatten if we're going to get a join of > MAX_TABLES. explain select * from @@ -186,7 +187,7 @@ a b a b 0 0 0 0 1 1 1 1 2 2 2 2 -set join_buffer_size=default; +set join_buffer_size=@save_join_buffer_size; prepare s1 from ' select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) @@ -333,7 +334,7 @@ INSERT INTO WORKS VALUES ('E3','P2',20); INSERT INTO WORKS VALUES ('E4','P2',20); INSERT INTO WORKS VALUES ('E4','P4',40); INSERT INTO WORKS VALUES ('E4','P5',80); -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@local_optimizer_switch; set optimizer_switch='materialization=off'; explain SELECT EMPNUM, EMPNAME FROM STAFF @@ -356,7 +357,7 @@ E1 Alice E2 Betty E3 Carmen E4 Don -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@local_optimizer_switch; drop table STAFF,WORKS,PROJ; # End of bug#45191 # @@ -444,7 +445,7 @@ COUNT(*) drop table t1, t2; drop view v1; drop procedure p1; -set SESSION optimizer_switch=@save_optimizer_switch; +set SESSION optimizer_switch=@local_optimizer_switch; # End of bug#46744 Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order @@ -595,7 +596,7 @@ v1field DROP TABLE t1,t2; DROP VIEW v1,v2; DROP PROCEDURE p1; -set SESSION optimizer_switch=@save_optimizer_switch; +set SESSION optimizer_switch=@local_optimizer_switch; # End of BUG#48834 Bug#49097 subquery with view generates wrong result with @@ -1076,7 +1077,7 @@ DROP TABLE t3; # LPBUG#602574: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, # uint): Assertion `join->best_read < # -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='materialization=off'; CREATE TABLE t1 ( varchar_key varchar(1) DEFAULT NULL, @@ -1110,7 +1111,7 @@ FROM t1 RIGHT JOIN t2 ON t1.varchar_key ) ); varchar_key -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2, t3; # # Bug#46692 "Crash occurring on queries with nested FROM subqueries @@ -1236,7 +1237,7 @@ DROP TABLE t1, t2, t3; # # BUG#53060: LooseScan semijoin strategy does not return all rows # -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='semijoin=on,materialization=off'; set optimizer_switch='firstmatch=off,loosescan=on'; CREATE TABLE t1 (i INTEGER); @@ -1253,7 +1254,7 @@ i 1 2 DROP TABLE t1, t2; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimizer_switch; # # BUG#49453: re-execution of prepared statement with view # and semijoin crashes @@ -1296,7 +1297,7 @@ create table t3 (a int); insert into t1 values(1),(1); insert into t2 values(1),(1),(1),(1); insert into t3 values(2),(2); -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='materialization=off'; set optimizer_switch='semijoin=off'; explain @@ -1320,7 +1321,7 @@ select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); a 1 1 -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2,t3; # # Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN)) @@ -1331,7 +1332,7 @@ CREATE TABLE t3 (a INT); INSERT INTO t1 VALUES(1),(1); INSERT INTO t2 VALUES(1),(1); INSERT INTO t3 VALUES(2),(2); -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimzer_switch=@@optimizer_switch; set optimizer_switch='semijoin=off,materialization=off'; EXPLAIN SELECT * FROM t1 @@ -1380,7 +1381,7 @@ FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); a 1 1 -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimzer_switch; DROP TABLE t1,t2,t3; # # BUG#52329 - Wrong result: subquery materialization, IN, @@ -1398,7 +1399,7 @@ INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), ('1 - 13', '2 - 23'); INSERT INTO t2b SELECT * FROM t2a; INSERT INTO t2c SELECT * FROM t2a; -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimzer_switch=@@optimizer_switch; set optimizer_switch='semijoin=off,materialization=on'; SELECT * FROM t1 WHERE (a1, a2) IN ( @@ -1420,7 +1421,7 @@ WHERE (a1, a2) IN ( SELECT b1, b2 FROM t2c WHERE b1 > '0'); a1 a2 1 - 12 2 - 22 -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimzer_switch; DROP TABLE t1,t2a,t2b,t2c; # # Bug#57623: subquery within before insert trigger causes crash (sj=on) @@ -1433,7 +1434,7 @@ INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); INSERT INTO ot2 VALUES(0),(2),(4),(6); INSERT INTO ot3 VALUES(0),(3),(6); INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimzer_switch=@@optimizer_switch; set optimizer_switch='semijoin=on'; set optimizer_switch='materialization=off'; explain SELECT * @@ -1537,7 +1538,7 @@ a a a 6 6 6 7 NULL NULL deallocate prepare s; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimzer_switch; DROP TABLE ot1, ot2, ot3, it1; # # Bug#59919/11766739: Crash in tmp_table_param::init() with semijoin=on @@ -1550,7 +1551,7 @@ INSERT INTO t2 VALUES (1,1), (2,1); INSERT INTO t3 VALUES (1,1), (2,1), (5,4), (7,3), (8,2), (8,1), (7,3), (9,5), (4,3), (7,2), (7,7), (3,1), (5,8), (9,7); -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimzer_switch=@@optimizer_switch; set optimizer_switch='semijoin=off,materialization=on'; EXPLAIN SELECT * FROM t3 @@ -1588,7 +1589,7 @@ f1 f2 2 1 8 1 3 1 -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimzer_switch; DROP TABLE t1, t2, t3 ; # # @@ -2981,7 +2982,7 @@ DROP TABLE t1,t2,t3; SET @tmp_mdev5059=@@join_cache_level; SET join_cache_level = 3; set @tmp_os_mdev5059= @@optimizer_switch; -set optimizer_switch=default; +set optimizer_switch=@local_optimizer_switch; CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,4),(2,5); CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM; diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 948be5766a2..fe3511d294c 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -618,7 +618,7 @@ set @save_join_buffer_size=@@join_buffer_size; set join_buffer_size=1; select * from t2 where filler1 in ( select a from t1); a b c filler1 -set join_buffer_size=default; +set join_buffer_size=@save_join_buffer_size; drop table t1, t2; create table t1 (a int not null); drop procedure if exists p1; diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 2955307eb86..168a23a6902 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -632,7 +632,7 @@ set @save_join_buffer_size=@@join_buffer_size; set join_buffer_size=1; select * from t2 where filler1 in ( select a from t1); a b c filler1 -set join_buffer_size=default; +set join_buffer_size=@save_join_buffer_size; drop table t1, t2; create table t1 (a int not null); drop procedure if exists p1; @@ -1436,10 +1436,6 @@ v v set optimizer_switch=@tmp_optimizer_switch; set join_cache_level=default; DROP TABLE t1,t2; -set join_cache_level=default; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 2 set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_subselect_sj2_test=NULL; set @join_cache_level_subselect_sj2_test=NULL; diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 281a4a44e91..d37cb826ce5 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -620,7 +620,7 @@ set @save_join_buffer_size=@@join_buffer_size; set join_buffer_size=1; select * from t2 where filler1 in ( select a from t1); a b c filler1 -set join_buffer_size=default; +set join_buffer_size=@save_join_buffer_size; drop table t1, t2; create table t1 (a int not null); drop procedure if exists p1; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index ec441d6724d..3c52dc515f3 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -5,10 +5,8 @@ set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; set @@optimizer_switch='join_cache_hashed=off'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @save_join_cache_level=@@join_cache_level; set join_cache_level=6; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 6 set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch; set @join_cache_level_for_subselect_sj_test=@@join_cache_level; drop table if exists t0, t1, t2, t3, t4, t5, t10, t11, t12; @@ -20,7 +18,7 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'outer_join_with_cache=off'); SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'semijoin_with_cache=off'); set join_cache_level=@join_cache_level_for_subselect_sj_test; -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); @@ -107,6 +105,7 @@ id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA 2 MATERIALIZED t10 index PRIMARY PRIMARY 4 NULL 10 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where `test`.`t1`.`A` = `<suBquery2>`.`pk`)))) where 1 +set @save_join_buffer_size=@@join_buffer_size; set join_buffer_size=8*1024; we shouldn't flatten if we're going to get a join of > MAX_TABLES. explain select * from @@ -199,7 +198,7 @@ a b a b 0 0 0 0 1 1 1 1 2 2 2 2 -set join_buffer_size=default; +set join_buffer_size=@save_join_buffer_size; prepare s1 from ' select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) @@ -346,7 +345,7 @@ INSERT INTO WORKS VALUES ('E3','P2',20); INSERT INTO WORKS VALUES ('E4','P2',20); INSERT INTO WORKS VALUES ('E4','P4',40); INSERT INTO WORKS VALUES ('E4','P5',80); -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@local_optimizer_switch; set optimizer_switch='materialization=off'; explain SELECT EMPNUM, EMPNAME FROM STAFF @@ -369,7 +368,7 @@ E1 Alice E2 Betty E3 Carmen E4 Don -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@local_optimizer_switch; drop table STAFF,WORKS,PROJ; # End of bug#45191 # @@ -457,7 +456,7 @@ COUNT(*) drop table t1, t2; drop view v1; drop procedure p1; -set SESSION optimizer_switch=@save_optimizer_switch; +set SESSION optimizer_switch=@local_optimizer_switch; # End of bug#46744 Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order @@ -608,7 +607,7 @@ v1field DROP TABLE t1,t2; DROP VIEW v1,v2; DROP PROCEDURE p1; -set SESSION optimizer_switch=@save_optimizer_switch; +set SESSION optimizer_switch=@local_optimizer_switch; # End of BUG#48834 Bug#49097 subquery with view generates wrong result with @@ -1089,7 +1088,7 @@ DROP TABLE t3; # LPBUG#602574: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint, # uint): Assertion `join->best_read < # -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='materialization=off'; CREATE TABLE t1 ( varchar_key varchar(1) DEFAULT NULL, @@ -1123,7 +1122,7 @@ FROM t1 RIGHT JOIN t2 ON t1.varchar_key ) ); varchar_key -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2, t3; # # Bug#46692 "Crash occurring on queries with nested FROM subqueries @@ -1249,7 +1248,7 @@ DROP TABLE t1, t2, t3; # # BUG#53060: LooseScan semijoin strategy does not return all rows # -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='semijoin=on,materialization=off'; set optimizer_switch='firstmatch=off,loosescan=on'; CREATE TABLE t1 (i INTEGER); @@ -1266,7 +1265,7 @@ i 1 2 DROP TABLE t1, t2; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimizer_switch; # # BUG#49453: re-execution of prepared statement with view # and semijoin crashes @@ -1309,7 +1308,7 @@ create table t3 (a int); insert into t1 values(1),(1); insert into t2 values(1),(1),(1),(1); insert into t3 values(2),(2); -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='materialization=off'; set optimizer_switch='semijoin=off'; explain @@ -1333,7 +1332,7 @@ select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a); a 1 1 -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2,t3; # # Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN)) @@ -1344,7 +1343,7 @@ CREATE TABLE t3 (a INT); INSERT INTO t1 VALUES(1),(1); INSERT INTO t2 VALUES(1),(1); INSERT INTO t3 VALUES(2),(2); -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimzer_switch=@@optimizer_switch; set optimizer_switch='semijoin=off,materialization=off'; EXPLAIN SELECT * FROM t1 @@ -1393,7 +1392,7 @@ FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a); a 1 1 -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimzer_switch; DROP TABLE t1,t2,t3; # # BUG#52329 - Wrong result: subquery materialization, IN, @@ -1411,7 +1410,7 @@ INSERT INTO t2a VALUES ('1 - 11', '2 - 21'), ('1 - 13', '2 - 23'); INSERT INTO t2b SELECT * FROM t2a; INSERT INTO t2c SELECT * FROM t2a; -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimzer_switch=@@optimizer_switch; set optimizer_switch='semijoin=off,materialization=on'; SELECT * FROM t1 WHERE (a1, a2) IN ( @@ -1433,7 +1432,7 @@ WHERE (a1, a2) IN ( SELECT b1, b2 FROM t2c WHERE b1 > '0'); a1 a2 1 - 12 2 - 22 -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimzer_switch; DROP TABLE t1,t2a,t2b,t2c; # # Bug#57623: subquery within before insert trigger causes crash (sj=on) @@ -1446,7 +1445,7 @@ INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); INSERT INTO ot2 VALUES(0),(2),(4),(6); INSERT INTO ot3 VALUES(0),(3),(6); INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7); -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimzer_switch=@@optimizer_switch; set optimizer_switch='semijoin=on'; set optimizer_switch='materialization=off'; explain SELECT * @@ -1550,7 +1549,7 @@ a a a 5 NULL NULL 7 NULL NULL deallocate prepare s; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimzer_switch; DROP TABLE ot1, ot2, ot3, it1; # # Bug#59919/11766739: Crash in tmp_table_param::init() with semijoin=on @@ -1563,7 +1562,7 @@ INSERT INTO t2 VALUES (1,1), (2,1); INSERT INTO t3 VALUES (1,1), (2,1), (5,4), (7,3), (8,2), (8,1), (7,3), (9,5), (4,3), (7,2), (7,7), (3,1), (5,8), (9,7); -set @save_optimizer_switch=@@optimizer_switch; +set @tmp_optimzer_switch=@@optimizer_switch; set optimizer_switch='semijoin=off,materialization=on'; EXPLAIN SELECT * FROM t3 @@ -1601,7 +1600,7 @@ f1 f2 2 1 8 1 3 1 -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimzer_switch; DROP TABLE t1, t2, t3 ; # # @@ -2995,7 +2994,7 @@ DROP TABLE t1,t2,t3; SET @tmp_mdev5059=@@join_cache_level; SET join_cache_level = 3; set @tmp_os_mdev5059= @@optimizer_switch; -set optimizer_switch=default; +set optimizer_switch=@local_optimizer_switch; CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,4),(2,5); CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM; @@ -3447,10 +3446,7 @@ set optimizer_switch=@tmp_optimizer_switch; set join_cache_level=@tmp_join_cache_level; DROP TABLE t1,t2; # End -set join_cache_level=default; -show variables like 'join_cache_level'; -Variable_name Value -join_cache_level 2 +set join_cache_level=@save_join_cache_level; set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_subselect_sj_test=NULL; set @join_cache_level_subselect_sj_test=NULL; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index aec9d5ae2b8..faa809b81fb 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1,4 +1,3 @@ -set @subselect_sj_mat_tmp= @@optimizer_switch; set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @optimizer_switch_local_default= @@optimizer_switch; @@ -195,7 +194,7 @@ a1 a2 1 - 02 2 - 02 select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='semijoin=off'; prepare st1 from @@ -228,7 +227,7 @@ execute st1; a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 -set @@optimizer_switch=@save_optimizer_switch; +set @@optimizer_switch=@local_optimizer_switch; explain extended select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -1221,7 +1220,7 @@ id select_type table type possible_keys key key_len ref rows Extra select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='materialization=off,in_to_exists=on'; explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); @@ -1257,7 +1256,7 @@ id select_type table type possible_keys key key_len ref rows Extra select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; min(a1) NULL -set @@optimizer_switch= @save_optimizer_switch; +set @@optimizer_switch= @local_optimizer_switch; drop table t1,t2; create table t1 (a char(2), b varchar(10)); insert into t1 values ('a', 'aaa'); @@ -1509,7 +1508,7 @@ INSERT INTO t1 (f1, f2) VALUES (10, 1.668); CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (1, 1.789); INSERT INTO t2 VALUES (13, 1.454); -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); @@ -1520,7 +1519,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) 2 -set @@optimizer_switch= @save_optimizer_switch; +set @@optimizer_switch= @local_optimizer_switch; DROP TABLE t1, t2; CREATE TABLE t1 ( pk int, @@ -1533,7 +1532,7 @@ PRIMARY KEY (pk) INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff'); -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); @@ -1548,7 +1547,7 @@ SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); pk 2 DROP TABLE t1, t2; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@local_optimizer_switch; # # BUG#50019: Wrong result for IN-subquery with materialization # @@ -1564,7 +1563,7 @@ i 2 3 4 -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; set session optimizer_switch='materialization=off,in_to_exists=on'; select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5); i @@ -1572,7 +1571,7 @@ i 3 2 1 -set session optimizer_switch=@save_optimizer_switch; +set session optimizer_switch=@local_optimizer_switch; drop table t1, t2, t3; create table t0 (a int); insert into t0 values (0),(1),(2); @@ -1949,7 +1948,7 @@ DROP TABLE t1,t2,t3; # # BUG#939009: Crash with aggregate function in IN subquery # -SET @save_optimizer_switch=@@optimizer_switch; +SET @local_optimizer_switch=@@optimizer_switch; SET optimizer_switch='materialization=on,semijoin=on'; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (7,1), (4,2), (7,7); @@ -1978,7 +1977,7 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); a b -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2; # # BUG#946055: Crash with semijoin IN subquery when hash join is used @@ -1989,8 +1988,7 @@ CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c)); INSERT INTO t2 VALUES (4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'), (7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j'); -SET @save_optimizer_switch=@@optimizer_switch; -SET @save_join_cache_level=@@join_cache_level; +SET @local_optimizer_switch=@@optimizer_switch; SET join_cache_level=2; EXPLAIN SELECT a, c FROM t1, t2 @@ -2030,7 +2028,7 @@ a c 7 1 7 1 7 1 -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=@local_optimizer_switch; SET join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; # @@ -2083,21 +2081,21 @@ insert into t2 values (1, 1), (2, 1), (3, 1), (4, 2), (5, 3), (6, 3), (7, 3); delete t2 from t2 where a_id in (select * from (select t1.id from t1 limit 2) as x); drop table t1,t2; # This must be at the end: -set optimizer_switch=@subselect_sj_mat_tmp; +set optimizer_switch=@save_optimizer_switch; set join_cache_level=@save_join_cache_level; # # MDEV-4908: Assertion `((Item_cond *) cond)->functype() == # ((Item_cond *) new_item)->functype()' fails on a query with # IN and equal conditions, AND/OR, materialization+semijoin # -SET @save_optimizer_switch=@@optimizer_switch; +SET @local_optimizer_switch=@@optimizer_switch; SET optimizer_switch = 'materialization=on,semijoin=on'; CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,3,5),(2,4,6); SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b ); pk a b drop table t1; -SET optimizer_switch=@save_optimizer_switch; +SET optimizer_switch=@local_optimizer_switch; # # MDEV-5011: ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries # @@ -2243,7 +2241,7 @@ WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); f2 foo -set @save_optimizer_switch= @@optimizer_switch; +set @local_optimizer_switch= @@optimizer_switch; set optimizer_switch='exists_to_in=off'; EXPLAIN SELECT sq1.f2 FROM t1 AS sq1 @@ -2261,7 +2259,7 @@ WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); f2 foo -set optimizer_switch= @save_optimizer_switch; +set optimizer_switch= @local_optimizer_switch; DROP TABLE t1; # # MDEV-12145: IN subquery used in WHERE of EXISTS subquery @@ -2272,7 +2270,7 @@ CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; INSERT INTO t2 VALUES (8),(7),(1); CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); -set @save_optimizer_switch= @@optimizer_switch; +set @local_optimizer_switch= @@optimizer_switch; set optimizer_switch='exists_to_in=off'; SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 @@ -2299,7 +2297,7 @@ WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); f1 6 -set optimizer_switch= @save_optimizer_switch; +set optimizer_switch= @local_optimizer_switch; DROP TABLE t1,t2,t3; # # MDEV-9686: IN subquery used in WHERE of a subquery from select list @@ -2331,7 +2329,7 @@ Warnings: Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1` # this checks the result set above -set @save_optimizer_switch= @@optimizer_switch; +set @local_optimizer_switch= @@optimizer_switch; set optimizer_switch= 'materialization=off,semijoin=off'; SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; @@ -2341,12 +2339,12 @@ pk f1 sq 3 3 5 4 6 5 5 3 5 -set optimizer_switch= @save_optimizer_switch; +set optimizer_switch= @local_optimizer_switch; DROP TABLE t1,t2; # # mdev-12838: scan of materialized of semi-join subquery in join # -set @save_optimizer_switch=@@optimizer_switch; +set @local_optimizer_switch=@@optimizer_switch; CREATE TABLE t1 ( dispatch_group varchar(32), assignment_group varchar(32), @@ -2430,7 +2428,7 @@ ec70316637232000158bbfc8bcbe5d60 7b10fd2637232000158bbfc8bcbe5d30 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@local_optimizer_switch; # # MDEV-16751: Server crashes in st_join_table::cleanup or # TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 @@ -2493,7 +2491,7 @@ a 4189 8732 5 -set @save_optimizer_switch= @@optimizer_switch; +set @local_optimizer_switch= @@optimizer_switch; SET optimizer_switch='materialization=off'; SELECT t1.a FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; @@ -2505,7 +2503,7 @@ WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c a 5 drop procedure prepare_data; -set @@optimizer_switch= @save_optimizer_switch; +set @@optimizer_switch= @local_optimizer_switch; drop table t1,t2,t3; CREATE TABLE t1 ( id int NOT NULL, key(id)); INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); diff --git a/mysql-test/r/truncate-stale-6500.result b/mysql-test/r/truncate-stale-6500.result index b6222716953..be8d0aa7143 100644 --- a/mysql-test/r/truncate-stale-6500.result +++ b/mysql-test/r/truncate-stale-6500.result @@ -1,3 +1,4 @@ +set @save_query_cache_size=@@query_cache_size; SET GLOBAL query_cache_size=1024*1024*8; CREATE TABLE `test` ( `uniqueId` INT NOT NULL, @@ -30,4 +31,4 @@ uniqueId partitionId SELECT SQL_NO_CACHE * FROM `test`; uniqueId partitionId DROP TABLE test; -SET GLOBAL query_cache_size=DEFAULT; +SET GLOBAL query_cache_size=@save_query_cache_size; diff --git a/mysql-test/r/udf_query_cache.result b/mysql-test/r/udf_query_cache.result index 8c25c127012..38bb78a8007 100644 --- a/mysql-test/r/udf_query_cache.result +++ b/mysql-test/r/udf_query_cache.result @@ -1,6 +1,7 @@ drop table if exists t1; CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; create table t1 (a char); +set @save_query_cache_size=@@query_cache_size; set GLOBAL query_cache_size=1355776; reset query cache; flush status; @@ -22,4 +23,4 @@ Variable_name Value Qcache_queries_in_cache 0 drop table t1; drop function metaphon; -set GLOBAL query_cache_size=default; +set GLOBAL query_cache_size=@save_query_cache_size; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 8741633c838..c02e590490e 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -560,12 +560,15 @@ a b 2 2 3 3 4 4 +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off',@@optimizer_use_condition_selectivity=1; explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index 2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index; Using join buffer (flat, BNL join) NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity,@@optimizer_switch=@save_optimizer_switch; explain (select * from t1 where a=1) union (select * from t1 where b=1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 @@ -1368,9 +1371,7 @@ t3 CREATE TABLE `t3` ( `left(a,100000000)` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop tables t1,t2,t3; -SELECT @tmp_max:= @@global.max_allowed_packet; -@tmp_max:= @@global.max_allowed_packet -16777216 +SET @tmp_max= @@global.max_allowed_packet; SET @@global.max_allowed_packet=25000000; Warnings: Warning 1292 Truncated incorrect max_allowed_packet value: '25000000' diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result index 6066da2481a..0d8732399c7 100644 --- a/mysql-test/r/user_var-binlog.result +++ b/mysql-test/r/user_var-binlog.result @@ -33,7 +33,7 @@ SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.uniq SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; INSERT INTO t1 VALUES(@`a b`) diff --git a/mysql-test/r/userstat-badlogin-4824.result b/mysql-test/r/userstat-badlogin-4824.result index 6a38c2bd601..757b20e0ce0 100644 --- a/mysql-test/r/userstat-badlogin-4824.result +++ b/mysql-test/r/userstat-badlogin-4824.result @@ -1,3 +1,4 @@ +set @save_userstat=@@global.userstat; create user foo@localhost identified by 'foo'; flush user_statistics; set global userstat=1; @@ -18,4 +19,4 @@ select user, bytes_received from information_schema.user_statistics where user = user bytes_received foo 18 drop user foo@localhost; -set global userstat=0; +set global userstat=@save_userstat; diff --git a/mysql-test/r/variables-notembedded.result b/mysql-test/r/variables-notembedded.result index a8bf7ecc429..70a068cd802 100644 --- a/mysql-test/r/variables-notembedded.result +++ b/mysql-test/r/variables-notembedded.result @@ -33,12 +33,12 @@ ERROR HY000: Variable 'log_slave_updates' is a read only variable # SHOW VARIABLES like 'relay_log'; Variable_name Value -relay_log mysqld-relay-bin +relay_log XXX-relay-bin SELECT @@session.relay_log; ERROR HY000: Variable 'relay_log' is a GLOBAL variable SELECT @@global.relay_log; @@global.relay_log -mysqld-relay-bin +XXX-relay-bin SET @@session.relay_log= 'x'; ERROR HY000: Variable 'relay_log' is a read only variable SET @@global.relay_log= 'x'; @@ -46,12 +46,12 @@ ERROR HY000: Variable 'relay_log' is a read only variable # SHOW VARIABLES like 'relay_log_basename'; Variable_name Value -relay_log_basename MYSQLTEST_VARDIR/mysqld.1/data/mysqld-relay-bin +relay_log_basename MYSQLTEST_VARDIR/mysqld.1/data/XXX-relay-bin SELECT @@session.relay_log_basename; ERROR HY000: Variable 'relay_log_basename' is a GLOBAL variable SELECT @@global.relay_log_basename; @@global.relay_log_basename -MYSQLTEST_VARDIR/mysqld.1/data/mysqld-relay-bin +MYSQLTEST_VARDIR/mysqld.1/data/XXX-relay-bin SET @@session.relay_log_basename= 'x'; ERROR HY000: Variable 'relay_log_basename' is a read only variable SET @@global.relay_log_basename= 'x'; @@ -72,12 +72,12 @@ ERROR HY000: Variable 'log_bin_basename' is a read only variable # SHOW VARIABLES like 'relay_log_index'; Variable_name Value -relay_log_index MYSQLTEST_VARDIR/mysqld.1/data/mysqld-relay-bin.index +relay_log_index MYSQLTEST_VARDIR/mysqld.1/data/XXX-relay-bin.index SELECT @@session.relay_log_index; ERROR HY000: Variable 'relay_log_index' is a GLOBAL variable SELECT @@global.relay_log_index; @@global.relay_log_index -MYSQLTEST_VARDIR/mysqld.1/data/mysqld-relay-bin.index +MYSQLTEST_VARDIR/mysqld.1/data/XXX-relay-bin.index SET @@session.relay_log_index= 'x'; ERROR HY000: Variable 'relay_log_index' is a read only variable SET @@global.relay_log_index= 'x'; @@ -152,6 +152,7 @@ ERROR HY000: Variable 'slave_skip_errors' is a read only variable # ARE NOT BEING HONORED # CREATE TABLE t1 (a MEDIUMTEXT); +set @save_max_allowed_packet=@@global.max_allowed_packet, @save_net_buffer_length=@@net_buffer_length; SET GLOBAL max_allowed_packet=2048; Warnings: Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length' @@ -170,7 +171,7 @@ connection default; disconnect con1; SELECT LENGTH(a) FROM t1; LENGTH(a) -SET GLOBAL max_allowed_packet=default; -SET GLOBAL net_buffer_length=default; +SET GLOBAL max_allowed_packet=@save_max_allowed_packet; +SET GLOBAL net_buffer_length=@save_net_buffer_length; DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 7549832e09d..aa3bbc1435c 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -27,7 +27,8 @@ set @my_thread_cache_size =@@global.thread_cache_size; set @my_max_allowed_packet =@@global.max_allowed_packet; set @my_delay_key_write =@@global.delay_key_write; set @my_join_buffer_size =@@global.join_buffer_size; -set @my_log_warnings =@@global.log_warnings; +set @my_log_warnings =@@global.log_warnings; +set @my_local_infile =@@global.local_infile; set @`test`=1; select @test, @`test`, @TEST, @`TEST`, @"teSt"; @test @`test` @TEST @`TEST` @"teSt" @@ -522,9 +523,6 @@ Warning 1292 Truncated incorrect max_heap_table_size value: '100' set max_join_size=100; set max_sort_length=100; set global max_user_connections=100; -select @@max_user_connections; -@@max_user_connections -100 set global max_write_lock_count=100; set myisam_sort_buffer_size=100; Warnings: @@ -648,13 +646,14 @@ select * from information_schema.global_variables where variable_name like 'myis VARIABLE_NAME VARIABLE_VALUE MYISAM_MAX_SORT_FILE_SIZE MAX_FILE_SIZE set global myisam_max_sort_file_size=default; +set @@global.max_user_connections=10, @@local.max_join_size=1000; select @@global.max_user_connections,@@local.max_join_size; @@global.max_user_connections @@local.max_join_size -100 100 +10 1000 set @svc=@@global.max_user_connections, @svj=@@local.max_join_size; select @@global.max_user_connections,@@local.max_join_size; @@global.max_user_connections @@local.max_join_size -100 100 +10 1000 set @@global.max_user_connections=111,@@local.max_join_size=222; select @@global.max_user_connections,@@local.max_join_size; @@global.max_user_connections @@local.max_join_size @@ -666,12 +665,13 @@ select @@global.max_user_connections,@@local.max_join_size; set @@global.max_user_connections=@svc, @@local.max_join_size=@svj; select @@global.max_user_connections,@@local.max_join_size; @@global.max_user_connections @@local.max_join_size -100 100 +10 1000 set @a=1, @b=2; set @a=@b, @b=@a; select @a, @b; @a @b 2 1 +set @@global.max_user_connections=@my_max_user_connections; set @@global.global.key_buffer_size= 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key_buffer_size= 1' at line 1 set GLOBAL global.key_buffer_size= 1; @@ -1061,12 +1061,13 @@ set global net_write_timeout =@my_net_write_timeout; set global net_read_timeout =@my_net_read_timeout; set global server_id =@my_server_id; set global slow_launch_time =@my_slow_launch_time; -set global default_storage_engine =@my_storage_engine; +set global default_storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; -set global max_allowed_packet = default; +set global max_allowed_packet =@my_max_allowed_packet; set global delay_key_write =@my_delay_key_write; set global join_buffer_size =@my_join_buffer_size; set global log_warnings =@my_log_warnings; +set global local_infile =@my_local_infile; show global variables where Variable_name='table_definition_cache'; Variable_name Value table_definition_cache # @@ -1136,12 +1137,12 @@ ERROR HY000: Variable 'ft_stopword_file' is a read only variable # SHOW VARIABLES like 'back_log'; Variable_name Value -back_log 80 +back_log # SELECT @@session.back_log; ERROR HY000: Variable 'back_log' is a GLOBAL variable -SELECT @@global.back_log; -@@global.back_log -80 +SELECT @@global.back_log > 0; +@@global.back_log > 0 +1 SET @@session.back_log= 7; ERROR HY000: Variable 'back_log' is a read only variable SET @@global.back_log= 7; @@ -1492,12 +1493,12 @@ SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; # # Bug #37168 : Missing variable - skip_name_resolve # -SELECT @@skip_name_resolve; -@@skip_name_resolve -0 +SELECT @@skip_name_resolve >= 0; +@@skip_name_resolve >= 0 +1 SHOW VARIABLES LIKE 'skip_name_resolve'; Variable_name Value -skip_name_resolve OFF +skip_name_resolve # # # Bug #43233 : Some server variables are clipped during "update," # not "check" stage @@ -1531,9 +1532,9 @@ SET @@global.max_binlog_cache_size=DEFAULT; SET @@global.max_join_size=DEFAULT; SET @@global.key_buffer_size=@kbs; SET @@global.key_cache_block_size=@kcbs; -select @@max_long_data_size; -@@max_long_data_size -16777216 +select @@max_long_data_size > 0; +@@max_long_data_size > 0 +1 # # Bug#11766424 59527: # Assert in DECIMAL_BIN_SIZE: diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 35140eebfce..b9bde3adefd 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1,4 +1,3 @@ -SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; create view v1 (c,d) as select a,b from t1; ERROR 42S02: Table 'test.t1' doesn't exist @@ -3773,76 +3772,76 @@ a 2 CREATE VIEW v2 AS SELECT t1.a FROM t1, v1 AS a; REPLACE INTO v2(a) SELECT 1 FROM t1,t1 AS c; -SELECT * FROM v2; +SELECT * FROM v2 order by 1; a 1 -2 1 -2 1 -2 1 2 +2 +2 +2 REPLACE INTO v2(a) SELECT 3 FROM t1,t1 AS c; -SELECT * FROM v2; +SELECT * FROM v2 order by 1; a 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 3 INSERT INTO v2(a) SELECT 1 FROM t1,t1 AS c ON DUPLICATE KEY UPDATE `v2`.`a`= 1; -SELECT * FROM v2; +SELECT * FROM v2 order by 1; a 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 -2 -3 1 2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 3 DROP VIEW v1; DROP VIEW v2; diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result index f49207c0e41..f4d54a78a5c 100644 --- a/mysql-test/r/xtradb_mrr.result +++ b/mysql-test/r/xtradb_mrr.result @@ -742,6 +742,7 @@ count(*) sum(table1.col_int_key*table2.pk) drop table t1,t2; set join_cache_level=@my_save_join_cache_level; set join_buffer_size=@my_save_join_buffer_size; +set optimizer_switch=@save_optimizer_switch; # # BUG#665669: Result differences on query re-execution # |