[HIVE-22262] Aggregate pushdown through join may generate additional rewriting opportunities

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.2
• Component/s: CBO, Materialized views
• Assignee: Vineet Garg
• Reporter: Steve Carlin
• Created: 27/Sep/19 21:20
• Updated: 27/Feb/24 22:23

Description

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;