cockroachdb / cockroachdb/cockroach
sql/schemachanger: CREATE INDEX fails with internal error on multi-column PARTITION ALL BY tables
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
## Summary
`maybeAddPartitionDescriptorForIndex` in the declarative schema changer has a bug in the partition column prepending loop. When a table uses multi-column PARTITION ALL BY (e.g., `PARTITION ALL BY LIST (a, b)`) and a user creates a secondary index whose first key column matches the first partition column (e.g., `CREATE INDEX ON t(a, d)`), the loop at line 537 breaks immediately after matching the first field, skipping all subsequent partition columns. This causes a mismatch between the cloned partitioning descriptor (which expects 2+ partition columns) and the actual index columns, leading to an internal error during schema change execution.
## Affected code
- `pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_index.go:537-538` — early `break` in partition field iteration loop
- `pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_index.go:565` — `NumImplicitColumns` set to 0 due to empty `columnsToPrepend`
## Reproduction
```
ERROR: internal error: executing declarative schema change PostCommitNonRevertiblePhase stage 1 of 2:
relation "t": table has PARTITION ALL BY defined, but index idx does not have matching PARTITION BY
```
The error occurs during `PostCommitNonRevertiblePhase`, which is a non-revertible stage, potentially leaving the table in a partially broken state.
## Suggested fix direction
The loop at lines 532-557 should not `break` when a partition field matches `idxSpec.columns[0]`. Instead, it should `continue` (skip prepending that field since it's already the leading column) and process remaining partition fields.
_This issue was found via automated deep static analysis._
Jira issue: CRDB-62025
Epic CRDB-68473
Contributor guide
Research direction
Read pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_index.go around lines 532-557, then reproduce the multi-column PARTITION ALL BY case with a secondary index whose first column matches the first partition column. Verify that the partition fields are all handled and the CREATE INDEX completes without the reported internal error during schema change execution.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 72/100