diff options
| -rw-r--r-- | mysql-test/main/subselect3.inc | 24 | ||||
| -rw-r--r-- | mysql-test/main/subselect3.result | 18 | ||||
| -rw-r--r-- | mysql-test/main/subselect3_jcl6.result | 20 | ||||
| -rw-r--r-- | mysql-test/main/subselect_sj2_jcl6.result | 7 | ||||
| -rw-r--r-- | sql/sql_select.cc | 5 |
5 files changed, 57 insertions, 17 deletions
diff --git a/mysql-test/main/subselect3.inc b/mysql-test/main/subselect3.inc index fb330dfa3bc..c9d20800026 100644 --- a/mysql-test/main/subselect3.inc +++ b/mysql-test/main/subselect3.inc @@ -242,8 +242,9 @@ from t2; drop table t1,t2,t3,t4; -# More tests for tricky multi-column cases, where some of pushed-down -# equalities are used for index lookups and some arent. +--echo # More tests for tricky multi-column cases, where some of pushed-down +--echo # equalities are used for index lookups and some are not. + create table t1 (oref char(4), grp int, ie1 int, ie2 int); insert into t1 (oref, grp, ie1, ie2) values ('aa', 10, 2, 1), @@ -470,8 +471,11 @@ explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; +explain select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); + select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); +explain select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); explain @@ -1012,7 +1016,6 @@ explain select * from t1 where a in (select a from t1); drop table t1; set @@optimizer_switch=@save_optimizer_switch; -set @@optimizer_switch=@save_optimizer_switch; set @@optimizer_switch='materialization=off'; # @@ -1034,9 +1037,9 @@ explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); explain select straight_join * from t2 X, t2 Y where X.a in (select straight_join A.a from t1 A, t1 B); -# -# SJ-Materialization scan + first table being system const table -# +--echo # +--echo # SJ-Materialization scan + first table being system const table +--echo # create table t0 (a int, b int); insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); @@ -1044,9 +1047,12 @@ create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); drop table t0,t1,t2,t3,t4; -# -# LooseScan with ref access -# +--echo # +--echo # LooseScan with ref access +--echo # + +set @@optimizer_switch='join_cache_hashed=off'; + 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, filler char(100), key(a,b)); diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index 710aa00d5aa..c9a1ea6ca61 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -278,6 +278,8 @@ a b oref Z NULL 1 100 0 NULL 2 100 NULL drop table t1,t2,t3,t4; +# More tests for tricky multi-column cases, where some of pushed-down +# equalities are used for index lookups and some are not. create table t1 (oref char(4), grp int, ie1 int, ie2 int); insert into t1 (oref, grp, ie1, ie2) values ('aa', 10, 2, 1), @@ -617,10 +619,18 @@ cc 2 0 cc NULL NULL aa 1 1 bb NULL NULL +explain select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using where select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); oref a cc 5 aa 1 +explain select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); oref a ee NULL @@ -1220,7 +1230,6 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) drop table t1; set @@optimizer_switch=@save_optimizer_switch; -set @@optimizer_switch=@save_optimizer_switch; set @@optimizer_switch='materialization=off'; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -1254,6 +1263,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +# +# SJ-Materialization scan + first table being system const table +# create table t0 (a int, b int); insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); @@ -1268,6 +1280,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Start temporary 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; End temporary drop table t0,t1,t2,t3,t4; +# +# LooseScan with ref access +# +set @@optimizer_switch='join_cache_hashed=off'; 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, filler char(100), key(a,b)); diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index 2bbc11863df..acd1269d875 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -281,6 +281,8 @@ a b oref Z NULL 1 100 0 NULL 2 100 NULL drop table t1,t2,t3,t4; +# More tests for tricky multi-column cases, where some of pushed-down +# equalities are used for index lookups and some are not. create table t1 (oref char(4), grp int, ie1 int, ie2 int); insert into t1 (oref, grp, ie1, ie2) values ('aa', 10, 2, 1), @@ -620,10 +622,18 @@ cc 2 0 cc NULL NULL aa 1 1 bb NULL NULL +explain select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 6 Using where +1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.oref,test.t1.ie 7 Using where; Using join buffer (flat, BNLH join) select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); oref a -aa 1 cc 5 +aa 1 +explain select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); oref a ee NULL @@ -1223,7 +1233,6 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 hash_ALL NULL #hash#$hj 6 test.t1.a 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join) drop table t1; set @@optimizer_switch=@save_optimizer_switch; -set @@optimizer_switch=@save_optimizer_switch; set @@optimizer_switch='materialization=off'; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -1257,6 +1266,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +# +# SJ-Materialization scan + first table being system const table +# create table t0 (a int, b int); insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); @@ -1271,6 +1283,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Start temporary 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan drop table t0,t1,t2,t3,t4; +# +# LooseScan with ref access +# +set @@optimizer_switch='join_cache_hashed=off'; 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, filler char(100), key(a,b)); diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index 672b1654aa8..6ccec99ba5e 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -264,9 +264,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED it ALL NULL NULL NULL NULL 52 +1 PRIMARY ot ALL NULL NULL NULL NULL 22 Using where +1 PRIMARY it hash_ALL NULL #hash#$hj 5 test.ot.a 52 Using where; FirstMatch(ot); Using join buffer (flat, BNLH join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -290,8 +289,8 @@ a mid(filler1, 1,10) length(filler1)=length(filler2) 16 filler1234 1 17 filler1234 1 18 filler1234 1 -19 filler1234 1 3 duplicate 1 +19 filler1234 1 19 duplicate 1 drop table t1, t2; create table t1 (a int, b int, key(a)); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1748080dd71..26987c9072e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8946,7 +8946,7 @@ best_access_path(JOIN *join, We assume here that, thanks to the hash, we don't have to compare all row combinations, only a HASH_FANOUT (10%) rows in the cache. */ - row_copy_cost= (ROW_COPY_COST_THD(thd) * 2 * + row_copy_cost= (ROW_COPY_COST_THD(thd) * JOIN_CACHE_ROW_COPY_COST_FACTOR(thd)); cmp_time= (record_count * row_copy_cost + rnd_records * record_count * HASH_FANOUT * @@ -8957,6 +8957,9 @@ best_access_path(JOIN *join, best.cost= cur_cost; best.records_read= best.records_after_filter= rows2double(s->records); best.records= rnd_records; +#ifdef NOT_YET + set_if_smaller(best.records_out, rnd_records * HASH_FANOUT); +#endif best.key= hj_start_key; best.ref_depends_map= 0; best.use_join_buffer= TRUE; |