Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
wiebeytec opened this issue Dec 16, 2024 · 0 comments · May be fixed by #17414
Open

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

wiebeytec opened this issue Dec 16, 2024 · 0 comments · May be fixed by #17414
Labels
Component: Query Serving Type: Bug

Comments

@wiebeytec
Copy link

wiebeytec commented Dec 16, 2024 •
edited
Loading

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
@wiebeytec wiebeytec added Needs Triage This issue needs to be correctly labelled and triaged Type: Bug labels Dec 16, 2024
@harshit-gangal harshit-gangal added Component: Query Serving and removed Needs Triage This issue needs to be correctly labelled and triaged labels Dec 16, 2024
@harshit-gangal harshit-gangal linked a pull request Dec 23, 2024 that will close this issue
Open
5 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Component: Query Serving Type: Bug
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Reference Table DML Join Fix planetscale/vitess
2 participants
@wiebeytec @harshit-gangal