sql: cannot remove enum value when referencing table has expression index

[DrewKimball]

When a table has an expression index (like INDEX ((x * 100))), CRDB adds a hidden crdb_internal_index_expr column to the table. The logic that checks for usages of an enum value before that value can be dropped does not know how to handle this, which results in an error:

root@localhost:26257/defaultdb> CREATE TYPE typ AS ENUM ('a', 'b', 'c');
CREATE TYPE

Time: 24ms total (execution 24ms / network 0ms)

root@localhost:26257/defaultdb> CREATE TABLE t (x TEXT, INDEX ((x::typ)));
CREATE TABLE

Time: 151ms total (execution 148ms / network 3ms)

root@localhost:26257/defaultdb> INSERT INTO t VALUES ('a');
INSERT 0 1

Time: 29ms total (execution 29ms / network 0ms)

root@localhost:26257/defaultdb> ALTER TYPE typ DROP VALUE 'a';
ERROR: could not validate removal of enum value "a": count-value-usage: column "crdb_internal_idx_expr" does not exist
SQLSTATE: 42703

This particular bug has likely existed since v21.2 when expression indexes were introduced. There may be other similar bugs due to hidden columns.

Jira issue: CRDB-40295

Epic CRDB-37763

[blathers-crl[bot]]

Hi @DrewKimball, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[DrewKimball]

This has been fixed by #126555.

[DrewKimball]

Nevermind, this is still relevant. The reason it still reproduces even with the fix is that the index expression is of the enum type.

[fqazi]

This is coming from the WHERE clause in the query to count usages: https://github.com/cockroachdb/cockroach/blob/b1402788998f107bce90f298ee30c25c4084ac0d/pkg/sql/type_change.go#L956

We are trying to reference the index expression, which isn't accessible. We might need to cheat a bit a make the columns accessible internally, like index validation: https://github.com/cockroachdb/cockroach/blob/b1402788998f107bce90f298ee30c25c4084ac0d/pkg/sql/backfill.go#L2095

[rafiss]

re-opening until backports are complete