[jira] [Commented] (HIVE-26054) Distinct + Groupby with column alias is failing

["Krisztian Kasa (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-26054?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17514726#comment-17514726 ] 

Krisztian Kasa commented on HIVE-26054:
---------------------------------------

The issue can be reproduced with a simple query like
{code:java}
select distinct b as alias_b
from t1
order by b;
{code}
When Hive builds Logical operator for Order By clause it uses the Project operator's row schema to resolve expressions in the order by clause. This row schema contains only the alias of the columns in the Select clause if distinct was defined.

> Distinct + Groupby with column alias is failing
> -----------------------------------------------
>
>                 Key: HIVE-26054
>                 URL: https://issues.apache.org/jira/browse/HIVE-26054
>             Project: Hive
>          Issue Type: Bug
>            Reporter: Naresh P R
>            Assignee: Krisztian Kasa
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> After [HIVE-16924|https://issues.apache.org/jira/browse/HIVE-16924], below query is failing.
> {code:java}
> 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) {code}
> Workaround is either remove distinct column alias "alias_col1" or use alias in order by.



--
This message was sent by Atlassian Jira
(v8.20.1#820001)