pingcap / pingcap/tidb

planner: Prepared plan cache reuses UNIX_TIMESTAMP return scale across executions

Open
#70,713 5 comments 0 reactions 0 assignees View on GitHub
affects-25.10 affects-26.3 affects-7.5 affects-8.1 affects-8.5 contribution severity/critical sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```sql
SET @@tidb_enable_prepared_plan_cache = 1;
DROP TABLE IF EXISTS t;
CREATE TABLE t (a INT);
INSERT INTO t VALUES (1);

PREPARE stmt FROM 'SELECT UNIX_TIMESTAMP(?) FROM t';

SET @v = '2020-01-01 00:00:00.1';
EXECUTE stmt USING @v;
SELECT @@last_plan_from_cache;

SET @v = '2020-01-01 00:00:00.123456';
EXECUTE stmt USING @v;
SELECT @@last_plan_from_cache;

SELECT UNIX_TIMESTAMP('2020-01-01 00:00:00.123456') FROM t;
```
The integer part can vary with the session time zone. The relevant output is the fractional part:
```sql
-- first EXECUTE
1577808000.1
@@last_plan_from_cache = 0

-- second EXECUTE
1577808000.1
@@last_plan_from_cache = 1

-- direct query without the prepared cached plan
1577808000.123456
```

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

Each execution should use the current parameter value. The second execution should preserve the six fractional digits and return the equivalent of 1577808000.123456.

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

The second execution hits the prepared plan cache but reuses the return scale derived from the first execution. Its result is truncated to one fractional digit, so the result depends on which parameter value populated the cache first.
UNIX_TIMESTAMP derives its return Decimal scale from the parameter value while building the builtin signature. The plan cache matches both parameters as the same string type and reuses the first plan. UNIX_TIMESTAMP is not in UnCacheableFunctions, and a cache hit does not rebuild the builtin signature.

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

Release Version: v9.0.0
Edition: Community
Git Commit Hash: 3f9b2e46e7d2d79a29619c987dd7856da5bb29fb
Git Branch: HEAD
UTC Build Time: None
GoVersion: go1.25.5
Race Enabled: false
Check Table Before Drop: false
Store: unistore

Contributor guide

Open the contributing guide

Research direction

Start by tracing the prepared plan cache handling for the UNIX_TIMESTAMP builtin signature, including the UnCacheableFunctions check and the cache-hit path. Run the SQL reproduction and verify that repeated executions preserve each parameter's fractional scale while @@last_plan_from_cache remains effective.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.