[
https://issues.apache.org/jira/browse/HIVE-21634?focusedWorklogId=231629&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-231629
]
ASF GitHub Bot logged work on HIVE-21634:
-----------------------------------------
Author: ASF GitHub Bot
Created on: 23/Apr/19 18:28
Start Date: 23/Apr/19 18:28
Worklog Time Spent: 10m
Work Description: jcamachor commented on pull request #602: HIVE-21634
URL: https://github.com/apache/hive/pull/602#discussion_r277813847
##########
File path: ql/src/test/queries/clientpositive/perf/mv_query67.q
##########
@@ -0,0 +1,154 @@
+set hive.mapred.mode=nonstrict;
+set hive.materializedview.rewriting.time.window=-1;
+
+CREATE MATERIALIZED VIEW `my_materialized_view_n100` AS
+select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,
s_store_id, sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
+from store_sales, date_dim, store, item
+where ss_sold_date_sk=d_date_sk
+ and ss_item_sk=i_item_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1212 and 1212+11
+group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy,
d_moy,s_store_id;
+
+-- start query 1 in stream 0 using template query67.tpl and seed 1819994127
+explain cbo
+select *
+from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rank() over (partition by i_category order by sumsales desc) rk
+ from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
+ from store_sales
+ ,date_dim
+ ,store
+ ,item
+ where ss_sold_date_sk=d_date_sk
+ and ss_item_sk=i_item_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1212 and 1212+11
+ group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy,
d_moy,s_store_id)dw1) dw2
+where rk <= 100
+order by i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rk
+limit 100;
+
+-- end query 1 in stream 0 using template query67.tpl
+
+explain cbo
+select *
+from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rank() over (partition by i_category order by sumsales desc) rk
+ from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sum(sumsales) sumsales
+ from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
+ from store_sales
+ ,date_dim
+ ,store
+ ,item
+ where ss_sold_date_sk=d_date_sk
+ and ss_item_sk=i_item_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1212 and 1212+11
+ group by i_category, i_class, i_brand, i_product_name, d_year,
d_qoy, d_moy,s_store_id
+ ) dw0
+ group by rollup(i_category, i_class, i_brand, i_product_name, d_year,
d_qoy, d_moy,s_store_id))dw1) dw2
+where rk <= 100
+order by i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rk
+limit 100;
+
+explain cbo
+select *
+from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rank() over (partition by i_category order by sumsales desc) rk
+ from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
+ from store_sales
+ ,date_dim
+ ,store
+ ,item
+ where ss_sold_date_sk=d_date_sk
+ and ss_item_sk=i_item_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1212 and 1212+11
+ group by rollup(i_category, i_class, i_brand, i_product_name, d_year,
d_qoy, d_moy,s_store_id))dw1) dw2
+where rk <= 100
+order by i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
Review comment:
I have added it in new commit.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
Issue Time Tracking
-------------------
Worklog Id: (was: 231629)
Time Spent: 0.5h (was: 20m)
> Materialized view rewriting over aggregate operators containing with grouping
> sets
> ----------------------------------------------------------------------------------
>
> Key: HIVE-21634
> URL: https://issues.apache.org/jira/browse/HIVE-21634
> Project: Hive
> Issue Type: Improvement
> Components: Materialized views
> Reporter: Jesus Camacho Rodriguez
> Assignee: Jesus Camacho Rodriguez
> Priority: Major
> Labels: pull-request-available
> Attachments: HIVE-21634.patch
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> A possible approach to support rewriting queries with an aggregate with
> grouping sets is implementing a rule that splits the aggregate in the query
> into an aggregate without grouping sets (bottom) and an aggregate with
> grouping sets (top). Then the materialized view rewriting rule will trigger
> on the former.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
