[
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)
