[jira] [Work logged] (HIVE-24929) Allow correlated exists subqueries with windowing clause

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-24929?focusedWorklogId=571060&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-571060 ]

ASF GitHub Bot logged work on HIVE-24929:
-----------------------------------------

                Author: ASF GitHub Bot
            Created on: 24/Mar/21 10:33
            Start Date: 24/Mar/21 10:33
    Worklog Time Spent: 10m 
      Work Description: kasakrisz opened a new pull request #2112:
URL: https://github.com/apache/hive/pull/2112


   ### What changes were proposed in this pull request?
   Loose correlated subquery restrictions: enable correlated exists/not exists subquery rewrite when subquery has windowing clause. 
   
   ### Why are the changes needed?
   In case of Exists/Not exists subqueries we are not interested in the result of the window function call but the existence of any record.
   
   ### Does this PR introduce _any_ user-facing change?
   Yes. Currently such queries can not be executed with Hive and an error message is printed. With this patch queries will be executed and the result will be printed. 
   
   ### How was this patch tested?
   ```
   mvn test -Dtest.output.overwrite -DskipSparkTests -Dtest=TestMiniLlapLocalCliDriver -Dqfile=subquery_exists_windowfunc.q -pl itests/qtest -Pitests
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Issue Time Tracking
-------------------

            Worklog Id:     (was: 571060)
    Remaining Estimate: 0h
            Time Spent: 10m

> Allow correlated exists subqueries with windowing clause
> --------------------------------------------------------
>
>                 Key: HIVE-24929
>                 URL: https://issues.apache.org/jira/browse/HIVE-24929
>             Project: Hive
>          Issue Type: Improvement
>          Components: Query Planning
>            Reporter: Krisztian Kasa
>            Assignee: Krisztian Kasa
>            Priority: Major
>             Fix For: 4.0.0
>
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> Currently queries which has windowing clause with subqueries are not supported by Hive: Hive rewrites subqueries to joins and the rewritten plan would lead to incorrect results such cases.
> However this restriction can be lifted in case of Exists/Not exists subqueries since those cases we don not interested in the result of the window function call but the existence of any record.
> {code}
> select id, int_col
> from alltypesagg a
> where exists
>   (select sum(int_col) over (partition by bool_col)
>    from alltypestiny b
>    where a.id = b.id);
> {code}
> {code}
> select id, int_col from alltypestiny t
> where not exists
>   (select sum(int_col) over (partition by bool_col)
>    from alltypesagg a where t.id = a.int_col);
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)