diff options
Diffstat (limited to 'mysql-test/r/subselect_sj_jcl6.result')
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 32 |
1 files changed, 32 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 2d1da2ea953..26a2f93fc86 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2029,6 +2029,38 @@ EXECUTE st1; b c b d DROP TABLE t1,t2,t3; set optimizer_switch=@tmp878753; +# +# Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off +# +create table t1 (a int); +insert into t1 values (7), (1), (5), (3); +create table t2 (a int); +insert into t2 values (4), (1), (8), (3), (9), (2); +set @tmp_otimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=on'; +set optimizer_switch='firstmatch=off'; +set optimizer_switch='semijoin_with_cache=on'; +explain +select * from t1 where t1.a in (select t2.a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) +select * from t1 where t1.a in (select t2.a from t2); +a +1 +3 +set optimizer_switch='semijoin_with_cache=off'; +explain +select * from t1 where t1.a in (select t2.a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary +select * from t1 where t1.a in (select t2.a from t2); +a +1 +3 +set optimizer_switch= @tmp_otimizer_switch; +drop table t1,t2; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off |