cockroachdb / cockroachdb/cockroach

sql/opt: internal error "index out of range" building generic query plan for window with subquery frame bound

Open
#172,263 1 comment 0 reactions 0 assignees View on GitHub
A-generic-query-plans C-bug O-agent T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

Building a generic (reusable) query plan for a prepared statement crashes with `internal error: runtime error: index out of range` when the statement has a window function whose frame bound is a scalar subquery. The optimizer panics while type-checking the subquery during reusable-memo construction.

The same statement executed via the simple query protocol (literals inlined, no prepared/generic plan) returns rows correctly. The crash is specific to the generic-plan build path.

**To Reproduce**

```sql
CREATE TABLE wt (a INT, b INT);
INSERT INTO wt SELECT i, i % 3 FROM generate_series(1, 40) AS g(i);

SET plan_cache_mode = force_generic_plan;
PREPARE p1 AS
SELECT count(*) OVER (
ORDER BY a
GROUPS BETWEEN $1 PRECEDING
AND (SELECT b FROM wt ORDER BY b DESC LIMIT 1) FOLLOWING
) FROM wt;
EXECUTE p1(1);
```

`EXECUTE` panics. Running the equivalent statement with the offset inlined via the simple protocol does not.

**Expected behavior**

The prepared statement builds a generic plan and executes without an internal error, returning the same rows as simple-protocol execution.

**Additional data**

Abbreviated stack (from `EXECUTE`):

```
panicBounds()
pkg/sql/opt/metadata.go:1141 ColumnMeta()
pkg/sql/opt/memo/typing.go:266 typeSubquery()
pkg/sql/opt/memo/typing.go:30 InferType()
pkg/sql/opt/memo/expr.og.go MemoizeSubquery()
pkg/sql/opt/optbuilder/subquery.go:355 buildSingleRowSubquery()
pkg/sql/opt/optbuilder/scalar.go:451 buildScalar()
pkg/sql/opt/optbuilder/window.go:159 buildWindow()
...
pkg/sql/plan_opt.go:668 buildReusableMemo()
pkg/sql/plan_opt.go:965 fetchPreparedMemo()
```

Reproduces in both `GROUPS` and `ROWS` framing. Found by differential testing that compares simple-protocol execution against extended-protocol prepared execution under generic query plans — the two diverge because only the generic-plan build panics. Same bug class as #152771 (a distinct root cause, now fixed).

**Environment:**
- CockroachDB version: v26.3.0-alpha.2-dev (`darwin arm64`, CCL)
- Client: `cockroach sql`

Jira issue: CRDB-65475

Contributor guide

Open the contributing guide

Research direction

Start with the generic-plan reproduction using SET plan_cache_mode = force_generic_plan and EXECUTE p1(1), then trace the stack through pkg/sql/opt/metadata.go, pkg/sql/opt/memo/typing.go, and pkg/sql/opt/optbuilder/window.go. Compare generic-plan execution with simple-protocol execution; done means the prepared statement no longer panics and returns the same rows.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.