[
https://issues.apache.org/jira/browse/HIVE-30146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Bereznyakov updated HIVE-30146:
------------------------------------------
Summary: 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 (was: "Wrong results:
CBO drops the left input of a LEFT JOIN when it is grouped on the join keys)
> 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
> Priority: Major
>
> Confirmed on latest master today:
>
> {code:java}
> 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 join t2 b on a.k = b.k;
> set hive.cbo.enable=false;
> select count(*) from (select distinct k from t1) a join t2 b on a.k = b.k;
> {code}
> The first *select* returns {*}4{*}, the second one returns *3* (correct).
> The results of "SELECT *..." seem to be unaffected.
>
> *Cause:* two parts of {{{}HiveSemiJoinRule{}}}, both added in HIVE-24041,
> 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 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 (not yet fully verified)
> *Workarounds:* {{{}hive.cbo.enable=false{}}}, or make the query above the
> join use a column from the left input, for example {{count(a.k)}} instead of
> {{count( * )}}
> Reproduced on master (227d8da1b1) with {{{}TestMiniLlapLocalCliDriver{}}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
