[
https://issues.apache.org/jira/browse/HIVE-30122?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Denys Kuzmenko updated HIVE-30122:
----------------------------------
Description:
With the default hive.transpose.aggr.join.unique=true (on by default since
HIVE-24084), when an aggregate is pushed below a join whose other side is
unique on the join key, the unique side projects only its group and join
columns. Aggregate arguments from that side are dropped. SUM/COUNT(col) return
the other side's row count instead; MIN/MAX fail to compile (AssertionError in
SqlSplittableAggFunction$SelfSplitter.topSplit, or
ArrayIndexOutOfBoundsException: -1 without assertions).
Repro: dim (x, k, y) with a RELY primary key on k, rows (10,1,100), (20,2,200),
(NULL,3,300); fact fk = 1,1,2,3,3,3. SELECT k, sum(x), count(x) FROM fact JOIN
dim ON fk = k GROUP BY k returns 2/1/3 instead of 20/20/NULL. Uniqueness from a
derived GROUP BY triggers it too.
{code}
┌────────────────────────────────────┬─────────────────────────────┬──────────────────────────────────┐
│ Query on a table with a unique key │ Default result │
Correct │
├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
│ k, sum(x), count(x), count(*) │ 1,2,2,2 / 2,1,1,1 / 3,3,3,3 │ 1,20,2,2
/ 2,20,1,1 / 3,NULL,0,3 │
├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
│ k, y, sum(y) │ 1→2 / 2→1 / 3→3 │ 1→200 /
2→200 / 3→900 │
├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
│ min(x) / max(x) │ fails to compile │ correct
values │
└────────────────────────────────────┴─────────────────────────────┴──────────────────────────────────┘
{code}
Cause: the unique branch at HiveAggregateJoinTransposeRule:197-201 never
registers splits for the aggregate arguments, so topSplit gets -1. Calcite
fixed its own rule in CALCITE-2195/CALCITE-3076;
Hive's copy never got the fix. Fix: project each argument via
splitter.singleton and register the split at its position among the projected
keys.
was:
hive.transpose.aggr.join=false doesn't turn it off
{code}
┌────────────────────────────────────┬─────────────────────────────┬──────────────────────────────────┐
│ Query on a table with a unique key │ Default result │
Correct │
├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
│ k, sum(x), count(x), count(*) │ 1,2,2,2 / 2,1,1,1 / 3,3,3,3 │ 1,20,2,2
/ 2,20,1,1 / 3,NULL,0,3 │
├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
│ k, y, sum(y) │ 1→2 / 2→1 / 3→3 │ 1→200 /
2→200 / 3→900 │
├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
│ min(x) / max(x) │ fails to compile │ correct
values │
└────────────────────────────────────┴─────────────────────────────┴──────────────────────────────────┘
{code}
- SUM and COUNT(col) over a column from the unique side silently return the
other side's row count.
- No constraint is needed: uniqueness from a derived GROUP BY triggers it too.
- Reachable by default since HIVE-24084 (2020), which turned the unique path
on.
- Where: CalcitePlanner:2295 registers the rule whenever AGGR_JOIN_TRANSPOSE
|| AGGR_JOIN_TRANSPOSE_UNIQUE.
Root cause: HiveAggregateJoinTransposeRule:197-201.
- The unique branch projects only the group and join keys, and never
registers a split for the aggregate arguments.
- topSplit then receives index −1 (:270-276): SUM/COUNT produce a count of
the other side, and MIN/MAX assert, or fail with ArrayIndexOutOfBounds when
assertions are off.
- Calcite fixed this in its own copy of the rule (CALCITE-2195, refined by
CALCITE-3076). Hive's copy dates from HIVE-10785 (2015) and never got the fix.
HIVE-29738, which is open, proposes
switching to Calcite's rule.
> CBO: HiveAggregateJoinTransposeRule drops aggregate arguments from a unique
> join side, returning wrong results
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-30122
> URL: https://issues.apache.org/jira/browse/HIVE-30122
> Project: Hive
> Issue Type: Bug
> Reporter: Denys Kuzmenko
> Priority: Major
>
> With the default hive.transpose.aggr.join.unique=true (on by default since
> HIVE-24084), when an aggregate is pushed below a join whose other side is
> unique on the join key, the unique side projects only its group and join
> columns. Aggregate arguments from that side are dropped. SUM/COUNT(col)
> return the other side's row count instead; MIN/MAX fail to compile
> (AssertionError in SqlSplittableAggFunction$SelfSplitter.topSplit, or
> ArrayIndexOutOfBoundsException: -1 without assertions).
>
> Repro: dim (x, k, y) with a RELY primary key on k, rows (10,1,100),
> (20,2,200), (NULL,3,300); fact fk = 1,1,2,3,3,3. SELECT k, sum(x), count(x)
> FROM fact JOIN dim ON fk = k GROUP BY k returns 2/1/3 instead of 20/20/NULL.
> Uniqueness from a derived GROUP BY triggers it too.
> {code}
>
> ┌────────────────────────────────────┬─────────────────────────────┬──────────────────────────────────┐
>
>
> │ Query on a table with a unique key │ Default result │
> Correct │
>
>
> ├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
>
>
> │ k, sum(x), count(x), count(*) │ 1,2,2,2 / 2,1,1,1 / 3,3,3,3 │
> 1,20,2,2 / 2,20,1,1 / 3,NULL,0,3 │
>
>
> ├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
>
>
> │ k, y, sum(y) │ 1→2 / 2→1 / 3→3 │ 1→200
> / 2→200 / 3→900 │
>
>
> ├────────────────────────────────────┼─────────────────────────────┼──────────────────────────────────┤
>
>
> │ min(x) / max(x) │ fails to compile │
> correct values │
>
>
> └────────────────────────────────────┴─────────────────────────────┴──────────────────────────────────┘
> {code}
> Cause: the unique branch at HiveAggregateJoinTransposeRule:197-201 never
> registers splits for the aggregate arguments, so topSplit gets -1. Calcite
> fixed its own rule in CALCITE-2195/CALCITE-3076;
> Hive's copy never got the fix. Fix: project each argument via
> splitter.singleton and register the split at its position among the projected
> keys.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
