[jira] [Commented] (HIVE-24199) Incorrect result when subquey in exists contains limit

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

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

Krisztian Kasa commented on HIVE-24199:
---------------------------------------

PR also restrict the usage of offset in subqueries of exists:
{code}
select * from web_sales ws1
where exists (select 1 from web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number limit 1 offset 20);
{code}
This case we skip the first 20 rows and if the subquery produces less than 21 rows the exists should return false.
HiveSubQueryRemoveRule introduces a LogicalCorrelate when rewrites the subquery and later HiveRelDecorrelator converts it to HiveSemiJoin because there is no Correlate or nested loop operator in Hive.
Since we can not guarantee that the inner query will be evaluated for every row of the outer query the HiveSortLimit shouldn't be allowed at the right hand side.

> Incorrect result when subquey in exists contains limit
> ------------------------------------------------------
>
>                 Key: HIVE-24199
>                 URL: https://issues.apache.org/jira/browse/HIVE-24199
>             Project: Hive
>          Issue Type: Bug
>            Reporter: Krisztian Kasa
>            Assignee: Krisztian Kasa
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 0.5h
>  Remaining Estimate: 0h
>
> {code:java}
> create table web_sales (ws_order_number int, ws_warehouse_sk int) stored as orc;
> insert into web_sales values
> (1, 1),
> (1, 2),
> (2, 1),
> (2, 2);
> select * from web_sales ws1
> where exists (select 1 from web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number limit 1);
> 1	1
> 1	2
> {code}
> {code:java}
> CBO PLAN:
> HiveSemiJoin(condition=[=($0, $2)], joinType=[semi])
>   HiveProject(ws_order_number=[$0], ws_warehouse_sk=[$1])
>     HiveFilter(condition=[IS NOT NULL($0)])
>       HiveTableScan(table=[[default, web_sales]], table:alias=[ws1])
>   HiveProject(ws_order_number=[$0])
>     HiveSortLimit(fetch=[1])          <-- This shouldn't be added
>       HiveProject(ws_order_number=[$0])
>         HiveFilter(condition=[IS NOT NULL($0)])
>           HiveTableScan(table=[[default, web_sales]], table:alias=[ws2])
> {code}
> Limit n on the right side of the join reduces the result set coming from the right to only n record hence not all the ws_order_number values are included which leads to correctness issue.



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