sql: internal error when executing query

[SteveLeungYL]

Describe the problem

The latest version of the CockroachDB (60313dca26 or v24.2.1) crashes when executing the following query:

SET experimental_enable_temp_tables = 'on';
CREATE TABLE v00 (v01 INT, v02 STRING);
CREATE TABLE v0 WITHOUT OIDS AS ( WITH RECURSIVE ta1 AS MATERIALIZED ( EXPERIMENTAL SCRUB TABLE v00 ) ( SELECT 'abc' ) );

To Reproduce

1. In operating system Ubuntu 20.04 LTS, download the CockroachDB source code from the github source.
2. Download the pre-build CockroachDB binaries (v24.2.1) from link
3. Run ./cockroach demo, and then paste the PoC query to the cockroach cli environment.
4. Observe the Internal Error and log the stack information.

Expected behavior

The CREATE TABLE statement should return syntax error.

Additional data / screenshots Here is the crash stack trace from CockroachDB v24.2.1:

*
* ERROR: Queued as error a20de354ba0c497190b47b9ed2cf2504
*
ERROR: internal error: unexpected error from the vectorized engine: runtime error: index out of range [0] with length 0
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:149: func1()
GOROOT/src/runtime/panic.go:770: gopanic()
GOROOT/src/runtime/panic.go:114: goPanicIndex()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/annotation.go:48: Set()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/annotation.go:35: SetAnnotation()
github.com/cockroachdb/cockroach/pkg/sql/resolver.go:1224: ResolveExistingObjectEx()
github.com/cockroachdb/cockroach/pkg/sql/scrub.go:91: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:524: func2()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:112: func1()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:299: visitInternal()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:79: visit()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:291: visitInternal()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:79: visit()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:43: walkPlan()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:527: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:171: Start()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:116: func1()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:152: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:115: Start()
github.com/cockroachdb/cockroach/pkg/sql/execinfra/processorsbase.go:731: Run()
github.com/cockroachdb/cockroach/pkg/sql/flowinfra/flow.go:579: Run()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:305: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:932: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1853: planAndRunSubquery()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1768: PlanAndRunSubqueries()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:446: 5()
github.com/cockroachdb/cockroach/pkg/sql/schema_resolver.go:444: runWithOptions()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:437: func2()
github.com/cockroachdb/cockroach/pkg/sql/internal.go:1940: func1()
github.com/cockroachdb/cockroach/pkg/sql/internal.go:2027: func4()
github.com/cockroachdb/cockroach/pkg/kv/txn.go:1049: exec()
github.com/cockroachdb/cockroach/pkg/kv/db.go:1103: runTxn()

Environment:

• CockroachDB version: v24.2.1
• Server OS: Ubuntu 20.04 LTS
• Client app: CockroachDB demo command. (./cockroach demo)
• Detailed CockroachDB version output:

cockroach version details:
Build Tag:        v24.2.1
Build Time:       2024/09/04 02:31:53
Distribution:     CCL
Platform:         linux arm64 (aarch64-unknown-linux-gnu)
Go Version:       go1.22.5 X:nocoverageredesign
C Compiler:       gcc 6.5.0
Build Commit ID:  b95f17a1ddc0676a7cd64c1a0e3415cd85c221ba
Build Type:       release
Enabled Assertions: false

Additional context

The bug could potentially be related to: https://github.com/cockroachdb/cockroach/issues/93931.

Jira issue: CRDB-42014

[blathers-crl[bot]]

Hi @SteveLeungYL, 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/kv (found keywords: kv)
• @cockroachdb/sql-queries (found keywords: vectorized,distsql,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.}

[mgartner]

Reduced reproduction:

CREATE TABLE t1 (i INT);

CREATE TABLE t2 AS (
  WITH w AS MATERIALIZED (
    EXPERIMENTAL SCRUB TABLE t1
  )
  SELECT 'abc'
);

This reproduces back to v23.1. Prior to that, I get a user error:

ERROR: relation "t1" does not exist
SQLSTATE: 42P01

The solution might be to simply disallow EXPERIMENTAL SCRUB TABLE t1 within a CTE. I don't think we ever intended that to be possible.