cockroachdb / cockroachdb/cockroach
sql: WITH ORDINALITY FOR UPDATE hits internal error: cols missing key column
- 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
Assessment
This issue has not been assessed yet.