[ 
https://issues.apache.org/jira/browse/HIVE-30274?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Krisztian Kasa updated HIVE-30274:
----------------------------------
    Description: 
An aggregate function argument can be
{code:java}
CASE WHEN cond THEN value [ELSE [NULL|0|0.0]] END
{code}
or
{code:java}
IF(cond, value, [NULL|0|0.0])
{code}
When an CASE/IF expression's condition (cond) references one join input and its 
value references the other, partial aggregate push-down is enabled only if the 
CASE/IF expression is split: the value goes into the pushed-down aggregate, 
while the condition remains in the aggregate above the join.
h4. Example
{code:sql}
create table t_fact (k int, q int, v int, p decimal(7,2));
create table t_dim (k int, g string, c string);

select d.g, sum(case when d.c = 'x' then f.v end)
from t_fact f
join t_dim d on f.k = d.k
group by d.g;
{code}
The plan after the push is equivalent to this query, where the subquery is the 
pushed-down aggregate:
{code:sql}
select d.g, sum(if(d.c = 'x', f.s, null))
from (select k, sum(v) s
      from t_fact
      group by k) f
join t_dim d on f.k = d.k
group by d.g;
{code}
In the actual plan, the pushed-down GROUP BY is a map-side PARTIAL_HASH 
aggregation in the t_fact vertex, so it can emit more than one partial row per 
k. The subquery is its logical equivalent.

See the aggregate function call in the original query
{code:sql}
sum(case when d.c = 'x' then f.v end)
{code}
the condition {{d.c = 'x'}} references {{t_dim}} and the value {{f.v}} 
references {{t_fact}}.
 * The value is aggregated below the join, on {{{}t_fact: sum(f.v){}}}, grouped 
by the join key {{{}f.k{}}}.
 * The merge above the join applies the condition: {{{}sum(if(d.c = 'x', 
partial, null)){}}}.

Every row in a group created by the pushed-down GROUP BY has the same join key, 
so it joins with the same rows from the other input. For each of those rows, 
the condition evaluates to either true or false for the entire group.
h4. Restrictions:
 * The value expression can only contain columns, constants, +, -, *, negation, 
and casts. In Hive / can be also enabled because division by 0 returns null 
without failing the query. Without partial aggregate push-down, the value 
expression is evaluated only for rows where the condition is true. Since the 
pushed-down aggregate does not include the condition, all values are 
aggregated—even for rows where the condition is false—so evaluating them must 
not fail in order to preserve the original behavior. For example, sum(case when 
d.c = 'x' then f.v div f.q end) is not split, because div fails on a zero 
divisor.
 * ELSE must be NULL or omitted. 0 is also allowed for SUM and $SUM0.
 * A CASE expression can have only one WHEN clause.

  was:
h4. CASE / IF split

An aggregate function argument can be
{code:java}
CASE WHEN cond THEN value [ELSE [NULL|0|0.0]] END
{code}
or
{code:java}
IF(cond, value)
IF(cond, value, [NULL|0|0.0])
{code}
where cond references one join input and value the other. For example, from 
TPC-DS query 43:
{code:java}
sum(case when d_day_name = 'Sunday' then ss_sales_price else null end)
{code}
 * The value is aggregated below the join: sum(ss_sales_price).
 * The merge above the join applies the condition: sum(if(d_day_name = 
'Sunday', partial, null)). COUNT merges with sum(if(cond, partial, 0)).

Every row in a group created by the pushed-down GROUP BY has the same join key, 
so it joins with the same rows from the other input. For each of those rows, 
the condition evaluates to either true or false for the entire group.

h4. Restrictions:
 * The value expression can only contain columns, constants, +, -, *, negation, 
and casts. Without partial aggregate push-down, the value expression is 
evaluated only for rows where the condition is true. Since the pushed-down 
aggregate does not include the condition, all values are aggregated—even for 
rows where the condition is false—so evaluating them must not fail in order to 
preserve the original behavior.
 * ELSE must be NULL or omitted. 0 is also allowed for SUM and $SUM0.
 * A CASE expression can have only one WHEN clause.


> Partial GROUP BY join transpose: split IF/CASE aggregation arguments across 
> the join
> ------------------------------------------------------------------------------------
>
>                 Key: HIVE-30274
>                 URL: https://issues.apache.org/jira/browse/HIVE-30274
>             Project: Hive
>          Issue Type: Sub-task
>          Components: Physical Optimizer
>            Reporter: Krisztian Kasa
>            Priority: Major
>
> An aggregate function argument can be
> {code:java}
> CASE WHEN cond THEN value [ELSE [NULL|0|0.0]] END
> {code}
> or
> {code:java}
> IF(cond, value, [NULL|0|0.0])
> {code}
> When an CASE/IF expression's condition (cond) references one join input and 
> its value references the other, partial aggregate push-down is enabled only 
> if the CASE/IF expression is split: the value goes into the pushed-down 
> aggregate, while the condition remains in the aggregate above the join.
> h4. Example
> {code:sql}
> create table t_fact (k int, q int, v int, p decimal(7,2));
> create table t_dim (k int, g string, c string);
> select d.g, sum(case when d.c = 'x' then f.v end)
> from t_fact f
> join t_dim d on f.k = d.k
> group by d.g;
> {code}
> The plan after the push is equivalent to this query, where the subquery is 
> the pushed-down aggregate:
> {code:sql}
> select d.g, sum(if(d.c = 'x', f.s, null))
> from (select k, sum(v) s
>       from t_fact
>       group by k) f
> join t_dim d on f.k = d.k
> group by d.g;
> {code}
> In the actual plan, the pushed-down GROUP BY is a map-side PARTIAL_HASH 
> aggregation in the t_fact vertex, so it can emit more than one partial row 
> per k. The subquery is its logical equivalent.
> See the aggregate function call in the original query
> {code:sql}
> sum(case when d.c = 'x' then f.v end)
> {code}
> the condition {{d.c = 'x'}} references {{t_dim}} and the value {{f.v}} 
> references {{t_fact}}.
>  * The value is aggregated below the join, on {{{}t_fact: sum(f.v){}}}, 
> grouped by the join key {{{}f.k{}}}.
>  * The merge above the join applies the condition: {{{}sum(if(d.c = 'x', 
> partial, null)){}}}.
> Every row in a group created by the pushed-down GROUP BY has the same join 
> key, so it joins with the same rows from the other input. For each of those 
> rows, the condition evaluates to either true or false for the entire group.
> h4. Restrictions:
>  * The value expression can only contain columns, constants, +, -, *, 
> negation, and casts. In Hive / can be also enabled because division by 0 
> returns null without failing the query. Without partial aggregate push-down, 
> the value expression is evaluated only for rows where the condition is true. 
> Since the pushed-down aggregate does not include the condition, all values 
> are aggregated—even for rows where the condition is false—so evaluating them 
> must not fail in order to preserve the original behavior. For example, 
> sum(case when d.c = 'x' then f.v div f.q end) is not split, because div fails 
> on a zero divisor.
>  * ELSE must be NULL or omitted. 0 is also allowed for SUM and $SUM0.
>  * A CASE expression can have only one WHEN clause.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

Reply via email to