delete() does nothing when its restriction contains a percent sign #1636

Summary

A delete restricted by a condition that contains % deletes nothing. (Label & "name = '50%'").delete() returns 0 and leaves the row and its descendants in place, and so does a dict restriction such as {"name": "50%"}. dj.Diagram.cascade(seed).counts() reports 0 rows for every table, although len(seed) is 1.

A restriction's conditions are stored as compiled SQL, with every % already escaped as %% for the database driver. The cascade applies the seed's conditions again to a fresh copy of the table through restrict(), which escapes every % a second time. The condition then looks for the literal text 50%% and matches no row.

A LIKE pattern keeps working, because %% and % match the same strings there.

Nothing is deleted that should not be, but the delete silently does nothing.

Reproduction

DataJoint 2.3.4 and master, MySQL 8.0 and PostgreSQL 15:

import datajoint as dj

schema = dj.Schema("scratch_percent")


@schema
class Label(dj.Manual):
    definition = """
    label_id : int32
    ---
    name : varchar(16)
    """


@schema
class Use(dj.Manual):
    definition = """
    -> Label
    use_id : int32
    """


Label.insert([(1, "50%"), (2, "other")])
Use.insert([(1, 1), (2, 1)])

seed = Label & "name = '50%'"
print(len(seed))                               # 1
print(dj.Diagram.cascade(seed).counts())       # 0 rows in each table
print(seed.delete(prompt=False))               # 0; both labels and both uses remain

Workaround

Restrict by the primary keys: (Label & seed.keys()).delete().

Expected behavior

The cascade selects the same rows as the restriction on its own: label 1 and its use.