[
https://issues.apache.org/jira/browse/PHOENIX-1580?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14321720#comment-14321720
]
Julian Hyde commented on PHOENIX-1580:
--------------------------------------
My understanding is (1) ORDER BY is only allowed on the outer-most query (i.e.
not in a sub-query), (2) it is allowed against a set-operation (e.g. UNION) and
since it has lower precedence than UNION it sorts the whole union, not just the
last statement.
For example, MySQL: {code}mysql> select * from days union all select * from
days order by 1 desc;
+-----+-----------+
| day | week_day |
+-----+-----------+
| 7 | Saturday |
| 7 | Saturday |
| 6 | Friday |
| 6 | Friday |
| 5 | Thursday |
| 5 | Thursday |
| 4 | Wednesday |
| 4 | Wednesday |
| 3 | Tuesday |
| 3 | Tuesday |
| 2 | Monday |
| 2 | Monday |
| 1 | Sunday |
| 1 | Sunday |
+-----+-----------+
14 rows in set (0.00 sec)
{code}
Likewise, LIMIT is applied to the whole query:{code}mysql> select * from days
union all select * from days order by 1 desc limit 3;
+-----+----------+
| day | week_day |
+-----+----------+
| 7 | Saturday |
| 7 | Saturday |
| 6 | Friday |
+-----+----------+
3 rows in set (0.00 sec){code}
If your parser gets the precedence wrong it will produce {code}Union(R1,
Sort(R2)){code}; if your parser gets the precedence right, it will produce
{code}Sort(Union(R1, R2)){code} and you will get the right result.
It makes sense to think of ORDER BY, OFFSET and LIMIT occurring together in SQL
semantics (if you have LIMIT or OFFSET without ORDER BY, you basically get a
stable sort on 0 columns... i.e. no sort at all). In Calcite, they are
represented in the same operator, {code}Sort{code}.
> Support UNION ALL
> -----------------
>
> Key: PHOENIX-1580
> URL: https://issues.apache.org/jira/browse/PHOENIX-1580
> Project: Phoenix
> Issue Type: Bug
> Reporter: Alicia Ying Shu
> Assignee: Alicia Ying Shu
> Attachments: unionall-wipe.patch
>
>
> Select * from T1
> UNION ALL
> Select * from T2
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
