Unexpected Crash in Multiple Table Join

[bajinsheng]

Describe the problem

Unexpected crash.

To Reproduce

CREATE TABLE t0 (c0 INT);
CREATE TABLE t1 (c0 TIMETZ[]);
CREATE TABLE t2 (c0 INT);
CREATE TABLE t3 (c0 INT);
CREATE TABLE t4 (c0 INT);
CREATE TABLE t5 (c0 INT);
EXPLAIN SELECT t2.c0, t0.c0, t3.c0, t1.c0 FROM t2, t0, t4, t5, t3 JOIN t1 ON t1.c0 IS NULL GROUP BY t2.c0, t0.c0, t3.c0, t1.c0; -- panic: runtime error: index out of range [1] with length 1 [recovered]

Expected behavior
Return query plan instead of crash.

Additional data / screenshots

 ERROR: a SQL panic has occurred while executing the following statement:
 EXPLAIN SELECT t2.c0, t0.c0, t3.c0, t1.c0 FROM t2, t0, t4, t5, t3 JOIN t1 ON t1.c0 IS NULL GROUP BY t2.c0, t0.c0, t3.c0, t1.c0


 ERROR: a panic has occurred!
 runtime error: index out of range [1] with length 1
 (1) attached stack trace
   -- stack trace:
   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:776
   | [...repeated from below...]
 Wraps: (2) while executing: EXPLAIN SELECT _._, _._, _._, _._ FROM _, _, _, _, _ JOIN _ ON _._ IS NULL GROUP BY _._, _._, _._, _._
 Wraps: (3) attached stack trace
   -- stack trace:
   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:776
   | runtime.gopanic
   |   GOROOT/src/runtime/panic.go:1038
   | runtime.goPanicIndex
   |   GOROOT/src/runtime/panic.go:90
   | github.com/cockroachdb/cockroach/pkg/sql/opt/indexrec.(*HypotheticalTable).Column
   |   github.com/cockroachdb/cockroach/pkg/sql/opt/indexrec/hypothetical_table.go:110
   | github.com/cockroachdb/cockroach/pkg/sql/opt.(*Metadata).UpdateTableMeta
   |   github.com/cockroachdb/cockroach/pkg/sql/opt/metadata.go:618
   | github.com/cockroachdb/cockroach/pkg/sql.(*optPlanningCtx).makeQueryIndexRecommendation
   |   github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:737
   | github.com/cockroachdb/cockroach/pkg/sql.(*optPlanningCtx).buildExecMemo
   |   github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:560
   | github.com/cockroachdb/cockroach/pkg/sql.(*planner).makeOptimizerPlan
   |   github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:225
   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).makeExecPlan
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1363
   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).dispatchToExecutionEngine
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1045
   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:690
   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145
   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd.func1
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1911
   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1915
   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1837
   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn
   |   github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:778
   | github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync.func1
   |   github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:727
   | runtime.goexit
   |   GOROOT/src/runtime/asm_amd64.s:1581
 Wraps: (4) runtime error: index out of range [1] with length 1
 Error types: (1) *withstack.withStack (2) *safedetails.withSafeDetails (3) *withstack.withStack (4) runtime.boundsError

panic: runtime error: index out of range [1] with length 1 [recovered]
        panic: runtime error: index out of range [1] with length 1

goroutine 4067 [running]:
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).closeWrapper(0xc003316000, {0x6767aa0, 0xc003468300}, {0x4d783a0, 0xc0047362d0})
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1076 +0x1b4
github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1({0x6767aa0, 0xc003468300}, {0x4b1ba94})
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:776 +0x4a
panic({0x4d783a0, 0xc0047362d0})
        GOROOT/src/runtime/panic.go:1038 +0x215
github.com/cockroachdb/cockroach/pkg/sql/opt/indexrec.(*HypotheticalTable).Column(0xc0032d9ec0, 0x5)
        github.com/cockroachdb/cockroach/pkg/sql/opt/indexrec/hypothetical_table.go:110 +0x7f
github.com/cockroachdb/cockroach/pkg/sql/opt.(*Metadata).UpdateTableMeta(0xc003188780, 0x6824dd0)
        github.com/cockroachdb/cockroach/pkg/sql/opt/metadata.go:618 +0x18e
github.com/cockroachdb/cockroach/pkg/sql.(*optPlanningCtx).makeQueryIndexRecommendation(0xc003316f50)
        github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:737 +0x238
github.com/cockroachdb/cockroach/pkg/sql.(*optPlanningCtx).buildExecMemo(0xc003316f50, {0x6767b48, 0xc004123530})
        github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:560 +0x56f
github.com/cockroachdb/cockroach/pkg/sql.(*planner).makeOptimizerPlan(0xc003316718, {0x6767b48, 0xc004123530})
        github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:225 +0xff
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).makeExecPlan(0xc003316000, {0x6767b48, 0xc004123530}, 0xc003316718)
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1363 +0x4e
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).dispatchToExecutionEngine(0xc003316000, {0x6767b48, 0xc004123530}, 0xc003316718, {0x7faf0b08dbc8, 0xc001a45140})
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1045 +0x296
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState(0xc003316000, {0x6767aa0, 0xc003b272c0}, {{0x6797bf8, 0xc001ce56e0}, {0xc0022460a1, 0x7e}, 0x0, 0x6}, 0x0, ...)
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:690 +0x2091
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt(0xc003316000, {0x6767aa0, 0xc003b272c0}, {{0x6797bf8, 0xc001ce56e0}, {0xc0022460a1, 0x7e}, 0x0, 0x6}, 0x0, ...)
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145 +0x59e
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd.func1({{{0x6797bf8, 0xc001ce56e0}, {0xc0022460a1, 0x7e}, 0x0, 0x6}, {0xc0b19c5ef13afda0, 0x5ce88c89, 0x0}, {0xc0b19c5ef13b037c, ...}, ...}, ...)
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1911 +0x2f6
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd(0xc003316000)
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1915 +0xb48
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run(0xc003316000, {0x6767aa0, 0xc003468300}, 0xc000779b90, 0x0, 0xc004d2fdb0)
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1837 +0x216
github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn(0xc000779ce0, {0x6767aa0, 0xc003468300}, {0xc000b8c000}, 0x3, 0xc004f5dbf0)
        github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:778 +0xfb
github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync.func1()
        github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:727 +0x3fe
created by github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync
        github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:638 +0x232

Environment:

• CockroachDB version [6374bd8]
• Server OS: [Ubuntu 20.04]
• Client app [cockroach sql]

Additional context

Jira issue: CRDB-18209

[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: 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.

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

[yuzefovich]

Thanks for the report!

This no longer results in a panic as of b1de533, so it looks like the bug has been fixed - seems like #84169 is the fix.