[jira] [Assigned] (CARBONDATA-2574) MV Datamap - MV is not working if there is aggregate function with group by and without any projections.

[Akash R Nilugal (Jira)]

     [ https://issues.apache.org/jira/browse/CARBONDATA-2574?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

xubo245 reassigned CARBONDATA-2574:
-----------------------------------

    Assignee: xubo245

> MV Datamap - MV is not working if there is aggregate function with group by and without any projections.
> --------------------------------------------------------------------------------------------------------
>
>                 Key: CARBONDATA-2574
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-2574
>             Project: CarbonData
>          Issue Type: Bug
>          Components: data-query
>         Environment: 3 Node Opensource ANT cluster.
>            Reporter: Prasanna Ravichandran
>            Assignee: xubo245
>            Priority: Minor
>              Labels: CARBONDATA., MV, Materialistic_Views
>         Attachments: MV_aggregate_without_projection_and_with_groupby.docx, data.csv
>
>
> User query is not fetching data from the MV datamap, if there is aggregate function with group by and without any projections.
> Test queries:(In Spark-shell)
>  
> scala> carbon.sql("CREATE TABLE originTable (empno int, empname String, designation String, doj Timestamp,workgroupcategory int, workgroupcategoryname String, deptno int, deptname String,projectcode int, projectjoindate Timestamp, projectenddate Timestamp,attendance int,utilization int,salary int) STORED BY 'org.apache.carbondata.format'").show(200,false)
> ++
> ||
> ++
> ++
>  
> scala> carbon.sql("LOAD DATA local inpath 'hdfs://hacluster/user/prasanna/data.csv' INTO TABLE originTable OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '\"','timestampformat'='dd-MM-yyyy')").show(200,false)
> ++
> ||
> ++
> ++
>  
>  
> scala> carbon.sql("create datamap Mv_misscol using 'mv' as select sum(salary) from origintable group by empno").show(200,false)
> ++
> ||
> ++
> ++
>  
>  
> scala> carbon.sql("rebuild datamap Mv_misscol").show(200,false)
> ++
> ||
> ++
> ++
>  
>  
> scala> carbon.sql("explain select sum(salary) from origintable group by empno").show(200,false)
> +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> |plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
> +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> |== CarbonData Profiler ==
> Table Scan on origintable
>  - total blocklets: 1
>  - filter: none
>  - pruned by Main DataMap
>     - skipped blocklets: 0
>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
> |== Physical Plan ==
> *HashAggregate(keys=[empno#3850], functions=[sum(cast(salary#3863 as bigint))])
> +- Exchange hashpartitioning(empno#3850, 200)
>    +- *HashAggregate(keys=[empno#3850], functions=[partial_sum(cast(salary#3863 as bigint))])
>       +- *BatchedScan CarbonDatasourceHadoopRelation [ {color:#d04437}*Database name :prasanna, Table name :origintable,*{color} 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))) ] prasanna.origintable[empno#3850,salary#3863]|
> +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>  
>  
> scala> carbon.sql("select sum(salary) from origintable group by empno").show(200,false)
> +-----------+
> |sum(salary)|
> +-----------+
> |7124       |
> |9054       |
> |5040       |
> |13547      |
> |11254      |
> |13245      |
> |9574       |
> |5040       |
> |11248      |
> |7245       |
> +-----------+
>  
>  
> scala> carbon.sql("show datamap on table origintable").show(200,false)
> +-----------+---------+-------------------------+
> |DataMapName|ClassName|Associated Table         |
> +-----------+---------+-------------------------+
> |Mv_misscol |mv       |prasanna.Mv_misscol_table|
> +-----------+---------+-------------------------+
>  
> It is working fine, if there is a projection, also present with the aggregate function along with the group by. Please see the attached document for more details.

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