pingcap / pingcap/tidb

planner, expression: reuse one hidden generated column for identical expression indexes

Open
#68,032 0 comments 0 reactions 0 assignees View on GitHub
component/ddl component/expression type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

Background:

- Issue #67552 exposed a failure mode when a table has multiple expression indexes backed by different hidden generated columns that share the same expression.
- PR #67692 fixes the immediate planner bug by resolving the exact generated column identity first.
- However, TiDB still creates multiple hidden generated columns for semantically identical expression indexes on the same table.

Proposal:

When multiple expression indexes on the same table use the same expression, TiDB should reuse a single hidden generated column instead of creating one hidden generated column per index.

For example, if a table already has an expression index backed by a hidden generated column for `lower(name)`, then creating another expression index on the same table that also uses `lower(name)` should reuse that existing hidden generated column rather than creating a second one.

Expected benefits:

- Reduce duplicated hidden generated columns in table metadata.
- Reduce planner ambiguity around duplicate virtual expressions.
- Avoid duplicated maintenance work for logically identical generated columns.
- Make expression-index behavior easier to reason about.

Design considerations:

- Reuse should only happen when the normalized expression and derived metadata are compatible, for example type and collation related properties.
- Dropping one index must not drop the shared hidden generated column if it is still referenced by other indexes.
- The first stage can focus on new DDLs; automatic rewrite of existing table metadata can be handled separately if needed.

Related work:

- Bug: #67552
- Existing fix: #67692

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.