hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From w...@apache.org
Subject [14/17] hive git commit: HIVE-16764: Support numeric as same as decimal (Pengcheng Xiong, reviewed by Ashutosh Chauhan)
Date Wed, 31 May 2017 00:11:52 GMT
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"]
 


Mime
View raw message