pingcap / pingcap/tidb

Prepared plan cache returns stale results when a folded/inferred payload depends on a session variable absent from the cache key

Open
#69,650 1 comment 0 reactions 0 assignees View on GitHub
affects-7.5 affects-8.1 affects-8.5 affects-9.0 found-by-ai severity/major sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

The prepared plan cache can reuse an evaluated payload that depends on a **session variable which is not part of the plan-cache key**. After the session changes such a variable, a cached `EXECUTE` returns a value computed under the *old* setting, while the identical statement run directly (or after `ADMIN FLUSH SESSION PLAN_CACHE`) returns the correct value. This is a user-visible wrong result — a `WHERE` predicate built on the stale value can silently drop rows.

Four distinct session variables reproduce this; each is a self-contained repro:

```sql
SET tidb_enable_prepared_plan_cache = 1;

-- (A) default_week_format: constant-folded WEEK() value
CREATE TABLE t(id INT PRIMARY KEY); INSERT INTO t VALUES (1),(2);
SET @@default_week_format = 0;
PREPARE s FROM 'SELECT COUNT(*) FROM t WHERE WEEK(''2008-02-20'') = 8';
EXECUTE s; -- cnt = 0 (correct under fmt 0)
SET @@default_week_format = 1;
EXECUTE s; -- cnt = 0 WRONG (@@last_plan_from_cache = 1)
SELECT (SELECT COUNT(*) FROM t WHERE WEEK('2008-02-20') = 8); -- direct = 2

-- (B) div_precision_increment: constant-folded division value
SET @@div_precision_increment = 4;
PREPARE d FROM 'SELECT 1/7';
EXECUTE d; -- 0.1429
SET @@div_precision_increment = 8;
EXECUTE d; -- 0.1429 WRONG, direct 1/7 = 0.14285714

-- (C) div_precision_increment: cached AVG() return scale
CREATE TABLE a(x DECIMAL(10,0)); INSERT INTO a VALUES (1),(2);
SET @@div_precision_increment = 4;
PREPARE g FROM 'SELECT CAST(AVG(x) AS CHAR) FROM a';
EXECUTE g; -- 1.5000
SET @@div_precision_increment = 8;
EXECUTE g; -- 1.5000 WRONG, direct = 1.50000000

-- (D) default_collation_for_utf8mb4: cached _utf8mb4 literal collation
SET @@default_collation_for_utf8mb4 = 'utf8mb4_bin';
PREPARE c FROM 'SELECT _utf8mb4''A'' = _utf8mb4''a''';
EXECUTE c; -- 0
SET @@default_collation_for_utf8mb4 = 'utf8mb4_general_ci';
EXECUTE c; -- 0 WRONG, direct = 1
```

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

A cached `EXECUTE` must return the same result as the identical statement executed directly under the current session. In every case above, running the same SQL directly (or the same prepared statement after `ADMIN FLUSH SESSION PLAN_CACHE`) yields the correct value for the new setting, so the plan should not be reused across the session-variable change (or the cached payload must be rebuilt).

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

After changing the session variable, the second `EXECUTE` reports `@@last_plan_from_cache = 1` and returns the value computed under the previous setting:

| variable | statement | direct / after flush | cached `EXECUTE` |
| --- | --- | --- | --- |
| `default_week_format` | `WEEK('2008-02-20') = 8` count | `2` | `0` |
| `div_precision_increment` | `SELECT 1/7` | `0.14285714` | `0.1429` |
| `div_precision_increment` | `CAST(AVG(x) AS CHAR)` | `1.50000000` | `1.5000` |
| `default_collation_for_utf8mb4` | `_utf8mb4'A' = _utf8mb4'a'` | `1` | `0` |

Controls stay correct, which localizes the bug to the cached payload rather than the evaluators: a column expression `WEEK(d)` / `a/b` follows the current setting under a cache hit; an explicit `WEEK(date,1)` or explicit `COLLATE` is stable; and `ADMIN FLUSH SESSION PLAN_CACHE` restores the correct value.

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

```
Release Version: v9.0.0-beta.2.pre-1774-g81ec977cb8
Git Commit Hash: 81ec977cb8bf97e0c9805dfc0be8ffcbd4b0bbeb
UTC Build Time: 2026-05-28 03:35:30
Edition: Community
Store: tikv
```

Likely root cause — cached payloads read session state absent from the plan-cache key

The prepared plan-cache key (`pkg/planner/core/plan_cache_utils.go`) includes SQL mode, timezone offset, connection charset/collation, `ForeignKeyChecks`, etc., but not `default_week_format`, `div_precision_increment`, or `default_collation_for_utf8mb4`. Meanwhile:

- `WEEK()` without an explicit mode reads `GetDefaultWeekFormatMode()`, and decimal division reads `GetDivPrecisionIncrement()` (`pkg/expression/builtin_arithmetic.go`). When all arguments are constants, `pkg/expression/constant_fold.go` folds the call into a plain `Constant`, and the mutable-constant guard only recognizes `ParamMarker` / `DeferredExpr`, so the folded value is cached (cases A, B).
- `AVG()` type inference reads `GetDivPrecisionIncrement()` and stores the scale in the aggregate descriptor `RetTp.Decimal` (`pkg/expression/aggregation/base_func.go`); a cache hit divides with current precision but rounds/renders through the old cached scale (case C).
- `_utf8mb4` literals without explicit `COLLATE` get `GetDefaultCollationForUTF8MB4()` written into the literal field type during expression rewrite (`pkg/planner/core/expression_rewriter.go`); a cache hit reuses the old literal collation (case D).

The unifying obligation: a cached payload must be a pure function of the plan-cache key; any session/config input consumed while building the payload must either be in the key or force a rebuild.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the four SQL cases and confirming the cache-hit results. Read pkg/planner/core/plan_cache_utils.go, pkg/expression/builtin_arithmetic.go, pkg/expression/constant_fold.go, pkg/expression/aggregation/base_func.go, and pkg/planner/core/expression_rewriter.go to trace session-variable inputs into cached payloads. Done means cached EXECUTE results match direct execution after each session-variable change, with the relevant regression coverage passing.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.