You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by px...@apache.org on 2017/01/19 02:27:31 UTC
[2/2] hive git commit: HIVE-15663: Add more interval tests to
HivePerfCliDriver (Pengcheng Xiong)
HIVE-15663: Add more interval tests to HivePerfCliDriver (Pengcheng Xiong)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/42ead344
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/42ead344
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/42ead344
Branch: refs/heads/master
Commit: 42ead344569cb37589bc680bfa84ccbdc705ed63
Parents: cc3ce16
Author: Pengcheng Xiong <px...@apache.org>
Authored: Wed Jan 18 18:27:08 2017 -0800
Committer: Pengcheng Xiong <px...@apache.org>
Committed: Wed Jan 18 18:27:08 2017 -0800
----------------------------------------------------------------------
.../test/queries/clientpositive/perf/query12.q | 12 +-
.../test/queries/clientpositive/perf/query21.q | 15 +-
.../test/queries/clientpositive/perf/query37.q | 16 +
.../test/queries/clientpositive/perf/query40.q | 28 +-
.../test/queries/clientpositive/perf/query5.q | 127 +++++
.../test/queries/clientpositive/perf/query72.q | 31 +-
.../test/queries/clientpositive/perf/query80.q | 96 +++-
.../test/queries/clientpositive/perf/query82.q | 17 +-
.../test/queries/clientpositive/perf/query98.q | 7 +-
.../results/clientpositive/perf/query12.q.out | 26 +-
.../results/clientpositive/perf/query21.q.out | 30 +-
.../results/clientpositive/perf/query37.q.out | 103 ++++
.../results/clientpositive/perf/query40.q.out | 56 ++-
.../results/clientpositive/perf/query5.q.out | 489 +++++++++++++++++++
.../results/clientpositive/perf/query72.q.out | 60 ++-
.../results/clientpositive/perf/query80.q.out | 196 +++++++-
.../results/clientpositive/perf/query82.q.out | 34 +-
.../results/clientpositive/perf/query98.q.out | 16 +-
18 files changed, 1315 insertions(+), 44 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query12.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query12.q b/ql/src/test/queries/clientpositive/perf/query12.q
index 034fa7b..7ef6cb8 100644
--- a/ql/src/test/queries/clientpositive/perf/query12.q
+++ b/ql/src/test/queries/clientpositive/perf/query12.q
@@ -1,3 +1,13 @@
set hive.cbo.enable=false;
-explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio from web_sales ,item ,date_dim where web_sales.ws_item_sk = item.i_item_sk and item.i_category in ('Jewelry', 'Sports', 'Books') and web_sales.ws_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100;
+explain
+select
+i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio
+from web_sales ,item ,date_dim
+where
+web_sales.ws_item_sk = item.i_item_sk
+and item.i_category in ('Jewelry', 'Sports', 'Books')
+and web_sales.ws_sold_date_sk = date_dim.d_date_sk
+and date_dim.d_date between cast('2001-01-12' as date)
+ and (cast('2001-01-12' as date) + 30 days)
+group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100;
set hive.cbo.enable=true;
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query21.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query21.q b/ql/src/test/queries/clientpositive/perf/query21.q
index a7b157a..4c68df9 100644
--- a/ql/src/test/queries/clientpositive/perf/query21.q
+++ b/ql/src/test/queries/clientpositive/perf/query21.q
@@ -1,8 +1,9 @@
-explain select *
+explain
+select *
from(select w_warehouse_name
,i_item_id
,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date))
- then inv_quantity_on_hand
+ then inv_quantity_on_hand
else 0 end) as inv_before
,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date))
then inv_quantity_on_hand
@@ -12,10 +13,11 @@ explain select *
,item
,date_dim
where i_current_price between 0.99 and 1.49
- and item.i_item_sk = inventory.inv_item_sk
- and inventory.inv_warehouse_sk = warehouse.w_warehouse_sk
- and inventory.inv_date_sk = date_dim.d_date_sk
- and d_date between '1998-03-09' and '1998-05-07'
+ and i_item_sk = inv_item_sk
+ and inv_warehouse_sk = w_warehouse_sk
+ and inv_date_sk = d_date_sk
+ and d_date between (cast ('1998-04-08' as date) - 30 days)
+ and (cast ('1998-04-08' as date) + 30 days)
group by w_warehouse_name, i_item_id) x
where (case when inv_before > 0
then inv_after / inv_before
@@ -24,3 +26,4 @@ explain select *
order by w_warehouse_name
,i_item_id
limit 100;
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query37.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query37.q b/ql/src/test/queries/clientpositive/perf/query37.q
new file mode 100644
index 0000000..c71ec56
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query37.q
@@ -0,0 +1,16 @@
+explain
+select i_item_id
+ ,i_item_desc
+ ,i_current_price
+ from item, inventory, date_dim, catalog_sales
+ where i_current_price between 22 and 22 + 30
+ and inv_item_sk = i_item_sk
+ and d_date_sk=inv_date_sk
+ and d_date between cast('2001-06-02' as date) and (cast('2001-06-02' as date) + 60 days)
+ and i_manufact_id in (678,964,918,849)
+ and inv_quantity_on_hand between 100 and 500
+ and cs_item_sk = i_item_sk
+ group by i_item_id,i_item_desc,i_current_price
+ order by i_item_id
+ limit 100;
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query40.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query40.q b/ql/src/test/queries/clientpositive/perf/query40.q
index 2a6c273..155fa08 100644
--- a/ql/src/test/queries/clientpositive/perf/query40.q
+++ b/ql/src/test/queries/clientpositive/perf/query40.q
@@ -1 +1,27 @@
-explain select w_state ,i_item_id ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date)) then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date)) then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after from catalog_sales left outer join catalog_returns on (catalog_sales.cs_order_number = catalog_returns.cr_order_number and catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) ,warehouse ,item ,date_dim where i_current_price between 0.99 and 1.49 and item.i_item_sk = catalog_sales.cs_item_sk and catalog_sales.cs_warehouse_sk = warehouse.w_warehouse_sk and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '1998-03-09' and '1998-05-08' group by w_state,i_item_id order by w_state,i_item_id limit 100;
+explain
+select
+ w_state
+ ,i_item_id
+ ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date))
+ then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before
+ ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date))
+ then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after
+ from
+ catalog_sales left outer join catalog_returns on
+ (cs_order_number = cr_order_number
+ and cs_item_sk = cr_item_sk)
+ ,warehouse
+ ,item
+ ,date_dim
+ where
+ i_current_price between 0.99 and 1.49
+ and i_item_sk = cs_item_sk
+ and cs_warehouse_sk = w_warehouse_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_date between (cast ('1998-04-08' as date) - 30 days)
+ and (cast ('1998-04-08' as date) + 30 days)
+ group by
+ w_state,i_item_id
+ order by w_state,i_item_id
+limit 100;
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query5.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query5.q b/ql/src/test/queries/clientpositive/perf/query5.q
new file mode 100644
index 0000000..a877c4c
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query5.q
@@ -0,0 +1,127 @@
+explain
+with ssr as
+ (select s_store_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select ss_store_sk as store_sk,
+ ss_sold_date_sk as date_sk,
+ ss_ext_sales_price as sales_price,
+ ss_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from store_sales
+ union all
+ select sr_store_sk as store_sk,
+ sr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ sr_return_amt as return_amt,
+ sr_net_loss as net_loss
+ from store_returns
+ ) salesreturns,
+ date_dim,
+ store
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and store_sk = s_store_sk
+ group by s_store_id)
+ ,
+ csr as
+ (select cp_catalog_page_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select cs_catalog_page_sk as page_sk,
+ cs_sold_date_sk as date_sk,
+ cs_ext_sales_price as sales_price,
+ cs_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from catalog_sales
+ union all
+ select cr_catalog_page_sk as page_sk,
+ cr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ cr_return_amount as return_amt,
+ cr_net_loss as net_loss
+ from catalog_returns
+ ) salesreturns,
+ date_dim,
+ catalog_page
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and page_sk = cp_catalog_page_sk
+ group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select web_site_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select ws_web_site_sk as wsr_web_site_sk,
+ ws_sold_date_sk as date_sk,
+ ws_ext_sales_price as sales_price,
+ ws_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from web_sales
+ union all
+ select ws_web_site_sk as wsr_web_site_sk,
+ wr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ wr_return_amt as return_amt,
+ wr_net_loss as net_loss
+ from web_returns left outer join web_sales on
+ ( wr_item_sk = ws_item_sk
+ and wr_order_number = ws_order_number)
+ ) salesreturns,
+ date_dim,
+ web_site
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and wsr_web_site_sk = web_site_sk
+ group by web_site_id)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(returns) as returns
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , 'store' || s_store_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from ssr
+ union all
+ select 'catalog channel' as channel
+ , 'catalog_page' || cp_catalog_page_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from csr
+ union all
+ select 'web channel' as channel
+ , 'web_site' || web_site_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100;
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query72.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query72.q b/ql/src/test/queries/clientpositive/perf/query72.q
index ee4c3d0..6c076d6 100644
--- a/ql/src/test/queries/clientpositive/perf/query72.q
+++ b/ql/src/test/queries/clientpositive/perf/query72.q
@@ -1 +1,30 @@
-explain select i_item_desc ,w_warehouse_name ,d1.d_week_seq ,count(case when p_promo_sk is null then 1 else 0 end) no_promo ,count(case when p_promo_sk is not null then 1 else 0 end) promo ,count(*) total_cnt from catalog_sales join inventory on (catalog_sales.cs_item_sk = inventory.inv_item_sk) join warehouse on (warehouse.w_warehouse_sk=inventory.inv_warehouse_sk) join item on (item.i_item_sk = catalog_sales.cs_item_sk) join customer_demographics on (catalog_sales.cs_bill_cdemo_sk = customer_demographics.cd_demo_sk) join household_demographics on (catalog_sales.cs_bill_hdemo_sk = household_demographics.hd_demo_sk) join date_dim d1 on (catalog_sales.cs_sold_date_sk = d1.d_date_sk) join date_dim d2 on (inventory.inv_date_sk = d2.d_date_sk) join date_dim d3 on (catalog_sales.cs_ship_date_sk = d3.d_date_sk) left outer join promotion on (catalog_sales.cs_promo_sk=promotion.p_promo_sk) left outer join catalog_returns on (catalog_returns.cr_item_sk = catalog_sales.cs_item_sk and catalog_
returns.cr_order_number = catalog_sales.cs_order_number) where d1.d_week_seq = d2.d_week_seq and inv_quantity_on_hand < cs_quantity and d3.d_date > d1.d_date + 5 and hd_buy_potential = '1001-5000' and d1.d_year = 2001 and hd_buy_potential = '1001-5000' and cd_marital_status = 'M' and d1.d_year = 2001 group by i_item_desc,w_warehouse_name,d1.d_week_seq order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq limit 100;
+explain
+select i_item_desc
+ ,w_warehouse_name
+ ,d1.d_week_seq
+ ,count(case when p_promo_sk is null then 1 else 0 end) no_promo
+ ,count(case when p_promo_sk is not null then 1 else 0 end) promo
+ ,count(*) total_cnt
+from catalog_sales
+join inventory on (cs_item_sk = inv_item_sk)
+join warehouse on (w_warehouse_sk=inv_warehouse_sk)
+join item on (i_item_sk = cs_item_sk)
+join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)
+join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)
+join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)
+join date_dim d2 on (inv_date_sk = d2.d_date_sk)
+join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)
+left outer join promotion on (cs_promo_sk=p_promo_sk)
+left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number)
+where d1.d_week_seq = d2.d_week_seq
+ and inv_quantity_on_hand < cs_quantity
+ and d3.d_date > d1.d_date + 5
+ and hd_buy_potential = '1001-5000'
+ and d1.d_year = 2001
+ and hd_buy_potential = '1001-5000'
+ and cd_marital_status = 'M'
+ and d1.d_year = 2001
+group by i_item_desc,w_warehouse_name,d1.d_week_seq
+order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq
+limit 100;
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query80.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query80.q b/ql/src/test/queries/clientpositive/perf/query80.q
index 5ded3c5..53f9a3c 100644
--- a/ql/src/test/queries/clientpositive/perf/query80.q
+++ b/ql/src/test/queries/clientpositive/perf/query80.q
@@ -1 +1,95 @@
-explain with ssr as (select s_store_id as store_id, sum(ss_ext_sales_price) as sales, sum(coalesce(sr_return_amt, 0)) as returns, sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit from store_sales left outer join store_returns on (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number), date_dim, store, item, promotion where ss_sold_date_sk = d_date_sk and d_date between cast('1998-08-04' as date) and (cast('1998-09-04' as date)) and ss_store_sk = s_store_sk and ss_item_sk = i_item_sk and i_current_price > 50 and ss_promo_sk = p_promo_sk and p_channel_tv = 'N' group by s_store_id) , csr as (select cp_catalog_page_id as catalog_page_id, sum(cs_ext_sales_price) as sales, sum(coalesce(cr_return_amount, 0)) as returns, sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit from catalog_sales left outer join catalog_returns on (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number), date_dim, catalog_page, item, promotion where cs_sold_date_sk = d_date_sk and d_da
te between cast('1998-08-04' as date) and (cast('1998-09-04' as date)) and cs_catalog_page_sk = cp_catalog_page_sk and cs_item_sk = i_item_sk and i_current_price > 50 and cs_promo_sk = p_promo_sk and p_channel_tv = 'N' group by cp_catalog_page_id) , wsr as (select web_site_id, sum(ws_ext_sales_price) as sales, sum(coalesce(wr_return_amt, 0)) as returns, sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit from web_sales left outer join web_returns on (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number), date_dim, web_site, item, promotion where ws_sold_date_sk = d_date_sk and d_date between cast('1998-08-04' as date) and (cast('1998-09-04' as date)) and ws_web_site_sk = web_site_sk and ws_item_sk = i_item_sk and i_current_price > 50 and ws_promo_sk = p_promo_sk and p_channel_tv = 'N' group by web_site_id) select channel , id , sum(sales) as sales , sum(returns) as returns , sum(profit) as profit from (select 'store channel' as channel , concat('store', store_id) as id
, sales , returns , profit from ssr union all select 'catalog channel' as channel , concat('catalog_page', catalog_page_id) as id , sales , returns , profit from csr union all select 'web channel' as channel , concat('web_site', web_site_id) as id , sales , returns , profit from wsr ) x group by channel, id with rollup order by channel ,id limit 100;
+explain
+with ssr as
+ (select s_store_id as store_id,
+ sum(ss_ext_sales_price) as sales,
+ sum(coalesce(sr_return_amt, 0)) as returns,
+ sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit
+ from store_sales left outer join store_returns on
+ (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number),
+ date_dim,
+ store,
+ item,
+ promotion
+ where ss_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and ss_store_sk = s_store_sk
+ and ss_item_sk = i_item_sk
+ and i_current_price > 50
+ and ss_promo_sk = p_promo_sk
+ and p_channel_tv = 'N'
+ group by s_store_id)
+ ,
+ csr as
+ (select cp_catalog_page_id as catalog_page_id,
+ sum(cs_ext_sales_price) as sales,
+ sum(coalesce(cr_return_amount, 0)) as returns,
+ sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit
+ from catalog_sales left outer join catalog_returns on
+ (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number),
+ date_dim,
+ catalog_page,
+ item,
+ promotion
+ where cs_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and cs_catalog_page_sk = cp_catalog_page_sk
+ and cs_item_sk = i_item_sk
+ and i_current_price > 50
+ and cs_promo_sk = p_promo_sk
+ and p_channel_tv = 'N'
+group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select web_site_id,
+ sum(ws_ext_sales_price) as sales,
+ sum(coalesce(wr_return_amt, 0)) as returns,
+ sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit
+ from web_sales left outer join web_returns on
+ (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number),
+ date_dim,
+ web_site,
+ item,
+ promotion
+ where ws_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and ws_web_site_sk = web_site_sk
+ and ws_item_sk = i_item_sk
+ and i_current_price > 50
+ and ws_promo_sk = p_promo_sk
+ and p_channel_tv = 'N'
+group by web_site_id)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(returns) as returns
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , 'store' || store_id as id
+ , sales
+ , returns
+ , profit
+ from ssr
+ union all
+ select 'catalog channel' as channel
+ , 'catalog_page' || catalog_page_id as id
+ , sales
+ , returns
+ , profit
+ from csr
+ union all
+ select 'web channel' as channel
+ , 'web_site' || web_site_id as id
+ , sales
+ , returns
+ , profit
+ from wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100;
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query82.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query82.q b/ql/src/test/queries/clientpositive/perf/query82.q
index aec30ea..cf882b8 100644
--- a/ql/src/test/queries/clientpositive/perf/query82.q
+++ b/ql/src/test/queries/clientpositive/perf/query82.q
@@ -1 +1,16 @@
-explain select i_item_id ,i_item_desc ,i_current_price from item, inventory, date_dim, store_sales where i_current_price between 30 and 30+30 and inv_item_sk = i_item_sk and d_date_sk=inv_date_sk and d_date between '2002-05-30' and '2002-07-30' and i_manufact_id in (437,129,727,663) and inv_quantity_on_hand between 100 and 500 and ss_item_sk = i_item_sk group by i_item_id,i_item_desc,i_current_price order by i_item_id limit 100;
+explain
+select i_item_id
+ ,i_item_desc
+ ,i_current_price
+ from item, inventory, date_dim, store_sales
+ where i_current_price between 30 and 30+30
+ and inv_item_sk = i_item_sk
+ and d_date_sk=inv_date_sk
+ and d_date between cast('2002-05-30' as date) and (cast('2002-05-30' as date) + 60 days)
+ and i_manufact_id in (437,129,727,663)
+ and inv_quantity_on_hand between 100 and 500
+ and ss_item_sk = i_item_sk
+ group by i_item_id,i_item_desc,i_current_price
+ order by i_item_id
+ limit 100;
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/queries/clientpositive/perf/query98.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query98.q b/ql/src/test/queries/clientpositive/perf/query98.q
index 930b7ef..ec4b553 100644
--- a/ql/src/test/queries/clientpositive/perf/query98.q
+++ b/ql/src/test/queries/clientpositive/perf/query98.q
@@ -1,2 +1,7 @@
set hive.mapred.mode=nonstrict;
-explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ss_ext_sales_price) as itemrevenue ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over (partition by i_class) as revenueratio from store_sales ,item ,date_dim where store_sales.ss_item_sk = item.i_item_sk and i_category in ('Jewelry', 'Sports', 'Books') and store_sales.ss_sold_date_sk = date_dim.d_date_sk and d_date between cast('2001-01-12' as date) and (cast('2001-02-11' as date)) group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio;
+explain
+select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ss_ext_sales_price) as itemrevenue ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over (partition by i_class) as revenueratio from store_sales ,item ,date_dim
+where store_sales.ss_item_sk = item.i_item_sk and i_category in ('Jewelry', 'Sports', 'Books') and store_sales.ss_sold_date_sk = date_dim.d_date_sk and
+d_date between cast('2001-01-12' as date)
+ and (cast('2001-01-12' as date) + 30 days)
+group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio;
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/results/clientpositive/perf/query12.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query12.q.out b/ql/src/test/results/clientpositive/perf/query12.q.out
index 3f25f56..afe2cd4 100644
--- a/ql/src/test/results/clientpositive/perf/query12.q.out
+++ b/ql/src/test/results/clientpositive/perf/query12.q.out
@@ -1,6 +1,26 @@
-PREHOOK: query: explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio from web_sales ,item ,date_dim where web_sales.ws_item_sk = item.i_item_sk and item.i_category in ('Jewelry', 'Sports', 'Books') and web_sales.ws_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100
+PREHOOK: query: explain
+select
+i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio
+from web_sales ,item ,date_dim
+where
+web_sales.ws_item_sk = item.i_item_sk
+and item.i_category in ('Jewelry', 'Sports', 'Books')
+and web_sales.ws_sold_date_sk = date_dim.d_date_sk
+and date_dim.d_date between cast('2001-01-12' as date)
+ and (cast('2001-01-12' as date) + 30 days)
+group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio from web_sales ,item ,date_dim where web_sales.ws_item_sk = item.i_item_sk and item.i_category in ('Jewelry', 'Sports', 'Books') and web_sales.ws_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100
+POSTHOOK: query: explain
+select
+i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio
+from web_sales ,item ,date_dim
+where
+web_sales.ws_item_sk = item.i_item_sk
+and item.i_category in ('Jewelry', 'Sports', 'Books')
+and web_sales.ws_sold_date_sk = date_dim.d_date_sk
+and date_dim.d_date between cast('2001-01-12' as date)
+ and (cast('2001-01-12' as date) + 30 days)
+group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100
POSTHOOK: type: QUERY
Vertex dependency in root stage
Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE)
@@ -47,7 +67,7 @@ Stage-0
SHUFFLE [RS_11]
PartitionCols:d_date_sk
Filter Operator [FIL_35] (rows=8116 width=1119)
- predicate:(d_date_sk is not null and d_date BETWEEN '2001-01-12' AND '2001-02-11')
+ predicate:(d_date_sk is not null and d_date BETWEEN 2001-01-12 AND 2001-02-11 00:00:00.0)
TableScan [TS_2] (rows=73049 width=1119)
default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
<-Reducer 2 [SIMPLE_EDGE]
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/results/clientpositive/perf/query21.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query21.q.out b/ql/src/test/results/clientpositive/perf/query21.q.out
index 0d3ea9d..67a49e2 100644
--- a/ql/src/test/results/clientpositive/perf/query21.q.out
+++ b/ql/src/test/results/clientpositive/perf/query21.q.out
@@ -1,8 +1,9 @@
-PREHOOK: query: explain select *
+PREHOOK: query: explain
+select *
from(select w_warehouse_name
,i_item_id
,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date))
- then inv_quantity_on_hand
+ then inv_quantity_on_hand
else 0 end) as inv_before
,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date))
then inv_quantity_on_hand
@@ -12,10 +13,11 @@ PREHOOK: query: explain select *
,item
,date_dim
where i_current_price between 0.99 and 1.49
- and item.i_item_sk = inventory.inv_item_sk
- and inventory.inv_warehouse_sk = warehouse.w_warehouse_sk
- and inventory.inv_date_sk = date_dim.d_date_sk
- and d_date between '1998-03-09' and '1998-05-07'
+ and i_item_sk = inv_item_sk
+ and inv_warehouse_sk = w_warehouse_sk
+ and inv_date_sk = d_date_sk
+ and d_date between (cast ('1998-04-08' as date) - 30 days)
+ and (cast ('1998-04-08' as date) + 30 days)
group by w_warehouse_name, i_item_id) x
where (case when inv_before > 0
then inv_after / inv_before
@@ -25,11 +27,12 @@ PREHOOK: query: explain select *
,i_item_id
limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select *
+POSTHOOK: query: explain
+select *
from(select w_warehouse_name
,i_item_id
,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date))
- then inv_quantity_on_hand
+ then inv_quantity_on_hand
else 0 end) as inv_before
,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date))
then inv_quantity_on_hand
@@ -39,10 +42,11 @@ POSTHOOK: query: explain select *
,item
,date_dim
where i_current_price between 0.99 and 1.49
- and item.i_item_sk = inventory.inv_item_sk
- and inventory.inv_warehouse_sk = warehouse.w_warehouse_sk
- and inventory.inv_date_sk = date_dim.d_date_sk
- and d_date between '1998-03-09' and '1998-05-07'
+ and i_item_sk = inv_item_sk
+ and inv_warehouse_sk = w_warehouse_sk
+ and inv_date_sk = d_date_sk
+ and d_date between (cast ('1998-04-08' as date) - 30 days)
+ and (cast ('1998-04-08' as date) + 30 days)
group by w_warehouse_name, i_item_id) x
where (case when inv_before > 0
then inv_after / inv_before
@@ -129,7 +133,7 @@ Stage-0
Select Operator [SEL_5] (rows=8116 width=1119)
Output:["_col0","_col1"]
Filter Operator [FIL_42] (rows=8116 width=1119)
- predicate:(d_date BETWEEN '1998-03-09' AND '1998-05-07' and d_date_sk is not null)
+ predicate:(d_date BETWEEN 1998-03-08 23:00:00.0 AND 1998-05-08 00:00:00.0 and d_date_sk is not null)
TableScan [TS_3] (rows=73049 width=1119)
default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/results/clientpositive/perf/query37.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query37.q.out b/ql/src/test/results/clientpositive/perf/query37.q.out
new file mode 100644
index 0000000..1b61198
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query37.q.out
@@ -0,0 +1,103 @@
+PREHOOK: query: explain
+select i_item_id
+ ,i_item_desc
+ ,i_current_price
+ from item, inventory, date_dim, catalog_sales
+ where i_current_price between 22 and 22 + 30
+ and inv_item_sk = i_item_sk
+ and d_date_sk=inv_date_sk
+ and d_date between cast('2001-06-02' as date) and (cast('2001-06-02' as date) + 60 days)
+ and i_manufact_id in (678,964,918,849)
+ and inv_quantity_on_hand between 100 and 500
+ and cs_item_sk = i_item_sk
+ group by i_item_id,i_item_desc,i_current_price
+ order by i_item_id
+ limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+select i_item_id
+ ,i_item_desc
+ ,i_current_price
+ from item, inventory, date_dim, catalog_sales
+ where i_current_price between 22 and 22 + 30
+ and inv_item_sk = i_item_sk
+ and d_date_sk=inv_date_sk
+ and d_date between cast('2001-06-02' as date) and (cast('2001-06-02' as date) + 60 days)
+ and i_manufact_id in (678,964,918,849)
+ and inv_quantity_on_hand between 100 and 500
+ and cs_item_sk = i_item_sk
+ group by i_item_id,i_item_desc,i_current_price
+ order by i_item_id
+ limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 5 (SIMPLE_EDGE), Reducer 7 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 7 <- Map 6 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 4
+ File Output Operator [FS_28]
+ Limit [LIM_27] (rows=100 width=135)
+ Number of rows:100
+ Select Operator [SEL_26] (rows=316788826 width=135)
+ Output:["_col0","_col1","_col2"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_25]
+ Group By Operator [GBY_23] (rows=316788826 width=135)
+ Output:["_col0","_col1","_col2"],keys:KEY._col0, KEY._col1, KEY._col2
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_22]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_21] (rows=633577652 width=135)
+ Output:["_col0","_col1","_col2"],keys:_col2, _col3, _col4
+ Merge Join Operator [MERGEJOIN_44] (rows=633577652 width=135)
+ Conds:RS_16._col0=RS_17._col0(Inner),RS_17._col0=RS_18._col1(Inner),Output:["_col2","_col3","_col4"]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_16]
+ PartitionCols:_col0
+ Select Operator [SEL_2] (rows=287989836 width=135)
+ Output:["_col0"]
+ Filter Operator [FIL_39] (rows=287989836 width=135)
+ predicate:cs_item_sk is not null
+ TableScan [TS_0] (rows=287989836 width=135)
+ default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_item_sk"]
+ <-Map 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col0
+ Select Operator [SEL_5] (rows=25666 width=1436)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_40] (rows=25666 width=1436)
+ predicate:((i_manufact_id) IN (678, 964, 918, 849) and i_current_price BETWEEN 22 AND 52 and i_item_sk is not null)
+ TableScan [TS_3] (rows=462000 width=1436)
+ default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id","i_item_desc","i_current_price","i_manufact_id"]
+ <-Reducer 7 [SIMPLE_EDGE]
+ SHUFFLE [RS_18]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_43] (rows=4593600 width=15)
+ Conds:RS_12._col0=RS_13._col0(Inner),Output:["_col1"]
+ <-Map 6 [SIMPLE_EDGE]
+ SHUFFLE [RS_12]
+ PartitionCols:_col0
+ Select Operator [SEL_8] (rows=4176000 width=15)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_41] (rows=4176000 width=15)
+ predicate:(inv_quantity_on_hand BETWEEN 100 AND 500 and inv_item_sk is not null and inv_date_sk is not null)
+ TableScan [TS_6] (rows=37584000 width=15)
+ default@inventory,inventory,Tbl:COMPLETE,Col:NONE,Output:["inv_date_sk","inv_item_sk","inv_quantity_on_hand"]
+ <-Map 8 [SIMPLE_EDGE]
+ SHUFFLE [RS_13]
+ PartitionCols:_col0
+ Select Operator [SEL_11] (rows=8116 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_42] (rows=8116 width=1119)
+ predicate:(d_date BETWEEN 2001-06-02 AND 2001-08-01 00:00:00.0 and d_date_sk is not null)
+ TableScan [TS_9] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/results/clientpositive/perf/query40.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query40.q.out b/ql/src/test/results/clientpositive/perf/query40.q.out
index a98831d..a45fdaf 100644
--- a/ql/src/test/results/clientpositive/perf/query40.q.out
+++ b/ql/src/test/results/clientpositive/perf/query40.q.out
@@ -1,6 +1,56 @@
-PREHOOK: query: explain select w_state ,i_item_id ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date)) then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date)) then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after from catalog_sales left outer join catalog_returns on (catalog_sales.cs_order_number = catalog_returns.cr_order_number and catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) ,warehouse ,item ,date_dim where i_current_price between 0.99 and 1.49 and item.i_item_sk = catalog_sales.cs_item_sk and catalog_sales.cs_warehouse_sk = warehouse.w_warehouse_sk and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '1998-03-09' and '1998-05-08' group by w_state,i_item_id order by w_state,i_item_id limit 100
+PREHOOK: query: explain
+select
+ w_state
+ ,i_item_id
+ ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date))
+ then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before
+ ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date))
+ then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after
+ from
+ catalog_sales left outer join catalog_returns on
+ (cs_order_number = cr_order_number
+ and cs_item_sk = cr_item_sk)
+ ,warehouse
+ ,item
+ ,date_dim
+ where
+ i_current_price between 0.99 and 1.49
+ and i_item_sk = cs_item_sk
+ and cs_warehouse_sk = w_warehouse_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_date between (cast ('1998-04-08' as date) - 30 days)
+ and (cast ('1998-04-08' as date) + 30 days)
+ group by
+ w_state,i_item_id
+ order by w_state,i_item_id
+limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select w_state ,i_item_id ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date)) then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date)) then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after from catalog_sales left outer join catalog_returns on (catalog_sales.cs_order_number = catalog_returns.cr_order_number and catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) ,warehouse ,item ,date_dim where i_current_price between 0.99 and 1.49 and item.i_item_sk = catalog_sales.cs_item_sk and catalog_sales.cs_warehouse_sk = warehouse.w_warehouse_sk and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '1998-03-09' and '1998-05-08' group by w_state,i_item_id order by w_state,i_item_id limit 100
+POSTHOOK: query: explain
+select
+ w_state
+ ,i_item_id
+ ,sum(case when (cast(d_date as date) < cast ('1998-04-08' as date))
+ then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before
+ ,sum(case when (cast(d_date as date) >= cast ('1998-04-08' as date))
+ then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after
+ from
+ catalog_sales left outer join catalog_returns on
+ (cs_order_number = cr_order_number
+ and cs_item_sk = cr_item_sk)
+ ,warehouse
+ ,item
+ ,date_dim
+ where
+ i_current_price between 0.99 and 1.49
+ and i_item_sk = cs_item_sk
+ and cs_warehouse_sk = w_warehouse_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_date between (cast ('1998-04-08' as date) - 30 days)
+ and (cast ('1998-04-08' as date) + 30 days)
+ group by
+ w_state,i_item_id
+ order by w_state,i_item_id
+limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.
@@ -69,7 +119,7 @@ Stage-0
Select Operator [SEL_8] (rows=8116 width=1119)
Output:["_col0","_col1"]
Filter Operator [FIL_52] (rows=8116 width=1119)
- predicate:(d_date BETWEEN '1998-03-09' AND '1998-05-08' and d_date_sk is not null)
+ predicate:(d_date BETWEEN 1998-03-08 23:00:00.0 AND 1998-05-08 00:00:00.0 and d_date_sk is not null)
TableScan [TS_6] (rows=73049 width=1119)
default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
<-Reducer 2 [SIMPLE_EDGE]
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/results/clientpositive/perf/query5.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query5.q.out b/ql/src/test/results/clientpositive/perf/query5.q.out
new file mode 100644
index 0000000..d3ec922
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query5.q.out
@@ -0,0 +1,489 @@
+PREHOOK: query: explain
+with ssr as
+ (select s_store_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select ss_store_sk as store_sk,
+ ss_sold_date_sk as date_sk,
+ ss_ext_sales_price as sales_price,
+ ss_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from store_sales
+ union all
+ select sr_store_sk as store_sk,
+ sr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ sr_return_amt as return_amt,
+ sr_net_loss as net_loss
+ from store_returns
+ ) salesreturns,
+ date_dim,
+ store
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and store_sk = s_store_sk
+ group by s_store_id)
+ ,
+ csr as
+ (select cp_catalog_page_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select cs_catalog_page_sk as page_sk,
+ cs_sold_date_sk as date_sk,
+ cs_ext_sales_price as sales_price,
+ cs_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from catalog_sales
+ union all
+ select cr_catalog_page_sk as page_sk,
+ cr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ cr_return_amount as return_amt,
+ cr_net_loss as net_loss
+ from catalog_returns
+ ) salesreturns,
+ date_dim,
+ catalog_page
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and page_sk = cp_catalog_page_sk
+ group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select web_site_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select ws_web_site_sk as wsr_web_site_sk,
+ ws_sold_date_sk as date_sk,
+ ws_ext_sales_price as sales_price,
+ ws_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from web_sales
+ union all
+ select ws_web_site_sk as wsr_web_site_sk,
+ wr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ wr_return_amt as return_amt,
+ wr_net_loss as net_loss
+ from web_returns left outer join web_sales on
+ ( wr_item_sk = ws_item_sk
+ and wr_order_number = ws_order_number)
+ ) salesreturns,
+ date_dim,
+ web_site
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and wsr_web_site_sk = web_site_sk
+ group by web_site_id)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(returns) as returns
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , 'store' || s_store_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from ssr
+ union all
+ select 'catalog channel' as channel
+ , 'catalog_page' || cp_catalog_page_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from csr
+ union all
+ select 'web channel' as channel
+ , 'web_site' || web_site_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+with ssr as
+ (select s_store_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select ss_store_sk as store_sk,
+ ss_sold_date_sk as date_sk,
+ ss_ext_sales_price as sales_price,
+ ss_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from store_sales
+ union all
+ select sr_store_sk as store_sk,
+ sr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ sr_return_amt as return_amt,
+ sr_net_loss as net_loss
+ from store_returns
+ ) salesreturns,
+ date_dim,
+ store
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and store_sk = s_store_sk
+ group by s_store_id)
+ ,
+ csr as
+ (select cp_catalog_page_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select cs_catalog_page_sk as page_sk,
+ cs_sold_date_sk as date_sk,
+ cs_ext_sales_price as sales_price,
+ cs_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from catalog_sales
+ union all
+ select cr_catalog_page_sk as page_sk,
+ cr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ cr_return_amount as return_amt,
+ cr_net_loss as net_loss
+ from catalog_returns
+ ) salesreturns,
+ date_dim,
+ catalog_page
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and page_sk = cp_catalog_page_sk
+ group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select web_site_id,
+ sum(sales_price) as sales,
+ sum(profit) as profit,
+ sum(return_amt) as returns,
+ sum(net_loss) as profit_loss
+ from
+ ( select ws_web_site_sk as wsr_web_site_sk,
+ ws_sold_date_sk as date_sk,
+ ws_ext_sales_price as sales_price,
+ ws_net_profit as profit,
+ cast(0 as decimal(7,2)) as return_amt,
+ cast(0 as decimal(7,2)) as net_loss
+ from web_sales
+ union all
+ select ws_web_site_sk as wsr_web_site_sk,
+ wr_returned_date_sk as date_sk,
+ cast(0 as decimal(7,2)) as sales_price,
+ cast(0 as decimal(7,2)) as profit,
+ wr_return_amt as return_amt,
+ wr_net_loss as net_loss
+ from web_returns left outer join web_sales on
+ ( wr_item_sk = ws_item_sk
+ and wr_order_number = ws_order_number)
+ ) salesreturns,
+ date_dim,
+ web_site
+ where date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 14 days)
+ and wsr_web_site_sk = web_site_sk
+ group by web_site_id)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(returns) as returns
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , 'store' || s_store_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from ssr
+ union all
+ select 'catalog channel' as channel
+ , 'catalog_page' || cp_catalog_page_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from csr
+ union all
+ select 'web channel' as channel
+ , 'web_site' || web_site_id as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Map 1 <- Union 2 (CONTAINS)
+Map 12 <- Union 13 (CONTAINS)
+Map 17 <- Union 13 (CONTAINS)
+Map 20 <- Union 21 (CONTAINS)
+Map 9 <- Union 2 (CONTAINS)
+Reducer 14 <- Map 18 (SIMPLE_EDGE), Union 13 (SIMPLE_EDGE)
+Reducer 15 <- Map 19 (SIMPLE_EDGE), Reducer 14 (SIMPLE_EDGE)
+Reducer 16 <- Reducer 15 (SIMPLE_EDGE), Union 6 (CONTAINS)
+Reducer 22 <- Map 28 (SIMPLE_EDGE), Union 21 (SIMPLE_EDGE)
+Reducer 23 <- Map 29 (SIMPLE_EDGE), Reducer 22 (SIMPLE_EDGE)
+Reducer 24 <- Reducer 23 (SIMPLE_EDGE), Union 6 (CONTAINS)
+Reducer 26 <- Map 25 (SIMPLE_EDGE), Map 27 (SIMPLE_EDGE), Union 21 (CONTAINS)
+Reducer 3 <- Map 10 (SIMPLE_EDGE), Union 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 11 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE), Union 6 (CONTAINS)
+Reducer 7 <- Union 6 (SIMPLE_EDGE)
+Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 8
+ File Output Operator [FS_93]
+ Limit [LIM_92] (rows=100 width=110)
+ Number of rows:100
+ Select Operator [SEL_91] (rows=1136898901 width=110)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ <-Reducer 7 [SIMPLE_EDGE]
+ SHUFFLE [RS_90]
+ Select Operator [SEL_89] (rows=1136898901 width=110)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Group By Operator [GBY_88] (rows=1136898901 width=110)
+ Output:["_col0","_col1","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1, KEY._col2
+ <-Union 6 [SIMPLE_EDGE]
+ <-Reducer 16 [CONTAINS]
+ Reduce Output Operator [RS_87]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_86] (rows=2273797803 width=110)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
+ Select Operator [SEL_49] (rows=191657181 width=132)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Group By Operator [GBY_48] (rows=191657181 width=132)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)"],keys:KEY._col0
+ <-Reducer 15 [SIMPLE_EDGE]
+ SHUFFLE [RS_47]
+ PartitionCols:_col0
+ Group By Operator [GBY_46] (rows=383314363 width=132)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col2)","sum(_col4)","sum(_col3)","sum(_col5)"],keys:_col9
+ Select Operator [SEL_45] (rows=383314363 width=132)
+ Output:["_col9","_col2","_col4","_col3","_col5"]
+ Merge Join Operator [MERGEJOIN_136] (rows=383314363 width=132)
+ Conds:RS_42._col0=RS_43._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col9"]
+ <-Map 19 [SIMPLE_EDGE]
+ SHUFFLE [RS_43]
+ PartitionCols:_col0
+ Select Operator [SEL_38] (rows=46000 width=460)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_126] (rows=46000 width=460)
+ predicate:cp_catalog_page_sk is not null
+ TableScan [TS_36] (rows=46000 width=460)
+ default@catalog_page,catalog_page,Tbl:COMPLETE,Col:NONE,Output:["cp_catalog_page_sk","cp_catalog_page_id"]
+ <-Reducer 14 [SIMPLE_EDGE]
+ SHUFFLE [RS_42]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_135] (rows=348467596 width=132)
+ Conds:Union 13._col1=RS_40._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5"]
+ <-Map 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_40]
+ PartitionCols:_col0
+ Select Operator [SEL_35] (rows=8116 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_125] (rows=8116 width=1119)
+ predicate:(d_date BETWEEN 1998-08-04 AND 1998-08-18 00:00:00.0 and d_date_sk is not null)
+ TableScan [TS_33] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
+ <-Union 13 [SIMPLE_EDGE]
+ <-Map 12 [CONTAINS]
+ Reduce Output Operator [RS_39]
+ PartitionCols:_col1
+ Select Operator [SEL_27] (rows=287989836 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_123] (rows=287989836 width=135)
+ predicate:(cs_sold_date_sk is not null and cs_catalog_page_sk is not null)
+ TableScan [TS_25] (rows=287989836 width=135)
+ Output:["cs_sold_date_sk","cs_catalog_page_sk","cs_ext_sales_price","cs_net_profit"]
+ <-Map 17 [CONTAINS]
+ Reduce Output Operator [RS_39]
+ PartitionCols:_col1
+ Select Operator [SEL_30] (rows=28798881 width=106)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_124] (rows=28798881 width=106)
+ predicate:(cr_returned_date_sk is not null and cr_catalog_page_sk is not null)
+ TableScan [TS_28] (rows=28798881 width=106)
+ Output:["cr_returned_date_sk","cr_catalog_page_sk","cr_return_amount","cr_net_loss"]
+ <-Reducer 24 [CONTAINS]
+ Reduce Output Operator [RS_87]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_86] (rows=2273797803 width=110)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
+ Select Operator [SEL_83] (rows=182955399 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Group By Operator [GBY_82] (rows=182955399 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)"],keys:KEY._col0
+ <-Reducer 23 [SIMPLE_EDGE]
+ SHUFFLE [RS_81]
+ PartitionCols:_col0
+ Group By Operator [GBY_80] (rows=365910798 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col2)","sum(_col4)","sum(_col3)","sum(_col5)"],keys:_col9
+ Select Operator [SEL_79] (rows=365910798 width=135)
+ Output:["_col9","_col2","_col4","_col3","_col5"]
+ Merge Join Operator [MERGEJOIN_138] (rows=365910798 width=135)
+ Conds:RS_76._col0=RS_77._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col9"]
+ <-Map 29 [SIMPLE_EDGE]
+ SHUFFLE [RS_77]
+ PartitionCols:_col0
+ Select Operator [SEL_72] (rows=84 width=1850)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_131] (rows=84 width=1850)
+ predicate:web_site_sk is not null
+ TableScan [TS_70] (rows=84 width=1850)
+ default@web_site,web_site,Tbl:COMPLETE,Col:NONE,Output:["web_site_sk","web_site_id"]
+ <-Reducer 22 [SIMPLE_EDGE]
+ SHUFFLE [RS_76]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_137] (rows=332646173 width=135)
+ Conds:Union 21._col1=RS_74._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5"]
+ <-Map 28 [SIMPLE_EDGE]
+ SHUFFLE [RS_74]
+ PartitionCols:_col0
+ Select Operator [SEL_69] (rows=8116 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_130] (rows=8116 width=1119)
+ predicate:(d_date BETWEEN 1998-08-04 AND 1998-08-18 00:00:00.0 and d_date_sk is not null)
+ TableScan [TS_67] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
+ <-Union 21 [SIMPLE_EDGE]
+ <-Map 20 [CONTAINS]
+ Reduce Output Operator [RS_73]
+ PartitionCols:_col1
+ Select Operator [SEL_54] (rows=144002668 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_127] (rows=144002668 width=135)
+ predicate:(ws_sold_date_sk is not null and ws_web_site_sk is not null)
+ TableScan [TS_52] (rows=144002668 width=135)
+ Output:["ws_sold_date_sk","ws_web_site_sk","ws_ext_sales_price","ws_net_profit"]
+ <-Reducer 26 [CONTAINS]
+ Reduce Output Operator [RS_73]
+ PartitionCols:_col1
+ Select Operator [SEL_64] (rows=158402938 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Merge Join Operator [MERGEJOIN_132] (rows=158402938 width=135)
+ Conds:RS_61._col0, _col2=RS_62._col1, _col2(Inner),Output:["_col1","_col3","_col6","_col7"]
+ <-Map 25 [SIMPLE_EDGE]
+ SHUFFLE [RS_61]
+ PartitionCols:_col0, _col2
+ Select Operator [SEL_57] (rows=144002668 width=135)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_128] (rows=144002668 width=135)
+ predicate:(ws_web_site_sk is not null and ws_order_number is not null and ws_item_sk is not null)
+ TableScan [TS_55] (rows=144002668 width=135)
+ default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_item_sk","ws_web_site_sk","ws_order_number"]
+ <-Map 27 [SIMPLE_EDGE]
+ SHUFFLE [RS_62]
+ PartitionCols:_col1, _col2
+ Select Operator [SEL_60] (rows=14398467 width=92)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Filter Operator [FIL_129] (rows=14398467 width=92)
+ predicate:(wr_returned_date_sk is not null and wr_item_sk is not null and wr_order_number is not null)
+ TableScan [TS_58] (rows=14398467 width=92)
+ default@web_returns,web_returns,Tbl:COMPLETE,Col:NONE,Output:["wr_returned_date_sk","wr_item_sk","wr_order_number","wr_return_amt","wr_net_loss"]
+ <-Reducer 5 [CONTAINS]
+ Reduce Output Operator [RS_87]
+ PartitionCols:_col0, _col1, _col2
+ Group By Operator [GBY_86] (rows=2273797803 width=110)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
+ Select Operator [SEL_24] (rows=383320021 width=87)
+ Output:["_col0","_col1","_col2","_col3","_col4"]
+ Group By Operator [GBY_23] (rows=383320021 width=87)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)"],keys:KEY._col0
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_22]
+ PartitionCols:_col0
+ Group By Operator [GBY_21] (rows=766640042 width=87)
+ Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col2)","sum(_col4)","sum(_col3)","sum(_col5)"],keys:_col9
+ Select Operator [SEL_20] (rows=766640042 width=87)
+ Output:["_col9","_col2","_col4","_col3","_col5"]
+ Merge Join Operator [MERGEJOIN_134] (rows=766640042 width=87)
+ Conds:RS_17._col0=RS_18._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col9"]
+ <-Map 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_18]
+ PartitionCols:_col0
+ Select Operator [SEL_13] (rows=1704 width=1910)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_122] (rows=1704 width=1910)
+ predicate:s_store_sk is not null
+ TableScan [TS_11] (rows=1704 width=1910)
+ default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_id"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_133] (rows=696945478 width=87)
+ Conds:Union 2._col1=RS_15._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5"]
+ <-Map 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_15]
+ PartitionCols:_col0
+ Select Operator [SEL_10] (rows=8116 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_121] (rows=8116 width=1119)
+ predicate:(d_date BETWEEN 1998-08-04 AND 1998-08-18 00:00:00.0 and d_date_sk is not null)
+ TableScan [TS_8] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
+ <-Union 2 [SIMPLE_EDGE]
+ <-Map 1 [CONTAINS]
+ Reduce Output Operator [RS_14]
+ PartitionCols:_col1
+ Select Operator [SEL_2] (rows=575995635 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_119] (rows=575995635 width=88)
+ predicate:(ss_sold_date_sk is not null and ss_store_sk is not null)
+ TableScan [TS_0] (rows=575995635 width=88)
+ Output:["ss_sold_date_sk","ss_store_sk","ss_ext_sales_price","ss_net_profit"]
+ <-Map 9 [CONTAINS]
+ Reduce Output Operator [RS_14]
+ PartitionCols:_col1
+ Select Operator [SEL_5] (rows=57591150 width=77)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_120] (rows=57591150 width=77)
+ predicate:(sr_returned_date_sk is not null and sr_store_sk is not null)
+ TableScan [TS_3] (rows=57591150 width=77)
+ Output:["sr_returned_date_sk","sr_store_sk","sr_return_amt","sr_net_loss"]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/results/clientpositive/perf/query72.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query72.q.out b/ql/src/test/results/clientpositive/perf/query72.q.out
index dff4c3a..828aa28 100644
--- a/ql/src/test/results/clientpositive/perf/query72.q.out
+++ b/ql/src/test/results/clientpositive/perf/query72.q.out
@@ -1,6 +1,62 @@
-PREHOOK: query: explain select i_item_desc ,w_warehouse_name ,d1.d_week_seq ,count(case when p_promo_sk is null then 1 else 0 end) no_promo ,count(case when p_promo_sk is not null then 1 else 0 end) promo ,count(*) total_cnt from catalog_sales join inventory on (catalog_sales.cs_item_sk = inventory.inv_item_sk) join warehouse on (warehouse.w_warehouse_sk=inventory.inv_warehouse_sk) join item on (item.i_item_sk = catalog_sales.cs_item_sk) join customer_demographics on (catalog_sales.cs_bill_cdemo_sk = customer_demographics.cd_demo_sk) join household_demographics on (catalog_sales.cs_bill_hdemo_sk = household_demographics.hd_demo_sk) join date_dim d1 on (catalog_sales.cs_sold_date_sk = d1.d_date_sk) join date_dim d2 on (inventory.inv_date_sk = d2.d_date_sk) join date_dim d3 on (catalog_sales.cs_ship_date_sk = d3.d_date_sk) left outer join promotion on (catalog_sales.cs_promo_sk=promotion.p_promo_sk) left outer join catalog_returns on (catalog_returns.cr_item_sk = catalog_sales.cs_item
_sk and catalog_returns.cr_order_number = catalog_sales.cs_order_number) where d1.d_week_seq = d2.d_week_seq and inv_quantity_on_hand < cs_quantity and d3.d_date > d1.d_date + 5 and hd_buy_potential = '1001-5000' and d1.d_year = 2001 and hd_buy_potential = '1001-5000' and cd_marital_status = 'M' and d1.d_year = 2001 group by i_item_desc,w_warehouse_name,d1.d_week_seq order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq limit 100
+PREHOOK: query: explain
+select i_item_desc
+ ,w_warehouse_name
+ ,d1.d_week_seq
+ ,count(case when p_promo_sk is null then 1 else 0 end) no_promo
+ ,count(case when p_promo_sk is not null then 1 else 0 end) promo
+ ,count(*) total_cnt
+from catalog_sales
+join inventory on (cs_item_sk = inv_item_sk)
+join warehouse on (w_warehouse_sk=inv_warehouse_sk)
+join item on (i_item_sk = cs_item_sk)
+join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)
+join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)
+join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)
+join date_dim d2 on (inv_date_sk = d2.d_date_sk)
+join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)
+left outer join promotion on (cs_promo_sk=p_promo_sk)
+left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number)
+where d1.d_week_seq = d2.d_week_seq
+ and inv_quantity_on_hand < cs_quantity
+ and d3.d_date > d1.d_date + 5
+ and hd_buy_potential = '1001-5000'
+ and d1.d_year = 2001
+ and hd_buy_potential = '1001-5000'
+ and cd_marital_status = 'M'
+ and d1.d_year = 2001
+group by i_item_desc,w_warehouse_name,d1.d_week_seq
+order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq
+limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain select i_item_desc ,w_warehouse_name ,d1.d_week_seq ,count(case when p_promo_sk is null then 1 else 0 end) no_promo ,count(case when p_promo_sk is not null then 1 else 0 end) promo ,count(*) total_cnt from catalog_sales join inventory on (catalog_sales.cs_item_sk = inventory.inv_item_sk) join warehouse on (warehouse.w_warehouse_sk=inventory.inv_warehouse_sk) join item on (item.i_item_sk = catalog_sales.cs_item_sk) join customer_demographics on (catalog_sales.cs_bill_cdemo_sk = customer_demographics.cd_demo_sk) join household_demographics on (catalog_sales.cs_bill_hdemo_sk = household_demographics.hd_demo_sk) join date_dim d1 on (catalog_sales.cs_sold_date_sk = d1.d_date_sk) join date_dim d2 on (inventory.inv_date_sk = d2.d_date_sk) join date_dim d3 on (catalog_sales.cs_ship_date_sk = d3.d_date_sk) left outer join promotion on (catalog_sales.cs_promo_sk=promotion.p_promo_sk) left outer join catalog_returns on (catalog_returns.cr_item_sk = catalog_sales.cs_ite
m_sk and catalog_returns.cr_order_number = catalog_sales.cs_order_number) where d1.d_week_seq = d2.d_week_seq and inv_quantity_on_hand < cs_quantity and d3.d_date > d1.d_date + 5 and hd_buy_potential = '1001-5000' and d1.d_year = 2001 and hd_buy_potential = '1001-5000' and cd_marital_status = 'M' and d1.d_year = 2001 group by i_item_desc,w_warehouse_name,d1.d_week_seq order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq limit 100
+POSTHOOK: query: explain
+select i_item_desc
+ ,w_warehouse_name
+ ,d1.d_week_seq
+ ,count(case when p_promo_sk is null then 1 else 0 end) no_promo
+ ,count(case when p_promo_sk is not null then 1 else 0 end) promo
+ ,count(*) total_cnt
+from catalog_sales
+join inventory on (cs_item_sk = inv_item_sk)
+join warehouse on (w_warehouse_sk=inv_warehouse_sk)
+join item on (i_item_sk = cs_item_sk)
+join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)
+join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)
+join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)
+join date_dim d2 on (inv_date_sk = d2.d_date_sk)
+join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)
+left outer join promotion on (cs_promo_sk=p_promo_sk)
+left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number)
+where d1.d_week_seq = d2.d_week_seq
+ and inv_quantity_on_hand < cs_quantity
+ and d3.d_date > d1.d_date + 5
+ and hd_buy_potential = '1001-5000'
+ and d1.d_year = 2001
+ and hd_buy_potential = '1001-5000'
+ and cd_marital_status = 'M'
+ and d1.d_year = 2001
+group by i_item_desc,w_warehouse_name,d1.d_week_seq
+order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq
+limit 100
POSTHOOK: type: QUERY
Plan optimized by CBO.