sql,execbuilder: input to ArrayFlatten should be uncorrelated

[SteveLeungYL]

Describe the problem

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

SET testing_optimizer_disable_rule_probability = 1.0;
CREATE TABLE v0 (c1 STRING);
SELECT ARRAY (SELECT SPLIT_PART(c1, '', 0) FROM (VALUES ('127.0.0.1'::INET))) FROM v0;

To Reproduce

Here is the detail steps to reproduce the bug.

1. In system Ubuntu 20.04, download the CockroachDB source code from the github source.
2. Use the latest version of the CockroachDB code (tested version: ec83b9eba5f2)
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 SELECT statement should be executed correctly and return the matching rows.

Additional data / screenshots

Here is the outputted stack trace:

ERROR: internal error: input to ArrayFlatten should be uncorrelated
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:492: buildArrayFlatten()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:105: buildScalar()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:997: buildProject()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:215: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:299: build()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:246: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:640: runExecBuilder()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:307: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1518: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1125: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:737: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:130: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2513: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:129: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1955: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1960: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1878: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:834: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:728: func1()
runtime/asm_amd64.s:1594: 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:

Build Tag:        v23.1.0-alpha.00000000-1124-gec83b9eba5-dirty
Build Time:       2022/12/11 22:31:33
Distribution:     CCL
Platform:         linux amd64 (x86_64-linux-gnu)
Go Version:       go1.19
C Compiler:       gcc 9.4.0
Build Commit ID:  ec83b9eba5f2b09f9fc8610c8a976e49854d212d
Build Type:       development

Additional context

The crashing problem may not happen in the real-world application, since it requires the specific setup: SET testing_optimizer_disable_rule_probability = 1.0;, which is a developer option used to control the query optimization. However, having an Internal Error is still unexpected from the resulted behavior. Therefore, this problem is reported here.

The Internal Error problem is reproducible on version v22.2.0 Production Releases.

Jira issue: CRDB-22316

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