diff options
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 48 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj_jcl6.test | 42 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 4 |
3 files changed, 92 insertions, 2 deletions
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 26a2f93fc86..6c4f5d30ade 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2094,6 +2094,54 @@ a 3 set optimizer_switch=@save_optimizer_switch; drop table t0, t1, t2; +# +# Bug #891995: IN subquery with join_cache_level >= 3 +# +CREATE TABLE t1 (a varchar(1)); +INSERT INTO t1 VALUES ('w'),('q'); +CREATE TABLE t2 (a int NOT NULL, b time, PRIMARY KEY (a)); +INSERT INTO t2 VALUES +(2,'18:56:33'), (5,'19:11:10'), (3,'18:56:33'), (7,'19:11:10'); +CREATE TABLE t3 ( +a int NOT NULL, b int, c int, d varchar(1), PRIMARY KEY (a), KEY (d,c) +); +INSERT INTO t3 VALUES +(25,158,10,'f'), (26,5,2,'v'), (27,163,103,'f'), (28,2,3,'q'), (29,8,6,'y'); +set @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on'; +SET SESSION optimizer_switch='join_cache_hashed=on'; +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1, t2 +WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary +1 PRIMARY t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2) +1 PRIMARY t2 hash_ALL PRIMARY #hash#PRIMARY 4 test.t3.b 4 End temporary; Using join buffer (flat, BNLH join) +SELECT * FROM t1, t2 +WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); +a a b +w 2 18:56:33 +q 2 18:56:33 +w 5 19:11:10 +SET SESSION optimizer_switch='mrr=on'; +SET SESSION join_cache_level=6; +EXPLAIN +SELECT * FROM t1, t2 +WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary +1 PRIMARY t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +SELECT * FROM t1, t2 +WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); +a a b +w 2 18:56:33 +q 2 18:56:33 +w 5 19:11:10 +set optimizer_switch=@save_optimizer_switch; +set join_cache_level=default; +DROP TABLE t1,t2,t3; # End set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test index e475adbf9a8..fc539ec1a01 100644 --- a/mysql-test/t/subselect_sj_jcl6.test +++ b/mysql-test/t/subselect_sj_jcl6.test @@ -43,6 +43,48 @@ SELECT * FROM t0 WHERE t0.a IN set optimizer_switch=@save_optimizer_switch; drop table t0, t1, t2; +--echo # +--echo # Bug #891995: IN subquery with join_cache_level >= 3 +--echo # + +CREATE TABLE t1 (a varchar(1)); +INSERT INTO t1 VALUES ('w'),('q'); + +CREATE TABLE t2 (a int NOT NULL, b time, PRIMARY KEY (a)); +INSERT INTO t2 VALUES + (2,'18:56:33'), (5,'19:11:10'), (3,'18:56:33'), (7,'19:11:10'); + +CREATE TABLE t3 ( + a int NOT NULL, b int, c int, d varchar(1), PRIMARY KEY (a), KEY (d,c) +); +INSERT INTO t3 VALUES + (25,158,10,'f'), (26,5,2,'v'), (27,163,103,'f'), (28,2,3,'q'), (29,8,6,'y'); + +set @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on'; + +SET SESSION optimizer_switch='join_cache_hashed=on'; +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1, t2 + WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); +SELECT * FROM t1, t2 + WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); + + +SET SESSION optimizer_switch='mrr=on'; +SET SESSION join_cache_level=6; +EXPLAIN +SELECT * FROM t1, t2 + WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); +SELECT * FROM t1, t2 + WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); + +set optimizer_switch=@save_optimizer_switch; +set join_cache_level=default; + +DROP TABLE t1,t2,t3; + --echo # End set join_cache_level=default; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index a954811bdfa..efaac11f53b 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3921,8 +3921,8 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, if (j != join->const_tables && js_tab->use_quick != 2 && j <= no_jbuf_after && ((js_tab->type == JT_ALL && join_cache_level != 0) || - (join_cache_level > 2 && (tab->type == JT_REF || - tab->type == JT_EQ_REF)))) + (join_cache_level > 2 && (js_tab->type == JT_REF || + js_tab->type == JT_EQ_REF)))) { /* Looks like we'll be using join buffer */ first_table= join->const_tables; |