[jira] [Comment Edited] (CARBONDATA-2533) MV Datamap - MV with Expression in the Aggregation is not fetching data from the MV datamap table.

["Ravindra Pesala (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CARBONDATA-2533?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16536558#comment-16536558 ] 

Ravindra Pesala edited comment on CARBONDATA-2533 at 7/9/18 6:06 AM:
---------------------------------------------------------------------

There is no issue with expression in aggregation function. It is the problem with the way mv is created, group by column must exists in the projection in MV in order to work properly. 

So Please correct the MV as follows and close the issue as invalid.
{code:java}
create datamap MV_expr1 using 'MV' as select empno, sum(case when deptno=11 and (utilization=92) then salary else 0 end) as t from fact_table1 group by empno

{code}


was (Author: ravi.pesala):
There is no issue with expression in aggregation function. It is the problem with the way mv is created, group by column must exists in the projection in MV in order to V work properly,

So Please correct the MV as follows.

{code}

create datamap MV_expr1 using 'MV' as select empno, sum(case when deptno=11 and (utilization=92) then salary else 0 end) as t from fact_table1 group by empno

{code}

> MV Datamap - MV with Expression in the Aggregation is not fetching data from the MV datamap table.
> --------------------------------------------------------------------------------------------------
>
>                 Key: CARBONDATA-2533
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-2533
>             Project: CarbonData
>          Issue Type: Bug
>          Components: data-query
>         Environment: 3 Node Opensource ANT cluster.
>            Reporter: Prasanna Ravichandran
>            Priority: Minor
>              Labels: CarbonData, MV, Materialistic_Views
>         Attachments: data_mv.csv
>
>
> MV with Expression in the Aggregation is not fetching data from the MV datamap table. It is fetching the data from the main table. Please see the below explain query - table name for more details.
> *Test queries from Spark -shell:*
> scala> carbon.sql("create datamap MV_expr1 using 'MV' as select sum(case when deptno=11 and (utilization=92) then salary else 0 end) as t from fact_table1 group by empno").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("rebuild datamap MV_expr1").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("explain select sum(case when deptno=11 and (utilization=92) then salary else 0 end) as t from fact_table1 group by empno").show(200,false)
> +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> |plan |
> +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> |== CarbonData Profiler ==
> Table Scan on fact_table1
>  - total blocklets: 2
>  - filter: none
>  - pruned by Main DataMap
>  - skipped blocklets: 0
>  |
> |== Physical Plan ==
> *HashAggregate(keys=[empno#252], functions=[sum(cast(CASE WHEN ((deptno#258 = 11) && (utilization#264 = 92)) THEN salary#265 ELSE 0 END as bigint))])
> +- Exchange hashpartitioning(empno#252, 200)
>  +- *HashAggregate(keys=[empno#252], functions=[partial_sum(cast(CASE WHEN ((deptno#258 = 11) && (utilization#264 = 92)) THEN salary#265 ELSE 0 END as bigint))])
>  +- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default, *Table name :fact_table1,* Schema :Some(StructType(StructField(empno,IntegerType,true), StructField(empname,StringType,true), StructField(designation,StringType,true), StructField(doj,TimestampType,true), StructField(workgroupcategory,IntegerType,true), StructField(workgroupcategoryname,StringType,true), StructField(deptno,IntegerType,true), StructField(deptname,StringType,true), StructField(projectcode,IntegerType,true), StructField(projectjoindate,TimestampType,true), StructField(projectenddate,TimestampType,true), StructField(attendance,IntegerType,true), StructField(utilization,IntegerType,true), StructField(salary,IntegerType,true))) ] default.fact_table1[empno#252,deptno#258,utilization#264,salary#265]|
> +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>  
>  
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)