From dee11f9633be3091bd7d3c0b868e4ea1efe4ac7f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 26 Mar 2014 21:05:31 +0400 Subject: MDEV-4362: {division by zero when lookup constant is outside the value table} - Fix Histogram::point_selectivity() to work in the case where the passed value_pos=0 (or 1) and the first (or the last) bucket in the histogram has zero value-range (i.e one value). --- mysql-test/r/selectivity.result | 31 +++++++++++++++++++++++++++++++ mysql-test/r/selectivity_innodb.result | 31 +++++++++++++++++++++++++++++++ mysql-test/t/selectivity.test | 16 ++++++++++++++++ 3 files changed, 78 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index c91e4345f68..27e05e22714 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1356,6 +1356,37 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = (-(1))) drop table t0, t1; +# +# MDEV-4362: Selectivity estimates for IN (...) do not depend on whether the values are in range +# +create table t1 (col1 int); +set @a=-1; +create table t2 (a int) select (@a:=@a+1) as a from information_schema.session_variables A limit 100; +insert into t1 select A.a from t2 A, t2 B where A.a < 100 and B.a < 100; +select min(col1), max(col1), count(*) from t1; +min(col1) max(col1) count(*) +0 99 10000 +set histogram_size=100; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status OK +explain extended select * from t1 where col1 in (1,2,3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 3.37 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` in (1,2,3)) +# Must not cause fp division by zero, or produce nonsense numbers: +explain extended select * from t1 where col1 in (-1,-2,-3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 3.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` in ((-(1)),(-(2)),(-(3)))) +explain extended select * from t1 where col1<=-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 1.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` <= (-(1))) +drop table t1, t2; set histogram_type=@save_histogram_type; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index a348836783e..104b465b5a4 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1366,6 +1366,37 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = (-(1))) drop table t0, t1; +# +# MDEV-4362: Selectivity estimates for IN (...) do not depend on whether the values are in range +# +create table t1 (col1 int); +set @a=-1; +create table t2 (a int) select (@a:=@a+1) as a from information_schema.session_variables A limit 100; +insert into t1 select A.a from t2 A, t2 B where A.a < 100 and B.a < 100; +select min(col1), max(col1), count(*) from t1; +min(col1) max(col1) count(*) +0 99 10000 +set histogram_size=100; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status OK +explain extended select * from t1 where col1 in (1,2,3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 3.37 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` in (1,2,3)) +# Must not cause fp division by zero, or produce nonsense numbers: +explain extended select * from t1 where col1 in (-1,-2,-3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 3.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` in ((-(1)),(-(2)),(-(3)))) +explain extended select * from t1 where col1<=-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 1.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` <= (-(1))) +drop table t1, t2; set histogram_type=@save_histogram_type; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index fe35d9652ff..3e4940d53e4 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -908,6 +908,22 @@ explain extended select * from t1 where a=-1; drop table t0, t1; +--echo # +--echo # MDEV-4362: Selectivity estimates for IN (...) do not depend on whether the values are in range +--echo # +create table t1 (col1 int); +set @a=-1; +create table t2 (a int) select (@a:=@a+1) as a from information_schema.session_variables A limit 100; +insert into t1 select A.a from t2 A, t2 B where A.a < 100 and B.a < 100; +select min(col1), max(col1), count(*) from t1; +set histogram_size=100; +analyze table t1 persistent for all; +explain extended select * from t1 where col1 in (1,2,3); +--echo # Must not cause fp division by zero, or produce nonsense numbers: +explain extended select * from t1 where col1 in (-1,-2,-3); +explain extended select * from t1 where col1<=-1; +drop table t1, t2; + set histogram_type=@save_histogram_type; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; -- cgit v1.2.1