diff options
Diffstat (limited to 'mysql-test/main/subselect_sj.result')
| -rw-r--r-- | mysql-test/main/subselect_sj.result | 46 |
1 files changed, 25 insertions, 21 deletions
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 246330db7d5..ea0f139ed0c 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/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#1 */ 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#2 */ 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,9 +1237,11 @@ 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'; +set @tmp_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=1; CREATE TABLE t1 (i INTEGER); INSERT INTO t1 VALUES (1), (2), (3), (4), (5); CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j)); @@ -1253,7 +1256,8 @@ i 1 2 DROP TABLE t1, t2; -set optimizer_switch=@save_optimizer_switch; +set optimizer_switch=@tmp_optimizer_switch; +set @@optimizer_use_condition_selectivity=@tmp_optimizer_use_condition_selectivity; # # BUG#49453: re-execution of prepared statement with view # and semijoin crashes @@ -1296,7 +1300,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 +1324,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 +1335,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 +1384,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 +1402,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 +1424,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 +1437,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 +1541,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 +1554,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 +1592,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 ; # # @@ -3038,7 +3042,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; |