Failure to cascade restriction with primary- followed by secondary-foreign-key inheritance

[CBroz1]

trafficstars

Bug Report

Description

In the example provided, the cascade process fails to evaluate the restriction for child tables, and keeps using the user-provided restriction as an invalid where_clause.

In testing, I also discovered that cascade will ignore a table named F. This may be escaping some regex check

Reproducibility

Include:

• OS: Linux
• Python Version: 3.9.16
• MySQL Version: 8.0.21
• MySQL Deployment Strategy: local-docker
• DataJoint Version: master branch, b42b239
• Minimum number of steps to reliably reproduce the issue
• Complete error stack as a result of evaluating the above steps

Script to Produce

import datajoint as dj

schema = dj.schema("temp")


@schema
class A(dj.Lookup):
    definition = """
    a_id: int
    """
    contents = [(0,), (2,), (4,)]


@schema
class B(dj.Lookup):
    definition = """
    -> A
    """
    contents = [(0,), (2,), (4,)]


@schema
class C(dj.Lookup):
    definition = """
    c_id: int
    ---
    -> B
    """
    contents = [(12, 0), (14, 2), (16, 4)]


@schema
class D(dj.Lookup):  # Cascade does not find this table if named `F`
    definition = """
    -> C
    """
    contents = [(12,), (14,), (16,)]


if __name__ == "__main__":
    (A & {"a_id": 0}).delete()

Error stack

[2024-06-03 14:54:51,117][INFO]: Connecting root@localhost:3307
[2024-06-03 14:54:51,125][INFO]: Connected root@localhost:3307
🌸 python 3.9.16 🌸
---------------------------------------------------------------------------
UnknownAttributeError                     Traceback (most recent call last)
File ~/wrk/spyglass/temp-del.py:41
     37     contents = [(12,), (14,), (16,)]
     40 if __name__ == "__main__":
---> 41     (A & {"a_id": 0}).delete()

File ~/wrk/datajoint-python/datajoint/table.py:598, in Table.delete(self, transaction, safemode, force_parts)
    596 # Cascading delete
    597 try:
--> 598     delete_count = cascade(self)
    599 except:
    600     if transaction:

File ~/wrk/datajoint-python/datajoint/table.py:568, in Table.delete.<locals>.cascade(table)
    566     else:
    567         child &= table.proj()
--> 568     cascade(child)
    569 else:
    570     deleted.add(table.full_table_name)

File ~/wrk/datajoint-python/datajoint/table.py:568, in Table.delete.<locals>.cascade(table)
    566     else:
    567         child &= table.proj()
--> 568     cascade(child)
    569 else:
    570     deleted.add(table.full_table_name)

File ~/wrk/datajoint-python/datajoint/table.py:568, in Table.delete.<locals>.cascade(table)
    566     else:
    567         child &= table.proj()
--> 568     cascade(child)
    569 else:
    570     deleted.add(table.full_table_name)

File ~/wrk/datajoint-python/datajoint/table.py:516, in Table.delete.<locals>.cascade(table)
    514 for _ in range(max_attempts):
    515     try:
--> 516         delete_count = table.delete_quick(get_count=True)
    517     except IntegrityError as error:
    518         match = foreign_key_error_regexp.match(error.args[0]).groupdict()

File ~/wrk/datajoint-python/datajoint/table.py:475, in Table.delete_quick(self, get_count)
    470 """
    471 Deletes the table without cascading and without user prompt.
    472 If this table has populated dependent tables, this will fail.
    473 """
    474 query = "DELETE FROM " + self.full_table_name + self.where_clause()
--> 475 self.connection.query(query)
    476 count = (
    477     self.connection.query("SELECT ROW_COUNT()").fetchone()[0]
    478     if get_count
    479     else None
    480 )
    481 self._log(query[:255])

File ~/wrk/datajoint-python/datajoint/connection.py:343, in Connection.query(self, query, args, as_dict, suppress_warnings, reconnect)
    341 cursor = self._conn.cursor(cursor=cursor_class)
    342 try:
--> 343     self._execute_query(cursor, query, args, suppress_warnings)
    344 except errors.LostConnectionError:
    345     if not reconnect:

File ~/wrk/datajoint-python/datajoint/connection.py:299, in Connection._execute_query(cursor, query, args, suppress_warnings)
    297         cursor.execute(query, args)
    298 except client.err.Error as err:
--> 299     raise translate_query_error(err, query)

UnknownAttributeError: Unknown column 'a_id' in 'where clause'

Expected Behavior

I expect the delete to include relevant entries in table D

Screenshots

n/a

Additional Research and Context

• Asked on DataJoint slack
• Reviewed open delete bugs