Fixed bug mdev-4350.

Wrong formulas used by the function Histogram::point_selectivity()
could result in a negative value of selectivity returned by the
function.

1 files changed, 40 insertions, 5 deletions

diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 02f58dbfebe..10e7b6a03c2 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -484,7 +484,7 @@ order by s_name
 limit 10;
 s_name	s_address
 Supplier#000000010	Saygah3gYWMp72i PY
-set histogram_size=15;
+set histogram_size=127;
 ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
 Table	Op	Msg_type	Msg_text
 dbt3_s001.part	analyze	status	OK
@@ -512,7 +512,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
 1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	part	ALL	PRIMARY	NULL	NULL	NULL	200	6.25	Using where
+2	MATERIALIZED	part	ALL	PRIMARY	NULL	NULL	NULL	200	7.03	Using where
 2	MATERIALIZED	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	4	dbt3_s001.part.p_partkey	3	100.00	Using where
 4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	14.37	Using where
 Warnings:
@@ -540,7 +540,7 @@ limit 10;
 s_name	s_address
 Supplier#000000010	Saygah3gYWMp72i PY
 set histogram_type='DOUBLE_PREC_HB';
-set histogram_size=30;
+set histogram_size=254;
 ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
 Table	Op	Msg_type	Msg_text
 dbt3_s001.part	analyze	status	OK
@@ -567,7 +567,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
 1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	part	ALL	PRIMARY	NULL	NULL	NULL	200	6.25	Using where
+2	MATERIALIZED	part	ALL	PRIMARY	NULL	NULL	NULL	200	7.03	Using where
 2	MATERIALIZED	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	4	dbt3_s001.part.p_partkey	3	100.00	Using where
 4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	14.37	Using where
 Warnings:
@@ -622,7 +622,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
 1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	part	ALL	PRIMARY	NULL	NULL	NULL	200	6.25	Using where
+2	MATERIALIZED	part	ALL	PRIMARY	NULL	NULL	NULL	200	7.03	Using where
 2	MATERIALIZED	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	4	dbt3_s001.part.p_partkey	3	100.00	Using where
 4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	14.37	Using where
 Warnings:
@@ -722,6 +722,41 @@ select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 a
 drop table t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# Bug mdev-4350: erroneous negative selectivity 
+#
+create table t1 (a int);
+insert into t1 values (1), (1);
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 values (0);
+select count(*) from t1;
+count(*)
+1025
+set use_stat_tables='preferably';
+set histogram_size=127;
+set histogram_type='SINGLE_PREC_HB';
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+flush table t1;
+set optimizer_use_condition_selectivity=4;
+explain extended select * from t1 where a=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1025	49.61	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 0)
+drop table t1;
+set histogram_size=@save_histogram_size;
+set histogram_type=@save_histogram_type;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 SET SESSION STORAGE_ENGINE=DEFAULT;