Cascade delete: a restriction referencing an earlier-deleted (downstream) table is silently invalidated by reverse-topological delete order — materialize (independent of MySQL 1093)

[dimitri-yatsenko]

Summary

A cascade delete whose restriction references a table that will be deleted earlier in the cascade
is silently invalidated by DataJoint's reverse-topological delete order — and this is independent
of MySQL error 1093. Table.delete deletes descendants (leaves) first and the seed last; if a
restriction references a table deleted before its own, that reference evaluates to empty by the
time its DELETE runs, so the row is stranded or mis-deleted. The fix is to materialize such
restrictions to literal keys before any deletion. MySQL 1093 is only an incidental, partial symptom
(see "Backend symptoms") — not the cause, and it must not shape the fix.

Root cause (backend-independent)

Table.delete executes one DELETE per table in reverse-topological order (table.py:1089), leaves
first, seed last. Restrictions are evaluated live at each table's delete time. Two kinds of
restriction reference a table deleted earlier:

1. User seed restriction referencing a descendant. (A & (X & cond)).delete() with X downstream
   of A: X (descendant) is deleted first; then A's DELETE re-evaluates A & (X & cond), but the
   matching X rows are gone → A matches nothing → A stranded while its X children were
   deleted.
2. Engine Part→Master upward reference. The Master's restriction is derived from its Part; the Part
   (descendant) is deleted first → the Master would strand. Already materialized today in
   _propagate_part_to_master.

Both are the same bug — a restriction referencing a table deleted before its own — on both backends.

Backend symptoms (secondary — NOT the framing)

In the sub-case where forward propagation makes a table's restriction reference itself, the
generated DELETE contains a self-referential subquery. MySQL rejects it with error 1093 and aborts
(an incidental, loud, partial backstop); PostgreSQL permits it and fails silently. But 1093 only
covers the self-referential subset — the seed-stranding case (#1: A's DELETE references X, not A)
does not trigger 1093 on either backend and is silent wherever it isn't materialized. So 1093 is
neither necessary nor sufficient to describe the problem.

Recommended approach

Materialize any cascade restriction that references a table deleted before it (a descendant in
delete order), before executing deletes. One backend-independent, 1093-free rule; it unifies the seed
case (#1) and the Part→Master case (#2).

• Detection (practical): text-search each table's compiled restriction for the
  fully-qualified, quoted name of any earlier-deleted table in the cascade set. DataJoint emits
  canonical qualified names, so this is reliable for engine-generated SQL; the action is materialize,
  not reject, so false positives cost only an unnecessary fetch('KEY'), never a wrong result.
  User-authored raw SQL with non-canonical names is the advanced user's responsibility.
  • Detecting only self-reference (a table's own name) is INSUFFICIENT — it catches the 1093
    sub-case but MISSES the seed (whose restriction names a descendant, not itself). The detection
    target is "references an earlier-deleted table," not "references itself."
  • Simplest conservative variant: always materialize the seed restriction in delete mode (plus the
    existing Part→Master materialization). One extra fetch of the keys being deleted; uniformly
    correct. Detection merely avoids that fetch for simple restrictions that reference nothing downstream.
• Unifying the Part→Master special-case (extract_master/_propagate_part_to_master) under this single
  rule is a larger v2.4 refactor; a targeted 2.3.x fix can add seed-restriction materialization
  first (the currently-unhandled case).

Delete vs. non-delete mode (materialize flag)

• Delete mode: materialize per the rule above.
• Non-delete mode (preview counts(), data export): materialize nothing. The ordering hazard
  exists only when rows are deleted; a preview/export issues SELECTs, which evaluate against current
  data (and self-referential SELECTs are legal on both backends — 1093 is DML-only). Removes today's
  wasted preview-time materialization (review F3) — a speedup.

Preview/delete divergence risk

Materialize from the same restricted expression the preview counts (restricted_T.fetch('KEY') vs
len(restricted_T)) so the affected set is identical by construction. Residual divergence is only
(a) concurrent data change between preview and delete (inherent to any preview-then-act split), and
(b) implementation drift between the count and materialize paths → mitigate with a shared builder and
parity tests across the tricky topologies (Part→Master, Part-of-Part, downstream-seed ref).

How it surfaces today

• Simple downstream semijoin seed (A & (X & cond)) trips an unrelated plan-time crash first —
  extract_column_names (condition.py:474) harvests the subquery's backtick identifiers (schema,
  table) as if columns → Attribute \` is not found`. So the semijoin case fails-closed by
  accident (cryptically) rather than stranding.
• More complex conditions that pass planning: MySQL may abort via 1093 (self-ref sub-case); PostgreSQL
  may silently strand. Backend-dependent patchwork.

References

• Surfaced in the 2.3.1 dj.Diagram review (finding F8).
• Code: table.py:1089 (reverse-topo delete loop), diagram.py:365 (seed restriction storage),
  diagram.py:1084 (__reversed__ → _restricted_table), _propagate_part_to_master (Part→Master
  materialization), condition.py:438-456 / :474 (restriction rendering / extract_column_names).