row count estimation on DATETIME type is over-estimated when time span across years/months

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

test.zip

use test;
source test.sql;
load stats 'test.json';
explain select * from test where updated_date > '2023-12-31 23:40:00' and updated_date<'2023-12-31 23:50:00';
explain select * from test where updated_date > '2023-12-31 23:50:00' and updated_date<'2024-01-01 00:00:00';

2. What did you expect to see? (Required)

mysql> explain select * from test where updated_date > '2023-12-31 23:40:00' and updated_date<'2023-12-31 23:50:00';
+-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
| id                            | estRows | task      | access object                                     | operator info                                                     |
+-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
| IndexLookUp_7                 | 1382.13 | root      |                                                   |                                                                   |
| ├─IndexRangeScan_5(Build)     | 1382.13 | cop[tikv] | table:test, index:IX_CUST_RELA_DATE(updated_date) | range:(2023-12-31 23:40:00,2023-12-31 23:50:00), keep order:false |
| └─TableRowIDScan_6(Probe)     | 1382.13 | cop[tikv] | table:test                                        | keep order:false                                                  |
+-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> explain select * from test where updated_date > '2023-12-31 23:50:00' and updated_date<'2024-01-01 00:00:00';
+-------------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows    | task      | access object                                     | operator info                                                                                                                                                                      |
+-------------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexLookUp_10                | 9355819.67 | root      |                                                   |                                                                                                                                                                                    |
| ├─IndexRangeScan_8(Build)     | 9355819.67 | cop[tikv] | table:test, index:IX_CUST_RELA_DATE(updated_date) | range:(2023-12-31 23:50:00,2024-01-01 00:00:00), keep order:false, stats:partial[ix_tpcnr_bcn:allEvicted, ix_tpcnr_epn:allEvicted, ix_tpcnr_pan:allEvicted...(more: 1 allEvicted)] |
| └─TableRowIDScan_9(Probe)     | 9355819.67 | cop[tikv] | table:test                                        | keep order:false, stats:partial[ix_tpcnr_bcn:allEvicted, ix_tpcnr_epn:allEvicted, ix_tpcnr_pan:allEvicted...(more: 1 allEvicted)]                                                  |
+-------------------------------+------------+-----------+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

v6.5.3

[pcqz]

/found gs

[pcqz]

Seems the histogram estimates the range size based on the converted values of type bytes/string, and the time type is not taken into account. Even if there are no out-of-range conditions, when the time span is in the range between different years/months/days, the over-estimated rows can still be seen, like this:

mysql> explain select * from test where updated_date > '2023-08-01 00:00:00' and updated_date<'2023-08-01 00:00:01';
+-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
| id                            | estRows | task      | access object                                     | operator info                                                     |
+-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
| IndexLookUp_10                | 1.50    | root      |                                                   |                                                                   |
| ├─IndexRangeScan_8(Build)     | 1.50    | cop[tikv] | table:test, index:IX_CUST_RELA_DATE(updated_date) | range:(2023-08-01 00:00:00,2023-08-01 00:00:01), keep order:false |
| └─TableRowIDScan_9(Probe)     | 1.50    | cop[tikv] | table:test                                        | keep order:false                                                  |
+-------------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> explain select * from test where updated_date > '2023-07-31 23:59:59' and updated_date<'2023-08-01 00:00:00';
+-------------------------------+-----------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
| id                            | estRows   | task      | access object                                     | operator info                                                     |
+-------------------------------+-----------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
| IndexLookUp_7                 | 246855.44 | root      |                                                   |                                                                   |
| ├─IndexRangeScan_5(Build)     | 246855.44 | cop[tikv] | table:test, index:IX_CUST_RELA_DATE(updated_date) | range:(2023-07-31 23:59:59,2023-08-01 00:00:00), keep order:false |
| └─TableRowIDScan_6(Probe)     | 246855.44 | cop[tikv] | table:test                                        | keep order:false                                                  |
+-------------------------------+-----------+-----------+---------------------------------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)