Description
More specifically, only the innermost order by ever makes it to the plan.
Consider the following query.
select * from (select * from (select * from `travel-sample` order by day) a order by flight) b order by utc;
(never mind the usefulness of this, BI tools generate worse things).
The plan produced is
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
,
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
,
{
"#operator": "InitialProject",
"result_terms": [
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
]
},
]
},
,
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
]
}
]
}
}
]
},
]
},
,
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
]
}
]
}
}
]
},
]
}
It is admirable that the optimiser tries to cut down on the number of order bys (clearly in this query only the outer one (utc) makes sense), however it does chose the wrong one (day).