[Commits] b7d8943a5be: 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
Fri Jan 25 15:32:51 EET 2019


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


More information about the commits mailing list