cockroachdb / cockroachdb/cockroach

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

Open
#133,029 3 comments 0 reactions 0 assignees View on GitHub
branch-master branch-release-24.1 branch-release-24.2 branch-release-24.3 C-bug O-community T-sql-queries X-blathers-triaged
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

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

**To Reproduce**
```sql
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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.