Overview of the Issue
When using a LIMIT and a column alias in combination with UNION ALL, the planner gets the column names wrong.
Reproduction Steps
- vschema
{
"sharded": true,
"tables": {
"contacts": {
"column_vindexes": [
{
"column": "id",
"name": "hash"
}
]
}
},
"vindexes": {
"hash": {
"type": "xxhash"
}
}
}
- schema
CREATE TABLE `contacts` (
`id` bigint unsigned NOT NULL,
`team_id` int unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `contacts_team_id_index` (`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
- Run a query which involves UNION ALL of SELECT statements that use both LIMIT and column aliases that need to scatter.
(SELECT `team_id` AS `foo` FROM `contacts` WHERE `team_id` = 1 ORDER BY `id` DESC LIMIT 1)
UNION ALL
(SELECT `team_id` AS `foo` FROM `contacts` WHERE `team_id` = 2 ORDER BY `id` DESC LIMIT 1);
If I remove the column aliases, the query runs. Here is the vexplain for the query without the aliases:
{
"OperatorType": "SimpleProjection",
"ColumnNames": [
"0:team_id"
],
"Columns": "0",
"Inputs": [
{
"OperatorType": "Concatenate",
"Inputs": [
{
"OperatorType": "Limit",
"Count": ":vtg1",
"Inputs": [
{
"OperatorType": "Route",
"Variant": "Scatter",
"Keyspace": {
"Name": "voxie",
"Sharded": true
},
"FieldQuery": "select team_id, id from contacts where 1 != 1",
"OrderBy": "1 DESC",
"Query": "select team_id, id from contacts where team_id = :team_id /* INT64 */ order by id desc limit :vtg1 /* INT64 */",
"Table": "contacts"
}
]
},
{
"OperatorType": "Limit",
"Count": ":vtg2",
"Inputs": [
{
"OperatorType": "Route",
"Variant": "Scatter",
"Keyspace": {
"Name": "voxie",
"Sharded": true
},
"FieldQuery": "select team_id, id from contacts where 1 != 1",
"OrderBy": "1 DESC",
"Query": "select team_id, id from contacts where team_id = :team_id1 /* INT64 */ order by id desc limit :vtg2 /* INT64 */",
"Table": "contacts"
}
]
}
]
}
]
}That plan looks ideal. It wants to run 2n queries, where n is the number of shards. We would like the very same plan for the query that has the column aliases.
Binary Version
Vitess 21.0.5 / PlanetScale V21 are both reproducers.
Operating System and Environment details
vttestserver image or PlanetScale are both reproducers.
Log Fragments
Overview of the Issue
When using a LIMIT and a column alias in combination with
UNION ALL, the planner gets the column names wrong.Reproduction Steps
{ "sharded": true, "tables": { "contacts": { "column_vindexes": [ { "column": "id", "name": "hash" } ] } }, "vindexes": { "hash": { "type": "xxhash" } } }If I remove the column aliases, the query runs. Here is the vexplain for the query without the aliases:
{ "OperatorType": "SimpleProjection", "ColumnNames": [ "0:team_id" ], "Columns": "0", "Inputs": [ { "OperatorType": "Concatenate", "Inputs": [ { "OperatorType": "Limit", "Count": ":vtg1", "Inputs": [ { "OperatorType": "Route", "Variant": "Scatter", "Keyspace": { "Name": "voxie", "Sharded": true }, "FieldQuery": "select team_id, id from contacts where 1 != 1", "OrderBy": "1 DESC", "Query": "select team_id, id from contacts where team_id = :team_id /* INT64 */ order by id desc limit :vtg1 /* INT64 */", "Table": "contacts" } ] }, { "OperatorType": "Limit", "Count": ":vtg2", "Inputs": [ { "OperatorType": "Route", "Variant": "Scatter", "Keyspace": { "Name": "voxie", "Sharded": true }, "FieldQuery": "select team_id, id from contacts where 1 != 1", "OrderBy": "1 DESC", "Query": "select team_id, id from contacts where team_id = :team_id1 /* INT64 */ order by id desc limit :vtg2 /* INT64 */", "Table": "contacts" } ] } ] } ] }That plan looks ideal. It wants to run 2n queries, where n is the number of shards. We would like the very same plan for the query that has the column aliases.
Binary Version
Operating System and Environment details
Log Fragments