[Commits] be5797a: MDEV-7316: Assertion `join->best_read < double(...) failed in bool greedy_search ...

Sergei Petrunia psergey at askmonty.org
Fri Feb 20 01:28:10 EET 2015


revision-id: be5797a00e56b845db351fb97ee04fb9332139ab
parent(s): 0f8b194146b50afe442682c5a14a8a179e28f3b8
committer: Sergei Petrunia
branch nick: 10.0-mdev7316
timestamp: 2015-02-20 02:28:10 +0300
message:

MDEV-7316: Assertion `join->best_read < double(...) failed in bool greedy_search ...

Make get_column_range_cardinality() never return 0. selectivity=0 is
an edge case, and it causes divisions by zero in subsequent selectivity
calculations.

---
 mysql-test/r/selectivity.result        |    4 ++--
 mysql-test/r/selectivity_innodb.result |   28 ++++++++++++++++++++++------
 mysql-test/t/selectivity_innodb.test   |   17 +++++++++++++++++
 sql/sql_statistics.cc                  |   14 ++++++++++++++
 4 files changed, 55 insertions(+), 8 deletions(-)

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 3f2343f..2eadb8f 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1242,8 +1242,8 @@ EXPLAIN EXTENDED
 SELECT * FROM language, country, continent
 WHERE country_group = lang_group AND lang_group IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	country	ALL	NULL	NULL	NULL	NULL	2	0.00	Using where
-1	SIMPLE	language	ALL	NULL	NULL	NULL	NULL	6	0.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	country	ALL	NULL	NULL	NULL	NULL	2	0.05	Using where
+1	SIMPLE	language	ALL	NULL	NULL	NULL	NULL	6	0.02	Using where; Using join buffer (flat, BNL join)
 1	SIMPLE	continent	ALL	NULL	NULL	NULL	NULL	6	100.00	Using join buffer (incremental, BNL join)
 Warnings:
 Note	1003	select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where ((`test`.`language`.`lang_group` = `test`.`country`.`country_group`) and isnull(`test`.`country`.`country_group`))
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 0b7f1c9..9bafb15 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -802,11 +802,10 @@ insert into t2 values (2),(3);
 explain extended 
 select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	0.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	0.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	0.10	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	0.05	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` > 3))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 3))
 select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 a
 drop table t1,t2;
@@ -1252,8 +1251,8 @@ EXPLAIN EXTENDED
 SELECT * FROM language, country, continent
 WHERE country_group = lang_group AND lang_group IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	country	ALL	NULL	NULL	NULL	NULL	2	0.00	Using where
-1	SIMPLE	language	ALL	NULL	NULL	NULL	NULL	6	0.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	country	ALL	NULL	NULL	NULL	NULL	2	0.05	Using where
+1	SIMPLE	language	ALL	NULL	NULL	NULL	NULL	6	0.02	Using where; Using join buffer (flat, BNL join)
 1	SIMPLE	continent	ALL	NULL	NULL	NULL	NULL	6	100.00	Using join buffer (incremental, BNL join)
 Warnings:
 Note	1003	select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where ((`test`.`language`.`lang_group` = `test`.`country`.`country_group`) and isnull(`test`.`country`.`country_group`))
@@ -1482,6 +1481,23 @@ select * from t1 where col2 != true;
 col1	col2
 drop table t1;
 #
+#  MDEV-7316: Assertion `join->best_read < double(...) failed in bool greedy_search with optimizer_use_condition_selectivity>2
+#
+CREATE TABLE t1 (a VARCHAR(16), b INT, c INT, PRIMARY KEY(a), KEY(c)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('USAChinese',1,10),('USAEnglish',2,20),('USAFrench',3,30);
+CREATE TABLE t2 (i int) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
+SET use_stat_tables=PREFERABLY, optimizer_use_condition_selectivity=3;
+ANALYZE TABLE t1, t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+SELECT * FROM t1, t2 WHERE b IN ( SELECT 2 UNION SELECT 3 ) AND a <> 'USARussian' AND c IS NULL;
+a	b	c	i
+DROP TABLE t1,t2;
+#
 # End of 10.0 tests
 #
 set use_stat_tables= @tmp_ust;
diff --git a/mysql-test/t/selectivity_innodb.test b/mysql-test/t/selectivity_innodb.test
index 5674cb5..3db4f26 100644
--- a/mysql-test/t/selectivity_innodb.test
+++ b/mysql-test/t/selectivity_innodb.test
@@ -80,6 +80,23 @@ select * from t1 where col2 != true;
 drop table t1;
 
 --echo #
+--echo #  MDEV-7316: Assertion `join->best_read < double(...) failed in bool greedy_search with optimizer_use_condition_selectivity>2
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(16), b INT, c INT, PRIMARY KEY(a), KEY(c)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('USAChinese',1,10),('USAEnglish',2,20),('USAFrench',3,30);
+
+CREATE TABLE t2 (i int) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
+
+SET use_stat_tables=PREFERABLY, optimizer_use_condition_selectivity=3;
+ANALYZE TABLE t1, t2;
+
+SELECT * FROM t1, t2 WHERE b IN ( SELECT 2 UNION SELECT 3 ) AND a <> 'USARussian' AND c IS NULL;
+
+DROP TABLE t1,t2;
+
+--echo #
 --echo # End of 10.0 tests
 --echo #
 
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index 4ce1f3e..b13d170 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -3468,6 +3468,17 @@ double get_column_avg_frequency(Field * field)
 } 
 
 
+/*
+  A value that selectivity estimation (histogram + nulls_fraction) code will
+  return when the calculation gives selectivity=0.0.
+
+  Returning a value of 0 causes divisions by zero and other edge effects.
+  Histogram data is neither precise, nor up to date, so we assume that
+  zero-selectivity just means that condition is highly-selective.
+*/
+const double SMALL_SELECTIVITY=0.001;
+
+
 /**
   @brief
   Estimate the number of rows in a column range using data from stat tables 
@@ -3597,6 +3608,9 @@ double get_column_range_cardinality(Field *field,
     if (nulls_incl)
       res+= col_nulls;
   }
+
+  if (res < SMALL_SELECTIVITY)
+    res= SMALL_SELECTIVITY;
   return res;
 }
 


More information about the commits mailing list