pingcap / pingcap/tidb

Prepared DECIMAL range aggregation on indexed column is treated as a plan-cache hit when it should replan

Open
#67,391 0 comments 0 reactions 0 assignees View on GitHub
AI-Testing fuzz/shiro severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Suggested labels: `type/bug`, `sig/planner`, `AI-Testing`, `fuzz/shiro`, `severity/moderate`

### 1. Minimal reproduce step (Required)

```sql
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`c0` decimal(12,2) NOT NULL,
`c1` date NOT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `idx_c0` (`c0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO t1 (id, c0, c1) VALUES
(278, 5.41, '2026-12-24'),
(279, 62.95, '2026-10-31'),
(280, 93.90, '2025-11-01');
```

Run the literal query once:

```sql
SELECT COUNT(*) AS cnt, SUM(c0) AS sum1
FROM t1
WHERE c0 > 3.49
AND c0 < 40.06;
```

Then run the prepared-statement sequence:

```sql
PREPARE stmt FROM 'SELECT COUNT(*) AS cnt, SUM(c0) AS sum1 FROM t1 WHERE c0 > ? AND c0 < ?';

SET @p1=30.31, @p2=74.77;
EXECUTE stmt USING @p1, @p2;
SELECT @@last_plan_from_cache;

SET @p1=3.49, @p2=40.06;
EXECUTE stmt USING @p1, @p2;
SELECT @@last_plan_from_cache;
```

A plan replayer captured from the failing run is attached as `plan_replayer.zip`.

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

The execution with:

```sql
SET @p1=3.49, @p2=40.06;
EXECUTE stmt USING @p1, @p2;
```

should not be treated as a plan-cache hit for this query shape, so `SELECT @@last_plan_from_cache` should return:

```sql
last_plan_from_cache=0
```

### 3. What did you see instead (Required)

`SELECT @@last_plan_from_cache` returns:

```sql
last_plan_from_cache=1
```

This was captured by Shiro's `PlanCache` oracle from local report directory:

```text
reports/case_0004_019d25f3-2ddf-7061-a79f-7030dfc82ef3
```

A related supporting artifact from the same fresh batch is:

```text
reports/case_0002_019d25e8-9d21-7c1f-8dbb-354515841120
```

It shows the same broad `PlanCache` symptom (`expected: last_plan_from_cache=0`, `actual: last_plan_from_cache=1`) on a more complex partitioned `timestamp` range case, but this issue uses `case_0004` as the primary repro because the `t1` aggregate reproducer is materially smaller.

### 4. What is your TiDB version? (Required)

```sql
Release Version: rc2-23450-g2ec17cff63
Edition: Community
Git Commit Hash: 2ec17cff63dbb3ae4a7040265f906b09b82c31c0
Git Branch: 65791
UTC Build Time: 2026-03-25 16:31:40
GoVersion: go1.25.8
Race Enabled: false
Check Table Before Drop: false
Store: tikv
Kernel Type: Classic
```

## Analysis

1. The prepared plan-cache eligibility check likely misses single-table aggregates whose access path is an index range over parameterized `DECIMAL` bounds. Evidence: the plan replayer isolates a single `IndexRangeScan` on `idx_c0` with pushed-down `StreamAgg`, yet the prepared replay reports a cache hit.
2. The prepared path may be reusing a cached range-scan plan after rebinding materially different numeric bounds (`30.31..74.77` vs `3.49..40.06`) without re-evaluating whether the statement should remain cacheable.
3. This looks like a parameter-sensitive access-path/cacheability issue rather than a generic aggregation bug, because the literal query is a simple single-table aggregate and the only changing inputs are the two range parameters.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.