`EXPLAIN` raises internal error: needed column not produced by group-by input

[r33s3n6]

Describe the problem EXPLAIN raises internal error: needed column not produced by group-by input

To Reproduce

EXPLAIN
    WITH
        v2 AS (SELECT '1' AS c0 UNION SELECT NULL),
        v0 AS (SELECT '1' AS c1 WHERE false),
        v1 AS (SELECT '1' AS c1 FROM v2 WHERE (SELECT v0.c1 FROM v0) IS NULL)
    SELECT
        1
    FROM
        v1
    WHERE
        (
            CASE WHEN (v1.c1 = (SELECT '1' FROM v1 LIMIT 1)) THEN 1 ELSE 1 END BETWEEN (CASE WHEN (EXISTS (SELECT 1 FROM v2 WHERE (v1.c1 IS NULL))) THEN 1 ELSE 1 END) AND 2
        );

error log:

ERROR:  internal error: needed column not produced by group-by input
DETAIL:  stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1835: buildGroupByInput()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1616: buildGroupBy()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:211: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:948: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:205: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1054: buildProject()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:208: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1795: buildGroupByInput()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1616: buildGroupBy()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:211: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:3193: buildWith()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:290: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:3193: buildWith()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:290: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:298: build()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:240: Build()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/statement.go:185: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt_exec_factory.go:2163: ConstructExplain()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/exec/explain/plan_gist_factory.og.go:606: ConstructExplain()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/exec/explain/explain_factory.og.go:1028: ConstructExplain()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/statement.go:169: buildExplain()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:302: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:298: build()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:240: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:681: runExecBuilder()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:322: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2325: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1817: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1150: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3457: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:144: execStmt()

Environment:

• CockroachDB version: 24.1.4
• Server OS: Ubuntu 22.04
• Client app: psql

about us We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned possible vulnerabilities that may lead to database internal error.

Jira issue: CRDB-43423

[blathers-crl[bot]]

Hi @r33s3n6, please add branch-* labels to identify which branch(es) this C-bug affects.

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

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: Optimizer,plan)
• @cockroachdb/sql-foundations (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

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

[rytaft]

Thanks for the report, @r33s3n6 -- I can confirm that this is still an issue.