revision-id: d87e960a68c5fb0625cd7b4ff5a01af5e51a0d07 (mariadb-10.1.38-181-gd87e960a68c) parent(s): cb248f880619431850e5638009a6098048050edf author: Varun Gupta committer: Varun Gupta timestamp: 2019-05-14 03:56:21 +0530 message: MDEV-18378: Estimates for a non-merged join having implicit grouping is not showing 1 with optimizer_use_condition_selectivity >1 The estimates for the number of records in non-merged joins are stored in Item_in_subselect:jtbm_record_count. Use this estimate instead of stats.records when we have optimizer_use_condition_selectivity > 1. The reason is we don't have selectivity estimates for tables filled at execution, so use the estimates provided by the function get_delayed_table_estimates() for such tables --- mysql-test/r/subselect_mat.result | 31 +++++++++++++++++++++++++++++++ mysql-test/r/subselect_sj_mat.result | 31 +++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 21 +++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 4 files changed, 91 insertions(+) diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 6fae057dd17..f9b84341431 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2568,6 +2568,37 @@ i1 i2 1 4 2 6 DROP TABLE t1; +# +# MDEV-18378: Estimates for a non-merged join having implicit grouping is not showing 1 +# with optimizer_use_condition_selectivity >1 +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=1; +explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +set optimizer_use_condition_selectivity=4; +explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1,t2,t3; set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 3cb3750cbd6..f7593945e41 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2608,3 +2608,34 @@ i1 i2 1 4 2 6 DROP TABLE t1; +# +# MDEV-18378: Estimates for a non-merged join having implicit grouping is not showing 1 +# with optimizer_use_condition_selectivity >1 +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=1; +explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +set optimizer_use_condition_selectivity=4; +explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +a +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1,t2,t3; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 1f514be23d2..cd6d614bad8 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2318,3 +2318,24 @@ WHERE alias1.i1 IN ( ); DROP TABLE t1; +--echo # +--echo # MDEV-18378: Estimates for a non-merged join having implicit grouping is not showing 1 +--echo # with optimizer_use_condition_selectivity >1 +--echo # + +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=1; +explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +set optimizer_use_condition_selectivity=4; +explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1,t2,t3; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cb8c0429674..de8753d64cc 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5859,6 +5859,14 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, TABLE *table= s->table; double sel= table->cond_selectivity; double table_records= table->stat_records(); + /* + For tables that are filled at execution, use the estimates that + are provided by get_delayed_table_estimates(). This is due to the + fact that we don't collect statistics for tables filled at execution. + see the function get_delayed_table_estimates() + */ + if (table->is_filled_at_execution()) + table_records= (double)s->found_records; dbl_records= table_records * sel; return dbl_records; }