[HIVE-9008] primary table in union

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Pengcheng Xiong
• Created: 02/Dec/14 18:47
• Updated: 02/Dec/14 19:02

Description

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

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements.

We can say

select A.id from A
union
select B.id from B
order by id

We can say

select A.id as a from A
union
select B.id from B
order by a

We can NOT say

select A.id as a from A
union
select B.id from B
order by A.id

ERROR 1054 (42S22): Unknown column 'A.id' in 'order clause'

We can NOT say

select A.id as a from A
union
select B.id from B
order by B.id

ERROR 1054 (42S22): Unknown column 'B.id' in 'order clause'

We can say

select A.id as a from A
union
(select B.id from B
order by B.id)