[
https://issues.apache.org/jira/browse/HIVE-22262?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17065043#comment-17065043
]
Vineet Garg commented on HIVE-22262:
------------------------------------
{code:sql}
explain cbo select pk1, dim2.fk4, sum(1), count(c1)
from fact, dim2
where to_date(fact.fk4) = dim2.fk4
group by pk1,dim2.fk4
order by pk1,dim2.fk4;
{code}
This doesn't fail anymore but we aren't able to rewrite it. I tried adding
{{HiveAggregateJoinTransposeRule}} before rewriting but it still doesn't kick
in rewriting.
Plan after the introduction of rule
{code}
HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3])
HiveAggregate(group=[{0, 1}], agg#0=[sum($2)], agg#1=[count($3)])
HiveProject($f0=[$1], $f1=[$2], $f2=[1], $f3=[$3])
HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none],
cost=[not available])
HiveProject(TO_DATE=[TO_DATE($3)])
HiveFilter(condition=[IS NOT NULL(TO_DATE($3))])
HiveTableScan(table=[[default, fact]], table:alias=[fact])
HiveProject(pk1=[$0], fk4=[$1], c1=[$2], CAST=[CAST($1):DATE])
HiveFilter(condition=[IS NOT NULL(CAST($1):DATE)])
HiveTableScan(table=[[default, dim2]], table:alias=[dim2])
{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
> 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)
