[
https://issues.apache.org/jira/browse/HIVE-27727?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Krisztian Kasa updated HIVE-27727:
----------------------------------
Description:
{code}
create table t1 (a int, b decimal(3,2)) stored as orc TBLPROPERTIES
('transactional'='true');
create materialized view mv1 as
select a, sum(b), count(b) from t1 group by a;
explain cbo
select a, avg(b) from t1 group by a;
{code}
MV is not used
{code}
CBO PLAN:
HiveProject(a=[$0], _o__c1=[CAST(/($1, $2)):DECIMAL(7, 6)])
HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[count($1)])
HiveTableScan(table=[[default, t1]], table:alias=[t1])
{code}
If {{avg}} input is not decimal but for example {{int}} the query plan is
rewritten to use the MV
> Materialized view query rewrite fails if query has decimal derived aggregate
> ----------------------------------------------------------------------------
>
> Key: HIVE-27727
> URL: https://issues.apache.org/jira/browse/HIVE-27727
> Project: Hive
> Issue Type: Bug
> Reporter: Krisztian Kasa
> Assignee: Krisztian Kasa
> Priority: Major
> Labels: cbo, materializedviews
>
> {code}
> create table t1 (a int, b decimal(3,2)) stored as orc TBLPROPERTIES
> ('transactional'='true');
> create materialized view mv1 as
> select a, sum(b), count(b) from t1 group by a;
> explain cbo
> select a, avg(b) from t1 group by a;
> {code}
> MV is not used
> {code}
> CBO PLAN:
> HiveProject(a=[$0], _o__c1=[CAST(/($1, $2)):DECIMAL(7, 6)])
> HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[count($1)])
> HiveTableScan(table=[[default, t1]], table:alias=[t1])
> {code}
> If {{avg}} input is not decimal but for example {{int}} the query plan is
> rewritten to use the MV
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
