[#MDEV-31067] selectivity_from_histogram >1.0 for col=const on DOUBLE_PREC

create table t10 (a int);

-- Fill the table, will provide INSERTs below

analyze table t10 persistent for all;

set optimizer_trace=1;

explain select * from t10  where a in (91303);

+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

|    1 | SIMPLE      | t10   | ALL  | NULL          | NULL | NULL    | NULL | 99840 |   100.00 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) from information_schema.optimizer_trace\G

*************************** 1. row ***************************

json_detailed(json_extract(trace, '$**.selectivity_for_columns')): [

            "column_name": "a",

            "ranges":

            ["91303 <= a <= 91303"],

            "selectivity_from_histogram": 18.28543534

create table bucket(a int);  # This holds how many rows we hold in a bucket.

insert into bucket select 1 from seq_1_to_780;

create table one_third_of_bucket(a int);  # one-third of a bucket

insert into one_third_of_bucket select 1 from seq_1_to_260;

create table t10 (a int);

insert into t10 select 0 from bucket, seq_1_to_4;

insert into t10 select 8693 from one_third_of_bucket;

insert into t10 select 8694 from one_third_of_bucket;

insert into t10 select 8695 from one_third_of_bucket;

insert into t10 select 34783 from one_third_of_bucket;

insert into t10 select 34784 from one_third_of_bucket;

insert into t10 select 34785 from one_third_of_bucket;

insert into t10 select 34785 from bucket, seq_1_to_8;

insert into t10 select 65214 from one_third_of_bucket;

insert into t10 select 65215 from one_third_of_bucket;

insert into t10 select 65216 from one_third_of_bucket;

insert into t10 select 65216 from bucket, seq_1_to_52;

insert into t10 select 65217 from one_third_of_bucket;

insert into t10 select 65218 from one_third_of_bucket;

insert into t10 select 65219 from one_third_of_bucket;

insert into t10 select 65219 from bucket;

insert into t10 select 73913 from one_third_of_bucket;

insert into t10 select 73914 from one_third_of_bucket;

insert into t10 select 73915 from one_third_of_bucket;

insert into t10 select 73915 from bucket, seq_1_to_40;

insert into t10 select 78257 from one_third_of_bucket;

insert into t10 select 78258 from one_third_of_bucket;

insert into t10 select 78259 from one_third_of_bucket;

insert into t10 select 91300 from one_third_of_bucket;

insert into t10 select 91301 from one_third_of_bucket;

insert into t10 select 91302 from one_third_of_bucket;

insert into t10 select 91302 from bucket, seq_1_to_6;

insert into t10 select 91303 from one_third_of_bucket;

insert into t10 select 91304 from one_third_of_bucket;

insert into t10 select 91305 from one_third_of_bucket;

insert into t10 select 91305 from bucket, seq_1_to_8;

insert into t10 select  99998 from one_third_of_bucket;

insert into t10 select  99999 from one_third_of_bucket;

insert into t10 select 100000 from one_third_of_bucket;

    else

/*

        The value 'pos' fits within one single histogram bucket.

...

*/

      HERE!

/*

So:

        - each bucket has the same #rows

        - values are unformly distributed across the [min_value,max_value] domain.

        If a bucket has value range that's N times bigger then average, than

        each value will have to have N times fewer rows than average.

*/

      sel= avg_sel * avg_bucket_width / current_bucket_width;

commit ad842b5f058d5342c22cdc86542baa2ae9db5e70

Author:	Sergey Petrunya <psergey@askmonty.org>  Wed Mar 26 17:55:00 2014

Committer:	Sergey Petrunya <psergey@askmonty.org>  Wed Mar 26 17:55:00 2014

Original File:	sql/sql_statistics.h

MDEV-5926: EITS: Histogram estimates for column=least_possible_value are wrong

[Attempt #2]

 sel= avg_sel * avg_bucket_width / current_bucket_width;

  bucket_Y.left_endpoint < position(X) < bucket_Y.right_endpoint

bucketY1 = {left_endp1,  left_endp1 + 0.5}

bucketY2 = {left_endp2,  left_endp2 + 0.0001}

[MDEV-31067] selectivity_from_histogram >1.0 for col=const on DOUBLE_PREC_HB histogram Created: 2023-04-17 Updated: 2023-05-22 Resolved: 2023-05-22
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s:	10.11.3, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4