Konstantin Bereznyakov created HIVE-30146:
---------------------------------------------
Summary: "Wrong results: CBO drops the left input of a LEFT JOIN
when it is grouped on the join keys
Key: HIVE-30146
URL: https://issues.apache.org/jira/browse/HIVE-30146
Project: Hive
Issue Type: Bug
Components: CBO
Reporter: Konstantin Bereznyakov
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)
