You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by we...@apache.org on 2017/05/31 00:11:52 UTC
[14/17] hive git commit: HIVE-16764: Support numeric as same as
decimal (Pengcheng Xiong, reviewed by Ashutosh Chauhan)
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query78.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query78.q b/ql/src/test/queries/clientpositive/perf/query78.q
new file mode 100644
index 0000000..ca9e6d6
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query78.q
@@ -0,0 +1,60 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query78.tpl and seed 1819994127
+explain
+with ws as
+ (select d_year AS ws_sold_year, ws_item_sk,
+ ws_bill_customer_sk ws_customer_sk,
+ sum(ws_quantity) ws_qty,
+ sum(ws_wholesale_cost) ws_wc,
+ sum(ws_sales_price) ws_sp
+ from web_sales
+ left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
+ join date_dim on ws_sold_date_sk = d_date_sk
+ where wr_order_number is null
+ group by d_year, ws_item_sk, ws_bill_customer_sk
+ ),
+cs as
+ (select d_year AS cs_sold_year, cs_item_sk,
+ cs_bill_customer_sk cs_customer_sk,
+ sum(cs_quantity) cs_qty,
+ sum(cs_wholesale_cost) cs_wc,
+ sum(cs_sales_price) cs_sp
+ from catalog_sales
+ left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
+ join date_dim on cs_sold_date_sk = d_date_sk
+ where cr_order_number is null
+ group by d_year, cs_item_sk, cs_bill_customer_sk
+ ),
+ss as
+ (select d_year AS ss_sold_year, ss_item_sk,
+ ss_customer_sk,
+ sum(ss_quantity) ss_qty,
+ sum(ss_wholesale_cost) ss_wc,
+ sum(ss_sales_price) ss_sp
+ from store_sales
+ left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
+ join date_dim on ss_sold_date_sk = d_date_sk
+ where sr_ticket_number is null
+ group by d_year, ss_item_sk, ss_customer_sk
+ )
+ select
+ss_sold_year, ss_item_sk, ss_customer_sk,
+round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from ss
+left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
+left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk)
+where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000
+order by
+ ss_sold_year, ss_item_sk, ss_customer_sk,
+ ss_qty desc, ss_wc desc, ss_sp desc,
+ other_chan_qty,
+ other_chan_wholesale_cost,
+ other_chan_sales_price,
+ round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)
+limit 100;
+
+-- end query 1 in stream 0 using template query78.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query79.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query79.q b/ql/src/test/queries/clientpositive/perf/query79.q
index d104602..dfa7017 100644
--- a/ql/src/test/queries/clientpositive/perf/query79.q
+++ b/ql/src/test/queries/clientpositive/perf/query79.q
@@ -1 +1,25 @@
-explain select c_last_name,c_first_name,substr(s_city,1,30) sub,ss_ticket_number,amt,profit from (select ss_ticket_number ,ss_customer_sk ,store.s_city ,sum(ss_coupon_amt) amt ,sum(ss_net_profit) profit from store_sales,date_dim,store,household_demographics where store_sales.ss_sold_date_sk = date_dim.d_date_sk and store_sales.ss_store_sk = store.s_store_sk and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0) and date_dim.d_dow = 1 and date_dim.d_year in (1998,1998+1,1998+2) and store.s_number_employees between 200 and 295 group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer where ms.ss_customer_sk = customer.c_customer_sk order by c_last_name,c_first_name,sub, profit limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query79.tpl and seed 2031708268
+explain
+select
+ c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit
+ from
+ (select ss_ticket_number
+ ,ss_customer_sk
+ ,store.s_city
+ ,sum(ss_coupon_amt) amt
+ ,sum(ss_net_profit) profit
+ from store_sales,date_dim,store,household_demographics
+ where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0)
+ and date_dim.d_dow = 1
+ and date_dim.d_year in (1998,1998+1,1998+2)
+ and store.s_number_employees between 200 and 295
+ group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
+ where ss_customer_sk = c_customer_sk
+ order by c_last_name,c_first_name,substr(s_city,1,30), profit
+limit 100;
+
+-- end query 1 in stream 0 using template query79.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query8.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query8.q b/ql/src/test/queries/clientpositive/perf/query8.q
index bb77f55..cfce366 100644
--- a/ql/src/test/queries/clientpositive/perf/query8.q
+++ b/ql/src/test/queries/clientpositive/perf/query8.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query8.tpl and seed 1766988859
explain
select s_store_name
,sum(ss_net_profit)
@@ -105,3 +107,4 @@ select s_store_name
order by s_store_name
limit 100;
+-- end query 1 in stream 0 using template query8.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/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 53f9a3c..651c5d7 100644
--- a/ql/src/test/queries/clientpositive/perf/query80.q
+++ b/ql/src/test/queries/clientpositive/perf/query80.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query80.tpl and seed 1819994127
explain
with ssr as
(select s_store_id as store_id,
@@ -93,3 +95,4 @@ group by web_site_id)
,id
limit 100;
+-- end query 1 in stream 0 using template query80.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query81.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query81.q b/ql/src/test/queries/clientpositive/perf/query81.q
index 77de348..fd072c3 100644
--- a/ql/src/test/queries/clientpositive/perf/query81.q
+++ b/ql/src/test/queries/clientpositive/perf/query81.q
@@ -1,6 +1,7 @@
set hive.mapred.mode=nonstrict;
-
-explain with customer_total_return as
+-- start query 1 in stream 0 using template query81.tpl and seed 1819994127
+explain
+with customer_total_return as
(select cr_returning_customer_sk as ctr_customer_sk
,ca_state as ctr_state,
sum(cr_return_amt_inc_tax) as ctr_total_return
@@ -28,3 +29,5 @@ explain with customer_total_return as
,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
,ca_location_type,ctr_total_return
limit 100;
+
+-- end query 1 in stream 0 using template query81.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/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 cf882b8..9aec0cb 100644
--- a/ql/src/test/queries/clientpositive/perf/query82.q
+++ b/ql/src/test/queries/clientpositive/perf/query82.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query82.tpl and seed 55585014
explain
select i_item_id
,i_item_desc
@@ -14,3 +16,4 @@ select i_item_id
order by i_item_id
limit 100;
+-- end query 1 in stream 0 using template query82.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query83.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query83.q b/ql/src/test/queries/clientpositive/perf/query83.q
index 0186e03..fd9184c 100644
--- a/ql/src/test/queries/clientpositive/perf/query83.q
+++ b/ql/src/test/queries/clientpositive/perf/query83.q
@@ -1,5 +1,7 @@
+set hive.mapred.mode=nonstrict;
-- start query 1 in stream 0 using template query83.tpl and seed 1930872976
-explain with sr_items as
+explain
+with sr_items as
(select i_item_id item_id,
sum(sr_return_quantity) sr_item_qty
from store_returns,
@@ -63,3 +65,5 @@ explain with sr_items as
order by sr_items.item_id
,sr_item_qty
limit 100;
+
+-- end query 1 in stream 0 using template query83.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query84.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query84.q b/ql/src/test/queries/clientpositive/perf/query84.q
index bcc2f4e..4ab5945 100644
--- a/ql/src/test/queries/clientpositive/perf/query84.q
+++ b/ql/src/test/queries/clientpositive/perf/query84.q
@@ -1 +1,23 @@
-explain select c_customer_id as customer_id ,concat(c_last_name, ', ', c_first_name) as customername from customer ,customer_address ,customer_demographics ,household_demographics ,income_band ,store_returns where ca_city = 'Hopewell' and customer.c_current_addr_sk = customer_address.ca_address_sk and ib_lower_bound >= 32287 and ib_upper_bound <= 32287 + 50000 and income_band.ib_income_band_sk = household_demographics.hd_income_band_sk and customer_demographics.cd_demo_sk = customer.c_current_cdemo_sk and household_demographics.hd_demo_sk = customer.c_current_hdemo_sk and store_returns.sr_cdemo_sk = customer_demographics.cd_demo_sk order by customer_id limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query84.tpl and seed 1819994127
+explain
+select c_customer_id as customer_id
+ ,c_last_name || ', ' || c_first_name as customername
+ from customer
+ ,customer_address
+ ,customer_demographics
+ ,household_demographics
+ ,income_band
+ ,store_returns
+ where ca_city = 'Hopewell'
+ and c_current_addr_sk = ca_address_sk
+ and ib_lower_bound >= 32287
+ and ib_upper_bound <= 32287 + 50000
+ and ib_income_band_sk = hd_income_band_sk
+ and cd_demo_sk = c_current_cdemo_sk
+ and hd_demo_sk = c_current_hdemo_sk
+ and sr_cdemo_sk = cd_demo_sk
+ order by c_customer_id
+ limit 100;
+
+-- end query 1 in stream 0 using template query84.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query85.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query85.q b/ql/src/test/queries/clientpositive/perf/query85.q
index b1e2b64..2e67e72 100644
--- a/ql/src/test/queries/clientpositive/perf/query85.q
+++ b/ql/src/test/queries/clientpositive/perf/query85.q
@@ -1 +1,86 @@
-explain select substr(r_reason_desc,1,20) as r ,avg(ws_quantity) wq ,avg(wr_refunded_cash) ref ,avg(wr_fee) fee from web_sales, web_returns, web_page, customer_demographics cd1, customer_demographics cd2, customer_address, date_dim, reason where web_sales.ws_web_page_sk = web_page.wp_web_page_sk and web_sales.ws_item_sk = web_returns.wr_item_sk and web_sales.ws_order_number = web_returns.wr_order_number and web_sales.ws_sold_date_sk = date_dim.d_date_sk and d_year = 1998 and cd1.cd_demo_sk = web_returns.wr_refunded_cdemo_sk and cd2.cd_demo_sk = web_returns.wr_returning_cdemo_sk and customer_address.ca_address_sk = web_returns.wr_refunded_addr_sk and reason.r_reason_sk = web_returns.wr_reason_sk and ( ( cd1.cd_marital_status = 'M' and cd1.cd_marital_status = cd2.cd_marital_status and cd1.cd_education_status = '4 yr Degree' and cd1.cd_education_status = cd2.cd_education_status and ws_sales_price between 100.00 and 150.00 ) or ( cd1.cd_marital_status = 'D' and cd1.cd_marital_status = c
d2.cd_marital_status and cd1.cd_education_status = 'Primary' and cd1.cd_education_status = cd2.cd_education_status and ws_sales_price between 50.00 and 100.00 ) or ( cd1.cd_marital_status = 'U' and cd1.cd_marital_status = cd2.cd_marital_status and cd1.cd_education_status = 'Advanced Degree' and cd1.cd_education_status = cd2.cd_education_status and ws_sales_price between 150.00 and 200.00 ) ) and ( ( ca_country = 'United States' and ca_state in ('KY', 'GA', 'NM') and ws_net_profit between 100 and 200 ) or ( ca_country = 'United States' and ca_state in ('MT', 'OR', 'IN') and ws_net_profit between 150 and 300 ) or ( ca_country = 'United States' and ca_state in ('WI', 'MO', 'WV') and ws_net_profit between 50 and 250 ) ) group by r_reason_desc order by r, wq, ref, fee limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query85.tpl and seed 622697896
+explain
+select substr(r_reason_desc,1,20)
+ ,avg(ws_quantity)
+ ,avg(wr_refunded_cash)
+ ,avg(wr_fee)
+ from web_sales, web_returns, web_page, customer_demographics cd1,
+ customer_demographics cd2, customer_address, date_dim, reason
+ where ws_web_page_sk = wp_web_page_sk
+ and ws_item_sk = wr_item_sk
+ and ws_order_number = wr_order_number
+ and ws_sold_date_sk = d_date_sk and d_year = 1998
+ and cd1.cd_demo_sk = wr_refunded_cdemo_sk
+ and cd2.cd_demo_sk = wr_returning_cdemo_sk
+ and ca_address_sk = wr_refunded_addr_sk
+ and r_reason_sk = wr_reason_sk
+ and
+ (
+ (
+ cd1.cd_marital_status = 'M'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = '4 yr Degree'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 100.00 and 150.00
+ )
+ or
+ (
+ cd1.cd_marital_status = 'D'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = 'Primary'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 50.00 and 100.00
+ )
+ or
+ (
+ cd1.cd_marital_status = 'U'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = 'Advanced Degree'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 150.00 and 200.00
+ )
+ )
+ and
+ (
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('KY', 'GA', 'NM')
+ and ws_net_profit between 100 and 200
+ )
+ or
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('MT', 'OR', 'IN')
+ and ws_net_profit between 150 and 300
+ )
+ or
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('WI', 'MO', 'WV')
+ and ws_net_profit between 50 and 250
+ )
+ )
+group by r_reason_desc
+order by substr(r_reason_desc,1,20)
+ ,avg(ws_quantity)
+ ,avg(wr_refunded_cash)
+ ,avg(wr_fee)
+limit 100;
+
+-- end query 1 in stream 0 using template query85.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query86.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query86.q b/ql/src/test/queries/clientpositive/perf/query86.q
index 07a9ec5..6670868 100644
--- a/ql/src/test/queries/clientpositive/perf/query86.q
+++ b/ql/src/test/queries/clientpositive/perf/query86.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query86.tpl and seed 1819994127
explain
select
sum(ws_net_paid) as total_sum
@@ -5,9 +7,9 @@ select
,i_class
,grouping(i_category)+grouping(i_class) as lochierarchy
,rank() over (
- partition by grouping(i_category)+grouping(i_class),
- case when grouping(i_class) = 0 then i_category end
- order by sum(ws_net_paid) desc) as rank_within_parent
+ partition by grouping(i_category)+grouping(i_class),
+ case when grouping(i_class) = 0 then i_category end
+ order by sum(ws_net_paid) desc) as rank_within_parent
from
web_sales
,date_dim d1
@@ -23,3 +25,4 @@ select
rank_within_parent
limit 100;
+-- end query 1 in stream 0 using template query86.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query87.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query87.q b/ql/src/test/queries/clientpositive/perf/query87.q
index fe12ee3..e4562c2 100644
--- a/ql/src/test/queries/clientpositive/perf/query87.q
+++ b/ql/src/test/queries/clientpositive/perf/query87.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query87.tpl and seed 1819994127
explain
select count(*)
from ((select distinct c_last_name, c_first_name, d_date
@@ -20,3 +22,4 @@ from ((select distinct c_last_name, c_first_name, d_date
) cool_cust
;
+-- end query 1 in stream 0 using template query87.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query88.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query88.q b/ql/src/test/queries/clientpositive/perf/query88.q
index bb6ef6d..265cc7c 100644
--- a/ql/src/test/queries/clientpositive/perf/query88.q
+++ b/ql/src/test/queries/clientpositive/perf/query88.q
@@ -1,13 +1,13 @@
-set hive.strict.checks.cartesian.product=false;
-
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query88.tpl and seed 318176889
explain
select *
from
(select count(*) h8_30_to_9
from store_sales, household_demographics , time_dim, store
- where store_sales.ss_sold_time_sk = time_dim.t_time_sk
- and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
- and store_sales.ss_store_sk = store.s_store_sk
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
and time_dim.t_hour = 8
and time_dim.t_minute >= 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
@@ -16,9 +16,9 @@ from
and store.s_store_name = 'ese') s1,
(select count(*) h9_to_9_30
from store_sales, household_demographics , time_dim, store
- where store_sales.ss_sold_time_sk = time_dim.t_time_sk
- and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
- and store_sales.ss_store_sk = store.s_store_sk
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
and time_dim.t_hour = 9
and time_dim.t_minute < 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
@@ -27,9 +27,9 @@ from
and store.s_store_name = 'ese') s2,
(select count(*) h9_30_to_10
from store_sales, household_demographics , time_dim, store
- where store_sales.ss_sold_time_sk = time_dim.t_time_sk
- and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
- and store_sales.ss_store_sk = store.s_store_sk
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
and time_dim.t_hour = 9
and time_dim.t_minute >= 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
@@ -38,9 +38,9 @@ from
and store.s_store_name = 'ese') s3,
(select count(*) h10_to_10_30
from store_sales, household_demographics , time_dim, store
- where store_sales.ss_sold_time_sk = time_dim.t_time_sk
- and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
- and store_sales.ss_store_sk = store.s_store_sk
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
and time_dim.t_hour = 10
and time_dim.t_minute < 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
@@ -49,9 +49,9 @@ from
and store.s_store_name = 'ese') s4,
(select count(*) h10_30_to_11
from store_sales, household_demographics , time_dim, store
- where store_sales.ss_sold_time_sk = time_dim.t_time_sk
- and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
- and store_sales.ss_store_sk = store.s_store_sk
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
and time_dim.t_hour = 10
and time_dim.t_minute >= 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
@@ -60,9 +60,9 @@ from
and store.s_store_name = 'ese') s5,
(select count(*) h11_to_11_30
from store_sales, household_demographics , time_dim, store
- where store_sales.ss_sold_time_sk = time_dim.t_time_sk
- and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
- and store_sales.ss_store_sk = store.s_store_sk
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
and time_dim.t_hour = 11
and time_dim.t_minute < 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
@@ -71,9 +71,9 @@ from
and store.s_store_name = 'ese') s6,
(select count(*) h11_30_to_12
from store_sales, household_demographics , time_dim, store
- where store_sales.ss_sold_time_sk = time_dim.t_time_sk
- and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
- and store_sales.ss_store_sk = store.s_store_sk
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
and time_dim.t_hour = 11
and time_dim.t_minute >= 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
@@ -82,9 +82,9 @@ from
and store.s_store_name = 'ese') s7,
(select count(*) h12_to_12_30
from store_sales, household_demographics , time_dim, store
- where store_sales.ss_sold_time_sk = time_dim.t_time_sk
- and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
- and store_sales.ss_store_sk = store.s_store_sk
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
and time_dim.t_hour = 12
and time_dim.t_minute < 30
and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or
@@ -92,3 +92,5 @@ from
(household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2))
and store.s_store_name = 'ese') s8
;
+
+-- end query 1 in stream 0 using template query88.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query89.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query89.q b/ql/src/test/queries/clientpositive/perf/query89.q
index a628c0e..3159229 100644
--- a/ql/src/test/queries/clientpositive/perf/query89.q
+++ b/ql/src/test/queries/clientpositive/perf/query89.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query89.tpl and seed 1719819282
explain
select *
from(
@@ -9,9 +11,9 @@ select i_category, i_class, i_brand,
(partition by i_category, i_brand, s_store_name, s_company_name)
avg_monthly_sales
from item, store_sales, date_dim, store
-where store_sales.ss_item_sk = item.i_item_sk and
- store_sales.ss_sold_date_sk = date_dim.d_date_sk and
- store_sales.ss_store_sk = store.s_store_sk and
+where ss_item_sk = i_item_sk and
+ ss_sold_date_sk = d_date_sk and
+ ss_store_sk = s_store_sk and
d_year in (2000) and
((i_category in ('Home','Books','Electronics') and
i_class in ('wallpaper','parenting','musical')
@@ -25,3 +27,4 @@ where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales
order by sum_sales - avg_monthly_sales, s_store_name
limit 100;
+-- end query 1 in stream 0 using template query89.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query9.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query9.q b/ql/src/test/queries/clientpositive/perf/query9.q
index 77bffca..421f5e1 100644
--- a/ql/src/test/queries/clientpositive/perf/query9.q
+++ b/ql/src/test/queries/clientpositive/perf/query9.q
@@ -1,11 +1,12 @@
set hive.mapred.mode=nonstrict;
-
-explain select case when (select count(*)
- from store_sales
+-- start query 1 in stream 0 using template query9.tpl and seed 1490436826
+explain
+select case when (select count(*)
+ from store_sales
where ss_quantity between 1 and 20) > 409437
- then (select avg(ss_ext_list_price)
- from store_sales
- where ss_quantity between 1 and 20)
+ then (select avg(ss_ext_list_price)
+ from store_sales
+ where ss_quantity between 1 and 20)
else (select avg(ss_net_paid_inc_tax)
from store_sales
where ss_quantity between 1 and 20) end bucket1 ,
@@ -14,7 +15,7 @@ explain select case when (select count(*)
where ss_quantity between 21 and 40) > 4595804
then (select avg(ss_ext_list_price)
from store_sales
- where ss_quantity between 21 and 40)
+ where ss_quantity between 21 and 40)
else (select avg(ss_net_paid_inc_tax)
from store_sales
where ss_quantity between 21 and 40) end bucket2,
@@ -47,4 +48,6 @@ explain select case when (select count(*)
where ss_quantity between 81 and 100) end bucket5
from reason
where r_reason_sk = 1
-;
\ No newline at end of file
+;
+
+-- end query 1 in stream 0 using template query9.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query90.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query90.q b/ql/src/test/queries/clientpositive/perf/query90.q
index 515da06..d17cbc4 100644
--- a/ql/src/test/queries/clientpositive/perf/query90.q
+++ b/ql/src/test/queries/clientpositive/perf/query90.q
@@ -1,2 +1,24 @@
set hive.mapred.mode=nonstrict;
-explain select cast(amc as decimal(15,4))/cast(pmc as decimal(15,4)) am_pm_ratio from ( select count(*) amc from web_sales, household_demographics , time_dim, web_page where ws_sold_time_sk = time_dim.t_time_sk and ws_ship_hdemo_sk = household_demographics.hd_demo_sk and ws_web_page_sk = web_page.wp_web_page_sk and time_dim.t_hour between 6 and 6+1 and household_demographics.hd_dep_count = 8 and web_page.wp_char_count between 5000 and 5200) at, ( select count(*) pmc from web_sales, household_demographics , time_dim, web_page where ws_sold_time_sk = time_dim.t_time_sk and ws_ship_hdemo_sk = household_demographics.hd_demo_sk and ws_web_page_sk = web_page.wp_web_page_sk and time_dim.t_hour between 14 and 14+1 and household_demographics.hd_dep_count = 8 and web_page.wp_char_count between 5000 and 5200) pt order by am_pm_ratio limit 100;
+-- start query 1 in stream 0 using template query90.tpl and seed 2031708268
+explain
+select cast(amc as decimal(15,4))/cast(pmc as decimal(15,4)) am_pm_ratio
+ from ( select count(*) amc
+ from web_sales, household_demographics , time_dim, web_page
+ where ws_sold_time_sk = time_dim.t_time_sk
+ and ws_ship_hdemo_sk = household_demographics.hd_demo_sk
+ and ws_web_page_sk = web_page.wp_web_page_sk
+ and time_dim.t_hour between 6 and 6+1
+ and household_demographics.hd_dep_count = 8
+ and web_page.wp_char_count between 5000 and 5200) at,
+ ( select count(*) pmc
+ from web_sales, household_demographics , time_dim, web_page
+ where ws_sold_time_sk = time_dim.t_time_sk
+ and ws_ship_hdemo_sk = household_demographics.hd_demo_sk
+ and ws_web_page_sk = web_page.wp_web_page_sk
+ and time_dim.t_hour between 14 and 14+1
+ and household_demographics.hd_dep_count = 8
+ and web_page.wp_char_count between 5000 and 5200) pt
+ order by am_pm_ratio
+ limit 100;
+
+-- end query 1 in stream 0 using template query90.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query91.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query91.q b/ql/src/test/queries/clientpositive/perf/query91.q
index 9458c0c..79ca713 100644
--- a/ql/src/test/queries/clientpositive/perf/query91.q
+++ b/ql/src/test/queries/clientpositive/perf/query91.q
@@ -1,2 +1,33 @@
set hive.mapred.mode=nonstrict;
-explain select cc_call_center_id Call_Center, cc_name Call_Center_Name, cc_manager Manager, sum(cr_net_loss) Returns_Loss from call_center, catalog_returns, date_dim, customer, customer_address, customer_demographics, household_demographics where catalog_returns.cr_call_center_sk = call_center.cc_call_center_sk and catalog_returns.cr_returned_date_sk = date_dim.d_date_sk and catalog_returns.cr_returning_customer_sk= customer.c_customer_sk and customer_demographics.cd_demo_sk = customer.c_current_cdemo_sk and household_demographics.hd_demo_sk = customer.c_current_hdemo_sk and customer_address.ca_address_sk = customer.c_current_addr_sk and d_year = 1999 and d_moy = 11 and ( (cd_marital_status = 'M' and cd_education_status = 'Unknown') or(cd_marital_status = 'W' and cd_education_status = 'Advanced Degree')) and hd_buy_potential like '0-500%' and ca_gmt_offset = -7 group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status order by Returns_Loss desc;
+-- start query 1 in stream 0 using template query91.tpl and seed 1930872976
+explain
+select
+ cc_call_center_id Call_Center,
+ cc_name Call_Center_Name,
+ cc_manager Manager,
+ sum(cr_net_loss) Returns_Loss
+from
+ call_center,
+ catalog_returns,
+ date_dim,
+ customer,
+ customer_address,
+ customer_demographics,
+ household_demographics
+where
+ cr_call_center_sk = cc_call_center_sk
+and cr_returned_date_sk = d_date_sk
+and cr_returning_customer_sk= c_customer_sk
+and cd_demo_sk = c_current_cdemo_sk
+and hd_demo_sk = c_current_hdemo_sk
+and ca_address_sk = c_current_addr_sk
+and d_year = 1999
+and d_moy = 11
+and ( (cd_marital_status = 'M' and cd_education_status = 'Unknown')
+ or(cd_marital_status = 'W' and cd_education_status = 'Advanced Degree'))
+and hd_buy_potential like '0-500%'
+and ca_gmt_offset = -7
+group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status
+order by sum(cr_net_loss) desc;
+
+-- end query 1 in stream 0 using template query91.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query92.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query92.q b/ql/src/test/queries/clientpositive/perf/query92.q
index 625e99f..f26fa5e 100644
--- a/ql/src/test/queries/clientpositive/perf/query92.q
+++ b/ql/src/test/queries/clientpositive/perf/query92.q
@@ -1 +1,32 @@
-explain SELECT sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) as store_only, sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) as catalog_only, sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) as store_and_catalog FROM (SELECT ss.ss_customer_sk as customer_sk, ss.ss_item_sk as item_sk FROM store_sales ss JOIN date_dim d1 ON (ss.ss_sold_date_sk = d1.d_date_sk) WHERE d1.d_month_seq >= 1206 and d1.d_month_seq <= 1217 GROUP BY ss.ss_customer_sk, ss.ss_item_sk) ssci FULL OUTER JOIN (SELECT cs.cs_bill_customer_sk as customer_sk, cs.cs_item_sk as item_sk FROM catalog_sales cs JOIN date_dim d2 ON (cs.cs_sold_date_sk = d2.d_date_sk) WHERE d2.d_month_seq >= 1206 and d2.d_month_seq <= 1217 GROUP BY cs.cs_bill_customer_sk, cs.cs_item_sk) csci ON (ssci.customer_sk=csci.customer_sk and ssci.item_sk = csci.item_sk);
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query92.tpl and seed 2031708268
+explain
+select
+ sum(ws_ext_discount_amt) as `Excess Discount Amount`
+from
+ web_sales
+ ,item
+ ,date_dim
+where
+i_manufact_id = 269
+and i_item_sk = ws_item_sk
+and d_date between '1998-03-18' and
+ (cast('1998-03-18' as date) + 90 days)
+and d_date_sk = ws_sold_date_sk
+and ws_ext_discount_amt
+ > (
+ SELECT
+ 1.3 * avg(ws_ext_discount_amt)
+ FROM
+ web_sales
+ ,date_dim
+ WHERE
+ ws_item_sk = i_item_sk
+ and d_date between '1998-03-18' and
+ (cast('1998-03-18' as date) + 90 days)
+ and d_date_sk = ws_sold_date_sk
+ )
+order by sum(ws_ext_discount_amt)
+limit 100;
+
+-- end query 1 in stream 0 using template query92.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query93.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query93.q b/ql/src/test/queries/clientpositive/perf/query93.q
index b60b041..7f4a093 100644
--- a/ql/src/test/queries/clientpositive/perf/query93.q
+++ b/ql/src/test/queries/clientpositive/perf/query93.q
@@ -1 +1,20 @@
-explain select ss_customer_sk ,sum(act_sales) sumsales from (select ss_item_sk ,ss_ticket_number ,ss_customer_sk ,case when sr_return_quantity is not null then (ss_quantity-sr_return_quantity)*ss_sales_price else (ss_quantity*ss_sales_price) end act_sales from store_sales left outer join store_returns on (store_returns.sr_item_sk = store_sales.ss_item_sk and store_returns.sr_ticket_number = store_sales.ss_ticket_number) ,reason where store_returns.sr_reason_sk = reason.r_reason_sk and r_reason_desc = 'Did not like the warranty') t group by ss_customer_sk order by sumsales, ss_customer_sk limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query93.tpl and seed 1200409435
+explain
+select ss_customer_sk
+ ,sum(act_sales) sumsales
+ from (select ss_item_sk
+ ,ss_ticket_number
+ ,ss_customer_sk
+ ,case when sr_return_quantity is not null then (ss_quantity-sr_return_quantity)*ss_sales_price
+ else (ss_quantity*ss_sales_price) end act_sales
+ from store_sales left outer join store_returns on (sr_item_sk = ss_item_sk
+ and sr_ticket_number = ss_ticket_number)
+ ,reason
+ where sr_reason_sk = r_reason_sk
+ and r_reason_desc = 'Did not like the warranty') t
+ group by ss_customer_sk
+ order by sumsales, ss_customer_sk
+limit 100;
+
+-- end query 1 in stream 0 using template query93.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query94.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query94.q b/ql/src/test/queries/clientpositive/perf/query94.q
index f9f4bc1..18253fa 100644
--- a/ql/src/test/queries/clientpositive/perf/query94.q
+++ b/ql/src/test/queries/clientpositive/perf/query94.q
@@ -1 +1,31 @@
-explain SELECT count(distinct ws_order_number) as order_count, sum(ws_ext_ship_cost) as total_shipping_cost, sum(ws_net_profit) as total_net_profit FROM web_sales ws1 JOIN customer_address ca ON (ws1.ws_ship_addr_sk = ca.ca_address_sk) JOIN web_site s ON (ws1.ws_web_site_sk = s.web_site_sk) JOIN date_dim d ON (ws1.ws_ship_date_sk = d.d_date_sk) LEFT SEMI JOIN (SELECT ws2.ws_order_number as ws_order_number FROM web_sales ws2 JOIN web_sales ws3 ON (ws2.ws_order_number = ws3.ws_order_number) WHERE ws2.ws_warehouse_sk <> ws3.ws_warehouse_sk ) ws_wh1 ON (ws1.ws_order_number = ws_wh1.ws_order_number) LEFT OUTER JOIN web_returns wr1 ON (ws1.ws_order_number = wr1.wr_order_number) WHERE d.d_date between '1999-05-01' and '1999-07-01' and ca.ca_state = 'TX' and s.web_company_name = 'pri' and wr1.wr_order_number is null limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query94.tpl and seed 2031708268
+explain
+select
+ count(distinct ws_order_number) as `order count`
+ ,sum(ws_ext_ship_cost) as `total shipping cost`
+ ,sum(ws_net_profit) as `total net profit`
+from
+ web_sales ws1
+ ,date_dim
+ ,customer_address
+ ,web_site
+where
+ d_date between '1999-5-01' and
+ (cast('1999-5-01' as date) + 60 days)
+and ws1.ws_ship_date_sk = d_date_sk
+and ws1.ws_ship_addr_sk = ca_address_sk
+and ca_state = 'TX'
+and ws1.ws_web_site_sk = web_site_sk
+and web_company_name = 'pri'
+and exists (select *
+ from web_sales ws2
+ where ws1.ws_order_number = ws2.ws_order_number
+ and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
+and not exists(select *
+ from web_returns wr1
+ where ws1.ws_order_number = wr1.wr_order_number)
+order by count(distinct ws_order_number)
+limit 100;
+
+-- end query 1 in stream 0 using template query94.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query95.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query95.q b/ql/src/test/queries/clientpositive/perf/query95.q
index fbd2d47..e9024a8 100644
--- a/ql/src/test/queries/clientpositive/perf/query95.q
+++ b/ql/src/test/queries/clientpositive/perf/query95.q
@@ -1 +1,34 @@
-explain SELECT count(distinct ws1.ws_order_number) as order_count, sum(ws1.ws_ext_ship_cost) as total_shipping_cost, sum(ws1.ws_net_profit) as total_net_profit FROM web_sales ws1 JOIN customer_address ca ON (ws1.ws_ship_addr_sk = ca.ca_address_sk) JOIN web_site s ON (ws1.ws_web_site_sk = s.web_site_sk) JOIN date_dim d ON (ws1.ws_ship_date_sk = d.d_date_sk) LEFT SEMI JOIN (SELECT ws2.ws_order_number as ws_order_number FROM web_sales ws2 JOIN web_sales ws3 ON (ws2.ws_order_number = ws3.ws_order_number) WHERE ws2.ws_warehouse_sk <> ws3.ws_warehouse_sk ) ws_wh1 ON (ws1.ws_order_number = ws_wh1.ws_order_number) LEFT SEMI JOIN (SELECT wr_order_number FROM web_returns wr JOIN (SELECT ws4.ws_order_number as ws_order_number FROM web_sales ws4 JOIN web_sales ws5 ON (ws4.ws_order_number = ws5.ws_order_number) WHERE ws4.ws_warehouse_sk <> ws5.ws_warehouse_sk ) ws_wh2 ON (wr.wr_order_number = ws_wh2.ws_order_number)) tmp1 ON (ws1.ws_order_number = tmp1.wr_order_number) WHERE d.d_date between '20
02-05-01' and '2002-06-30' and ca.ca_state = 'GA' and s.web_company_name = 'pri';
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query95.tpl and seed 2031708268
+explain
+with ws_wh as
+(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2
+ from web_sales ws1,web_sales ws2
+ where ws1.ws_order_number = ws2.ws_order_number
+ and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
+ select
+ count(distinct ws_order_number) as `order count`
+ ,sum(ws_ext_ship_cost) as `total shipping cost`
+ ,sum(ws_net_profit) as `total net profit`
+from
+ web_sales ws1
+ ,date_dim
+ ,customer_address
+ ,web_site
+where
+ d_date between '1999-5-01' and
+ (cast('1999-5-01' as date) + 60 days)
+and ws1.ws_ship_date_sk = d_date_sk
+and ws1.ws_ship_addr_sk = ca_address_sk
+and ca_state = 'TX'
+and ws1.ws_web_site_sk = web_site_sk
+and web_company_name = 'pri'
+and ws1.ws_order_number in (select ws_order_number
+ from ws_wh)
+and ws1.ws_order_number in (select wr_order_number
+ from web_returns,ws_wh
+ where wr_order_number = ws_wh.ws_order_number)
+order by count(distinct ws_order_number)
+limit 100;
+
+-- end query 1 in stream 0 using template query95.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query96.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query96.q b/ql/src/test/queries/clientpositive/perf/query96.q
index 29265ed..a306d6c 100644
--- a/ql/src/test/queries/clientpositive/perf/query96.q
+++ b/ql/src/test/queries/clientpositive/perf/query96.q
@@ -1 +1,18 @@
-explain select count(*) as c from store_sales ,household_demographics ,time_dim, store where store_sales.ss_sold_time_sk = time_dim.t_time_sk and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk and store_sales.ss_store_sk = store.s_store_sk and time_dim.t_hour = 8 and time_dim.t_minute >= 30 and household_demographics.hd_dep_count = 5 and store.s_store_name = 'ese' order by c limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query96.tpl and seed 1819994127
+explain
+select count(*)
+from store_sales
+ ,household_demographics
+ ,time_dim, store
+where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 8
+ and time_dim.t_minute >= 30
+ and household_demographics.hd_dep_count = 5
+ and store.s_store_name = 'ese'
+order by count(*)
+limit 100;
+
+-- end query 1 in stream 0 using template query96.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query97.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query97.q b/ql/src/test/queries/clientpositive/perf/query97.q
index 4995309..7203e52 100644
--- a/ql/src/test/queries/clientpositive/perf/query97.q
+++ b/ql/src/test/queries/clientpositive/perf/query97.q
@@ -1 +1,27 @@
-explain select sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) store_only ,sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) catalog_only ,sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) store_and_catalog from ( select ss_customer_sk customer_sk ,ss_item_sk item_sk from store_sales JOIN date_dim ON store_sales.ss_sold_date_sk = date_dim.d_date_sk where d_month_seq between 1193 and 1193 + 11 group by ss_customer_sk ,ss_item_sk) ssci full outer join ( select cs_bill_customer_sk customer_sk ,cs_item_sk item_sk from catalog_sales JOIN date_dim ON catalog_sales.cs_sold_date_sk = date_dim.d_date_sk where d_month_seq between 1193 and 1193 + 11 group by cs_bill_customer_sk ,cs_item_sk) csci on (ssci.customer_sk=csci.customer_sk and ssci.item_sk = csci.item_sk) limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query97.tpl and seed 1819994127
+explain
+with ssci as (
+select ss_customer_sk customer_sk
+ ,ss_item_sk item_sk
+from store_sales,date_dim
+where ss_sold_date_sk = d_date_sk
+ and d_month_seq between 1212 and 1212 + 11
+group by ss_customer_sk
+ ,ss_item_sk),
+csci as(
+ select cs_bill_customer_sk customer_sk
+ ,cs_item_sk item_sk
+from catalog_sales,date_dim
+where cs_sold_date_sk = d_date_sk
+ and d_month_seq between 1212 and 1212 + 11
+group by cs_bill_customer_sk
+ ,cs_item_sk)
+ select sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) store_only
+ ,sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) catalog_only
+ ,sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) store_and_catalog
+from ssci full outer join csci on (ssci.customer_sk=csci.customer_sk
+ and ssci.item_sk = csci.item_sk)
+limit 100;
+
+-- end query 1 in stream 0 using template query97.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/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 ec4b553..6168f2a 100644
--- a/ql/src/test/queries/clientpositive/perf/query98.q
+++ b/ql/src/test/queries/clientpositive/perf/query98.q
@@ -1,7 +1,34 @@
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-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;
+-- start query 1 in stream 0 using template query98.tpl and seed 345591136
+explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,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
+ ss_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and ss_sold_date_sk = 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;
+
+-- end query 1 in stream 0 using template query98.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query99.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query99.q b/ql/src/test/queries/clientpositive/perf/query99.q
new file mode 100644
index 0000000..83be1d0
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query99.q
@@ -0,0 +1,37 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query99.tpl and seed 1819994127
+explain
+select
+ substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,cc_name
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk <= 30 ) then 1 else 0 end) as `30 days`
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 30) and
+ (cs_ship_date_sk - cs_sold_date_sk <= 60) then 1 else 0 end ) as `31-60 days`
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 60) and
+ (cs_ship_date_sk - cs_sold_date_sk <= 90) then 1 else 0 end) as `61-90 days`
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 90) and
+ (cs_ship_date_sk - cs_sold_date_sk <= 120) then 1 else 0 end) as `91-120 days`
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 120) then 1 else 0 end) as `>120 days`
+from
+ catalog_sales
+ ,warehouse
+ ,ship_mode
+ ,call_center
+ ,date_dim
+where
+ d_month_seq between 1212 and 1212 + 11
+and cs_ship_date_sk = d_date_sk
+and cs_warehouse_sk = w_warehouse_sk
+and cs_ship_mode_sk = sm_ship_mode_sk
+and cs_call_center_sk = cc_call_center_sk
+group by
+ substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,cc_name
+order by substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,cc_name
+limit 100;
+
+-- end query 1 in stream 0 using template query99.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query1.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query1.q.out b/ql/src/test/results/clientpositive/perf/query1.q.out
index 7c583e4..da4a65c 100644
--- a/ql/src/test/results/clientpositive/perf/query1.q.out
+++ b/ql/src/test/results/clientpositive/perf/query1.q.out
@@ -1,4 +1,5 @@
-PREHOOK: query: explain with customer_total_return as
+PREHOOK: query: explain
+with customer_total_return as
(select sr_customer_sk as ctr_customer_sk
,sr_store_sk as ctr_store_sk
,sum(SR_FEE) as ctr_total_return
@@ -21,7 +22,8 @@ and ctr1.ctr_customer_sk = c_customer_sk
order by c_customer_id
limit 100
PREHOOK: type: QUERY
-POSTHOOK: query: explain with customer_total_return as
+POSTHOOK: query: explain
+with customer_total_return as
(select sr_customer_sk as ctr_customer_sk
,sr_store_sk as ctr_store_sk
,sum(SR_FEE) as ctr_total_return
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/results/clientpositive/perf/query10.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query10.q.out b/ql/src/test/results/clientpositive/perf/query10.q.out
new file mode 100644
index 0000000..9b6621c
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query10.q.out
@@ -0,0 +1,296 @@
+PREHOOK: query: explain
+select
+ cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ count(*) cnt1,
+ cd_purchase_estimate,
+ count(*) cnt2,
+ cd_credit_rating,
+ count(*) cnt3,
+ cd_dep_count,
+ count(*) cnt4,
+ cd_dep_employed_count,
+ count(*) cnt5,
+ cd_dep_college_count,
+ count(*) cnt6
+ from
+ customer c,customer_address ca,customer_demographics
+ where
+ c.c_current_addr_sk = ca.ca_address_sk and
+ ca_county in ('Walker County','Richland County','Gaines County','Douglas County','Dona Ana County') and
+ cd_demo_sk = c.c_current_cdemo_sk and
+ exists (select *
+ from store_sales,date_dim
+ where c.c_customer_sk = ss_customer_sk and
+ ss_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 and 4+3) and
+ (exists (select *
+ from web_sales,date_dim
+ where c.c_customer_sk = ws_bill_customer_sk and
+ ws_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 ANd 4+3) or
+ exists (select *
+ from catalog_sales,date_dim
+ where c.c_customer_sk = cs_ship_customer_sk and
+ cs_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 and 4+3))
+ group by cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ cd_purchase_estimate,
+ cd_credit_rating,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+ order by cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ cd_purchase_estimate,
+ cd_credit_rating,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+select
+ cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ count(*) cnt1,
+ cd_purchase_estimate,
+ count(*) cnt2,
+ cd_credit_rating,
+ count(*) cnt3,
+ cd_dep_count,
+ count(*) cnt4,
+ cd_dep_employed_count,
+ count(*) cnt5,
+ cd_dep_college_count,
+ count(*) cnt6
+ from
+ customer c,customer_address ca,customer_demographics
+ where
+ c.c_current_addr_sk = ca.ca_address_sk and
+ ca_county in ('Walker County','Richland County','Gaines County','Douglas County','Dona Ana County') and
+ cd_demo_sk = c.c_current_cdemo_sk and
+ exists (select *
+ from store_sales,date_dim
+ where c.c_customer_sk = ss_customer_sk and
+ ss_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 and 4+3) and
+ (exists (select *
+ from web_sales,date_dim
+ where c.c_customer_sk = ws_bill_customer_sk and
+ ws_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 ANd 4+3) or
+ exists (select *
+ from catalog_sales,date_dim
+ where c.c_customer_sk = cs_ship_customer_sk and
+ cs_sold_date_sk = d_date_sk and
+ d_year = 2002 and
+ d_moy between 4 and 4+3))
+ group by cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ cd_purchase_estimate,
+ cd_credit_rating,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+ order by cd_gender,
+ cd_marital_status,
+ cd_education_status,
+ cd_purchase_estimate,
+ cd_credit_rating,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 11 <- Map 10 (SIMPLE_EDGE), Map 13 (SIMPLE_EDGE)
+Reducer 12 <- Reducer 11 (SIMPLE_EDGE)
+Reducer 14 <- Map 13 (SIMPLE_EDGE), Map 18 (SIMPLE_EDGE)
+Reducer 15 <- Reducer 14 (SIMPLE_EDGE)
+Reducer 16 <- Map 13 (SIMPLE_EDGE), Map 19 (SIMPLE_EDGE)
+Reducer 17 <- Reducer 16 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 12 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 17 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 7
+ File Output Operator [FS_74]
+ Limit [LIM_73] (rows=100 width=88)
+ Number of rows:100
+ Select Operator [SEL_72] (rows=383325119 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13"]
+ <-Reducer 6 [SIMPLE_EDGE]
+ SHUFFLE [RS_71]
+ Select Operator [SEL_70] (rows=383325119 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col6","_col8","_col10","_col12"]
+ Group By Operator [GBY_69] (rows=383325119 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["count(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6, KEY._col7
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_68]
+ PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7
+ Group By Operator [GBY_67] (rows=766650239 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["count()"],keys:_col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13
+ Select Operator [SEL_66] (rows=766650239 width=88)
+ Output:["_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13"]
+ Filter Operator [FIL_65] (rows=766650239 width=88)
+ predicate:(_col16 is not null or _col18 is not null)
+ Merge Join Operator [MERGEJOIN_112] (rows=766650239 width=88)
+ Conds:RS_62._col0=RS_63._col0(Left Outer),Output:["_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col16","_col18"]
+ <-Reducer 17 [SIMPLE_EDGE]
+ SHUFFLE [RS_63]
+ PartitionCols:_col0
+ Select Operator [SEL_61] (rows=158394413 width=135)
+ Output:["_col0","_col1"]
+ Group By Operator [GBY_60] (rows=158394413 width=135)
+ Output:["_col0"],keys:KEY._col0
+ <-Reducer 16 [SIMPLE_EDGE]
+ SHUFFLE [RS_59]
+ PartitionCols:_col0
+ Group By Operator [GBY_58] (rows=316788826 width=135)
+ Output:["_col0"],keys:_col1
+ Merge Join Operator [MERGEJOIN_110] (rows=316788826 width=135)
+ Conds:RS_54._col0=RS_55._col0(Inner),Output:["_col1"]
+ <-Map 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_55]
+ PartitionCols:_col0
+ Select Operator [SEL_53] (rows=4058 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_105] (rows=4058 width=1119)
+ predicate:((d_year = 2002) and d_moy BETWEEN 4 AND 7 and d_date_sk is not null)
+ TableScan [TS_12] (rows=73049 width=1119)
+ default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"]
+ <-Map 19 [SIMPLE_EDGE]
+ SHUFFLE [RS_54]
+ PartitionCols:_col0
+ Select Operator [SEL_50] (rows=287989836 width=135)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_104] (rows=287989836 width=135)
+ predicate:(cs_ship_customer_sk is not null and cs_sold_date_sk is not null)
+ TableScan [TS_48] (rows=287989836 width=135)
+ default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_ship_customer_sk"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_62]
+ PartitionCols:_col0
+ Select Operator [SEL_47] (rows=696954748 width=88)
+ Output:["_col0","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col16"]
+ Merge Join Operator [MERGEJOIN_111] (rows=696954748 width=88)
+ Conds:RS_43._col0=RS_44._col0(Left Outer),RS_43._col0=RS_45._col0(Inner),Output:["_col0","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col15"]
+ <-Reducer 12 [SIMPLE_EDGE]
+ SHUFFLE [RS_44]
+ PartitionCols:_col0
+ Select Operator [SEL_22] (rows=79201469 width=135)
+ Output:["_col0","_col1"]
+ Group By Operator [GBY_21] (rows=79201469 width=135)
+ Output:["_col0"],keys:KEY._col0
+ <-Reducer 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_20]
+ PartitionCols:_col0
+ Group By Operator [GBY_19] (rows=158402938 width=135)
+ Output:["_col0"],keys:_col1
+ Merge Join Operator [MERGEJOIN_108] (rows=158402938 width=135)
+ Conds:RS_15._col0=RS_16._col0(Inner),Output:["_col1"]
+ <-Map 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_16]
+ PartitionCols:_col0
+ Select Operator [SEL_14] (rows=4058 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_101] (rows=4058 width=1119)
+ predicate:((d_year = 2002) and d_moy BETWEEN 4 AND 7 and d_date_sk is not null)
+ Please refer to the previous TableScan [TS_12]
+ <-Map 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_15]
+ PartitionCols:_col0
+ Select Operator [SEL_11] (rows=144002668 width=135)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_100] (rows=144002668 width=135)
+ predicate:(ws_bill_customer_sk is not null and ws_sold_date_sk is not null)
+ TableScan [TS_9] (rows=144002668 width=135)
+ default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_bill_customer_sk"]
+ <-Reducer 15 [SIMPLE_EDGE]
+ SHUFFLE [RS_45]
+ PartitionCols:_col0
+ Group By Operator [GBY_35] (rows=316797606 width=88)
+ Output:["_col0"],keys:KEY._col0
+ <-Reducer 14 [SIMPLE_EDGE]
+ SHUFFLE [RS_34]
+ PartitionCols:_col0
+ Group By Operator [GBY_33] (rows=633595212 width=88)
+ Output:["_col0"],keys:_col1
+ Merge Join Operator [MERGEJOIN_109] (rows=633595212 width=88)
+ Conds:RS_29._col0=RS_30._col0(Inner),Output:["_col1"]
+ <-Map 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_30]
+ PartitionCols:_col0
+ Select Operator [SEL_28] (rows=4058 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_103] (rows=4058 width=1119)
+ predicate:((d_year = 2002) and d_moy BETWEEN 4 AND 7 and d_date_sk is not null)
+ Please refer to the previous TableScan [TS_12]
+ <-Map 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_29]
+ PartitionCols:_col0
+ Select Operator [SEL_25] (rows=575995635 width=88)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_102] (rows=575995635 width=88)
+ predicate:(ss_customer_sk is not null and ss_sold_date_sk is not null)
+ TableScan [TS_23] (rows=575995635 width=88)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_customer_sk"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_43]
+ PartitionCols:_col0
+ Merge Join Operator [MERGEJOIN_107] (rows=96800003 width=860)
+ Conds:RS_40._col1=RS_41._col0(Inner),Output:["_col0","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13"]
+ <-Map 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_41]
+ PartitionCols:_col0
+ Select Operator [SEL_8] (rows=1861800 width=385)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
+ Filter Operator [FIL_99] (rows=1861800 width=385)
+ predicate:cd_demo_sk is not null
+ TableScan [TS_6] (rows=1861800 width=385)
+ default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status","cd_purchase_estimate","cd_credit_rating","cd_dep_count","cd_dep_employed_count","cd_dep_college_count"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_40]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_106] (rows=88000001 width=860)
+ Conds:RS_37._col2=RS_38._col0(Inner),Output:["_col0","_col1"]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_37]
+ PartitionCols:_col2
+ Select Operator [SEL_2] (rows=80000000 width=860)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_97] (rows=80000000 width=860)
+ predicate:(c_current_addr_sk is not null and c_current_cdemo_sk is not null)
+ TableScan [TS_0] (rows=80000000 width=860)
+ default@customer,c,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_current_cdemo_sk","c_current_addr_sk"]
+ <-Map 8 [SIMPLE_EDGE]
+ SHUFFLE [RS_38]
+ PartitionCols:_col0
+ Select Operator [SEL_5] (rows=20000000 width=1014)
+ Output:["_col0"]
+ Filter Operator [FIL_98] (rows=20000000 width=1014)
+ predicate:((ca_county) IN ('Walker County', 'Richland County', 'Gaines County', 'Douglas County', 'Dona Ana County') and ca_address_sk is not null)
+ TableScan [TS_3] (rows=40000000 width=1014)
+ default@customer_address,ca,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_county"]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/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 fb6d1af..0506eca 100644
--- a/ql/src/test/results/clientpositive/perf/query12.q.out
+++ b/ql/src/test/results/clientpositive/perf/query12.q.out
@@ -1,27 +1,69 @@
-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
+PREHOOK: query: explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,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
+ ws_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and ws_sold_date_sk = 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
+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
+POSTHOOK: query: explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,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
+ ws_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and ws_sold_date_sk = 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
+limit 100
POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
Vertex dependency in root stage
Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE)
Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
@@ -31,62 +73,66 @@ Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
Stage-0
Fetch Operator
- limit:100
+ limit:-1
Stage-1
Reducer 6
- File Output Operator [FS_26]
- Limit [LIM_25] (rows=100 width=135)
+ File Output Operator [FS_29]
+ Limit [LIM_27] (rows=100 width=135)
Number of rows:100
- Select Operator [SEL_24] (rows=21780404 width=135)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Select Operator [SEL_26] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
<-Reducer 5 [SIMPLE_EDGE]
- SHUFFLE [RS_23]
- Select Operator [SEL_21] (rows=21780404 width=135)
+ SHUFFLE [RS_25]
+ Select Operator [SEL_23] (rows=87121617 width=135)
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
- PTF Operator [PTF_20] (rows=21780404 width=135)
+ PTF Operator [PTF_22] (rows=87121617 width=135)
Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col3 ASC NULLS FIRST","partition by:":"_col3"}]
- Select Operator [SEL_19] (rows=21780404 width=135)
+ Select Operator [SEL_21] (rows=87121617 width=135)
Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
<-Reducer 4 [SIMPLE_EDGE]
- SHUFFLE [RS_18]
+ SHUFFLE [RS_20]
PartitionCols:_col3
- Group By Operator [GBY_17] (rows=21780404 width=135)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4
- <-Reducer 3 [SIMPLE_EDGE]
- SHUFFLE [RS_16]
- PartitionCols:_col0, _col1, _col2, _col3, _col4
- Group By Operator [GBY_15] (rows=43560808 width=135)
- Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col23)"],keys:_col38, _col41, _col49, _col47, _col42
- Select Operator [SEL_14] (rows=43560808 width=135)
- Output:["_col23","_col38","_col41","_col42","_col47","_col49"]
- Filter Operator [FIL_32] (rows=43560808 width=135)
- predicate:((_col3 = _col37) and (_col0 = _col62))
- Merge Join Operator [MERGEJOIN_37] (rows=174243235 width=135)
- Conds:RS_9._col0=RS_11.d_date_sk(Inner),Output:["_col0","_col3","_col23","_col37","_col38","_col41","_col42","_col47","_col49","_col62"]
- <-Map 8 [SIMPLE_EDGE]
- 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 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]
- SHUFFLE [RS_9]
- PartitionCols:_col0
- Merge Join Operator [MERGEJOIN_36] (rows=158402938 width=135)
- Conds:RS_4.ws_item_sk=RS_6.i_item_sk(Inner),Output:["_col0","_col3","_col23","_col37","_col38","_col41","_col42","_col47","_col49"]
- <-Map 1 [SIMPLE_EDGE]
- SHUFFLE [RS_4]
- PartitionCols:ws_item_sk
- Filter Operator [FIL_33] (rows=144002668 width=135)
+ Select Operator [SEL_19] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Group By Operator [GBY_18] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_17]
+ PartitionCols:_col0, _col1, _col2, _col3, _col4
+ Group By Operator [GBY_16] (rows=174243235 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)"],keys:_col10, _col9, _col6, _col7, _col8
+ Merge Join Operator [MERGEJOIN_39] (rows=174243235 width=135)
+ Conds:RS_12._col1=RS_13._col0(Inner),Output:["_col2","_col6","_col7","_col8","_col9","_col10"]
+ <-Map 8 [SIMPLE_EDGE]
+ SHUFFLE [RS_13]
+ PartitionCols:_col0
+ Select Operator [SEL_8] (rows=231000 width=1436)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Filter Operator [FIL_37] (rows=231000 width=1436)
+ predicate:((i_category) IN ('Jewelry', 'Sports', 'Books') and i_item_sk is not null)
+ TableScan [TS_6] (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_class","i_category"]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_12]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_38] (rows=158402938 width=135)
+ Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col1","_col2"]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_9]
+ PartitionCols:_col0
+ Select Operator [SEL_2] (rows=144002668 width=135)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_35] (rows=144002668 width=135)
predicate:(ws_item_sk is not null and ws_sold_date_sk is not null)
TableScan [TS_0] (rows=144002668 width=135)
default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_item_sk","ws_ext_sales_price"]
- <-Map 7 [SIMPLE_EDGE]
- SHUFFLE [RS_6]
- PartitionCols:i_item_sk
- Filter Operator [FIL_34] (rows=231000 width=1436)
- predicate:(i_item_sk is not null and (i_category) IN ('Jewelry', 'Sports', 'Books'))
- TableScan [TS_1] (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_class","i_category"]
+ <-Map 7 [SIMPLE_EDGE]
+ SHUFFLE [RS_10]
+ PartitionCols:_col0
+ Select Operator [SEL_5] (rows=8116 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_36] (rows=8116 width=1119)
+ predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 2001-01-12 00:00:00.0 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"]