cockroachdb / cockroachdb/cockroach

sql: WITH ORDINALITY FOR UPDATE hits internal error: cols missing key column

Open
#127,869 1 comment 0 reactions 0 assignees View on GitHub
A-read-committed A-sql-optimizer branch-master branch-release-23.2 branch-release-24.1 branch-release-24.2 C-bug O-qa T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

When using the new Lock op implementation of SELECT FOR UPDATE, I get an internal error when trying to use WITH ORDINALITY. Here's how to reproduce it on v24.2.0-beta.2:

```sql
CREATE TABLE t (a INT);
SET optimizer_use_lock_op_for_serializable = on;
SELECT * FROM (SELECT * FROM t WHERE a > 4) WITH ORDINALITY FOR UPDATE;
```

Here's the stack:

```
demo@127.0.0.1:26257/defaultdb> SELECT * FROM (SELECT * FROM t WHERE a > 4) WITH ORDINALITY FOR UPDATE;
ERROR: internal error: cols missing key column 2
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/locking.go:426: buildLock()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/locking.go:387: buildLocking()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1217: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1128: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:116: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1127: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:356: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:299: buildStmtAtRootWithScope()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:280: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:259: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:843: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:251: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2291: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1841: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1174: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:146: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3429: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2316: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2321: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2238: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:963: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:253: processCommands()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/server.go:1036: func4()
src/runtime/asm_arm64.s:1222: goexit()
```

Jira issue: CRDB-40645

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.