Merge branch '10.2' into 10.3

1 files changed, 27 insertions, 29 deletions

diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result
index d38b40586b0..898b1b69117 100644
--- a/mysql-test/main/subselect_mat.result
+++ b/mysql-test/main/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#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((/* select#2 */ 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);