cockroachdb / cockroachdb/cockroach

sql/schemachanger: CREATE INDEX fails with internal error on multi-column PARTITION ALL BY tables

Open
#166,613 1 comment 0 reactions 0 assignees View on GitHub
branch-master C-bug O-agent T-sql-foundations
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.