summaryrefslogtreecommitdiff
path: root/mysql-test/t/subselect_sj.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/subselect_sj.test')
-rw-r--r--mysql-test/t/subselect_sj.test43
1 files changed, 22 insertions, 21 deletions
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 33ac85f9cf2..e619a5df52c 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -22,7 +22,7 @@ if (`select @join_cache_level_for_subselect_sj_test is not null`)
}
# The 'default' value within the scope of this test:
-set @save_optimizer_switch=@@optimizer_switch;
+set @local_optimizer_switch=@@optimizer_switch;
#
# 1. Subqueries that are converted into semi-joins
@@ -79,6 +79,7 @@ select * from t1 left join (t2 A, t2 B) on ( A.a= t1.a and B.a in (select pk fro
explain extended
select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10));
+set @save_join_buffer_size=@@join_buffer_size;
set join_buffer_size=8*1024;
--echo we shouldn't flatten if we're going to get a join of > MAX_TABLES.
@@ -99,7 +100,7 @@ select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
-set join_buffer_size=default;
+set join_buffer_size=@save_join_buffer_size;
#
# Prepared statements
@@ -247,7 +248,7 @@ 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
@@ -264,7 +265,7 @@ WHERE EMPNUM IN
WHERE PNUM IN
(SELECT PNUM FROM PROJ));
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@local_optimizer_switch;
drop table STAFF,WORKS,PROJ;
@@ -383,7 +384,7 @@ drop table t1, t2;
drop view v1;
drop procedure p1;
-set SESSION optimizer_switch=@save_optimizer_switch;
+set SESSION optimizer_switch=@local_optimizer_switch;
--echo # End of bug#46744
@@ -550,7 +551,7 @@ 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;
--echo # End of BUG#48834
@@ -966,7 +967,7 @@ DROP TABLE t3;
--echo # LPBUG#602574: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint,
--echo # uint): Assertion `join->best_read <
--echo #
-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,
@@ -1002,7 +1003,7 @@ WHERE (SELECT varchar_key FROM t3
FROM t1 RIGHT JOIN t2 ON t1.varchar_key
)
);
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@tmp_optimizer_switch;
DROP TABLE t1, t2, t3;
--echo #
@@ -1144,7 +1145,7 @@ DROP TABLE t1, t2, t3;
--echo # BUG#53060: LooseScan semijoin strategy does not return all rows
--echo #
-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';
@@ -1159,7 +1160,7 @@ SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0);
DROP TABLE t1, t2;
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@tmp_optimizer_switch;
--echo #
--echo # BUG#49453: re-execution of prepared statement with view
@@ -1202,7 +1203,7 @@ 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';
@@ -1216,7 +1217,7 @@ explain
select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@tmp_optimizer_switch;
drop table t1,t2,t3;
@@ -1231,7 +1232,7 @@ 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
@@ -1260,7 +1261,7 @@ SELECT * FROM t1
WHERE t1.a IN (SELECT t2.a
FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@tmp_optimzer_switch;
DROP TABLE t1,t2,t3;
@@ -1286,7 +1287,7 @@ INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
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';
@@ -1308,7 +1309,7 @@ SELECT * FROM t1
WHERE (a1, a2) IN (
SELECT b1, b2 FROM t2c WHERE b1 > '0');
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@tmp_optimzer_switch;
DROP TABLE t1,t2a,t2b,t2c;
@@ -1333,7 +1334,7 @@ FROM ot1
(ot2 JOIN ot3 on ot2.a=ot3.a)
ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
-set @save_optimizer_switch=@@optimizer_switch;
+set @tmp_optimzer_switch=@@optimizer_switch;
set optimizer_switch='semijoin=on';
@@ -1353,7 +1354,7 @@ execute s;
execute s;
deallocate prepare s;
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@tmp_optimzer_switch;
DROP TABLE ot1, ot2, ot3, it1;
@@ -1371,7 +1372,7 @@ 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
@@ -1391,7 +1392,7 @@ SELECT * FROM t3
WHERE f2 IN (SELECT t1.f1
FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
-set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch=@tmp_optimzer_switch;
DROP TABLE t1, t2, t3 ;
@@ -2673,7 +2674,7 @@ SET @tmp_mdev5059=@@join_cache_level;
# Any value > 2 will do
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);
View Lorry Controller Status