EXPLAIN ANALYZE DML may skip foreign key cascades after a pessimistic transaction retry

[YangKeao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Enable foreign keys and create two tables with an ON UPDATE CASCADE foreign key:

SET GLOBAL tidb_enable_foreign_key = ON;
SET foreign_key_checks = ON;

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE gift (
    id BIGINT PRIMARY KEY,
    expires_at BIGINT
);

CREATE TABLE user_gift (
    id BIGINT PRIMARY KEY,
    gift_id BIGINT,
    INDEX gift_id_idx (gift_id),
    CONSTRAINT user_gift_fk
        FOREIGN KEY (gift_id) REFERENCES gift(id)
        ON UPDATE CASCADE
);

INSERT INTO gift VALUES (1, 1);
INSERT INTO user_gift VALUES (1, 1);
BEGIN PESSIMISTIC;

2. Force the DML to encounter a retryable pessimistic write conflict. The issue can be reproduced deterministically in an executor test with:

testfailpoint.Enable(
    t,
    "github.com/pingcap/tidb/pkg/store/mockstore/unistore/tikv/pessimisticLockReturnWriteConflict",
    "1*return(false)->1*return(true)->return(false)",
)

3. Execute an EXPLAIN ANALYZE DML that triggers the foreign key cascade:

EXPLAIN ANALYZE
UPDATE gift SET id = 2, expires_at = 2 WHERE id = 1;

SELECT * FROM gift;
SELECT * FROM user_gift;

The first execution attempt encounters a write conflict. TiDB rebuilds the executor and retries the statement.

2. What did you expect to see? (Required)

The retried EXPLAIN ANALYZE UPDATE should execute with the same foreign key semantics as a normal UPDATE.

Both the parent row and the referencing child row should be updated:

gift:
2  2

user_gift:
1  2

The same requirement applies to other foreign key actions. For example, an EXPLAIN ANALYZE DELETE with ON DELETE CASCADE must delete both the parent and child rows.

3. What did you see instead (Required)

After the pessimistic retry, the rebuilt executor can be an ExplainExec. The underlying DML is executed, but foreign key trigger handling receives the wrapper executor and does not find the foreign key cascade attached to
the underlying DML executor.

As a result, the parent row is updated while the child row keeps the old foreign key value:

gift:
2  2

user_gift:
1  1

This can break referential integrity. The equivalent ON DELETE CASCADE case may delete the parent row without deleting its child rows.

4. What is your TiDB version? (Required)

Release Version: master
Git Commit Hash: 2ddad9e