internal error: expected subquery to be lazily planned as a routine

[DerZc]

Describe the problem

Please describe the issue you observed, and any steps we can take to reproduce it:

To Reproduce

The following program triggers this error

CREATE DATABASE database7;
USE database7;
SET CLUSTER SETTING diagnostics.reporting.enabled    = false;
SET CLUSTER SETTING diagnostics.reporting.send_crash_reports = false;
SET CLUSTER SETTING sql.metrics.statement_details.plan_collection.enabled = 'off';
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = 'off';
SET CLUSTER SETTING timeseries.storage.enabled = 'off';
set experimental_enable_hash_sharded_indexes='on';


CREATE TABLE t0 (c0 FLOAT);
CREATE TABLE t1 (c0 SERIAL4);
CREATE TABLE t2 (c0 SERIAL2);

UPSERT INTO t0 (c0) VALUES(1);
UPSERT INTO t2 (rowid) VALUES(1);

SELECT t2.c0 FROM t0 FULL OUTER JOIN t2 ON (((t2.rowid) IN (SELECT t0.rowid FROM t2, t0 WHERE (((0) NOT BETWEEN (t0.rowid) AND ((0)))OR((t2.c0) IN (SELECT ALL BIT_AND(t2.rowid) FROM t2, t1 WHERE false))))));

This is the error message:

> SELECT t2.c0 FROM t0 FULL OUTER JOIN t2 ON (((t2.rowid) IN (SELECT t0.rowid FROM t2, t0 WHERE (((0) NOT BETWEEN (t0.rowid) AND ((0)))OR((t2.c0) IN (SELECT ALL BIT_AND(t2.rowid) FROM t2, t1 WHERE false))
))));
ERROR: internal error: expected subquery to be lazily planned as a routine
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:1107: expectedLazyRoutineError()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:1087: func2()
github.com/cockroachdb/cockroach/pkg/sql/routine.go:147: Start()
github.com/cockroachdb/cockroach/pkg/sql/routine.go:49: EvalRoutineExpr()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/expr.go:625: EvalRoutineExpr()
github.com/cockroachdb/cockroach/bazel-out/k8-fastbuild/bin/pkg/sql/sem/tree/eval_expr_generated.go:346: Eval()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/expr.go:197: EvalCoalesceExpr()
github.com/cockroachdb/cockroach/bazel-out/k8-fastbuild/bin/pkg/sql/sem/tree/eval_expr_generated.go:101: Eval()
github.com/cockroachdb/cockroach/pkg/sql/sem/eval/expr.go:26: Expr()
github.com/cockroachdb/cockroach/pkg/sql/execinfrapb/pkg/sql/execinfrapb/expr.go:229: RunFilter()
github.com/cockroachdb/cockroach/pkg/sql/execinfrapb/pkg/sql/execinfrapb/expr.go:218: EvalFilter()                                                                                                 [56/1891]
github.com/cockroachdb/cockroach/pkg/sql/rowexec/joinerbase.go:172: render()
github.com/cockroachdb/cockroach/pkg/sql/rowexec/hashjoiner.go:348: probeRow()
github.com/cockroachdb/cockroach/pkg/sql/rowexec/hashjoiner.go:188: Next()
github.com/cockroachdb/cockroach/pkg/sql/colexec/columnarizer.go:239: Next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:118: next()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:126: Next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:250: nextAdapter()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:254: next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:286: Run()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:317: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:902: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1913: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1645: func3()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1648: PlanAndRunAll()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2091: execWithDistSQLEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1649: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:964: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:142: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2980: execWithProfiling()

HINT: You have encountered an unexpected error.

Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.

If you would rather not post publicly, please contact us directly
using the support form.

We appreciate your feedback.

If possible, provide steps to reproduce the behavior:

I build CockroachDB from the last commit version 8124bff, and run it with ./cockroach start-single-node --insecure

Expected behavior
A clear and concise description of what you expected to happen.
No error.

Environment:

• CockroachDB version 8124bff
• Server OS: Ubuntu 22.04
• Client app CLI

Jira issue: CRDB-27198

[blathers-crl]

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: Vectorized,distsql,plan)
• @dhartunian (found keywords: metrics)

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.

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

[knz]

Very nice find. Thank you!

cc @mgartner for triage.

[blathers-crl]

Hi @mgartner, please add branch-* labels to identify which branch(es) this release-blocker affects.

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

[mgartner]

Simplified reproduction:

CREATE TABLE t1 (i INT);
CREATE TABLE t2 (i INT);

INSERT INTO t1 (i) VALUES (1);
INSERT INTO t2 (i) VALUES (1);

SELECT t2.i FROM t1
FULL JOIN t2 ON t2.i IN (
  SELECT t2.i FROM t2, t1
  WHERE t1.i = 0
    OR t2.i IN (SELECT t2.i FROM t2)
);

[mgartner]

Removing the GA-blocker label. This is very minor - it's an internal error instead of a user-error "could not decorrelate subquery". This can wait until 23.1.1.