Fixed bug mdev-6843.

The function  get_column_range_cardinality() returned a wrong result for any column
containing only null values.

4 files changed, 91 insertions, 1 deletions

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 088e169ed0c..d2383f24f2d 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1380,5 +1380,33 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ref	a	a	5	test.t1.a	10	100.00	
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10))
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t0,t1,t2;
+#
+# Bug mdev-6843: col IS NULL in where condition when col is always NULL
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 (a int, b int);
+insert into t2 select NULL, a from t1;
+set use_stat_tables='preferably';
+set histogram_size=100;
+set optimizer_use_condition_selectivity=4;
+analyze table t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+explain extended
+select * from t2 A straight_join t2 B where A.a is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	1000	100.00	Using where
+1	SIMPLE	B	ALL	NULL	NULL	NULL	NULL	1000	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`A`.`a` AS `a`,`test`.`A`.`b` AS `b`,`test`.`B`.`a` AS `a`,`test`.`B`.`b` AS `b` from `test`.`t2` `A` straight_join `test`.`t2` `B` where isnull(`test`.`A`.`a`)
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t0,t1,t2;
 set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index af9c31afae1..c4c398a9ff6 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1390,6 +1390,34 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ref	a	a	5	test.t1.a	10	100.00	
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10))
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t0,t1,t2;
+#
+# Bug mdev-6843: col IS NULL in where condition when col is always NULL
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 (a int, b int);
+insert into t2 select NULL, a from t1;
+set use_stat_tables='preferably';
+set histogram_size=100;
+set optimizer_use_condition_selectivity=4;
+analyze table t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+explain extended
+select * from t2 A straight_join t2 B where A.a is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	1000	100.00	Using where
+1	SIMPLE	B	ALL	NULL	NULL	NULL	NULL	1000	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`A`.`a` AS `a`,`test`.`A`.`b` AS `b`,`test`.`B`.`a` AS `a`,`test`.`B`.`b` AS `b` from `test`.`t2` `A` straight_join `test`.`t2` `B` where isnull(`test`.`A`.`a`)
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t0,t1,t2;
 set use_stat_tables=@save_use_stat_tables;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index 16226b07751..5db6f3b9d6a 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -912,6 +912,35 @@ select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10;
 explain extended 
 select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10;
 
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+drop table t0,t1,t2;
+
+--echo #
+--echo # Bug mdev-6843: col IS NULL in where condition when col is always NULL
+--echo #
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 (a int, b int);
+insert into t2 select NULL, a from t1;
+
+set use_stat_tables='preferably';
+set histogram_size=100;
+
+set optimizer_use_condition_selectivity=4;
+analyze table t2 persistent for all;
+
+explain extended
+select * from t2 A straight_join t2 B where A.a is null;
+
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
 drop table t0,t1,t2;
 
 set use_stat_tables=@save_use_stat_tables;
+
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index a38df24cfb7..9807a26f772 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -3502,7 +3502,12 @@ double get_column_range_cardinality(Field *field,
                    !(range_flag & NEAR_MIN);
 
   if (col_non_nulls < 1)
-    res= 0; /* this is likely wrong, see MDEV-6843 */
+  {
+    if (nulls_incl)
+      res= col_nulls;
+    else
+      res= 0;
+  }
   else if (min_endp && max_endp && min_endp->length == max_endp->length &&
            !memcmp(min_endp->key, max_endp->key, min_endp->length))
   {