cockroachdb / cockroachdb/cockroach

sql/inspect: consistency checks skip dangling entries outside existing primary-row bounds

Open
#174,882 1 comment 0 reactions 1 assignee Claimed by @bowencrl View on GitHub
A-sql-execution C-bug O-agent T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

INSPECT can return success while a regular or partial secondary index contains a
dangling entry. This happens when the entry's primary key is inside the span
assigned to the checker but outside the first and last existing primary rows.

The job planner assigns spans covering the table's primary-index key space.
For a nonempty span, `getPredicateAndQueryArgs` uses the first and last existing
primary rows to construct SQL bounds. It applies those bounds to the secondary
scan too. This excludes the empty portions at either end, even though those
portions may have stale secondary entries.

For example:

```text
Assigned span: 0 <= id < 100
First and last existing primary row: id = 20 and id = 80
Dangling secondary entry: id = 10
Generated check bounds: 20 <= id <= 80
```

The entry at 10 is never checked. This is a coverage bug in INSPECT's own query
construction, separate from optimizer scan narrowing using a partial-index
predicate.

**To Reproduce**

Add this temporary test to `pkg/sql/inspect/index_consistency_check_test.go`:

```go
func TestInspectScanBoundExclusionsIssue(t *testing.T) {
defer leaktest.AfterTest(t)()
defer log.Scope(t).Close(t)

runIndexConsistencyCheckTestCases(t, []indexConsistencyCheckTestCase{
{
desc: "regular index entry before first primary row",
indexDDL: []string{
"CREATE INDEX idx_t_regular ON test.t (b)",
},
danglingIndexEntryInsertQuery: "SELECT 0, 6000, 300, 'ghost', 'e_x', 1.5",
expectedIssues: []inspectIssue{
{ErrorType: "dangling_secondary_index_entry", PrimaryKey: "e'(0, \\'ghost\\')'"},
},
expectedErrRegex: expectedInspectFoundInconsistencies,
},
{
desc: "partial index entry before first primary row",
indexDDL: []string{
"CREATE INDEX idx_t_partial ON test.t (b) WHERE b > 5000",
},
danglingIndexEntryInsertQuery: "SELECT 0, 6000, 300, 'ghost', 'e_x', 1.5",
expectedIssues: []inspectIssue{
{ErrorType: "dangling_secondary_index_entry", PrimaryKey: "e'(0, \\'ghost\\')'"},
},
expectedErrRegex: expectedInspectFoundInconsistencies,
},
})
}
```

Run:

```sh
./dev test pkg/sql/inspect -f TestInspectScanBoundExclusionsIssue -v
```

The existing harness creates a table with primary key `(a, d)` and primary rows
whose `a` values run from 1 to 2000. It directly inserts a secondary-index KV entry
for `(a, d) = (0, 'ghost')`, without inserting the corresponding primary row.
The entry has `b = 6000`, so it satisfies the partial predicate in the second
case. Normal SQL inserts cannot create this corruption; the harness bypasses
index maintenance.

**Expected behavior**

Both cases should report one `dangling_secondary_index_entry` for
`(0, 'ghost')`. Entries inside an assigned span must not be excluded merely
because there are no primary rows at that end of the span.

**Environment:**

- Source: PR [#3821](https://github.com/cockroachlabs/cockroach/pull/3821),
commit `69b163af014dda0d037ba1190d4bf94157b338b6`.
- Reproduction date: 2026-09-07.
- Server: in-process three-node Go test cluster on macOS arm64.
- Client: existing INSPECT SQL/KV corruption-test harness.
- The shared row-derived bounds predate the partial-index changes; the earliest
affected release has not been verified.

Code references:

- [Span assignment](https://github.com/cockroachlabs/cockroach/blob/69b163af014dda0d037ba1190d4bf94157b338b6/pkg/sql/inspect/inspect_resumer.go#L196).
- [First/last-row bounds](https://github.com/cockroachlabs/cockroach/blob/69b163af014dda0d037ba1190d4bf94157b338b6/pkg/sql/spanutils/query_bounds.go#L60).
- [SQL-bound construction](https://github.com/cockroachlabs/cockroach/blob/69b163af014dda0d037ba1190d4bf94157b338b6/pkg/sql/inspect/check_helpers.go#L306).

Epic CRDB-65904

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.