planner, expression: reuse one hidden generated column for identical expression indexes
- 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
Assessment
This issue has not been assessed yet.