opt: error when selecting from table with partitioned primary and secondary index with different partition column types

[rytaft]

Describe the problem

On 22.1.0-22.1.5, attempting to select data from a table which has different partitioning columns used for the primary and secondary indexes can cause an error. This occurs if the primary index has zone configurations applied with different regions for different partitions, and the secondary index has a different column type than the primary index for its partitioning column.

To Reproduce

Run a demo cluster with CockroachDB v22.1.5 using:

./cockroach demo --demo-locality=region=gcp-europe-west1,az=1:region=gcp-us-east1,az=2:region=gcp-us-west1,az=3 --nodes 3

Then run:

CREATE DATABASE bug;
USE bug;

CREATE TYPE region_enum AS ENUM ('EUROPE_WEST', 'US_WEST', 'US_EAST');
CREATE TABLE "user" (
  region region_enum NOT NULL,
  id uuid NOT NULL DEFAULT uuid_generate_v4(),
  col1 int2  AS (col2::int2) VIRTUAL,
  col2 varchar NOT NULL,
  PRIMARY KEY (region, id)
);

CREATE UNIQUE INDEX ON "user" (col1, col2);

ALTER TABLE "user" PARTITION BY LIST (region) (
  PARTITION user_eu_west VALUES IN ('EUROPE_WEST'),
  PARTITION user_us_east VALUES IN ('US_EAST'),
  PARTITION user_us_west VALUES IN ('US_WEST'),
  PARTITION DEFAULT VALUES IN (default)
);

ALTER PARTITION user_eu_west OF TABLE "user" CONFIGURE ZONE USING
  num_replicas = 5,
  num_voters = 3,
  lease_preferences = '[[+region=gcp-europe-west1]]',
  voter_constraints = '[+region=gcp-europe-west1]';

ALTER INDEX "user"@user_col1_col2_key PARTITION BY LIST (col1) (
  PARTITION user_col1_col2_key_eu_west VALUES IN (43,32,500,600),
  PARTITION user_col1_col2_key_us_west VALUES IN (1),
  PARTITION DEFAULT VALUES IN (default)
);

After a few seconds, run:

SELECT *
FROM "user"
WHERE region = 'EUROPE_WEST';

It produces this error:

ERROR: unsupported comparison: region_enum to int
SQLSTATE: 42804

Expected behavior
This should not produce any error.

Environment:

• CockroachDB version 22.1.5
• Server OS: MacOS
• Client app cockroach demo

Jira issue: CRDB-18609

[rytaft]

Reopening until #86218 is merged

[rytaft]

#86218 is merged, closing