sql: Internal Error when using EXPLAIN with SET TRACING

[SteveLeungYL]

Describe the problem

The latest version of the CockroachDB (5cf0823e2ab46) shows Internal Error when executing the following query:

EXPLAIN SET TRACING = 0;

To Reproduce

Here is the detail steps to reproduce the bug.

1. In operating system Ubuntu 20.04 LTS, download the CockroachDB source code from the github source.
2. Use the latest version of the CockroachDB code (tested version: 5cf0823e2ab46)
3. Directly make install in the root repository folder.
4. Run ./cockroach demo, and then paste the PoC query to the cockroach cli environment.
5. Observe the Internal Error and log the stack information.

Expected behavior The query should show semantic error indicating that EXPLAIN SET TRACING is not supported.

Additional data / screenshots

Here is the outputted stack trace:

demo@127.0.0.1:26257/movr> EXPLAIN SET TRACING = 0;
ERROR: internal error: unexpected statement: *tree.SetTracing
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:415: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:253: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/explain.go:31: buildExplain()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:337: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:253: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:227: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:570: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:237: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1532: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1139: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:744: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:130: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2527: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:129: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1959: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1964: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1882: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:836: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:731: func1()
runtime/asm_arm64.s:1165: goexit()

HINT: You have encountered an unexpected error.

Environment:

• CockroachDB version: v23.1.0-alpha
• Server OS: Ubuntu 20.04 LTS
• Client app: CockroachDB demo command. (./cockroach demo)
• Detailed CockroachDB version output:

cockroach version details:
Build Tag:        v23.1.0-alpha.1-1171-g5cf0823e2a
Build Time:       2023/01/25 21:45:51
Distribution:     CCL
Platform:         linux arm64 (aarch64-linux-gnu)
Go Version:       go1.19
C Compiler:       gcc 9.4.0
Build Commit ID:  5cf0823e2ab4646ed98ebf2709ea79f3541935e5
Build Type:       development

Additional context

The bug can be reproduced by CockroachDB release version: CockroachDB v23.1 Testing Releases , v22.2.2 and v22.1. We haven't tested CockroachDB release versions before v22.1.

Jira issue: CRDB-23785

[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)

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.}

[SteveLeungYL]

The bug seems to be auto-reported by our local testing. So it is duplicated with auto report: https://github.com/cockroachdb/cockroach/issues/95869, https://github.com/cockroachdb/cockroach/issues/95866 and https://github.com/cockroachdb/cockroach/issues/95867.

[yuzefovich]

Thanks for the report! Indeed, most of the "internal errors" are reported as sentry events and then are converted automatically into github issues.

[msirek]

Maybe we should find all statements which currently have no EXPLAIN support and handle them all in one shot.

[SteveLeungYL]

Another PoC that triggers a similar bug:

( ( ( WITH ta0 AS NOT MATERIALIZED ( SET SESSION TRACING TO NONE ) ( SELECT 'abc' ) ) ) );

Stace trace from version: v24.2.1-dev, b95f17a1ddc0676a7cd64c1a0e3415cd85c221ba

ERROR: internal error: unexpected statement: *tree.SetTracing
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:473: 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/explain.go:31: buildExplain()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:389: 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:862: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:251: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2291: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1841: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1174: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:146: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3429: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2316: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2321: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2238: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:963: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:253: processCommands()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/server.go:1052: func4()
src/runtime/asm_amd64.s:1695: goexit()