sql: index out of range with implicit locking

[gastonqiu]

Describe the problem
Encountered an unexpected error when performing a cascade delete operation:

SQL Error [XX000]: ERROR: internal error: runtime error: index out of range [1] with length 1
  Detail: stack trace:
pkg/util/errorutil/catch.go:24: ShouldCatch()
pkg/sql/opt/exec/execbuilder/builder.go:356: func1()
GOROOT/src/runtime/panic.go:791: gopanic()
GOROOT/src/runtime/panic.go:115: goPanicIndex()
pkg/sql/opt_catalog.go:1431: Index()
pkg/sql/opt_exec_factory.go:1390: ordinalsToIndexes()
pkg/sql/opt_exec_factory.go:1773: ConstructDelete()
bazel-out/k8-opt/bin/pkg/sql/opt/exec/explain/plan_gist_factory.og.go:826: ConstructDelete()
bazel-out/k8-opt/bin/pkg/sql/opt/exec/explain/explain_factory.og.go:1360: ConstructDelete()
pkg/sql/opt/exec/execbuilder/mutation.go:627: buildDelete()
pkg/sql/opt/exec/execbuilder/relational.go:267: buildRelational()
pkg/sql/opt/exec/execbuilder/builder.go:380: build()
pkg/sql/opt/exec/execbuilder/builder.go:298: Build()
pkg/sql/plan_opt.go:935: runExecBuilder()
pkg/sql/plan_opt.go:344: runExecBuild()
pkg/sql/plan_opt.go:267: makeOptimizerPlan()
pkg/sql/conn_executor_exec.go:3320: makeExecPlan()
pkg/sql/conn_executor_exec.go:2867: dispatchToExecutionEngine()
pkg/sql/conn_executor_exec.go:1085: execStmtInOpenState()
pkg/sql/conn_executor_exec.go:170: func2()
pkg/sql/conn_executor_exec.go:4476: execWithProfiling()
pkg/sql/conn_executor_exec.go:169: execStmt()
pkg/sql/conn_executor_exec.go:320: execPortal()
pkg/sql/conn_executor.go:2556: func2()
pkg/sql/conn_executor.go:2558: execCmd()
pkg/sql/conn_executor.go:2366: run()
pkg/sql/conn_executor.go:1032: ServeConn()
pkg/sql/pgwire/conn.go:252: processCommands()
pkg/sql/pgwire/server.go:1197: func4()
src/runtime/asm_arm64.s:1223: goexit()

  Hint: You have encountered an unexpected error.

Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.

If you would rather not post publicly, please contact us directly
using the support form.

We appreciate your feedback.

To Reproduce

Set up CockroachDB cluster:

pool, err := dockertest.NewPool("")
	if err != nil {
		log.Fatalf("Could not construct pool: %s", err)
	}

resource, err := pool.RunWithOptions(&dockertest.RunOptions{
		Repository: "cockroachdb/cockroach",
		Tag:        "v25.2.0", 
		Cmd: []string{
			"start-single-node",
			"--insecure",
			"--store=type=mem,size=1GiB",
			"--max-go-memory=2GiB",
			"--max-sql-memory=2GiB",
		},
		Auth: authCfg,
	}, func(config *docker.HostConfig) {
		config.AutoRemove = true
		config.RestartPolicy = docker.RestartPolicy{Name: "no"}
	})
	if err != nil {
		log.Fatalf("Could not start resource: %s", err)
	}

SQL command:

CREATE TABLE legal_entity (
   id TEXT PRIMARY KEY
);

CREATE TABLE entity_address (
   id TEXT PRIMARY KEY
);

CREATE TABLE legal_entity_address (
    legal_entity_id text NOT NULL,
    entity_address_id text NOT NULL
);

ALTER TABLE legal_entity_address
    ADD CONSTRAINT fk_legal_entity_id FOREIGN KEY (legal_entity_id) REFERENCES legal_entity(id);

ALTER TABLE legal_entity_address
    ADD CONSTRAINT fk_entity_address_id FOREIGN KEY (entity_address_id) REFERENCES entity_address(id);

ALTER TABLE legal_entity_address
    ADD CONSTRAINT legal_entity_address_unique UNIQUE (legal_entity_id, entity_address_id);


DELETE FROM address WHERE id in (
	SELECT address_id FROM legal_entity_address WHERE legal_entity_id = @id
)

Expected behavior
This operation succeeded without errors in v24.3. It is expected to continue working in newer versions without runtime panics.

Additional data / screenshots

Environment:

• CockroachDB version: v25.2.0
• Server OS: mac
• Client app: pgx

Additional context
What was the impact?
This issue is blocking our upgrade to the latest version, as it breaks both our test cases and local development setup.

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: ALTER TABLE,pgx)
• @cockroachdb/sql-queries (found keywords: Optimizer,plan)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[gastonqiu]

Might related to this issue but I can't see the details for this issue.
#148583

[yuzefovich]

Thanks for the report! On a quick glance, it seems like there is a race between adding UNIQUE constraint being picked up by the optimizer (in some code path it's already available while in another one it's not yet available). The codepath that results in an internal error is new in 25.2 release and we'll look into this more closely soon; however, if you're blocked, as a temporary workaround you can do

SET enable_implicit_select_for_update = false;

Note that using this might result in increased contention / performance degradation, but it's hard to provide more specific guidance without knowing your workload, so use this workaround with caution.