cockroachdb / cockroachdb/cockroach

EXPLAIN (OPT, MEMO) in bracketed table expression crashes during memo formatting

Open
#169,752 2 comments 0 reactions 0 assignees View on GitHub
C-bug O-community T-sql-queries X-blathers-triaged
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

## Describe the problem

When `EXPLAIN (OPT, MEMO)` is used as the inner statement of a **bracketed table expression** (`FROM [ ... ]`), CockroachDB returns an **internal error** (nil pointer dereference) while formatting the optimizer memo during `EXPLAIN` handling.

This crash path is **distinct** from the known open issue [#153465](https://github.com/cockroachdb/cockroach/issues/153465), which involves `EXPLAIN (OPT)` subquery output combined with `ORDER BY` and fails in `setLowestCostTree()`. Here the failure occurs in memo formatting (`FormatMemo` / `memo_format`) and reproduces **without** `ORDER BY`.

Example signature:

```text
ERROR: internal error: runtime error: invalid memory address or nil pointer dereference
```

Representative stack frames (see full trace on your build):

- `pkg/sql/opt/xform/memo_format.go` (`numberExpr` / `numberMemo`)
- `pkg/sql/opt/operator.og.go` (`IsListItemOp`) — generated code path
- `pkg/sql/opt/xform/optimizer.go` (`FormatMemo`)
- `pkg/sql/opt/exec/execbuilder/statement.go` (`buildExplainOpt`)

---

## To Reproduce

Minimal reproduction (empty database is sufficient):

1. Start a single-node cluster for testing, e.g.
`cockroach start-single-node --insecure --listen-addr=localhost`

2. Run SQL (via `cockroach sql --insecure` or equivalent):

```sql
SELECT *
FROM [ EXPLAIN (OPT, MEMO) SELECT 1 ];
```

Or run the checked-in PoC:

```bash
cockroach sql --insecure --host=:26257 \
-f new_bug/poc_explain_opt_memo_subquery_npe.sql
```

3. Observe **internal error** with stack trace pointing at memo formatting / `IsListItemOp` as above.

**Control:** The same `EXPLAIN (OPT, MEMO)` text executed **without** the bracketed table source form succeeds, e.g.:

```sql
EXPLAIN (OPT, MEMO) SELECT 1;
```

---

## Expected behavior

- The bracketed form should either:
- produce the same logical outcome as other supported `EXPLAIN` table sources, or
- return a **clear user-facing error** if this syntax combination is unsupported.

- Under no circumstances should a simple `SELECT` from an explain virtual table cause an **internal error** / panic wrapped as `XX000`.

---

## Environment

- **CockroachDB:** `v26.3.0-alpha.00000000-dev`, CCL, linux amd64, Go 1.26.2
- **OS:** Linux 4.18.0-553.el8_10.x86_64
- **Client:** `cockroach sql --insecure`

Jira issue: CRDB-63637

Contributor guide

Open the contributing guide

Research direction

Start with the checked-in new_bug/poc_explain_opt_memo_subquery_npe.sql reproduction, then inspect pkg/sql/opt/xform/memo_format.go around numberExpr and numberMemo, along with the IsListItemOp path in pkg/sql/opt/operator.og.go. Run the bracketed EXPLAIN (OPT, MEMO) query and confirm it no longer produces an internal error, or returns a clear user-facing error if unsupported.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.