[jira] [Work started] (HIVE-30146) Wrong results for LEFT JOIN when the left input is grouped on the join keys and no left-side column is used above the join

["Ryu Kobayashi (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-30146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Work on HIVE-30146 started by Ryu Kobayashi.
--------------------------------------------
> Wrong results for LEFT JOIN when the left input is grouped on the join keys and no left-side column is used above the join
> --------------------------------------------------------------------------------------------------------------------------
>
>                 Key: HIVE-30146
>                 URL: https://issues.apache.org/jira/browse/HIVE-30146
>             Project: Hive
>          Issue Type: Bug
>          Components: CBO
>            Reporter: Konstantin Bereznyakov
>            Assignee: Ryu Kobayashi
>            Priority: Major
>
> A LEFT JOIN returns wrong results when its left input is an aggregate grouped exactly on the join keys (DISTINCT, GROUP BY, or UNION) and the query above the join uses no column from that left input. CBO removes the left input and the join, and the query reads only the right table.
> {code:sql}
> create table t1 (k int);
> create table t2 (k int);
> insert into t1 values (1), (1), (2);
> insert into t2 values (1), (1), (3), (4);
> select count(*) from (select distinct k from t1) a left join t2 b on a.k = b.k;
> select b.k from (select distinct k from t1) a left join t2 b on a.k = b.k;
> {code}
> ||Query||Expected||Actual||
> |select count( * ) ...|3|4|
> |select b.k ...|1, 1, NULL|1, 1, 3, 4|
> The second query returns the values 3 and 4, which have no match in t1. With {{hive.cbo.enable=false}} both queries are correct, and the same query written as an INNER join is also correct (2).
> The CBO plan has no join and no scan of t1:
> {noformat}
> HiveProject(_c0=[$0])
>   HiveAggregate(group=[{}], agg#0=[count()])
>     HiveProject(k=[$0])
>       HiveFilter(condition=[IS NOT NULL($0)])
>         HiveTableScan(table=[[default, t2]], table:alias=[b])
> {noformat}
> *Cause:* two parts of {{HiveSemiJoinRule}} work together:
> * {{HiveToSemiJoinRuleSwapInputs.onMatch}} matches a join whose left input is an Aggregate grouped on the join keys, with no column of that input used above the join. It does not check the join type (the code comment says "Join is INNER"). {{swapInputs}} then builds the swapped join with the same join type, so "agg LEFT JOIN t2" becomes "t2 LEFT JOIN agg", which has different semantics.
> * {{HiveSemiJoinRuleBase.perform}} handles a LEFT join by keeping only the left input. That is correct for the original orientation (the right side is unique on the join keys and not referenced), but after the swap it keeps t2 and drops the real left input.
> The swap without a join-type check was added for a Project above the join in HIVE-20826, and HIVE-24041 added the same swap for an Aggregate above the join. The LEFT handling in {{perform}} dates from HIVE-15458.
> The log shows {{HiveAggregateJoinToSemiJoinRuleSwapInputs}} firing for the count query and {{HiveProjectJoinToSemiJoinRuleSwapInputs}} for the projection query. RIGHT and FULL joins are not affected, because {{perform}} returns for any type other than INNER or LEFT.
> *Suggested fix:* in {{HiveToSemiJoinRuleSwapInputs.onMatch}}, return unless the join type is INNER.
> *Workaround:* {{hive.cbo.enable=false}}.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)