MySQL Bugs: #100151: explain loss Histogram

Bug #100151	explain loss Histogram
Submitted:	8 Jul 2020 7:12	Modified:	10 Aug 2020 13:14
Reporter:	HULONG CUI	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	histogram

Description:
mysql verson: 8.0.20
I created the histogram and used that's ok
but i restarted mysql server。
explain lossed histogram and user full scan

How to repeat:
1）create table
CREATE TABLE `employees01` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) COLLATE utf8mb4_bin NOT NULL,
  `last_name` varchar(16) COLLATE utf8mb4_bin NOT NULL,
  `gender` enum('M','F') COLLATE utf8mb4_bin NOT NULL,
  `hire_date` date NOT NULL,
  KEY  `emp_no`  (`emp_no`),
  KEY `idx_fname` (`first_name`),
  KEY `idx_dt_birth` (`birth_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
2）insert sample many data

3）ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 35 BUCKETS;

4）explain select * from employees where birth_date>'1964-02-01';
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_dt_birth  | idx_dt_birth | 3       | NULL | 44288 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+

1 row in set, 1 warning (0.00 sec)

5）restart mysql server
mysql> explain select * from employees where birth_date>'1964-02-01';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_dt_birth  | NULL | NULL    | NULL | 299379 |    14.79 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

6）loss HISTOGRAM

Hi Mr. jacky,

Thank you for your bug report.

However, we do need some additional info in order to verify your report.

First of all, we need that data. We do not need all of them, but the minimum number so that we can reproduce the behaviour.

Second, you have to let us know the value that you use for histogram_generation_max_mem_size . See if changing that value changes the behaviour.

Third, make sure that after your query and reboot, you have not changed any data in that table.

We wait on your feedback.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi Mr. jacky,

We have managed to make a test case and repeat the behaviour as reported.

Verified as reported.