[
https://issues.apache.org/jira/browse/HIVE-22262?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17065206#comment-17065206
]
Vineet Garg commented on HIVE-22262:
------------------------------------
Unfortunately rewriting still isn't triggered for above query.
AggregateJoinTransposeRule does group by pushdown (along with eager count) and
adds an extra count() on both side generating the following plan
{noformat}
HiveAggregate(group=[{3, 4}], agg#0=[sum($8)], agg#1=[$SUM0($9)])
HiveProject(TO_DATE=[$0], $f1=[$1], count=[$2], pk1=[$3], fk4=[$4],
CAST=[$5], count0=[$6], $f4=[$7], $f8=[*($1, $6)], $f9=[*($2, $7)])
HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none],
cost=[not available])
HiveAggregate(group=[{1}], agg#0=[sum($0)], count=[count()])
HiveProject(subset=[rel#500:Subset#2.HIVE.[]], fk3=[$2],
TO_DATE=[TO_DATE($3)])
HiveFilter(subset=[rel#498:Subset#1.HIVE.[]], condition=[IS NOT
NULL(TO_DATE($3))])
HiveTableScan(subset=[rel#496:Subset#0.HIVE.[]], table=[[default,
fact]], table:alias=[fact])
HiveAggregate(group=[{0, 1, 3}], count=[count()], agg#1=[count($2)])
HiveProject(subset=[rel#505:Subset#5.HIVE.[]], pk1=[$0], fk4=[$1],
c1=[$2], CAST=[CAST($1):DATE])
HiveFilter(subset=[rel#503:Subset#4.HIVE.[]], condition=[IS NOT
NULL(CAST($1):DATE)])
HiveTableScan(subset=[rel#501:Subset#3.HIVE.[]], table=[[default,
dim2]], table:alias=[dim2]) {noformat}
Currently MV rewriting isn't capable of rewriting if there is an aggregate on
query which isn't in view
More specific repro
{code:sql}
create materialized view av3 stored as orc as select fk1, fk2, fk3, fk4,
sum(fk3) from fact group by fk1,fk2,fk3,fk4;
explain cbo select sum(fk3), count(*) from fact group by fk4;
{code}
> Aggregate pushdown through join may generate additional rewriting
> opportunities
> -------------------------------------------------------------------------------
>
> Key: HIVE-22262
> URL: https://issues.apache.org/jira/browse/HIVE-22262
> Project: Hive
> Issue Type: Sub-task
> Components: CBO, Materialized views
> Affects Versions: 3.1.2
> Reporter: Steve Carlin
> Assignee: Vineet Garg
> Priority: Major
> Attachments: eager-v2.sql
>
>
> In this case, there is a function used in the query and materialized view,
> but the aggregate is not being pushed down. Script is attached.
> Example query and materialized view:
> create materialized view av1 stored as orc as select fk1, fk2, fk3,
> to_date(fk4), sum(1) from fact group by 1, 2, 3, 4;
> explain cbo select pk1, dim2.fk4, sum(1), count(c1)
> from fact, dim2
> where to_date(fact.fk4) = dim2.fk4
> group by 1, 2
> order by 1, 2;
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
