pingcap / pingcap/tidb

[ttl] TTL jobs can skip expired rows when clustered primary keys contain SET

Open
#70,836 0 comments 0 reactions 0 assignees View on GitHub
affects-7.1 affects-7.5 affects-8.1 affects-8.5 severity/major sig/sql-infra 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)

The following steps use only SQL on an unmodified TiDB built from the current `master` branch. No source change, failpoint, or test hook is required.

```sql
SET GLOBAL tidb_ttl_job_enable = ON;
SET GLOBAL tidb_ttl_scan_batch_size = 1;
SET GLOBAL tidb_ttl_delete_batch_size = 1;

DROP DATABASE IF EXISTS ttl_set_issue_repro;
CREATE DATABASE ttl_set_issue_repro;

CREATE TABLE ttl_set_issue_repro.t_set (
status SET('z','a','b') NOT NULL,
id INT NOT NULL,
expire_at DATETIME NOT NULL,
PRIMARY KEY (status, id) CLUSTERED
) TTL = expire_at + INTERVAL 1 DAY
TTL_ENABLE = 'ON'
TTL_JOB_INTERVAL = '24h';

INSERT INTO ttl_set_issue_repro.t_set VALUES
('z', 1, '2000-01-01 00:00:00'),
('a', 2, '2000-01-01 00:00:00'),
('b', 3, '2000-01-01 00:00:00');
```

Wait until the first TTL job finishes:

```sql
SELECT t.TABLE_NAME, s.LAST_JOB_FINISH_TIME, s.LAST_JOB_SUMMARY
FROM mysql.tidb_ttl_table_status AS s
JOIN information_schema.tables AS t ON t.TIDB_TABLE_ID = s.TABLE_ID
WHERE t.TABLE_SCHEMA = 'ttl_set_issue_repro'
AND t.TABLE_NAME = 't_set';

SELECT status + 0 AS physical_value, status, id
FROM ttl_set_issue_repro.t_set
ORDER BY status, id;
```

The first job was reported as finished with this summary:

```text
{"total_rows":1,"success_rows":1,"error_rows":0,"total_scan_task":1,"scheduled_scan_task":1,"finished_scan_task":1}
```

The table still contained:

```text
physical_value status id
2 a 2
4 b 3
```

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

The first TTL job should scan and delete all three expired rows. Its summary should report three successful rows, and the table should be empty.

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

The TTL job reports `finished` after scanning and deleting only the first physical SET value. Rows after that value are skipped until a later TTL job starts from the beginning.

TTL paginates a clustered primary key with SQL cursor predicates. A SET common handle is physically ordered by its bitmask, but the cursor is currently emitted as a display string. When member declaration order differs from lexical order, the cursor predicate no longer represents the physical scan position.

This issue tracks SET pagination separately from the ENUM issue #70764 and its fix #70766. Fixing it requires a lossless cursor representation that the planner can still turn into a common-handle range; changing the TTL DDL to reject SET would introduce a compatibility problem.

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

The reproduction above was verified with a real TiKV started by TiUP and a clean worktree at `master` commit `db35d47066648fe73abce6318d53fc625df51490`.

```text
Release Version: v9.0.0-beta.2.pre-2176-gdb35d47066
Edition: Community
Git Commit Hash: db35d47066648fe73abce6318d53fc625df51490
Git Branch: codex/fix-ttl-enum-set-pagination
UTC Build Time: 2026-08-31 15:42:59
GoVersion: go1.26.5-X:nodwarf5
Race Enabled: false
Check Table Before Drop: false
Store: tikv
Kernel Type: Classic
```

Contributor guide

Open the contributing guide

Research direction

Start with the SQL reproduction for a clustered primary key containing SET values, then trace TTL pagination, its SQL cursor predicates, and common-handle range planning. Done means the first TTL job deletes all three expired rows, reports three successful rows, and leaves the table empty without rejecting SET compatibility.

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
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.