MySQL Bugs: #99998: For large ranges, the range estimate will never exceed 50%

Bug #99998	For large ranges, the range estimate will never exceed 50%
Submitted:	26 Jun 2020 7:58	Modified:	29 Jun 2020 12:40
Reporter:	Øystein Grøvlen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There seems to be an upper limit for the range estimates for large ranges to 50% of the table.  Example:

mysql> select max(o_orderdate) from orders;
+------------------+
| max(o_orderdate) |
+------------------+
| 1998-08-02       |
+------------------+
1 row in set (0.00 sec)

mysql> explain select * from orders where o_orderdate <= "1998-08-02";
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | i_o_orderdate | NULL | NULL    | NULL | 15000000 |    50.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

How to repeat:
Using a DBT-3 database:

select max(o_orderdate) from orders;
explain select * from orders where o_orderdate <= "1998-08-02";

Suggested fix:
In btr_estimate_n_rows_in_range_low(), we find this comment:

      /* Do not estimate the number of rows in the range
      to over 1 / 2 of the estimated rows in the whole
      table */

Stop doing this!

Hi Mr. Øystein Grøvlen,

Thank you for your bug report.

However, this bug also seems to be a duplicate of the bug #73386.

If you disagree, send us a test case, since we are not using the dataset that you have mentioned.

You are right.  This is a duplicate.  I am sorry that I did not discovered that before reporting.

By the way, do you use some bigger data set than the world database? The world database is too small for bugs like this, and it would be good to not have to upload a very large database each time we report a bug.

Hi Øystein Grøvlen,

First of all, from now on, this bug is a duplicate of #73386.

We also use all sysbench available tests, where you can make quite a large dataset.