sql: indexes on expressions of ENUM types causes "column does not exist" errors

[fqazi]

If a table exists with an index expression that will require validation via synthetic check constraints:

CREATE TYPE typ_127147 AS ENUM ('a', 'b', 'c');
CREATE TABLE t (x TEXT PRIMARY KEY, INDEX ((x::typ_127147)));
INSERT INTO t VALUES ('a');

Then delete statement on the table can fail with:

DELETE FROM t where x='a';
ERROR: column "crdb_internal_idx_expr" does not exist
SQLSTATE: 42703

This happens because a synthetic check constraint is inject into the statement to ensure that crdb_internal_expr is a valid enum value of "typ".

Jira issue: CRDB-40415

[michae2]

Just confirming, is this different from https://github.com/cockroachdb/cockroach/issues/127147?

[fqazi]

Yeah, this was found while working on a fix that issue on the schema changer side. I noticed that we couldn't delete values in the first place in that example table :/

[DrewKimball]

[triage] Marcus will take a look, and figure out where this issue should live.

[mgartner]

You don't need DELETE to hit this. SELECT works fine:

CREATE TYPE foobar AS ENUM ('a', 'b', 'c');
CREATE TABLE t (x TEXT PRIMARY KEY, INDEX ((x::foobar)));
SELECT * FROM t where x='a';

[mgartner]

This is somewhat related to #61298: using column names to resolve columns in "table expressions", i.e., check constraint expressions, computed column expressions, partial index predicates, etc., is fragile.

In this case, it breaks because there is an implicit CHECK expression crdb_internal_idx_expr IN ('a', 'b', 'c') where crdb_internal_idx_expr is an inaccessible computed column with the expression x::foobar.

I have a few ideas on how to solve this in the short-term that I'll explore.

[mgartner]

Thanks for the report @fqazi! This was a nasty rough edge. #129080 fixes it.

[blathers-crl[bot]]

Based on the specified backports for linked PR #129080, I applied the following new label(s) to this issue: branch-release-23.1, branch-release-23.2, branch-release-24.1, branch-release-24.2. Please adjust the labels as needed to match the branches actually affected by this issue, including adding any known older branches.

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