[Bug]: DATA BRANCH cannot apply primary-key updates with ON UPDATE CASCADE

[gouhongshen]

Is there an existing issue for the same bug?

• I searched open and closed DATA BRANCH issues; none covers primary-key update provenance with foreign-key ON UPDATE actions.

Branch Name

main

Commit ID

ad143902644719e65fa703df6397a011dc7d7dfa (PR #27996 head reviewed on 2026-09-02)

Other Environment Information

• Local embedded single-CN / single-TN / single-Log test cluster
• Parent table with an integer primary key and a referencing child foreign key using ON UPDATE CASCADE

Actual Behavior

A branch-side primary-key change is emitted as an independent delete of the old key plus an insert of the new key. DATA BRANCH MERGE and DATA BRANCH PICK replay that as DELETE/INSERT, so the parent-row delete fails with the foreign-key constraint error. An ordinary UPDATE on the same parent key succeeds and cascades to the child.

Expected Behavior

When a source update changes a referenced primary key, Data Branch apply should preserve it as one native destination UPDATE, allowing declared ON UPDATE CASCADE actions to run.

Steps to Reproduce

create database branch_pk_cascade;
use branch_pk_cascade;
create table parent_t (id int primary key, payload varchar(32));
create table child_t (id int primary key, parent_id int,
  constraint fk_child_parent foreign key (parent_id) references parent_t(id) on update cascade);
insert into parent_t values (2, 'two');
insert into child_t values (1, 2);

data branch create table merge_src from parent_t;
update merge_src set id = 3 where id = 2;
data branch merge merge_src into parent_t when conflict accept;

-- Control: update parent_t set id = 3 where id = 2; cascades child_t.parent_id.

The equivalent PICK setup (source and destination branches, keys(2)) fails for the same reason.

Root Cause and Scope

multi_update deletes the old row while writing the replacement without a durable operation identity. databranchutils.CollectChanges therefore exposes the old row ID/key as a tombstone and the replacement row ID/key as data. buildHashmapForTable and findDeleteAndUpdateBat correlate the two only by primary-key value; once the key changes, there is no safe association.

Matching rows by commit timestamp or iteration order would conflate independent delete/insert operations with updates and could trigger unintended cascades, so it is not a safe fix. This needs an explicit old-to-new update provenance link through the change stream, followed by native UPDATE apply and MERGE/PICK regressions for cascade, non-cascade rejection, and atomicity.

Related

• Related to [Bug]: Data Branch MERGE/PICK rejects non-key updates on referenced rows #27992: fix(frontend): apply Data Branch updates in place #27996 fixes the documented stable-key non-key-update case.
• Related to PR fix(frontend): apply Data Branch updates in place #27996: this issue tracks the distinct primary-key-move protocol gap identified during review.

[gouhongshen]

I investigated this on the issue's stated baseline, PR #27996 head ad143902644719e65fa703df6397a011dc7d7dfa, using a fresh embedded single-CN/single-TN/single-Log instance built from that exact commit. I am deferring this issue rather than opening a PR because the required fix is a persistent change-stream protocol decision, and no safe local implementation exists on the current contract.

Reproduction and executable-test attempt

I constructed a self-contained 82-line BVT draft covering four distinct semantic cells:

1. MERGE of a real primary-key UPDATE 2 -> 3 into a parent referenced by ON UPDATE CASCADE.
2. PICK of the old key keys(2) for the same primary-key update, with the PICK destination referenced by ON UPDATE CASCADE.
3. The corresponding non-cascade/RESTRICT rejection and post-error parent/child state (failure atomicity).
4. A counterexample in which DELETE id=2 and INSERT id=3 are independent statements in one source transaction. This must not acquire UPDATE semantics.

Observed results on ad14390264:

• MERGE and PICK both returned ERROR 20101 ... Cannot delete or update a parent row: a foreign key constraint fails.
• After each failed apply, the parent remained id=2 and the child remained parent_id=2, proving statement atomicity on the current failing path.
• The ordinary control UPDATE parent_t SET id=3 WHERE id=2 succeeded and changed the child to parent_id=3, proving that native UPDATE supplies the required FK action.
• The RESTRICT case rejected and retained both original rows.
• The independent same-transaction DELETE/INSERT counterexample produced the same FK rejection and retained both original rows. Therefore commit timestamp, transaction identity, and iteration adjacency cannot safely distinguish it from a primary-key update.
• DATA BRANCH DIFF exposes the primary-key update exactly as DELETE id=2 plus INSERT id=3.

I did not add this draft under test/distributed/cases: there is no correct post-fix result to assert until the protocol below is selected and implemented. Adding a skipped/issue-tagged test would not satisfy the executable-proof requirement.

Proven root cause and ownership boundary

• multi_update.updateOneBatch calls the relation delete path and then the insert path; no operation identity is passed between them.
• disttae.Transaction.writeBatchWithAutoIncrEpochKnown generates a fresh physical RowID for each insert, so the replacement cannot be associated by RowID with the deleted row.
• engine.ChangesHandle.Next can return only a data batch and a tombstone batch. It has no update/provenance channel.
• TAE logtail construction emits delete and insert batches independently. Persisted tombstones contain deleted RowID, PK, and commit timestamp; they contain no replacement identity.
• Although api.Entry_Update exists as an enum value, repository search finds no TN/logtail/checkpoint/replay implementation that consumes it as a durable row update.
• Data Branch consequently receives old-key tombstone 2 and new-key data 3. The existing hash reconciliation is keyed by PK and cannot pair them after the PK changes.

This also explains why a frontend-only patch is unsafe. Pairing by commit TS, transaction, or stream order would misclassify semantic cell 4 and could run ON UPDATE CASCADE for an independent delete/insert. Reusing the old RowID is not viable because RowID is the physical address assigned to the new append. Carrying only an in-memory marker would fail after checkpoint/recovery, which is inside the supported Data Branch range lifecycle.

Required decision before implementation

A complete fix must choose and specify one durable representation, for example either:

• an update record that carries an explicit old-to-new identity through CN workspace writes, TN application, logtail, objects/checkpoints, replay, and CollectChanges; or
• a separately persisted provenance log with retention/GC tied to the Data Branch lineage window.

That decision must also define mixed-version behavior and the contract for the other CollectChanges consumers (table_changes, CDC, ISCP, and publication), plus checkpoint/compaction/restart preservation and bounded GC. These are storage/protocol compatibility choices, not implementation details I can safely infer in a bug-fix PR.

There is also a delivery dependency: the issue's stated baseline is the still-open #27996. Current main does not contain its native stable-key UPDATE apply path. Copying #27996's four commits into a #28026 PR would duplicate and expand this issue's scope to #27992 rather than produce a focused change.

Decision

Excluded from a PR and deferred pending an approved persistent update-provenance design and the disposition of #27996. No source files were changed and no PR was opened.

[gouhongshen]

Automated AI triage

This issue requires human involvement and cannot currently be completed independently by the
automated fix loop.

Reason

Requires a storage/Data Branch architecture decision. A human maintainer must approve a durable update-provenance format, including history/recovery compatibility, before implementation.

Evidence

The report identifies missing durable old/new operation identity; current commit 24d0d6b explicitly leaves primary-key-changing updates, including ON UPDATE CASCADE, to #28026.

Please use this analysis to provide the missing decision, environment, evidence, or ownership
needed before the issue is returned to the AI candidate pool.

[gouhongshen]

Triage decision: defer.

Current-main verification (99ed717, fresh embedded single-CN/single-TN/single-Log instance):

• The exact MERGE primary-key move 2 -> 3 with ON UPDATE CASCADE still fails with ERROR 20101. DATA BRANCH DIFF reports DELETE 2 plus INSERT 3. After the error, parent id=2 and child parent_id=2 remain, proving statement atomicity.
• The equivalent PICK from sibling branches with keys(2) and ON UPDATE CASCADE fails identically and leaves both destination rows unchanged.
• A native UPDATE parent_t SET id=3 WHERE id=2 succeeds and changes child.parent_id to 3, proving the FK implementation already has the required native-update behavior.
• With the default RESTRICT action, both Data Branch apply and native UPDATE reject the key move and retain parent id=2 plus child parent_id=2.
• Counterexample: DELETE id=2 and INSERT id=3 executed as two independent statements in one source transaction produce the same DELETE 2 plus INSERT 3 change stream. Applying them correctly retains DELETE semantics and fails against the referencing child. Therefore commit timestamp, transaction identity, or iteration adjacency cannot safely infer update provenance.

Root cause on current main:

• multi_update.updateOneBatch invokes Relation.Delete and Relation.Write separately.
• disttae generates a new physical RowID for the replacement insert.
• The CN precommit protocol serializes user DML only as api.Entry_Insert or api.Entry_Delete. api.Entry_Update is declared but has no TN HandleWrite, logtail, partition-state, checkpoint, or CollectChanges implementation.
• engine.ChangesHandle exposes only data and tombstone batches, and Data Branch reconciliation keys them by PK. Once the PK changes, no durable old-to-new association remains.

A frontend pairing patch would silently convert the independent DELETE/INSERT counterexample into UPDATE semantics and could trigger unintended ON UPDATE actions. An in-memory marker would also be lost across logtail/checkpoint/recovery. The narrowest correct fix therefore requires an approved persistent update-provenance contract spanning CN workspace writes, TN/WAL, logtail, checkpoints/replay, and ChangesHandle, including mixed-version behavior, retention/GC, and semantics for table_changes, CDC, ISCP, publication, MERGE, and PICK.

The prior dependency on #27996 is now resolved because that PR is merged; the protocol/design blocker remains. I attempted the complete public-path regression matrix above, but no passing UT/BVT can be committed until that contract is selected and implemented. A skipped case would not be executable proof.

To promote later: storage/Data Branch owners approve the durable old/new identity representation and compatibility/retention contract. At that point the implementation must include MERGE and PICK cascade success, RESTRICT rejection, independent DELETE/INSERT non-conflation, restart/checkpoint preservation, and failure atomicity tests.