pingcap / pingcap/tidb

Prepared plan cache causes incorrect NULL-safe comparison for nullable TIME columns

Open
#70,681 5 comments 0 reactions 0 assignees View on GitHub
affects-25.10 affects-26.3 affects-8.5 contribution may-affects-7.5 may-affects-8.1 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
DROP TABLE IF EXISTS t;
CREATE TABLE t (
tm TIME NULL
);

INSERT INTO t VALUES (NULL), ('12:00:00');

PREPARE stmt FROM
'SELECT COUNT(*) FROM t WHERE tm <=> ?';

-- Build the prepared plan with a valid value.
SET @p = '12:00:00';
EXECUTE stmt USING @p;

-- Reuse the cached plan with an invalid temporal value.
SET @p = 'not-a-time';
EXECUTE stmt USING @p;
```

For comparison, repeat the same test with:

```sql
SET SESSION tidb_enable_prepared_plan_cache = OFF;
```

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

Both plan-cache-enabled and plan-cache-disabled executions should return 0.
`not-a-time` is an invalid non-NULL value. It should not be treated as SQL NULL in a NULL-safe comparison.

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

With the prepared plan cache enabled:

```sql
mysql> SET SESSION tidb_enable_prepared_plan_cache = ON;
Query OK, 0 rows affected (0.001 sec)
mysql> SET @p = '12:00:00';
Query OK, 0 rows affected (0.000 sec)

mysql> EXECUTE s USING @p;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.001 sec)
mysql> SET @p = 'not-a-time';
Query OK, 0 rows affected (0.000 sec)
mysql> EXECUTE s USING @p;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set, 2 warnings (0.006 sec)
```

With the prepared plan cache disabled:

```sql
mysql> SET SESSION tidb_enable_prepared_plan_cache = OFF;
Query OK, 0 rows affected (0.001 sec)
mysql> SET @p = '12:00:00';
Query OK, 0 rows affected (0.001 sec)
mysql> EXECUTE s USING @p;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.005 sec)
mysql> SET @p = 'not-a-time';
Query OK, 0 rows affected (0.000 sec)
mysql> EXECUTE s USING @p;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set, 1 warning (0.002 sec)
```

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

```sql
mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.1.9-0.2-16-gd41b737498-dirty
Edition: Enterprise
Core Version: v8.5.5
Git Commit Hash: d41b737498814ac64b350a0b920ad1d94bc2fc32
Git Branch: fix/time-column-temporal-comparison-7.1.9-0
UTC Build Time: 2026-08-26 09:15:56
GoVersion: go1.25.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
```

Contributor guide

Open the contributing guide

Research direction

Start by running the provided SQL reproduction with tidb_enable_prepared_plan_cache both ON and OFF, comparing the cached and uncached executions for the nullable TIME column. Trace the prepared-plan cache and NULL-safe temporal-comparison paths; done means the invalid non-NULL value produces 0 in both modes without changing the expected warning behavior.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.