[HIVE-9002] union all does not generate correct result for order by and limit

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.2.0
• Assignee: Pengcheng Xiong
• Reporter: Pengcheng Xiong
• Created: 02/Dec/14 00:01
• Updated: 18/May/15 19:53
• Resolved: 09/Feb/15 21:42

Description

Right now if we have
select col from A
union all
select col from B [Operator]

it is treated as

(select col from A)
union all
(select col from B [Operator])

Although it is correct for where, group by (having) join operators, it is not correct for order by and limit operators. They should be

(select col from A
union all
select col from B) [order by, limit]

For order by, we can refer to MySQL, Oracle, DB2

mysql

http://dev.mysql.com/doc/refman/5.1/en/union.html

oracle

https://docs.oracle.com/cd/E17952_01/refman-5.0-en/union.html

ibm

http://www-01.ibm.com/support/knowledgecenter/ssw_i5_54/sqlp/rbafykeyu.htm

Issue Links

• is related to: HIVE-6189 Support top level union all statements
• is related to: HIVE-9039 Support Union Distinct
• is related to: HIVE-1398 Support union all without an outer select *

Sub-Tasks

• primary table in union: Open
• order by (limit) meaning for the last subquery of union in Hive is different from other main stream RDBMS: Closed