unknown column with aggregation inside derived table #16367
Description
For the query:
select u.id, u.name, t.num_segments from (select id, count(*) as num_segments from user group by 1 order by 2 desc limit 20) t join unsharded u on u.id = t.idWe produce the following plan:
{
"OperatorType": "Join",
"Variant": "Join",
"JoinColumnIndexes": "R:0,R:1,L:0",
"JoinVars": {
"t_id": 1
},
"TableName": "`user`_unsharded",
"Inputs": [
{
"OperatorType": "Limit",
"Count": "20",
"Inputs": [
{
"OperatorType": "Route",
"Variant": "Scatter",
"Keyspace": {
"Name": "user",
"Sharded": true
},
"FieldQuery": "select t.num_segments, t.id, count(*) from `user` where 1 != 1 group by id",
"OrderBy": "2 DESC",
"Query": "select t.num_segments, t.id, count(*) from `user` group by id order by count(*) desc limit 20",
"Table": "`user`"
}
]
},
{
"OperatorType": "Route",
"Variant": "Unsharded",
"Keyspace": {
"Name": "main",
"Sharded": false
},
"FieldQuery": "select u.id, u.`name` from unsharded as u where 1 != 1",
"Query": "select u.id, u.`name` from unsharded as u where u.id = :t_id",
"Table": "unsharded"
}
]
}The issue is with this line:
select t.num_segments, t.id, count(*) from `user` group by id order by count(*) desc limit 20The derived table t doesn't exist, so this query errors out with:
target: xyz.-24.replica: vttablet: rpc error: code = NotFound desc = Unknown column 't.num_segments' in 'field list' (errno 1054)