Bug Report: Setting fk column to NULL in prepared statements

[GuptaManan100]

Overview of the Issue

This bug was found using the fuzzer introduced in #13980.

The test with prepared statements started failing when NULL values were enabled in it. It was noticed that running updates such that the foreign key related column was set to NULL causes Vitess to not handle it correctly. -

=== RUN   TestFkFuzzTest/Unsharded_-_Single_Thread_-_Balanced_Inserts_and_Updates_Prepared_-_true
    mysql.go:232: Vitess and MySQL are not erroring the same way.
        Vitess error: <nil>
        MySQL error: Cannot delete or update a parent row: a foreign key constraint fails (`ks`.`fk_t13`, CONSTRAINT `fk_t13_ibfk_1` FOREIGN KEY (`col`) REFERENCES `fk_t11` (`col`) ON DELETE RESTRICT ON UPDATE RESTRICT) (errno 1451) (sqlstate 23000) during query: execute stmt_update using @col, @id
E0922 11:15:13.251375   71283 fk_fuzz_test.go:503] Failing query - [prepare stmt_update from 'update fk_t10 set col = ? where id = ?' SET @id = 3 SET @col = NULL execute stmt_update using @col, @id]

It seems that MySQL rejects the query, but Vitess doesn't.

Schema for the tables in question is as follows -

/*
 *                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
 */

The data prior to running the query in the database was -

E0922 11:14:29.281132   71283 fk_fuzz_test.go:202] Initial data for fk_t10 -
[[INT64(1) VARCHAR("5")] [INT64(2) VARCHAR("3")] [INT64(3) VARCHAR("2")] [INT64(4) VARCHAR("1")] [INT64(5) VARCHAR("4")] [INT64(6) VARCHAR("2")] [INT64(7) VARCHAR("1")] [INT64(8) NULL] [INT64(9) VARCHAR("2")] [INT64(10) VARCHAR("3")]]
E0922 11:14:29.281149   71283 fk_fuzz_test.go:202] Initial data for fk_t11 -
[[INT64(1) VARCHAR("2")] [INT64(2) VARCHAR("3")] [INT64(5) VARCHAR("1")] [INT64(6) VARCHAR("2")] [INT64(7) VARCHAR("3")] [INT64(8) VARCHAR("1")] [INT64(9) VARCHAR("5")] [INT64(10) VARCHAR("1")]]
E0922 11:14:29.281155   71283 fk_fuzz_test.go:202] Initial data for fk_t12 -
[[INT64(1) NULL] [INT64(3) NULL] [INT64(4) VARCHAR("1")] [INT64(5) NULL] [INT64(6) VARCHAR("3")] [INT64(7) VARCHAR("1")] [INT64(8) VARCHAR("1")] [INT64(9) NULL]]
E0922 11:14:29.281165   71283 fk_fuzz_test.go:202] Initial data for fk_t13 -
[[INT64(3) VARCHAR("2")] [INT64(4) VARCHAR("2")] [INT64(5) VARCHAR("5")] [INT64(6) NULL] [INT64(8) VARCHAR("1")] [INT64(9) VARCHAR("3")]]

After running the query, the data in Vitess and MySQL doesn't match -

mysql.go:223: Query (SELECT * FROM fk_t10 ORDER BY id) results mismatched.
        Vitess Results:
        [INT64(1) VARCHAR("5")]
        [INT64(2) VARCHAR("3")]
        [INT64(3) NULL]
        [INT64(4) VARCHAR("1")]
        [INT64(5) VARCHAR("4")]
        [INT64(6) VARCHAR("2")]
        [INT64(7) VARCHAR("1")]
        [INT64(8) NULL]
        [INT64(9) VARCHAR("2")]
        [INT64(10) VARCHAR("3")]
        Vitess RowsAffected: 0
        MySQL Results:
        [INT64(1) VARCHAR("5")]
        [INT64(2) VARCHAR("3")]
        [INT64(3) VARCHAR("2")]
        [INT64(4) VARCHAR("1")]
        [INT64(5) VARCHAR("4")]
        [INT64(6) VARCHAR("2")]
        [INT64(7) VARCHAR("1")]
        [INT64(8) NULL]
        [INT64(9) VARCHAR("2")]
        [INT64(10) VARCHAR("3")]
        MySQL RowsAffected: 0

mysql.go:223: Query (SELECT * FROM fk_t11 ORDER BY id) results mismatched.
        Vitess Results:
        [INT64(1) NULL]
        [INT64(2) VARCHAR("3")]
        [INT64(5) VARCHAR("1")]
        [INT64(6) NULL]
        [INT64(7) VARCHAR("3")]
        [INT64(8) VARCHAR("1")]
        [INT64(9) VARCHAR("5")]
        [INT64(10) VARCHAR("1")]
        Vitess RowsAffected: 0
        MySQL Results:
        [INT64(1) VARCHAR("2")]
        [INT64(2) VARCHAR("3")]
        [INT64(5) VARCHAR("1")]
        [INT64(6) VARCHAR("2")]
        [INT64(7) VARCHAR("3")]
        [INT64(8) VARCHAR("1")]
        [INT64(9) VARCHAR("5")]
        [INT64(10) VARCHAR("1")]
        MySQL RowsAffected: 0

Reproduction Steps

1. Create an unsharded keyspace with foreign keys as stated and Vitess managing the foreign keys.
2. Insert the data into the tables such that the initial state is reached
3. Run the prepared statement queries as stated.
4. See that Vitess doesn't reject the query even though it should due to a contraint failure on t13.

Binary Version

main

Operating System and Environment details

main

Log Fragments

No response