sql: internal error: invalid index

[maddyblue]

CREATE TABLE IF NOT EXISTS tab_orig AS
	SELECT
		g % 2 = 0 AS _bool
	FROM
		generate_series(0, 0) AS g;

SELECT
	(
		SELECT
			NULL
		FROM
			tab_orig
			LEFT JOIN tab_orig AS tab_57077
				RIGHT JOIN tab_orig AS tab_57078
					FULL JOIN tab_orig AS tab_57079 ON
							true ON tab_57069._bool
				CROSS JOIN tab_orig AS tab_57080
				INNER JOIN tab_orig AS tab_57081 ON true ON
					EXISTS(
						SELECT
							NULL
						FROM
							tab_orig AS tab_57082
							LEFT JOIN tab_orig ON
									EXISTS(
										SELECT
											NULL
										FROM
											tab_orig
											
									)
					)
	)
FROM
	tab_orig AS tab_57069;

pq: internal error: invalid index 3 for "EXISTS (SELECT NULL FROM tab_orig)"
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/subquery.go:46: EvalSubquery()
github.com/cockroachdb/cockroach/pkg/sql/distsqlplan/expression.go:119: VisitPre()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:667: WalkExpr()
github.com/cockroachdb/cockroach/pkg/sql/distsqlplan/expression.go:86: MakeExpression()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:894: initTableReaderSpec()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:1070: createTableReaders()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2312: createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2197: createPlanForJoin()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2324: createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_physical_planner.go:2365: createPlanForNode()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:826: planAndRunSubquery()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:764: PlanAndRunSubqueries()
github.com/cockroachdb/cockroach/pkg/sql/apply_join.go:303: runRightSidePlan()
github.com/cockroachdb/cockroach/pkg/sql/apply_join.go:273: Next()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:171: Next()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/base.go:171: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/processors.go:793: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsqlrun/flow.go:581: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:333: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:942: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:910: execWithDistSQLEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:734: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:417: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:99: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1211: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1140: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:442: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:584: func1()

[jordanlewis]

This issue is caused by a weird combination of subqueries. It seems like one of the subqueries has an embedded subquery, and when planning the outer subquery, the inner subquery isn't yet available, or something?

Here's the explain plan:

            tree           |    field     |                                description
+--------------------------+--------------+----------------------------------------------------------------------------+
  root                     |              |
   ├── render              |              |
   │    └── apply-join     |              |
   │         │             | type         | left outer
   │         └── scan      |              |
   │                       | table        | tab_orig@primary
   │                       | spans        | ALL
   └── subquery            |              |
        │                  | id           | @S1
        │                  | original sql | EXISTS (SELECT NULL FROM tab_orig AS tab_57082 LEFT JOIN tab_orig ON true)
        │                  | exec mode    | exists
        └── limit          |              |
             │             | count        | 1
             └── hash-join |              |
                  │        | type         | left outer
                  ├── scan |              |
                  │        | table        | tab_orig@primary
                  │        | spans        | ALL
                  └── scan |              |
                           | table        | tab_orig@primary
                           | spans        | ALL
(21 rows)

Explain opt:

                                   text
+------------------------------------------------------------------------+
  project
   ├── left-join-apply
   │    ├── scan tab_57069
   │    ├── max1-row
   │    │    └── project
   │    │         ├── left-join (hash)
   │    │         │    ├── scan tab_orig
   │    │         │    ├── inner-join (hash)
   │    │         │    │    ├── inner-join (hash)
   │    │         │    │    │    ├── right-join (hash)
   │    │         │    │    │    │    ├── scan tab_57077
   │    │         │    │    │    │    ├── full-join (hash)
   │    │         │    │    │    │    │    ├── scan tab_57078
   │    │         │    │    │    │    │    ├── scan tab_57079
   │    │         │    │    │    │    │    └── filters (true)
   │    │         │    │    │    │    └── filters
   │    │         │    │    │    │         └── variable: tab_57069._bool
   │    │         │    │    │    ├── scan tab_57080
   │    │         │    │    │    └── filters (true)
   │    │         │    │    ├── scan tab_57081
   │    │         │    │    └── filters (true)
   │    │         │    └── filters
   │    │         │         └── exists
   │    │         │              └── limit
   │    │         │                   ├── left-join (hash)
   │    │         │                   │    ├── scan tab_57082
   │    │         │                   │    ├── scan tab_orig
   │    │         │                   │    └── filters (true)
   │    │         │                   └── const: 1
   │    │         └── projections
   │    │              └── null
   │    └── filters (true)
   └── projections
        └── variable: ?column?
(34 rows)

[jordanlewis]

I think the issue here is perhaps that a subquery within an apply join expects to be able to reference a subquery outside of the apply join, but it cannot because the subqueries array passed to the apply join is solely the subqueries that are contained within the apply join itself??

I don't know, I'm lost. @RaduBerinde can you take a look? Sorry for the deluge of apply join stuff that we can't figure out.

[RaduBerinde]

I see.. when we construct a new plan with the execbuilder, we'd want to include the subqueries of the parent plan (but not execute those with PlanAndRunSubqueries). It's simple conceptually but not easy to make it work I think. It might help to make tree.Subquery refer to something less fragile than an index in a slice. We may also want to have a factory function to "allocate" a subquery when we create tree.Subquery, rather than passing the subquery info in a slice at the end. Some more thought is needed.

While it's important to iron out these issues, I don't think this work is a high priority unless a user runs into problems (it's a corner case of apply, which itself is a corner case). If it's impeding other sqlsmith coverage, we could add a knob for disabling apply joins and do runs with them off.

[asubiotto]

@RaduBerinde it seems like the actionable item here is around plan creation so moving to triage in SQL Planning.

[yuzefovich]

We hit another several instances of this bug, all on sqlsmith queries (#54166), here is a reduced one:

SELECT
  (
    SELECT
      tab_4.col_4
    FROM
      (VALUES (1)) AS tab_1 (col_1)
      JOIN (
          VALUES
            (
              (
                SELECT
                  1
                FROM
                  (SELECT 1)
                WHERE
                  EXISTS(SELECT 1)
              )
            )
        )
          AS tab_6 (col_6) ON (tab_1.col_1) = (tab_6.col_6)
  )
FROM
  (VALUES (NULL)) AS tab_4 (col_4),
  (VALUES (NULL), (NULL)) AS tab_5 (col_5);

[yuzefovich]

Another reproduction is here.