Merge branch '10.3' into 10.4

1 files changed, 29 insertions, 29 deletions

diff --git a/mysql-test/main/subselect_sj_mat.test b/mysql-test/main/subselect_sj_mat.test
index 91a67faba57..1de8701ecbb 100644
--- a/mysql-test/main/subselect_sj_mat.test
+++ b/mysql-test/main/subselect_sj_mat.test
@@ -3,7 +3,8 @@
 # (WL#1110: Subquery optimization: materialization)
 #
 
-set @subselect_sj_mat_tmp= @@optimizer_switch;
+--source include/default_optimizer_switch.inc
+
 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;
@@ -136,7 +137,7 @@ select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
 
 # test re-optimization/re-execution with different execution methods
 # prepare once, exec with different modes
-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
@@ -158,7 +159,7 @@ execute st1;
 set @@optimizer_switch=@optimizer_switch_local_default;
 set @@optimizer_switch='materialization=off,in_to_exists=on';
 execute st1;
-set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch=@local_optimizer_switch;
 
 # materialize the result of ORDER BY
 # No longer really happens as the optimizer is now smart enough not to sort in this case
@@ -848,7 +849,7 @@ insert into t1 values (5);
 explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 # Query with group by, executed via IN=>EXISTS
-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);
@@ -870,7 +871,7 @@ select min(a1) from t1 where 7 in (select b1 from t2);
 -- echo # but when we go around MWL#90 code, the problem still shows up:
 explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
 select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
-set @@optimizer_switch= @save_optimizer_switch;
+set @@optimizer_switch= @local_optimizer_switch;
 drop table t1,t2;
 
 #
@@ -1156,12 +1157,12 @@ 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);
 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
-set @@optimizer_switch= @save_optimizer_switch;
+set @@optimizer_switch= @local_optimizer_switch;
 
 DROP TABLE t1, t2;
 
@@ -1181,14 +1182,14 @@ 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);
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
 DROP TABLE t1, t2;
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@local_optimizer_switch;
 
 --echo #
 --echo # BUG#50019: Wrong result for IN-subquery with materialization
@@ -1200,10 +1201,10 @@ insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
 create table t3(i int);
 insert into t3 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
 select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5);
-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);
-set session optimizer_switch=@save_optimizer_switch;
+set session optimizer_switch=@local_optimizer_switch;
 drop table t1, t2, t3;
 
 #
@@ -1589,7 +1590,7 @@ DROP TABLE t1,t2,t3;
 --echo # BUG#939009: Crash with aggregate function in IN subquery 
 --echo #
 
-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);
@@ -1610,7 +1611,7 @@ SELECT * FROM t1
 SELECT * FROM t1
   WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 
-SET optimizer_switch=@save_optimizer_switch;
+SET optimizer_switch=@local_optimizer_switch;
 
 DROP TABLE t1,t2;
 
@@ -1627,8 +1628,7 @@ 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
@@ -1649,7 +1649,7 @@ SELECT a, c FROM t1, t2
   WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
                      WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
 
-SET optimizer_switch=@save_optimizer_switch;
+SET optimizer_switch=@local_optimizer_switch;
 SET join_cache_level=@save_join_cache_level;
 
 DROP TABLE t1,t2;
@@ -1709,7 +1709,7 @@ delete t2 from t2 where a_id in (select * from (select t1.id from t1 limit 2) as
 drop table t1,t2;
 
 --echo # 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;
 
 --echo #
@@ -1719,7 +1719,7 @@ set join_cache_level=@save_join_cache_level;
 --echo # 
 
 
-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;
@@ -1727,7 +1727,7 @@ 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 );
 
 drop table t1;
-SET optimizer_switch=@save_optimizer_switch;
+SET optimizer_switch=@local_optimizer_switch;
 
 --echo #
 --echo # MDEV-5011: ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries
@@ -1881,7 +1881,7 @@ SELECT sq1.f2    FROM t1 AS sq1
   WHERE EXISTS ( SELECT * FROM t1 AS sq2
                    WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
 
-set @save_optimizer_switch= @@optimizer_switch;
+set @local_optimizer_switch= @@optimizer_switch;
 
 set optimizer_switch='exists_to_in=off';
 EXPLAIN
@@ -1895,7 +1895,7 @@ SELECT sq1.f2    FROM t1 AS sq1
   WHERE EXISTS ( SELECT * FROM t1 AS sq2
                    WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
 
-set optimizer_switch= @save_optimizer_switch;
+set optimizer_switch= @local_optimizer_switch;
 
 DROP TABLE t1;
 
@@ -1912,7 +1912,7 @@ 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
@@ -1929,7 +1929,7 @@ SELECT * FROM t1
   WHERE EXISTS ( SELECT * FROM t2, t3
                    WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
 
-set optimizer_switch= @save_optimizer_switch;
+set optimizer_switch= @local_optimizer_switch;
 
 DROP TABLE t1,t2,t3;
 
@@ -1954,11 +1954,11 @@ SELECT pk, f1, ( SELECT COUNT(*) FROM t2
                    WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
 
 --echo # 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;
-set optimizer_switch= @save_optimizer_switch;
+set optimizer_switch= @local_optimizer_switch;
 
 DROP TABLE t1,t2;
 
@@ -1966,7 +1966,7 @@ DROP TABLE t1,t2;
 --echo # mdev-12838: scan of materialized of semi-join subquery in join
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
+set @local_optimizer_switch=@@optimizer_switch;
 
 CREATE TABLE t1 (
   dispatch_group varchar(32),
@@ -2167,7 +2167,7 @@ eval explain $q;
 eval $q;
 
 DROP TABLE t1,t2,t3;
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@local_optimizer_switch;
 
 --echo #
 --echo # MDEV-16751: Server crashes in st_join_table::cleanup or 
@@ -2224,7 +2224,7 @@ CALL prepare_data();
 
 SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27);
 
-set @save_optimizer_switch= @@optimizer_switch;
+set @local_optimizer_switch= @@optimizer_switch;
 SET optimizer_switch='materialization=off';
 
 SELECT t1.a FROM t1
@@ -2236,7 +2236,7 @@ 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;
 
 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));