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