[
https://issues.apache.org/jira/browse/CARBONDATA-2574?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16536878#comment-16536878
]
Ravindra Pesala commented on CARBONDATA-2574:
---------------------------------------------
It is duplicate to Jira 2568 , Please close it.
> 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: Major
> 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)
