[HIVE-26054] Distinct + Groupby with column alias is failing

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Labels: pull-request-available
• Assignee: Krisztian Kasa
• Reporter: Naresh P R
• Created: 21/Mar/22 20:11
• Updated: 30/Nov/23 19:06
• Resolved: 09/Feb/23 07:33

Description

After HIVE-16924, below query is failing.

create table table1 (col1 bigint, col2 string);
create table table2 (t2_col1 string);

Select distinct col1 as alias_col1
from table1
where col2 = (SELECT max(t2_col1) as currentdate from table2 limit 1)
order by col1;

Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression '1': Only SubQuery expressions that are top level conjuncts are allowed (state=42000,code=40000) 

Workaround is either remove distinct column alias "alias_col1" or use alias in order by.

Issue Links

• relates to: HIVE-27923 Query fails with SemanticException if column referenced with name in ORDER by
• links to: GitHub Pull Request #3155
• links to: GitHub Pull Request #3891