revision-id: b7d8943a5be1745d14bdf48c4f6a4b897c4c3f75 (mariadb-10.0.37-58-gb7d8943a5be) parent(s): e6fcd7230954c6111bba63e7f7201fc81e50178e author: Varun Gupta committer: Varun Gupta timestamp: 2019-01-25 18:59:32 +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 for subqueries with implicit grouping stats.records is always set to a minimum value of 2. --- mysql-test/r/subselect_mat.result | 28 ++++++++++++++++++++++++++++ mysql-test/t/subselect_mat.test | 20 ++++++++++++++++++++ sql/sql_select.cc | 2 ++ 3 files changed, 50 insertions(+) diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 7907b86135e..1cafba8d61e 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2838,3 +2838,31 @@ select 1 from t1 where exists (select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; 1 drop table t1,t2; +# +# 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 t2 ALL NULL NULL NULL NULL 2 Using where +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 t2 ALL NULL NULL NULL NULL 2 Using where +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 +drop table t1,t2,t3; diff --git a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test index 66a6cc97acb..abf726ea654 100644 --- a/mysql-test/t/subselect_mat.test +++ b/mysql-test/t/subselect_mat.test @@ -280,3 +280,23 @@ select 1 from t1 where exists select 1 from t1 where exists (select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; drop table t1,t2; + +--echo # +--echo # MDEV-18378: Estimates for a non-merged join having implicit grouping is not +--echo # showing 1 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; +drop table t1,t2,t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6fafbbb11df..c3e7a6198b0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5693,6 +5693,8 @@ 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(); + if (s->table->is_filled_at_execution()) + table_records= (double)s->found_records; dbl_records= table_records * sel; return dbl_records; }