Bug Report: Cascading Delete fails while using Prepared statements

[GuptaManan100]

Overview of the Issue

This bug was found by the fuzzer that is added in #13980.

We are running a DELETE query via prepared statements on table fk_t11 and it causes the data in fk_t12 to mismatch -

--- FAIL: TestFkFuzzTest/Unsharded_-_Single_Thread_-_Balanced_Inserts_and_Deletes_Prepared_-_true (60.23s)
E0920 13:24:55.391476   15896 fk_fuzz_test.go:505] Failing query - [prepare stmt_del from 'delete from fk_t11 where id = ?' SET @id = 8 execute stmt_del using @id]
mysql.go:223: Query (SELECT * FROM fk_t12 ORDER BY id) results mismatched.
        Vitess Results:
        [INT64(2) VARCHAR("1")]
        [INT64(7) VARCHAR("1")]
        Vitess RowsAffected: 0
        MySQL Results:
        [INT64(2) VARCHAR("1")]
        [INT64(7) VARCHAR("1")]
        [INT64(9) VARCHAR("3")]
        MySQL RowsAffected: 0

The schema we are using looks like -

/*
 *                fk_t10
 *                   │
 * On Delete Cascade │
 * On Update Cascade │
 *                   │
 *                   ▼
 *                fk_t11──────────────────┐
 *                   │                    │
 *                   │                    │ On Delete Restrict
 * On Delete Cascade │                    │ On Update Restrict
 * On Update Cascade │                    │
 *                   │                    │
 *                   ▼                    ▼
 *                fk_t12               fk_t13
 */

Initially the state of the database looks like -

E0920 13:23:55.596002   15896 fk_fuzz_test.go:224] MySQL data for fk_t10 -
[[INT64(9) VARCHAR("3")] [INT64(10) VARCHAR("1")]]
E0920 13:23:55.596005   15896 fk_fuzz_test.go:224] MySQL data for fk_t11 -
[[INT64(3) VARCHAR("1")] [INT64(6) NULL] [INT64(8) VARCHAR("3")] [INT64(10) VARCHAR("3")]]
E0920 13:23:55.596009   15896 fk_fuzz_test.go:224] MySQL data for fk_t12 -
[[INT64(2) VARCHAR("1")] [INT64(7) VARCHAR("1")] [INT64(9) VARCHAR("3")]]
E0920 13:23:55.596013   15896 fk_fuzz_test.go:224] MySQL data for fk_t13 -
[[INT64(6) VARCHAR("3")] [INT64(8) VARCHAR("3")] [INT64(9) VARCHAR("3")]]

On executing the query, we expect the query to fail because deleting the row in fk_t11 with id 8 shouldn't succeed as fk_t13 has rows which prevent its deletion.

We do see that Vitess reports the correct error. But somehow it ends up deleting some rows from fk_t12 when it shouldn't. Post running the query -

E0920 13:24:55.391619   15896 fk_fuzz_test.go:507] MySQL data for fk_t12 -
[[INT64(2) VARCHAR("1")] [INT64(7) VARCHAR("1")] [INT64(9) VARCHAR("3")]]
E0920 13:24:55.391629   15896 fk_fuzz_test.go:508] Vitess data for fk_t12 -
[[INT64(2) VARCHAR("1")] [INT64(7) VARCHAR("1")]]

Reproduction Steps

Create the tables with the given schema and insert the rows as stated.
Then, try running the delete query as listed above.

Binary Version

main

Operating System and Environment details

main

Log Fragments

No response