Joins inside derived tables #14993
Description
A query like this:
SELECT count(*)
FROM (SELECT DISTINCT u.user_id
FROM user u
JOIN user_extra ue ON u.id = ue.user_id
JOIN music m ON m.id = u.id) dtWill be planned like this by the vtgate planner.
{
"QueryType": "SELECT",
"Original": "SELECT count(*) FROM (SELECT DISTINCT u.user_id FROM user u JOIN user_extra ue ON u.id = ue.user_id JOIN music m ON m.id = u.id) dt",
"Instructions": {
"OperatorType": "Aggregate",
"Variant": "Scalar",
"Aggregates": "count_star(0) AS count(*)",
"Inputs": [
{
"OperatorType": "SimpleProjection",
"Columns": [
1
],
"Inputs": [
{
"OperatorType": "Distinct",
"Collations": [
"(0:2)",
"1"
],
"Inputs": [
{
"OperatorType": "Join",
"Variant": "Join",
"JoinColumnIndexes": "R:0,L:1,R:1",
"JoinVars": {
"m_id": 2
},
"TableName": "music_`user`, user_extra",
"Inputs": [
{
"OperatorType": "Route",
"Variant": "Scatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"FieldQuery": "select m.id, 1, dt.`m.id` from (select m.id as `m.id` from music as m where 1 != 1) as dt where 1 != 1",
"Query": "select distinct m.id, 1, dt.`m.id` from (select m.id as `m.id` from music as m) as dt",
"Table": "music"
},
{
"OperatorType": "Route",
"Variant": "EqualUnique",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"FieldQuery": "select u.user_id, weight_string(u.user_id) from (select u.user_id from `user` as u, user_extra as ue where 1 != 1) as dt where 1 != 1",
"Query": "select distinct u.user_id, weight_string(u.user_id) from (select u.user_id from `user` as u, user_extra as ue where u.id = :m_id and u.id = ue.user_id) as dt",
"Table": "`user`, user_extra",
"Values": [
":m_id"
],
"Vindex": "user_index"
}
]
}
]
}
]
}
]
},
"TablesUsed": [
"user.music",
"user.user",
"user.user_extra"
]
}Both queries we are sending down to the tablets are obviously wrong:
-- left
select distinct m.id, 1, dt.`m.id` from (select m.id as `m.id` from music as m) as dt;
--right
select distinct u.user_id, weight_string(u.user_id)
from (select u.user_id
from `user` as u,
user_extra as ue
where u.id = :m_id
and u.id = ue.user_id) as dtWe are using the internal tables names on the outside of the derived table, and this will just make MySQL throw an error.
I've only been able to trigger the issue when there are three tables being joined, and two of these tables queries can be merged into a single one.