[
https://issues.apache.org/jira/browse/HIVE-30146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Bereznyakov updated HIVE-30146:
------------------------------------------
Description:
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{}}}.
was:
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{}}}.
> "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
> 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)
