Repository navigation
Equal predicate at end of range or out of range does not recognize modifyCount #62665
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
The following is a (significantly) simplified version of an issue seen at a customer. The customer's example was a date column, but this example is simplified to use INT.
Statistics were collected early in the day, and during the day - new rows were inserted with today's date (100,000s of rows). But since ANALYZE had not been executed again - the estimated number of rows was lower than the actual number of rows.
1. Minimal reproduce step (Required)
-- disable auto analyze (to ensure that analyze does not automatically correct the issue)
set @@global.tidb_enable_auto_analyze=OFF;
CREATE TABLE `t1` (
`a` int,
`b` int,
`c` int,
KEY `ia` (`a`)
);
set @@cte_max_recursion_depth=100000;
INSERT INTO t1 (a, b, c)
SELECT mod(a, 150) as a, mod(a, 1000) AS b, mod(a, 10) AS c
FROM (
WITH RECURSIVE x AS (
SELECT 1 AS a
UNION ALL
SELECT a + 1 AS a
FROM x
WHERE a < 10000
)
SELECT a
FROM x
) AS subquery;
-- check the max values for each column
select max(a), max(b), max(c) from t1;
+--------+--------+--------+
| max(a) | max(b) | max(c) |
+--------+--------+--------+
| 149 | 999 | 9 |
+--------+--------+--------+
-- insert a single new row as the last row in the table
Insert into t1 values (150, 1000, 10);
-- collect statistics
Analyze table t1;
-- insert 600+ new rows with the same maximum values as the single row
Insert into t1 select 150, 100, 10 from t1 where a < 10;
show stats_meta;
+---------+------------+----------------+---------------------+--------------+-----------+---------------------+
| Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count | Last_analyze_time |
+---------+------------+----------------+---------------------+--------------+-----------+---------------------+
| test | t1 | | 2025-07-28 14:57:53 | 669 | 10670 | 2025-07-28 14:53:23 |
+---------+------------+----------------+---------------------+--------------+-----------+---------------------+
2. What did you expect to see? (Required)
Explain of the query should recognize that there have been rows inserted. Given that the count for this value (using reproduce steps above) shows 669 rows.
select count(*) from t1 where a = 150;
+----------+
| count(*) |
+----------+
| 669 |
+----------+
3. What did you see instead (Required)
The explain output is similar to before the rows were inserted
tidb> explain select * from t1 where a = 150;
+--------------------------------+---------+-----------+-----------------------+-----------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+-----------+-----------------------+-----------------------------------+
| IndexLookUp_11 | 1.07 | root | | |
| ├─IndexRangeScan_9(Build) | 1.07 | cop[tikv] | table:t1, index:ia(a) | range:[150,150], keep order:false |
| └─TableRowIDScan_10(Probe) | 1.07 | cop[tikv] | table:t1 | keep order:false |
+--------------------------------+---------+-----------+-----------------------+-----------------------------------+
3 rows in set (0.00 sec)
4. What is your TiDB version? (Required)
Current release 8.5
Activity
planner: handle histogram last bucket end value underrepresented (#62695
planner: handle histogram last bucket end value underrepresented (#62695