cockroachdb / cockroachdb/cockroach
sql/inspect: add INSPECT support for indexes on virtual columns
- 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
Assessment
This issue has not been assessed yet.