[Commits] d87e960a68c: MDEV-18378: Estimates for a non-merged join having implicit grouping is not showing 1 with optimizer_use_condition_selectivity >1

Varun varunraiko1803 at gmail.com
Tue May 14 01:26:46 EEST 2019


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;
   }


More information about the commits mailing list