When a query uses the UNION, EXCEPT, or INTERSECT operators, the ORDER BY clause must be specified at the end of the statement and the results of the combined queries are sorted. ORDER BY clauses are not allowed in individual branches unless the branch is enclosed by parentheses.
There are two bugs currently:
- An ORDER BY is allowed in a branch of a UNION that is not enclosed in parentheses
- The final ORDER BY of a UNION is attached to the nearest branch when it should be sorting the combined results of the UNION(s)
For example, this is not valid syntax but is allowed in Impala
And for queries like this, the ORDER BY should order the unioned result, not just the nearest branch which is the current behavior.
If one wants ordering within a branch, the query block must be enclosed by parentheses like such:
Here is an example where incorrect results are returned.