pingcap / pingcap/tidb

partial index ADD INDEX can publish invalid-date predicate and block future writes

Open
#70,571 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Related to #70119, but this is a different consumer path: partial-index DDL backfill.

### 1. Minimal reproduce step

Run TiDB with a real TiKV backend, then execute:

```sql
use test;
set @@session.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';

drop table if exists dml_blocked_by_partial_index;
create table dml_blocked_by_partial_index (
id int primary key,
u int,
d date
);

insert into dml_blocked_by_partial_index values
(1, 10, '2024-01-15'),
(2, 20, '2025-01-15');

alter table dml_blocked_by_partial_index
add unique index uidx(u) where '2024-00-01' < d;

admin check table dml_blocked_by_partial_index;

insert into dml_blocked_by_partial_index values (3, 30, '2026-01-15');
```

### 2. What did you expect to see?

The `ADD INDEX` should reject the partial-index predicate under `NO_ZERO_IN_DATE`, return `1292 Incorrect datetime value: '2024-00-01'`, and roll back before publishing the index.

This is also what happens if the same DDL is forced through the TiDB-side fallback checker:

```sql
insert into mysql.expr_pushdown_blacklist values('<', 'tikv', 'partial-index-sql-mode-control');
admin reload expr_pushdown_blacklist;

drop table if exists partial_index_fallback_control;
create table partial_index_fallback_control (
id int primary key,
u int,
d date
);

insert into partial_index_fallback_control values
(1, 10, '2024-01-15'),
(2, 20, '2025-01-15');

alter table partial_index_fallback_control
add unique index uidx(u) where '2024-00-01' < d;
```

The fallback-control `ADD INDEX` returns:

```text
[types:1292]Incorrect datetime value: '2024-00-01'
```

and the DDL job rolls back without publishing the index.

### 3. What did you see instead?

With default TiKV expression pushdown, the `ADD UNIQUE INDEX` succeeds:

- DDL terminal: success
- final schema state: public
- index KV count over the two existing valid rows: 2
- `ADMIN CHECK TABLE` succeeds

But the published partial index then blocks an ordinary later write:

```sql
insert into dml_blocked_by_partial_index values (3, 30, '2026-01-15');
```

returns:

```text
[types:1292]Incorrect datetime value: '2024-00-01'
```

The failing later write reaches the partial-index DML maintenance path:

```text
pkg/table/tables.(*index).MeetPartialConditionWithChunk
pkg/table/tables.(*TableCommon).addIndices
pkg/table/tables.(*TableCommon).addRecord
```

So the problem is not only that the initial DDL terminal differs. A bad public partial-index schema is created, and future writes to the table can fail because TiDB later re-evaluates the stored invalid predicate.

### 4. Root cause suspicion

The DDL partial-index backfill request can push the predicate to TiKV as `TableScan -> Selection`, but the DDL DAG request does not carry SQL mode:

- `pkg/ddl/index_cop.go:175-215` sets timezone and flags on `tipb.DAGRequest`, but does not set `SqlMode`.
- When `conditionPushed=true` and only one index is being backfilled, `pkg/ddl/backfilling_operators.go:911-917` skips the TiDB-side `indexConditionCheckers`.
- The pushed path can therefore publish the index using TiKV-side permissive evaluation, while the TiDB fallback checker rejects the same predicate under strict SQL mode.

This looks like the same missing `DAGRequest.SqlMode` root family as #70119, but through DDL partial-index backfill instead of DML delete/update.

### 5. Version

Observed on TiDB commit:

```text
ca95cc55e1b28678956465f160059ea0834d5fc9
```

with a local real TiKV playground. The earlier root issue #70119 is still open, and this commit still omits SQL mode from the DDL partial-index DAG request.

Contributor guide

Open the contributing guide

Research direction

Run the minimal reproduction with a real TiKV backend, then inspect pkg/ddl/index_cop.go:175-215 and pkg/ddl/backfilling_operators.go:911-917, along with the reported DML maintenance path. Compare the pushed and fallback checker behavior under NO_ZERO_IN_DATE. Done when ADD INDEX rejects the invalid predicate before publishing and later writes are not blocked by a published invalid partial index.

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.