partial index ADD INDEX can publish invalid-date predicate and block future writes
- 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
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