revision-id: 9a86900b3534d2d2bf112c32d7a7c0a3d3a1e46e (mariadb-10.6.1-349-g9a86900b353) parent(s): bdb90055c02a348e224574bab8113e76c5105a65 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-11 17:09:55 +0300 message: MDEV-26901: Estimation for filtered rows less precise ... #4 In Histogram_json_hb::point_selectivity(), do return selectivity of 0.0 when the histogram says so. The logic of "Do not return 0.0 estimate as it causes a multiply-by-zero meltdown in cost and cardinality calculations" is moved into records_in_column_ranges() where it is one *once* per column pair (as opposed to doing once per range, which can cause the error to add-up to large number when there are many ranges) --- mysql-test/main/statistics_json.result | 20 ++++++++++++++++++++ mysql-test/main/statistics_json.test | 16 ++++++++++++++++ sql/opt_histogram_json.cc | 2 +- 3 files changed, 37 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index 600b2ddd4ee..9eb8cf87c44 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -8264,3 +8264,23 @@ ANALYZE SELECT * FROM t1 WHERE f > '00:01:00'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 50.00 50.00 Using where drop table t1; +# +# MDEV-26901: Estimation for filtered rows less precise ... #4 +# +create table t1 (f int); +insert into t1 values +(7),(5),(0),(5),(112),(9),(9),(7),(5),(9), +(1),(7),(0),(6),(6),(2),(1),(6),(169),(7); +select f from t1 where f in (77, 1, 144, 73, 14, 12); +f +1 +1 +set histogram_type= JSON_HB; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze select f from t1 where f in (77, 1, 144, 73, 14, 12); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 10.00 10.00 Using where +drop table t1; diff --git a/mysql-test/main/statistics_json.test b/mysql-test/main/statistics_json.test index 93a526dbfb0..0a1b886f8fd 100644 --- a/mysql-test/main/statistics_json.test +++ b/mysql-test/main/statistics_json.test @@ -429,3 +429,19 @@ ANALYZE TABLE t1 PERSISTENT FOR ALL; ANALYZE SELECT * FROM t1 WHERE f > '00:01:00'; drop table t1; +--echo # +--echo # MDEV-26901: Estimation for filtered rows less precise ... #4 +--echo # +create table t1 (f int); +insert into t1 values + (7),(5),(0),(5),(112),(9),(9),(7),(5),(9), + (1),(7),(0),(6),(6),(2),(1),(6),(169),(7); + +select f from t1 where f in (77, 1, 144, 73, 14, 12); + +set histogram_type= JSON_HB; +analyze table t1 persistent for all; + +analyze select f from t1 where f in (77, 1, 144, 73, 14, 12); +drop table t1; + diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc index 4305737df1c..73284025511 100644 --- a/sql/opt_histogram_json.cc +++ b/sql/opt_histogram_json.cc @@ -921,7 +921,7 @@ double Histogram_json_hb::point_selectivity(Field *field, key_range *endpoint, The bucket has a single value and it doesn't match! Return a very small value. */ - sel= 1.0 / total_rows; + sel= 0.0; } else {