sql: histogram does not match column type after ALTER TABLE ALTER COLUMN SET DATA TYPE

[cockroach-teamcity]

pkg/ccl/testccl/sqlccl/sqlccl_test.TestExplainGist failed on master @ 9d3dd0530818b9c5c0f7dac04e8a8de4acf1bba4:

            count(*) AS col_975
        FROM
            defaultdb.public.seed AS tab_692
        GROUP BY
            tab_692._jsonb
        HAVING
            bool_and(true::BOOL)::BOOL
        ORDER BY
            tab_692._jsonb NULLS LAST, tab_692._jsonb DESC NULLS LAST, tab_692._jsonb ASC NULLS FIRST, tab_692._jsonb NULLS LAST
        LIMIT
            57:::INT8;
        DELETE FROM defaultdb.public.seed AS tab_693 USING defaultdb.public.seed AS tab_694 WHERE true;
        DELETE FROM defaultdb.public.seed AS tab_695 USING defaultdb.public.seed AS tab_696 WHERE false;
        INSERT
        INTO
            defaultdb.public.seed AS tab_697
        SELECT
            NULL AS col_976,
            tab_698._int2 AS col_977,
            (-2136037883700172646):::INT8 AS col_978,
            tab_698._float8 AS col_979,
            0.47847028619282:::FLOAT8 AS col_980,
            '1971-04-24':::DATE AS col_981,
            tab_698._timestamp AS col_982,
            NULL AS col_983,
            NULL AS col_984,
            true AS col_985,
            tab_698._decimal AS col_986,
            tab_698._string AS col_987,
            '\xdd4e':::BYTES AS col_988,
            tab_698._uuid AS col_989,
            tab_698._inet AS col_990,
            tab_698._jsonb AS col_991,
            tab_698._enum AS col_992
        FROM
            defaultdb.public.seed AS tab_698;
        SELECT
            NULL AS col_993, '-16 years -10 mons -69 days -21:55:19.159286':::INTERVAL AS col_994
        FROM
            defaultdb.public.seed@seed__int8__float8__date_idx AS tab_699
        WHERE
            true
        ORDER BY
            tab_699._int8 ASC
        LIMIT
            48:::INT8;
        ALTER TABLE defaultdb.public.seed ALTER COLUMN _timestamptz SET DATA TYPE TIMESTAMP;

    explain_test.go:205: pq: internal error: type check failed while initializing stat 977112995467198465: histogram for table seed column _timestamptz created_at 2024-06-13 06:53:41.725681 does not match column type TIMESTAMP: TIMESTAMPTZ: UPDATE defaultdb.public.seed AS tab_701 SET _int4 = 1016711112:::INT8 WHERE tab_701._bool
    --- FAIL: TestExplainGist/main (3.83s)

Parameters:

• attempt=1
• run=23
• shard=2
  Help

See also: How To Investigate a Go Test Failure (internal)

This test on roachdash | Improve this report!

Jira issue: CRDB-39531

[yuzefovich]

cc @michae2 looks like the type check added for the histograms recently fired

[michae2]

Here's a repro (requires a crdb_test binary which can be built with ./dev build short -- --crdb_test):

CREATE TABLE t AS SELECT TIMESTAMPTZ '1999-12-31 23:59:59.99999' AS t;
ANALYZE t;
ALTER TABLE t ALTER COLUMN t SET DATA TYPE TIMESTAMP;
SELECT * FROM t;

Results in this:

demo@127.0.0.1:26257/demoapp/defaultdb> SELECT * FROM t;
ERROR: internal error: type check failed while initializing stat 977295010453782529: histogram for table t column t created_at 2024-06-13 22:19:28.324039 does not match column type TIMESTAMP: TIMESTAMPTZ
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt_catalog.go:1815: init()
github.com/cockroachdb/cockroach/pkg/sql/opt_catalog.go:1088: newOptTable()
github.com/cockroachdb/cockroach/pkg/sql/opt_catalog.go:576: dataSourceForTable()
github.com/cockroachdb/cockroach/pkg/sql/opt_catalog.go:516: dataSourceForDesc()
github.com/cockroachdb/cockroach/pkg/sql/opt_catalog.go:270: ResolveDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/util.go:670: resolveDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:139: buildDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:94: buildDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1421: buildFromTablesRightDeep()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1398: buildFromTables()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1328: buildFrom()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1244: buildSelectClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1176: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1128: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:116: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1127: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:356: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:299: buildStmtAtRootWithScope()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:280: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:259: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:599: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:250: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2290: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1840: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1173: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3423: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:144: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2312: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2317: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2234: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:963: ServeConn()

Looking at descriptors, it seems like ALTER TABLE t ALTER COLUMN t SET DATA TYPE TIMESTAMP; is not changing the column ID. 🤔

[michae2]

I think what's going on here is that in https://github.com/cockroachdb/cockroach/pull/25777 we added support for some type conversions that are metadata only (the encoding on disk doesn't change).

[mgartner]

This seems like a fairly bad rough edge, I think we should prioritize a fix.