planner: enable COALESCE UPDATE plan caching in stages with decimal precision guards

Enhancement

Support non-prepared plan caching for a conservative subset of COALESCE assignments in UPDATE, then expand support in stages. The immediate priority is Step 1: address a customer UPDATE cache-miss pattern promptly, while retaining the correctness guards for unsupported expression shapes.

Problem and expected behavior

At master commit 51a1a4abfc192a91f98fe968ad87eced9221f663, COALESCE is in expression.UnCacheableFunctions. Consequently, otherwise eligible UPDATE statements containing it cannot use the non-prepared plan cache even when both tidb_enable_non_prepared_plan_cache and tidb_enable_non_prepared_plan_cache_for_dml are enabled.

A sanitized example of the relevant SQL shape is:

CREATE TABLE cache_update_test (
    id BIGINT PRIMARY KEY,
    state INT,
    amount DECIMAL(20, 6),
    revision BIGINT
);
INSERT INTO cache_update_test VALUES (1, 0, 0, 1);

SET tidb_enable_non_prepared_plan_cache = ON;
SET tidb_enable_non_prepared_plan_cache_for_dml = ON;

BEGIN PESSIMISTIC;
UPDATE cache_update_test
SET state = COALESCE(2, state),
    amount = COALESCE(1.25, amount),
    revision = GREATEST(2, revision)
WHERE id = 1 AND revision <= 2;

-- Repeat to check reuse after warm-up.
UPDATE cache_update_test
SET state = COALESCE(2, state),
    amount = COALESCE(1.25, amount),
    revision = GREATEST(2, revision)
WHERE id = 1 AND revision <= 2;
SELECT @@last_plan_from_cache;
ROLLBACK;

Current behavior: the COALESCE eligibility check prevents caching. Desired behavior: eligible repeated UPDATEs can hit the cache, with stored values, warnings, and transaction behavior preserved. This targets avoidable planning work; it does not claim to resolve lock waits or establish a production latency improvement.

Why not simply remove the blacklist entry?

Related proposal: https://github.com/pingcap/tidb/pull/69149.

Local experiments with a blanket removal found precision-sensitive differences. For example, warming up an UPDATE that assigns COALESCE(1.23456789, 0.01) to a VARCHAR column and then executing the same parameterized shape with COALESCE(3.14159, 0.1) could store "3.14159000" on a cache hit instead of the cache-disabled result "3.14159". Existing parameter-type compatibility permits some decimal precision/scale reductions, but the cached expression and cast types can retain the earlier precision.

These differences were observed after experimentally relaxing the blacklist, not as a wrong-result report against unmodified master. Separating precision signatures is also insufficient for every expression shape: cross-column assignments involving decimal-to-string conversion can differ during cache-aware planning even on a first miss. This motivates a narrow first step rather than general COALESCE support.

Step 1 - Prioritize the customer UPDATE pattern

A local implementation and scoped tests are available; this is not yet merged or released.

• Allow only direct non-prepared assignments of the form SET col = COALESCE(numeric_literal, col), where the target and fallback are the same integer or DECIMAL column.
• Accept integer and exact decimal literals. Keep NULL literals, floating-point/string arguments, nested expressions, cross-column assignments, SELECT, and prepared COALESCE statements outside this exception.
• Retain the shared function blacklist and existing parameter-type compatibility checks.
• For eligible statements, add each DECIMAL parameter's position, precision, and scale to the cache key. Different decimal signatures use separate entries; returning to a previously cached signature can hit again. Apply this guard to both session and instance plan caches.
• Do not mutate expression types in shared cached plans. Integer digit-length changes do not require separate entries.
• Add regression coverage for repeated hits, precision changes, supported UPDATE semantics, pessimistic transactions/rollback, rejected shapes, and cross-session instance-cache reuse.

Tradeoff: varying decimal precision can create more cache entries. This conservative scope is intended to unblock the supported UPDATE pattern quickly without depending on a general expression-type redesign.

Step 2 - Generalize expression and cast type handling

Follow-up work before permitting broader shapes or cross-precision reuse:

• Rebuild or otherwise correctly preserve the COALESCE aggregate return type and dependent implicit casts, including enclosing expressions.
• Account for constant folding, deferred parameter evaluation, and both cache-miss and cache-hit paths.
• Use safe cloning/rebuilding or reject reuse when needed; never change types in an expression shared by an instance cache.

The Step 1 implementation includes a TODO for this follow-up.

Step 3 - Expand compatibility and regression validation

Before widening eligibility, add broader coverage for:

• NULL transitions, mixed types, signed/unsigned values, precision/scale changes in both directions, strings, and collations.
• Stored values, result metadata, rounding, errors, and warnings; scalar and vectorized evaluation.
• Prepared and non-prepared execution, session/instance caches, and concurrent reuse.
• MySQL differential checks that distinguish normal execution from prepared-statement behavior, plus server/protocol and real-storage validation where applicable.

The Step 1 implementation includes a TODO for this broader validation. Step 1 already has tests for its supported scope; Step 3 concerns generalizing that scope.

Validation so far

Local validation on the master SHA above includes customer-shaped UPDATE tests using a TiDB-compatible schema fixture, full plancache and instanceplancache package runs during implementation, focused final tests in normal and nextgen builds, and lint. The final schema/parameter fixture extension was rerun in both builds. Tests cover high-scale decimal value changes, reduced-scale inputs, predicate rejection, rollback, cache-disabled result comparisons, and explicit exclusion of unsupported cases.

These are local mock-storage tests, not a production replay, real-TiKV test, MySQL-server differential run, or performance benchmark. The production build has not been mapped to this master SHA. All SQL identifiers and data shown here are synthetic; customer DDL, deployment identifiers, and diagnostic links are omitted.