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.

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

Joins with derived table and aliases produces bad plan #15957

Closed
systay opened this issue May 16, 2024 · 0 comments · Fixed by #15956
Closed

Joins with derived table and aliases produces bad plan #15957

systay opened this issue May 16, 2024 · 0 comments · Fixed by #15956

Comments

@systay
Copy link
Collaborator

systay commented May 16, 2024

For a query that can be merged into a single route, if one of the tables is a derived tables, and it is aliasing a column that is used in
a join predicate, Vitess produces the wrong query.

For the query:

select 1 
from user 
  join (
     select id as uid 
     from user) as t 
where t.uid = user.id

vtgate is now producing the following route:

{
  "OperatorType": "Route",
  "Variant": "Scatter",
  "Keyspace": {
    "Name": "user",
    "Sharded": true
  },
  "FieldQuery": "select 1 from (select id as uid from `user` where 1 != 1) as t, `user` where 1 != 1",
  "Query": "select 1 from (select id as uid from `user` where id = :user_id) as t, `user` where t.uid = `user`.id",
  "Table": "`user`"
}

This is the only operator in the plan - there is not join feeding the :user_id argument, so the query fails to run.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
1 participant