[HIVE-4564] Distinct along with order by is not working when table name is part of column name in order by clause

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 0.9.0
• Fix Version/s: 0.9.1
• Component/s: Query Processor
• Labels: distinct hive, order
• Assignee: Unassigned
• Reporter: chandra sekhar gunturi
• Created: 15/May/13 10:26
• Updated: 23/May/13 08:41

Description

I have following table named 'region'.

hive> desc region;
r_regionkey int
r_name string
r_comment string

When we use <distinct, orderby> clause combination in table_name.column_name format, the query throws SemanticException.

For example, the following query throws error.
hive> select distinct region.r_name from region order by region.r_name;
FAILED: SemanticException [Error 10004]: Line 1:51 Invalid table alias or column reference 'region': (possible column names are: _col0)

The same query works fine if the same query is used without table name in order by clause.
The following query works fine for region table.
hive> select distinct region.r_name from region order by r_name;

This is a common scenario in actual real world scenarios.

For example, I want to find out what are all the cities my employees are from.
>> SELECT DISTINCT CITY.NAME FROM EMPLOYEE, CITY WHERE EMPLOYEE.CID=CITY.CID ORDER BY CITY.NAME
Here we are forced to use CITY.NAME as it may conflict with EMPLOYEE.NAME.

Issue Links

• duplicates: HIVE-2954 The statement fails when a column part of an ORDER BY is not specified in the SELECT.