[jira] [Commented] (CARBONDATA-2528) MV Datamap - When the MV is created with the order by, then when we execute the corresponding query defined in MV with order by, then the data is not accessed from the MV.

[Akash R Nilugal (Jira)]

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

Prasanna Ravichandran commented on CARBONDATA-2528:
---------------------------------------------------

Now the data is fetching from the MV datamap for the order by queries. Working fine.

 explain select attendance,sum(salary)+sum(utilization) as total from originTable group by attendance order by attendance DESC;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| == CarbonData Profiler ==
Table Scan on mv_desc_attendance_table
 - total blocklets: 4
 - filter: none
 - pruned by Main DataMap
 - skipped blocklets: 0
 |
| == Physical Plan ==
*Sort [attendance#12952 DESC NULLS LAST], true, 0
+- Exchange rangepartitioning(attendance#12952 DESC NULLS LAST, 200)
 +- *Project [origintable_attendance#12897 AS attendance#12952, total#12898L]
 +- *FileScan carbondata b011.*mv_desc_attendance_table*[origintable_attendance#12897,total#12898L] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+–+

explain select empno,sum(salary)+sum(utilization) as total from originTable group by empno order by empno;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| == CarbonData Profiler ==
Table Scan on mv_order_table
 - total blocklets: 6
 - filter: none
 - pruned by Main DataMap
 - skipped blocklets: 0
 |
| == Physical Plan ==
*Sort [empno#12822 ASC NULLS FIRST], true, 0
+- Exchange rangepartitioning(empno#12822 ASC NULLS FIRST, 200)
 +- *Project [origintable_empno#10724 AS empno#12822, total#10725L]
 +- *FileScan carbondata b011.mv_order_table[origintable_empno#10724,total#10725L] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+

 

 

 

 

> MV Datamap - When the MV is created with the order by, then when we execute the corresponding query defined in MV with order by, then the data is not accessed from the MV.
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: CARBONDATA-2528
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-2528
>             Project: CarbonData
>          Issue Type: Bug
>          Components: data-query
>         Environment: 3 node Opensource ANT cluster. (Opensource Hadoop 2.7.2+ Opensource Spark 2.2.1+ Opensource Carbondata 1.3.1)
>            Reporter: Prasanna Ravichandran
>            Assignee: Ravindra Pesala
>            Priority: Minor
>              Labels: CarbonData, MV, Materialistic_Views
>             Fix For: 1.5.0, 1.4.1
>
>         Attachments: MV_orderby.docx, data.csv
>
>          Time Spent: 6h
>  Remaining Estimate: 0h
>
> When the MV is created with the order by condition, then when we execute the corresponding query defined in MV along with order by, then the data is not accessed from the MV. The data is being accessed from the maintable only. 
> Test queries:
> create datamap MV_order using 'mv' as select empno,sum(salary)+sum(utilization) as total from originTable group by empno order by empno;
> create datamap MV_desc_order using 'mv' as select empno,sum(salary)+sum(utilization) as total from originTable group by empno order by empno DESC;
> rebuild datamap MV_order;
> rebuild datamap MV_desc_order;
> explain select empno,sum(salary)+sum(utilization) as total from originTable group by empno order by empno;
> explain select empno,sum(salary)+sum(utilization) as total from originTable group by empno order by empno DESC;
> Expected result: MV with order by condition should access data from the MV table only.
>  
> Please see the attached document for more details.

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