Bug Report: Deleting a vdiff by UUID deletes all of the vdiffs

[austenLacy]

Overview of the Issue

We noticed that when we had multiple vdiffs with different UUIDs if we deleted a vdiff by UUID using the command

VDiff  -- --v2  unsharded2sharded delete <UUID_HERE>

it would delete all of the vdiffs instead of just the vdiff specified by the UUID.

Reproduction Steps

VDiff  -- --v2  unsharded2sharded delete <UUID_HERE>
VDiff -- --v2 --format=json ks.unsharded2sharded show all

The show command returns zero results even though we only deleted the vdiff for a single UUID.

We believe this could be caused by the DELETE query run on the _vt.vdiff and _vt.vdiff_table tables.

It uses a DELETE FROM LEFT JOIN but uses an AND instead of a WHERE clause and is likely caused by the nature of a left join which will delete the rows from the left table even if not present in the right table. This is reproducible with a trivial example in mysql.

root@mysql> CREATE TABLE vdiff (
    ->   id INTEGER PRIMARY KEY AUTO_INCREMENT,
    ->   vdiff_uuid INTEGER
    -> );
Query OK, 0 rows affected (0.01 sec)

root@mysql> CREATE TABLE vdiff_table (
    ->   id INTEGER PRIMARY KEY AUTO_INCREMENT,
    ->   vdiff_id INTEGER
    -> );
Query OK, 0 rows affected (0.01 sec)

root@mysql> INSERT INTO vdiff(vdiff_uuid) VALUES (1);
Query OK, 1 row affected (0.00 sec)

root@mysql> INSERT INTO vdiff(vdiff_uuid) VALUES (2);
Query OK, 1 row affected (0.01 sec)

root@mysql> INSERT INTO vdiff(vdiff_uuid) VALUES (3);
Query OK, 1 row affected (0.00 sec)

root@mysql> INSERT INTO vdiff_table(vdiff_id) VALUES (1);
Query OK, 1 row affected (0.00 sec)

root@mysql> INSERT INTO vdiff_table(vdiff_id) VALUES (1);
Query OK, 1 row affected (0.00 sec)

root@mysql> INSERT INTO vdiff_table(vdiff_id) VALUES (2);
Query OK, 1 row affected (0.01 sec)

root@mysql> INSERT INTO vdiff_table(vdiff_id) VALUES (2);
Query OK, 1 row affected (0.00 sec)

root@mysql> # BEFORE DELETE WITH 'LEFT JOIN AND'
root@mysql> select vd.* from vdiff as vd
    ->   left join vdiff_table on (vd.id = vdiff_table.vdiff_id);
+----+------------+
| id | vdiff_uuid |
+----+------------+
|  1 |          1 |
|  1 |          1 |
|  2 |          2 |
|  2 |          2 |
|  3 |          3 |
+----+------------+
5 rows in set (0.00 sec)

root@mysql> delete from vd, vdt using vdiff as vd left join vdiff_table as vdt on (vd.id = vdt.vdiff_id)
    -> and vd.vdiff_uuid = 2;
Query OK, 5 rows affected (0.01 sec)

root@mysql> # AFTER DELETE WITH 'LEFT JOIN AND'
root@mysql> select vd.* from vdiff as vd
    ->   left join vdiff_table on (vd.id = vdiff_table.vdiff_id);
Empty set (0.00 sec)

However, if I switch to use a where clause in WHERE vd.vdiff_uuid = 2 we see it deletes the rows correctly.

root@mysql> delete from vd, vdt using vdiff as vd left join vdiff_table as vdt on (vd.id = vdt.vdiff_id)
    -> where vd.vdiff_uuid = 2;
Query OK, 3 rows affected (0.00 sec)

root@mysql> select * from vdiff;
+----+------------+
| id | vdiff_uuid |
+----+------------+
|  1 |          1 |
|  3 |          3 |
+----+------------+
2 rows in set (0.00 sec)

root@mysql> select * from vdiff_table;
+----+----------+
| id | vdiff_id |
+----+----------+
|  1 |        1 |
|  2 |        1 |
+----+----------+
2 rows in set (0.00 sec)

Binary Version

Version: 15.0.3 (Git revision f3899036152acc676612494938a6c1f48e3e5068 branch 'HEAD') built on Tue Apr 18 20:21:51 UTC 2023 by vitess@buildkitsandbox using go1.18.9 linux/amd64

Operating System and Environment details

$ cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

Log Fragments

No response

[mattlord]

Thanks, @austenLacy ! You're right, that should be using WHERE there instead of AND. I think this was the result of ending up with a mix of the original query and the final one -- where I realized that a LEFT JOIN was needed as you might not have have any vdiff_table records. But when switching from a WHERE with implicit INNER JOIN to the LEFT JOIN I left the final AND clause untouched.