[
https://issues.apache.org/jira/browse/HIVE-30154?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Denys Kuzmenko updated HIVE-30154:
----------------------------------
Description:
With hive.transpose.aggr.join=true, a SUM over a DECIMAL column that the rule
pushes below a join returns a type 10 digits wider than the same query without
the rule. The values are equal, but the type is visible to users: CTAS column
types and result set metadata change.
*Repro:*
{code}
create table zz_fact (k int, v decimal(7,2));
create table zz_dim (k int, name string);
insert into zz_fact values (1, 10.25), (1, 20.50), (2, 30.75), (2, 1.00), (3,
5.55);
insert into zz_dim values (1, 'a'), (2, 'b'), (3, 'a');
analyze table zz_fact compute statistics for columns;
analyze table zz_dim compute statistics for columns;
set hive.transpose.aggr.join=true;
create table zz_ctas_on as select name, sum(v) s from zz_fact join zz_dim on
zz_fact.k = zz_dim.k group by name;
describe zz_ctas_on; -- s decimal(27,2)
set hive.transpose.aggr.join=false;
create table zz_ctas_off as select name, sum(v) s from zz_fact join zz_dim on
zz_fact.k = zz_dim.k group by name;
describe zz_ctas_off; -- s decimal(17,2)
{code}
Both tables hold the same rows: a 36.30, b 31.75.
*Cause: *
- The rule splits SUM into a lower SUM below the join and an upper SUM over
the lower result times the other side's count. The CBO plan:
HiveAggregate(group=[{3}], agg#0=[sum($5)]) over $f5=[CAST(*($1,
$4)):DECIMAL(17, 2)].
- HiveSumSplitter.topSplit keeps the original call type, decimal(17,2), in
Calcite.
- The plan reaches the operator tree through the AST as a plain sum(...), and
Calcite's type isn't carried over. GenericUDAFSumHiveDecimal in
PARTIAL1/COMPLETE adds 10 to its input precision again: decimal(17,2) →
decimal(27,2).
Possible fixes:
- Derive the upper SUM's type from its input, as Hive will (decimal(27,2)),
and cast back to the original type in a project above the aggregate.
HivePartialAggregateJoinTransposeRule did this
before it switched to merge modes.
- Or plan the upper aggregate as a merge of the lower one (map-side PARTIAL2,
reducer FINAL), which keeps decimal(17,2) without a cast.
This is off by default (hive.transpose.aggr.join=false), and it is separate
from HIVE-30122, a wrong-results bug in the same rule.
was:
With hive.transpose.aggr.join=true, a SUM over a DECIMAL column that the rule
pushes below a join returns a type 10 digits wider than the same query without
the rule. The values are equal, but the type is visible to users: CTAS column
types and result set metadata change.
Repro:
{code}
create table zz_fact (k int, v decimal(7,2));
create table zz_dim (k int, name string);
insert into zz_fact values (1, 10.25), (1, 20.50), (2, 30.75), (2, 1.00), (3,
5.55);
insert into zz_dim values (1, 'a'), (2, 'b'), (3, 'a');
analyze table zz_fact compute statistics for columns;
analyze table zz_dim compute statistics for columns;
set hive.transpose.aggr.join=true;
create table zz_ctas_on as select name, sum(v) s from zz_fact join zz_dim on
zz_fact.k = zz_dim.k group by name;
describe zz_ctas_on; -- s decimal(27,2)
set hive.transpose.aggr.join=false;
create table zz_ctas_off as select name, sum(v) s from zz_fact join zz_dim on
zz_fact.k = zz_dim.k group by name;
describe zz_ctas_off; -- s decimal(17,2)
{code}
Both tables hold the same rows: a 36.30, b 31.75.
> CBO: HiveAggregateJoinTransposeRule changes the result type of a DECIMAL SUM
> ----------------------------------------------------------------------------
>
> Key: HIVE-30154
> URL: https://issues.apache.org/jira/browse/HIVE-30154
> Project: Hive
> Issue Type: Bug
> Reporter: Denys Kuzmenko
> Priority: Major
>
> With hive.transpose.aggr.join=true, a SUM over a DECIMAL column that the rule
> pushes below a join returns a type 10 digits wider than the same query
> without the rule. The values are equal, but the type is visible to users:
> CTAS column types and result set metadata change.
> *Repro:*
> {code}
> create table zz_fact (k int, v decimal(7,2));
>
>
> create table zz_dim (k int, name string);
>
>
> insert into zz_fact values (1, 10.25), (1, 20.50), (2, 30.75), (2, 1.00),
> (3, 5.55);
>
> insert into zz_dim values (1, 'a'), (2, 'b'), (3, 'a');
>
>
> analyze table zz_fact compute statistics for columns;
>
>
> analyze table zz_dim compute statistics for columns;
>
>
>
>
>
> set hive.transpose.aggr.join=true;
>
>
> create table zz_ctas_on as select name, sum(v) s from zz_fact join zz_dim
> on zz_fact.k = zz_dim.k group by name;
>
> describe zz_ctas_on; -- s decimal(27,2)
>
>
>
>
>
> set hive.transpose.aggr.join=false;
>
>
> create table zz_ctas_off as select name, sum(v) s from zz_fact join zz_dim
> on zz_fact.k = zz_dim.k group by name;
>
> describe zz_ctas_off; -- s decimal(17,2)
> {code}
> Both tables hold the same rows: a 36.30, b 31.75.
> *Cause: *
>
>
> - The rule splits SUM into a lower SUM below the join and an upper SUM over
> the lower result times the other side's count. The CBO plan:
>
> HiveAggregate(group=[{3}], agg#0=[sum($5)]) over $f5=[CAST(*($1,
> $4)):DECIMAL(17, 2)].
>
> - HiveSumSplitter.topSplit keeps the original call type, decimal(17,2), in
> Calcite.
>
> - The plan reaches the operator tree through the AST as a plain sum(...),
> and Calcite's type isn't carried over. GenericUDAFSumHiveDecimal in
> PARTIAL1/COMPLETE adds 10 to its input precision again: decimal(17,2) →
> decimal(27,2).
>
>
>
>
>
> Possible fixes:
>
>
> - Derive the upper SUM's type from its input, as Hive will (decimal(27,2)),
> and cast back to the original type in a project above the aggregate.
> HivePartialAggregateJoinTransposeRule did this
> before it switched to merge modes.
>
>
> - Or plan the upper aggregate as a merge of the lower one (map-side
> PARTIAL2, reducer FINAL), which keeps decimal(17,2) without a cast.
>
>
>
>
> This is off by default (hive.transpose.aggr.join=false), and it is separate
> from HIVE-30122, a wrong-results bug in the same rule.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
