[
https://issues.apache.org/jira/browse/IMPALA-4741?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16969143#comment-16969143
]
Norbert Luksa commented on IMPALA-4741:
---------------------------------------
Had a look at the parser, it looks like it's deliberately different from
MySQL's parsing, [as stated in a
comment|https://github.com/apache/impala/blob/f49f8d8a32d12128eafb4c76632ca2908d22fa28/fe/src/main/cup/sql-parser.cup#L2281].
Looks like the current parser is designed around UNION, and the binding of the
ORDER BY relies on its left precedence.
Tried to play around the precedence, but did not find a simple solution.
Changing the precedence for ORDER BY just for this scenario would introduce
conflicts at other parts of the parser which did not seem to be easy fixes.
Also, in another go I tried to define precedence between UNION and SELECT, but
that did not work either, breaking other things.
> ORDER BY behavior with UNION is incorrect
> -----------------------------------------
>
> Key: IMPALA-4741
> URL: https://issues.apache.org/jira/browse/IMPALA-4741
> Project: IMPALA
> Issue Type: Bug
> Components: Frontend
> Affects Versions: Impala 2.8.0
> Reporter: Greg Rahn
> Assignee: Norbert Luksa
> Priority: Critical
> Labels: correctness, incompatibility, ramp-up, sql-language,
> tpc-ds
> Attachments: query36a.sql, query49.sql
>
>
> 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
> {code}
> select * from t1 order by 1
> union all
> select * from t2
> {code}
> And for queries like this, the ORDER BY should order the unioned result, not
> just the nearest branch which is the current behavior.
> {code}
> select * from t1
> union all
> select * from t2
> order by 1
> {code}
> If one wants ordering within a branch, the query block must be enclosed by
> parentheses like such:
> {code}
> (select * from t1 order by 1)
> union all
> (select * from t2 order by 2)
> {code}
> Here is an example where incorrect results are returned.
> Impala
> {code}
> [impalad:21000] > select r_regionkey, r_name from region union all select
> r_regionkey, r_name from region order by 1 limit 2;
> +-------------+-------------+
> | r_regionkey | r_name |
> +-------------+-------------+
> | 0 | AFRICA |
> | 1 | AMERICA |
> | 2 | ASIA |
> | 3 | EUROPE |
> | 4 | MIDDLE EAST |
> | 0 | AFRICA |
> | 1 | AMERICA |
> +-------------+-------------+
> Fetched 7 row(s) in 0.12s
> {code}
> PostgreSQL
> {code}
> tpch=# select r_regionkey, r_name from region union all select r_regionkey,
> r_name from region order by 1 limit 2;
> r_regionkey | r_name
> -------------+---------------------------
> 0 | AFRICA
> 0 | AFRICA
> (2 rows)
> {code}
> see also https://cloud.google.com/spanner/docs/query-syntax#syntax_5
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
