repro.md

CockroachDB version: v23.2.4

Issue summary

Virtual columns with user defined types cannot be analyzed once virtual column stats collection has been enabled.

Steps to reproduce

Cluster

cockroach demo --insecure --no-example-database

Demo (virtual column)

Create objects

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'dispatched', 'delivered');

CREATE TABLE orders (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "customer_id" UUID NOT NULL,
  "total" DECIMAL NOT NULL,
  "balance" DECIMAL NOT NULL,
  "order_ts" TIMESTAMPTZ(0) NOT NULL DEFAULT now(),
  "dispatch_ts" TIMESTAMPTZ(0),
  "delivery_ts" TIMESTAMPTZ(0),
  "status" order_status AS (
    CASE
      WHEN "delivery_ts" IS NOT NULL THEN 'delivered'
      WHEN "dispatch_ts" IS NOT NULL THEN 'dispatched'
      WHEN "balance" = 0 THEN 'paid'
      ELSE 'pending'
    END) VIRTUAL,

  INDEX ("status")
);

INSERT INTO orders ("customer_id", "total", "balance", "dispatch_ts", "delivery_ts") VALUES
  ('bdeb232e-12e9-4a33-9dd5-7bb9b694291a', 100, 100, NULL, NULL),
  ('0dc59725-d20b-4370-a05d-11db025a0064', 200, 0, NULL, NULL),
  ('d53d4021-9390-4b3a-9e5a-4bf1ff3e5a4c', 300, 0, now(), NULL),
  ('d53d4021-9390-4b3a-9e5a-4bf1ff3e5a4c', 400, 0, now(), now());

SELECT "total", "balance", "dispatch_ts", "delivery_ts", "status"
FROM orders;

Enable virtual column stats collection

SET CLUSTER SETTING sql.stats.virtual_computed_columns.enabled = 'on';
SET optimizer_use_virtual_computed_column_stats = 'on';

Force stats collection

ANALYZE orders;

Results in the following error:

ERROR: incompatible value type: type resolver unavailable to resolve type OID 100108
SQLSTATE: 42704