cockroachdb / cockroachdb/cockroach
sql/opt: internal error "index out of range" building generic query plan for window with subquery frame bound
- 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
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