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

[Akash R Nilugal (Jira)]

Prasanna Ravichandran created CARBONDATA-2574:
-------------------------------------------------

             Summary: 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
         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)