sql: statement bundles with expression indexes should have reproducible stats

[mgartner]

Expression indexes are implemented using inaccessible virtual computed columns. These columns are not shown shown in the schema.sql file of a statement bundle. But they are included in the stats-*.sql files.

When the schema.sql file is executed to try to reproduce a customer issue, the names of these inaccessible columns are dependent on the order of their associated indexes. This means that the names of the columns can be different from what they were in the customer's schema. This can create cases where the inaccessible columns' statistics refer to the wrong columns.

For example, suppose a customer created the following table and collected a statement bundle:

SET CLUSTER SETTING sql.stats.histogram_collection.enabled = true;
SET experimental_enable_expression_indexes = true;

CREATE TABLE t (a INT, INDEX i1 ((a + 10)), INDEX i2 ((a > 500)));

DROP INDEX i1;

CREATE INDEX i3 ON t ((a + 100));

INSERT INTO t SELECT generate_series(1, 1000);

ANALYZE t;

EXPLAIN ANALYZE (DEBUG) SELECT * FROM t;

The statement bundle would include a schema.sql like this:

CREATE TABLE t (
	a INT8 NULL,
	rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
	CONSTRAINT "primary" PRIMARY KEY (rowid ASC),
	INDEX i2 ((a > 500:::INT8) ASC),
	INDEX i3 ((a + 100:::INT8) ASC),
	FAMILY "primary" (a, rowid)
);

And a statistics file like this (I've abbreviated it here):

ALTER TABLE t INJECT STATISTICS '[
    {
        "columns": [
            "crdb_internal_idx_expr_1"
        ],
        "created_at": "2021-07-28 15:57:20.789126",
        "distinct_count": 1,
        "histo_col_type": "BOOL",
        "null_count": 1000,
        "row_count": 1000
    },
    {
        "columns": [
            "crdb_internal_idx_expr"
        ],
        "created_at": "2021-07-28 15:57:20.789126",
        "distinct_count": 1,
        "histo_col_type": "INT8",
        "null_count": 1000,
        "row_count": 1000
    }
]';

Note that the statistics list the type of crdb_internal_idx_expr_1 as BOOL. However, when you execute schema.sql, the crdb_internal_idx_expr_1 column is the virtual column backing i3's a + 100:::INT8 expression:

> SELECT * FROM (
  SELECT json_array_elements(
    crdb_internal.pb_to_json('cockroach.sql.sqlbase.Descriptor', descriptor, false)->'table'->'columns'
  ) AS desc FROM system.descriptor WHERE id = 't'::REGCLASS
) AS cols WHERE cols.desc->'name' = '"crdb_internal_idx_expr_1"';
                                                                                                desc
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  {"computeExpr": "a + 100:::INT8", "id": 4, "inaccessible": true, "name": "crdb_internal_idx_expr_1", "nullable": true, "type": {"family": "IntFamily", "oid": 20, "width": 64}, "virtual": true}

This will make debugging statement bundles that have expression indexes hard to debug. It will not be clear when the column names do not line up, and will likely lead to confusion when a customer issue cannot be reproduced with the statement bundle.

One proposed solution: for the statement bundle schema.sql file only, list the inaccessible columns as hidden virtual columns and print these column names as the index elements of expression indexes rather than their expression.

Jira issue: CRDB-8919

Epic CRDB-14510

[mgartner]

As of #68312, we no longer collect statistics for inaccessible virtual columns that back expression indexes. If that changes, we'll need to address this issue. For now, I'll move this to the backlog.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!