delete() fails when the table is restricted by another query #1610

Summary

delete() fails when the table is restricted by another query. The cascade applies the seed's compiled conditions again to a fresh copy of the table, through restrict(). restrict() collects column names from the whole condition text, subquery included, and rejects every name the seed table does not have.

• When the subquery names a column of another table, the delete raises Attribute ... is not found in query before deleting anything. On MySQL it also trips over the backquoted schema name, so even Subject & Session raises.
• On PostgreSQL, (Subject & Session).delete() gets past that check. It deletes the sessions first, as cascades do (children before parents), and then finds no subject that has a session. It returns 0 and rolls back without deleting anything (Cascade delete: a restriction referencing an earlier-deleted (downstream) table is silently invalidated by reverse-topological delete order — materialize (independent of MySQL 1093) #1496).

Nothing is deleted in either case, so no data is lost. But on MySQL no delete restricted by another query works, and on PostgreSQL only those whose subquery reads an upstream table and names no other table's columns do.

Reproduction

DataJoint 2.3.4 and master, MySQL 8.0 and PostgreSQL 15:

import datajoint as dj

schema = dj.Schema("scratch_subquery_seed")


@schema
class Subject(dj.Manual):
    definition = """
    subject_id : int32
    """


@schema
class Session(dj.Manual):
    definition = """
    -> Subject
    session_id : int32
    ---
    quality : int32
    """


Subject.insert([(1,), (2,), (3,), (4,)])
Session.insert([(1, 1, 1), (1, 2, 0), (2, 1, 1), (3, 1, 0)])

(Subject & (Session & "quality = 0")).delete(prompt=False)
# MySQL:      DataJointError: Attribute `scratch_subquery_seed` is not found in query.
#             (or `session`, or `quality`: whichever name the check meets first)
# PostgreSQL: DataJointError: Attribute `quality` is not found in query.

(Subject & Session).delete(prompt=False)
# MySQL:      DataJointError: Attribute `scratch_subquery_seed` is not found in query.
# PostgreSQL: returns 0; all four subjects and all four sessions remain.

dj.Diagram.cascade(seed).counts() fails the same way.

Workaround

Restrict the table by the primary keys the query selects:

seed = Subject & (Session & "quality = 0")
(Subject & seed.keys()).delete()

Expected behavior

The delete removes the rows the restriction selects when the delete starts, together with their descendants:

• Subject & (Session & "quality = 0") deletes subjects 1 and 3 and their three sessions;
• Subject - (Session & "quality = 0") deletes subjects 2 and 4 and session (2, 1);
• Subject & Session deletes subjects 1, 2 and 3 and all four sessions.

The seed's restriction can be replaced by the primary keys it selects, the same as for a dj.Top seed (#1589).