Bug Report: Routing DML of reference tables to the source fails when it's part of a join

[wiebeytec]

Overview of the Issue

The following UPDATE and DELETE queries are expected to work on legacy.users, but it updates the reference table target in sites2024. This is wrong.

To demonstrate; when I run the following in mysql --comments:

vexplain /*vt+ EXECUTE_DML_QUERIES */ queries
update legacy.users as u

join legacy.users2sites as u2s
on u2s.idUser = u.idUser

inner join sites2024.sites as s
on s.idSite = u2s.idSite

set email = '[email protected]'
where s.idSite = 234234234;

+------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| #    | keyspace  | shard | query                                                                                                                                                                |
+------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|    0 | sites2024 | cc-ee | update sites as s, users, users2sites as u2s set users.email = '[email protected]' where s.idSite = 234234234 and u2s.idUser = users.idUser and s.idSite = u2s.idSite |
+------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Expected result: probably some kind of scatter query. The final update must go to legacy.users.

Also with DELETE:

vexplain /*vt+ EXECUTE_DML_QUERIES */ queries
delete users.*

from legacy.users

join legacy.users2sites as u2s
on u2s.idUser = users.idUser

inner join sites2024.sites as s
on s.idSite = u2s.idSite

where s.idSite = 234234234;

Results in the deleting from the wrong keyspace:

+------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| #    | keyspace  | shard | query                                                                                                                                      |
+------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
|    0 | sites2024 | cc-ee | delete users from sites as s, users, users2sites as u2s where s.idSite = 234234234 and u2s.idUser = users.idUser and s.idSite = u2s.idSite |
+------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------+

To compare: when I do a simple UPDATE query of sites2024.users, it does properly update legacy.users.

Reproduction Steps

Relevant part of the sites2024 vschema:

{
  "sharded": true,
  "require_explicit_routing": false,
  "vindexes": {
    "a_standard_hash": {
      "type": "xxhash"
    }
  },
  "tables": {
    "users": {
      "type": "reference",
      "source": "legacy.users"
    },
    "users2sites": {
      "type": "reference",
      "source": "legacy.users2sites"
    },
    "sites": {
      "column_vindexes": [
        {
          "column": "idSite",
          "name": "a_standard_hash"
        }
      ],
      "auto_increment": {
        "column": "idSite",
        "sequence": "sites_seq"
      }
    }
  }
}

The legacy keyspace:

{
  "require_explicit_routing": false,
  "sharded": false,
  "tables": {
    "users": {},
    "users2sites": {}
  }
}

In sites2024:

CREATE TABLE `sites` (
  `idSite` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idSite`),  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

In legacy:

CREATE TABLE `users` (
  `idUser` int unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`idUser`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `users2sites` (
  `idUser` int unsigned NOT NULL,
  `idSite` int unsigned NOT NULL,
  PRIMARY KEY (`idUser`,`idSite`),
  KEY `idSite` (`idSite`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Then do what's described earlier.

Binary Version

vtgate version Version: 21.0.1 (Git revision 3d4f41db2fbc32611c7d2ea2af3dc68b9d962415 branch 'HEAD') built on Tue Dec  3 05:39:35 UTC 2024 by runner@fv-az2029-313 using go1.23.3 linux/amd64

Operating System and Environment details

# cat /etc/lsb-release 
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=24.04
DISTRIB_CODENAME=noble
DISTRIB_DESCRIPTION="Ubuntu 24.04.1 LTS"

Linux 6.8.0-1008-aws #8-Ubuntu SMP Sat Apr 20 00:46:25 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux

Log Fragments

No response