hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From pxi...@apache.org
Subject [1/2] hive git commit: HIVE-15663: Add more interval tests to HivePerfCliDriver (Pengcheng Xiong)
Date Thu, 19 Jan 2017 02:27:30 GMT
Repository: hive
Updated Branches:
  refs/heads/master cc3ce1614 -> 42ead3445


http://git-wip-us.apache.org/repos/asf/hive/blob/42ead344/ql/src/test/results/clientpositive/perf/query80.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query80.q.out b/ql/src/test/results/clientpositive/perf/query80.q.out
index 10ec36a..7338d9c 100644
--- a/ql/src/test/results/clientpositive/perf/query80.q.out
+++ b/ql/src/test/results/clientpositive/perf/query80.q.out
@@ -1,6 +1,192 @@
-PREHOOK: query: 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_date 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
+PREHOOK: query: 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
 PREHOOK: type: QUERY
-POSTHOOK: query: 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_date 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
+POSTHOOK: query: 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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
@@ -109,7 +295,7 @@ Stage-0
                                                   Select Operator [SEL_47] (rows=8116 width=1119)
                                                     Output:["_col0"]
                                                     Filter Operator [FIL_194] (rows=8116
width=1119)
-                                                      predicate:(d_date BETWEEN 1998-08-04
AND 1998-09-04 and d_date_sk is not null)
+                                                      predicate:(d_date BETWEEN 1998-08-04
AND 1998-09-03 00:00:00.0 and d_date_sk is not null)
                                                       TableScan [TS_45] (rows=73049 width=1119)
                                                         default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
                                               <-Reducer 17 [SIMPLE_EDGE]
@@ -201,7 +387,7 @@ Stage-0
                                                   Select Operator [SEL_88] (rows=8116 width=1119)
                                                     Output:["_col0"]
                                                     Filter Operator [FIL_200] (rows=8116
width=1119)
-                                                      predicate:(d_date BETWEEN 1998-08-04
AND 1998-09-04 and d_date_sk is not null)
+                                                      predicate:(d_date BETWEEN 1998-08-04
AND 1998-09-03 00:00:00.0 and d_date_sk is not null)
                                                       TableScan [TS_86] (rows=73049 width=1119)
                                                         default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"]
                                               <-Reducer 29 [SIMPLE_EDGE]
@@ -293,7 +479,7 @@ Stage-0
                                                   Select Operator [SEL_8] (rows=8116 width=1119)
                                                     Output:["_col0"]
                                                     Filter Operator [FIL_188] (rows=8116
width=1119)
-                                                      predicate:(d_date BETWEEN 1998-08-04
AND 1998-09-04 and d_date_sk is not null)
+                                                      predicate:(d_date BETWEEN 1998-08-04
AND 1998-09-03 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/query82.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query82.q.out b/ql/src/test/results/clientpositive/perf/query82.q.out
index 6f784c4..9c0042c 100644
--- a/ql/src/test/results/clientpositive/perf/query82.q.out
+++ b/ql/src/test/results/clientpositive/perf/query82.q.out
@@ -1,6 +1,34 @@
-PREHOOK: query: 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
+PREHOOK: query: 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
 PREHOOK: type: QUERY
-POSTHOOK: query: 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
+POSTHOOK: query: 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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
@@ -69,7 +97,7 @@ Stage-0
                             Select Operator [SEL_11] (rows=8116 width=1119)
                               Output:["_col0"]
                               Filter Operator [FIL_42] (rows=8116 width=1119)
-                                predicate:(d_date BETWEEN '2002-05-30' AND '2002-07-30' and
d_date_sk is not null)
+                                predicate:(d_date BETWEEN 2002-05-30 AND 2002-07-29 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/query98.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query98.q.out b/ql/src/test/results/clientpositive/perf/query98.q.out
index 35cc897..1bae9be 100644
--- a/ql/src/test/results/clientpositive/perf/query98.q.out
+++ b/ql/src/test/results/clientpositive/perf/query98.q.out
@@ -1,6 +1,16 @@
-PREHOOK: query: 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
+PREHOOK: query: 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
 PREHOOK: type: QUERY
-POSTHOOK: query: 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
+POSTHOOK: query: 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
 POSTHOOK: type: QUERY
 Plan optimized by CBO.
 
@@ -72,7 +82,7 @@ Stage-0
                                       Select Operator [SEL_5] (rows=8116 width=1119)
                                         Output:["_col0"]
                                         Filter Operator [FIL_34] (rows=8116 width=1119)
-                                          predicate:(d_date BETWEEN 2001-01-12 AND 2001-02-11
and d_date_sk is not null)
+                                          predicate:(d_date BETWEEN 2001-01-12 AND 2001-02-11
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"]
 


Mime
View raw message