cockroachdb / cockroachdb/cockroach

sql/inspect: add INSPECT support for indexes on virtual columns

Open
#155,841 0 comments 0 reactions 0 assignees View on GitHub
C-enhancement T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Currently, we are unable to validate consistency of any index that has keys over virtual columns. Internally, INSPECT will run a `LEFT LOOKUP JOIN ... FORCE_INDEX`. And this fails if the keys included in the join are for virtual columns. The plan fails with an error like:
```
could not produce a query plan conforming to the LOOKUP JOIN hint
```

These types of indexes are blocked. This issue is opened to remove the block and allow them to be inspected. The thinking is that we can do a merge join instead of a lookup join. But this requires more investigation.

Before the block, you could hit an error during INSPECT with this set of commands:
```
CREATE TABLE t1 (
c1 INT,
c2 INT AS (c1 + 1) VIRTUAL,
INDEX (c2)
);
-- CREATE TABLE

INSERT INTO t1 VALUES (1);
-- INSERT 0 1

SET enable_inspect_command = true;

INSPECT TABLE t1;
-- ERROR: INSPECT found inconsistencies
-- SQLSTATE: 22000
```

Jira issue: CRDB-55650

Epic CRDB-55075

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.