http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query67.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query67.q b/ql/src/test/queries/clientpositive/perf/cbo_query67.q new file mode 100644 index 0000000..5781aac --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query67.q @@ -0,0 +1,46 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query67.tpl and seed 1819994127 +explain cbo +select * +from (select i_category + ,i_class + ,i_brand + ,i_product_name + ,d_year + ,d_qoy + ,d_moy + ,s_store_id + ,sumsales + ,rank() over (partition by i_category order by sumsales desc) rk + from (select i_category + ,i_class + ,i_brand + ,i_product_name + ,d_year + ,d_qoy + ,d_moy + ,s_store_id + ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales + from store_sales + ,date_dim + ,store + ,item + where ss_sold_date_sk=d_date_sk + and ss_item_sk=i_item_sk + and ss_store_sk = s_store_sk + and d_month_seq between 1212 and 1212+11 + group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2 +where rk <= 100 +order by i_category + ,i_class + ,i_brand + ,i_product_name + ,d_year + ,d_qoy + ,d_moy + ,s_store_id + ,sumsales + ,rk +limit 100; + +-- end query 1 in stream 0 using template query67.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query68.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query68.q b/ql/src/test/queries/clientpositive/perf/cbo_query68.q new file mode 100644 index 0000000..520b9d7 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query68.q @@ -0,0 +1,44 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query68.tpl and seed 803547492 +explain cbo +select c_last_name + ,c_first_name + ,ca_city + ,bought_city + ,ss_ticket_number + ,extended_price + ,extended_tax + ,list_price + from (select ss_ticket_number + ,ss_customer_sk + ,ca_city bought_city + ,sum(ss_ext_sales_price) extended_price + ,sum(ss_ext_list_price) list_price + ,sum(ss_ext_tax) extended_tax + from store_sales + ,date_dim + ,store + ,household_demographics + ,customer_address + 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 store_sales.ss_addr_sk = customer_address.ca_address_sk + and date_dim.d_dom between 1 and 2 + and (household_demographics.hd_dep_count = 2 or + household_demographics.hd_vehicle_count= 1) + and date_dim.d_year in (1998,1998+1,1998+2) + and store.s_city in ('Cedar Grove','Wildwood') + group by ss_ticket_number + ,ss_customer_sk + ,ss_addr_sk,ca_city) dn + ,customer + ,customer_address current_addr + where ss_customer_sk = c_customer_sk + and customer.c_current_addr_sk = current_addr.ca_address_sk + and current_addr.ca_city <> bought_city + order by c_last_name + ,ss_ticket_number + limit 100; + +-- end query 1 in stream 0 using template query68.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query69.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query69.q b/ql/src/test/queries/clientpositive/perf/cbo_query69.q new file mode 100644 index 0000000..01183fb --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query69.q @@ -0,0 +1,49 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query69.tpl and seed 797269820 +explain cbo +select + cd_gender, + cd_marital_status, + cd_education_status, + count(*) cnt1, + cd_purchase_estimate, + count(*) cnt2, + cd_credit_rating, + count(*) cnt3 + from + customer c,customer_address ca,customer_demographics + where + c.c_current_addr_sk = ca.ca_address_sk and + ca_state in ('CO','IL','MN') 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 = 1999 and + d_moy between 1 and 1+2) and + (not 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 = 1999 and + d_moy between 1 and 1+2) and + not 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 = 1999 and + d_moy between 1 and 1+2)) + group by cd_gender, + cd_marital_status, + cd_education_status, + cd_purchase_estimate, + cd_credit_rating + order by cd_gender, + cd_marital_status, + cd_education_status, + cd_purchase_estimate, + cd_credit_rating + limit 100; + +-- end query 1 in stream 0 using template query69.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query7.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query7.q b/ql/src/test/queries/clientpositive/perf/cbo_query7.q new file mode 100644 index 0000000..3fb7130 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query7.q @@ -0,0 +1,23 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query7.tpl and seed 1930872976 +explain cbo +select i_item_id, + avg(ss_quantity) agg1, + avg(ss_list_price) agg2, + avg(ss_coupon_amt) agg3, + avg(ss_sales_price) agg4 + from store_sales, customer_demographics, date_dim, item, promotion + where ss_sold_date_sk = d_date_sk and + ss_item_sk = i_item_sk and + ss_cdemo_sk = cd_demo_sk and + ss_promo_sk = p_promo_sk and + cd_gender = 'F' and + cd_marital_status = 'W' and + cd_education_status = 'Primary' and + (p_channel_email = 'N' or p_channel_event = 'N') and + d_year = 1998 + group by i_item_id + order by i_item_id + limit 100; + +-- end query 1 in stream 0 using template query7.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query70.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query70.q b/ql/src/test/queries/clientpositive/perf/cbo_query70.q new file mode 100644 index 0000000..5b8b76e --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query70.q @@ -0,0 +1,40 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query70.tpl and seed 1819994127 +explain cbo +select + sum(ss_net_profit) as total_sum + ,s_state + ,s_county + ,grouping(s_state)+grouping(s_county) as lochierarchy + ,rank() over ( + partition by grouping(s_state)+grouping(s_county), + case when grouping(s_county) = 0 then s_state end + order by sum(ss_net_profit) desc) as rank_within_parent + from + store_sales + ,date_dim d1 + ,store + where + d1.d_month_seq between 1212 and 1212+11 + and d1.d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + and s_state in + ( select s_state + from (select s_state as s_state, + rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking + from store_sales, store, date_dim + where d_month_seq between 1212 and 1212+11 + and d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + group by s_state + ) tmp1 + where ranking <= 5 + ) + group by rollup(s_state,s_county) + order by + lochierarchy desc + ,case when lochierarchy = 0 then s_state end + ,rank_within_parent + limit 100; + +-- end query 1 in stream 0 using template query70.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query71.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query71.q b/ql/src/test/queries/clientpositive/perf/cbo_query71.q new file mode 100644 index 0000000..e4d4df5 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query71.q @@ -0,0 +1,42 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query71.tpl and seed 2031708268 +explain cbo +select i_brand_id brand_id, i_brand brand,t_hour,t_minute, + sum(ext_price) ext_price + from item, (select ws_ext_sales_price as ext_price, + ws_sold_date_sk as sold_date_sk, + ws_item_sk as sold_item_sk, + ws_sold_time_sk as time_sk + from web_sales,date_dim + where d_date_sk = ws_sold_date_sk + and d_moy=12 + and d_year=2001 + union all + select cs_ext_sales_price as ext_price, + cs_sold_date_sk as sold_date_sk, + cs_item_sk as sold_item_sk, + cs_sold_time_sk as time_sk + from catalog_sales,date_dim + where d_date_sk = cs_sold_date_sk + and d_moy=12 + and d_year=2001 + union all + select ss_ext_sales_price as ext_price, + ss_sold_date_sk as sold_date_sk, + ss_item_sk as sold_item_sk, + ss_sold_time_sk as time_sk + from store_sales,date_dim + where d_date_sk = ss_sold_date_sk + and d_moy=12 + and d_year=2001 + ) as tmp,time_dim + where + sold_item_sk = i_item_sk + and i_manager_id=1 + and time_sk = t_time_sk + and (t_meal_time = 'breakfast' or t_meal_time = 'dinner') + group by i_brand, i_brand_id,t_hour,t_minute + order by ext_price desc, i_brand_id + ; + +-- end query 1 in stream 0 using template query71.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query72.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query72.q b/ql/src/test/queries/clientpositive/perf/cbo_query72.q new file mode 100644 index 0000000..941a643 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query72.q @@ -0,0 +1,33 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query72.tpl and seed 2031708268 +explain cbo +select i_item_desc + ,w_warehouse_name + ,d1.d_week_seq + ,count(case when p_promo_sk is null then 1 else 0 end) no_promo + ,count(case when p_promo_sk is not null then 1 else 0 end) promo + ,count(*) total_cnt +from catalog_sales +join inventory on (cs_item_sk = inv_item_sk) +join warehouse on (w_warehouse_sk=inv_warehouse_sk) +join item on (i_item_sk = cs_item_sk) +join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk) +join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk) +join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk) +join date_dim d2 on (inv_date_sk = d2.d_date_sk) +join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk) +left outer join promotion on (cs_promo_sk=p_promo_sk) +left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number) +where d1.d_week_seq = d2.d_week_seq + and inv_quantity_on_hand < cs_quantity + and d3.d_date > d1.d_date + 5 + and hd_buy_potential = '1001-5000' + and d1.d_year = 2001 + and hd_buy_potential = '1001-5000' + and cd_marital_status = 'M' + and d1.d_year = 2001 +group by i_item_desc,w_warehouse_name,d1.d_week_seq +order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq +limit 100; + +-- end query 1 in stream 0 using template query72.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query73.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query73.q b/ql/src/test/queries/clientpositive/perf/cbo_query73.q new file mode 100644 index 0000000..c78225e --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query73.q @@ -0,0 +1,30 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query73.tpl and seed 1971067816 +explain cbo +select c_last_name + ,c_first_name + ,c_salutation + ,c_preferred_cust_flag + ,ss_ticket_number + ,cnt from + (select ss_ticket_number + ,ss_customer_sk + ,count(*) cnt + 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 date_dim.d_dom between 1 and 2 + and (household_demographics.hd_buy_potential = '>10000' or + household_demographics.hd_buy_potential = 'unknown') + and household_demographics.hd_vehicle_count > 0 + and case when household_demographics.hd_vehicle_count > 0 then + household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count else null end > 1 + and date_dim.d_year in (2000,2000+1,2000+2) + and store.s_county in ('Mobile County','Maverick County','Huron County','Kittitas County') + group by ss_ticket_number,ss_customer_sk) dj,customer + where ss_customer_sk = c_customer_sk + and cnt between 1 and 5 + order by cnt desc; + +-- end query 1 in stream 0 using template query73.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query74.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query74.q b/ql/src/test/queries/clientpositive/perf/cbo_query74.q new file mode 100644 index 0000000..71954c8 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query74.q @@ -0,0 +1,63 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query74.tpl and seed 1556717815 +explain cbo +with year_total as ( + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,d_year as year + ,max(ss_net_paid) year_total + ,'s' sale_type + from customer + ,store_sales + ,date_dim + where c_customer_sk = ss_customer_sk + and ss_sold_date_sk = d_date_sk + and d_year in (2001,2001+1) + group by c_customer_id + ,c_first_name + ,c_last_name + ,d_year + union all + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,d_year as year + ,max(ws_net_paid) year_total + ,'w' sale_type + from customer + ,web_sales + ,date_dim + where c_customer_sk = ws_bill_customer_sk + and ws_sold_date_sk = d_date_sk + and d_year in (2001,2001+1) + group by c_customer_id + ,c_first_name + ,c_last_name + ,d_year + ) + select + t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name + from year_total t_s_firstyear + ,year_total t_s_secyear + ,year_total t_w_firstyear + ,year_total t_w_secyear + where t_s_secyear.customer_id = t_s_firstyear.customer_id + and t_s_firstyear.customer_id = t_w_secyear.customer_id + and t_s_firstyear.customer_id = t_w_firstyear.customer_id + and t_s_firstyear.sale_type = 's' + and t_w_firstyear.sale_type = 'w' + and t_s_secyear.sale_type = 's' + and t_w_secyear.sale_type = 'w' + and t_s_firstyear.year = 2001 + and t_s_secyear.year = 2001+1 + and t_w_firstyear.year = 2001 + and t_w_secyear.year = 2001+1 + and t_s_firstyear.year_total > 0 + and t_w_firstyear.year_total > 0 + and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end + > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end + order by 2,1,3 +limit 100; + +-- end query 1 in stream 0 using template query74.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query75.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query75.q b/ql/src/test/queries/clientpositive/perf/cbo_query75.q new file mode 100644 index 0000000..2d92853 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query75.q @@ -0,0 +1,72 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query75.tpl and seed 1819994127 +explain cbo +WITH all_sales AS ( + SELECT d_year + ,i_brand_id + ,i_class_id + ,i_category_id + ,i_manufact_id + ,SUM(sales_cnt) AS sales_cnt + ,SUM(sales_amt) AS sales_amt + FROM (SELECT d_year + ,i_brand_id + ,i_class_id + ,i_category_id + ,i_manufact_id + ,cs_quantity - COALESCE(cr_return_quantity,0) AS sales_cnt + ,cs_ext_sales_price - COALESCE(cr_return_amount,0.0) AS sales_amt + FROM catalog_sales JOIN item ON i_item_sk=cs_item_sk + JOIN date_dim ON d_date_sk=cs_sold_date_sk + LEFT JOIN catalog_returns ON (cs_order_number=cr_order_number + AND cs_item_sk=cr_item_sk) + WHERE i_category='Sports' + UNION + SELECT d_year + ,i_brand_id + ,i_class_id + ,i_category_id + ,i_manufact_id + ,ss_quantity - COALESCE(sr_return_quantity,0) AS sales_cnt + ,ss_ext_sales_price - COALESCE(sr_return_amt,0.0) AS sales_amt + FROM store_sales JOIN item ON i_item_sk=ss_item_sk + JOIN date_dim ON d_date_sk=ss_sold_date_sk + LEFT JOIN store_returns ON (ss_ticket_number=sr_ticket_number + AND ss_item_sk=sr_item_sk) + WHERE i_category='Sports' + UNION + SELECT d_year + ,i_brand_id + ,i_class_id + ,i_category_id + ,i_manufact_id + ,ws_quantity - COALESCE(wr_return_quantity,0) AS sales_cnt + ,ws_ext_sales_price - COALESCE(wr_return_amt,0.0) AS sales_amt + FROM web_sales JOIN item ON i_item_sk=ws_item_sk + JOIN date_dim ON d_date_sk=ws_sold_date_sk + LEFT JOIN web_returns ON (ws_order_number=wr_order_number + AND ws_item_sk=wr_item_sk) + WHERE i_category='Sports') sales_detail + GROUP BY d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id) + SELECT prev_yr.d_year AS prev_year + ,curr_yr.d_year AS year + ,curr_yr.i_brand_id + ,curr_yr.i_class_id + ,curr_yr.i_category_id + ,curr_yr.i_manufact_id + ,prev_yr.sales_cnt AS prev_yr_cnt + ,curr_yr.sales_cnt AS curr_yr_cnt + ,curr_yr.sales_cnt-prev_yr.sales_cnt AS sales_cnt_diff + ,curr_yr.sales_amt-prev_yr.sales_amt AS sales_amt_diff + FROM all_sales curr_yr, all_sales prev_yr + WHERE curr_yr.i_brand_id=prev_yr.i_brand_id + AND curr_yr.i_class_id=prev_yr.i_class_id + AND curr_yr.i_category_id=prev_yr.i_category_id + AND curr_yr.i_manufact_id=prev_yr.i_manufact_id + AND curr_yr.d_year=2002 + AND prev_yr.d_year=2002-1 + AND CAST(curr_yr.sales_cnt AS DECIMAL(17,2))/CAST(prev_yr.sales_cnt AS DECIMAL(17,2))<0.9 + ORDER BY sales_cnt_diff + limit 100; + +-- end query 1 in stream 0 using template query75.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query76.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query76.q b/ql/src/test/queries/clientpositive/perf/cbo_query76.q new file mode 100644 index 0000000..9d2d89e --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query76.q @@ -0,0 +1,26 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query76.tpl and seed 2031708268 +explain cbo +select channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, SUM(ext_sales_price) sales_amt FROM ( + SELECT 'store' as channel, 'ss_addr_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price + FROM store_sales, item, date_dim + WHERE ss_addr_sk IS NULL + AND ss_sold_date_sk=d_date_sk + AND ss_item_sk=i_item_sk + UNION ALL + SELECT 'web' as channel, 'ws_web_page_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price + FROM web_sales, item, date_dim + WHERE ws_web_page_sk IS NULL + AND ws_sold_date_sk=d_date_sk + AND ws_item_sk=i_item_sk + UNION ALL + SELECT 'catalog' as channel, 'cs_warehouse_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price + FROM catalog_sales, item, date_dim + WHERE cs_warehouse_sk IS NULL + AND cs_sold_date_sk=d_date_sk + AND cs_item_sk=i_item_sk) foo +GROUP BY channel, col_name, d_year, d_qoy, i_category +ORDER BY channel, col_name, d_year, d_qoy, i_category +limit 100; + +-- end query 1 in stream 0 using template query76.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query77.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query77.q b/ql/src/test/queries/clientpositive/perf/cbo_query77.q new file mode 100644 index 0000000..3d0f484 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query77.q @@ -0,0 +1,109 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query77.tpl and seed 1819994127 +explain cbo +with ss as + (select s_store_sk, + sum(ss_ext_sales_price) as sales, + sum(ss_net_profit) as profit + from store_sales, + date_dim, + store + where ss_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ss_store_sk = s_store_sk + group by s_store_sk) + , + sr as + (select s_store_sk, + sum(sr_return_amt) as returns, + sum(sr_net_loss) as profit_loss + from store_returns, + date_dim, + store + where sr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and sr_store_sk = s_store_sk + group by s_store_sk), + cs as + (select cs_call_center_sk, + sum(cs_ext_sales_price) as sales, + sum(cs_net_profit) as profit + from catalog_sales, + date_dim + where cs_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + group by cs_call_center_sk + ), + cr as + (select + sum(cr_return_amount) as returns, + sum(cr_net_loss) as profit_loss + from catalog_returns, + date_dim + where cr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + ), + ws as + ( select wp_web_page_sk, + sum(ws_ext_sales_price) as sales, + sum(ws_net_profit) as profit + from web_sales, + date_dim, + web_page + where ws_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ws_web_page_sk = wp_web_page_sk + group by wp_web_page_sk), + wr as + (select wp_web_page_sk, + sum(wr_return_amt) as returns, + sum(wr_net_loss) as profit_loss + from web_returns, + date_dim, + web_page + where wr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and wr_web_page_sk = wp_web_page_sk + group by wp_web_page_sk) + select channel + , id + , sum(sales) as sales + , sum(returns) as returns + , sum(profit) as profit + from + (select 'store channel' as channel + , ss.s_store_sk as id + , sales + , coalesce(returns, 0) as returns + , (profit - coalesce(profit_loss,0)) as profit + from ss left join sr + on ss.s_store_sk = sr.s_store_sk + union all + select 'catalog channel' as channel + , cs_call_center_sk as id + , sales + , returns + , (profit - profit_loss) as profit + from cs + , cr + union all + select 'web channel' as channel + , ws.wp_web_page_sk as id + , sales + , coalesce(returns, 0) returns + , (profit - coalesce(profit_loss,0)) as profit + from ws left join wr + on ws.wp_web_page_sk = wr.wp_web_page_sk + ) x + group by rollup (channel, id) + order by channel + ,id + limit 100; + +-- end query 1 in stream 0 using template query77.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query78.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query78.q b/ql/src/test/queries/clientpositive/perf/cbo_query78.q new file mode 100644 index 0000000..87f94dc --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_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 cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query79.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query79.q b/ql/src/test/queries/clientpositive/perf/cbo_query79.q new file mode 100644 index 0000000..ce05a9a --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query79.q @@ -0,0 +1,25 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query79.tpl and seed 2031708268 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query8.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query8.q b/ql/src/test/queries/clientpositive/perf/cbo_query8.q new file mode 100644 index 0000000..71ab234 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query8.q @@ -0,0 +1,110 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query8.tpl and seed 1766988859 +explain cbo +select s_store_name + ,sum(ss_net_profit) + from store_sales + ,date_dim + ,store, + (select ca_zip + from ( + (SELECT substr(ca_zip,1,5) ca_zip + FROM customer_address + WHERE substr(ca_zip,1,5) IN ( + '89436','30868','65085','22977','83927','77557', + '58429','40697','80614','10502','32779', + '91137','61265','98294','17921','18427', + '21203','59362','87291','84093','21505', + '17184','10866','67898','25797','28055', + '18377','80332','74535','21757','29742', + '90885','29898','17819','40811','25990', + '47513','89531','91068','10391','18846', + '99223','82637','41368','83658','86199', + '81625','26696','89338','88425','32200', + '81427','19053','77471','36610','99823', + '43276','41249','48584','83550','82276', + '18842','78890','14090','38123','40936', + '34425','19850','43286','80072','79188', + '54191','11395','50497','84861','90733', + '21068','57666','37119','25004','57835', + '70067','62878','95806','19303','18840', + '19124','29785','16737','16022','49613', + '89977','68310','60069','98360','48649', + '39050','41793','25002','27413','39736', + '47208','16515','94808','57648','15009', + '80015','42961','63982','21744','71853', + '81087','67468','34175','64008','20261', + '11201','51799','48043','45645','61163', + '48375','36447','57042','21218','41100', + '89951','22745','35851','83326','61125', + '78298','80752','49858','52940','96976', + '63792','11376','53582','18717','90226', + '50530','94203','99447','27670','96577', + '57856','56372','16165','23427','54561', + '28806','44439','22926','30123','61451', + '92397','56979','92309','70873','13355', + '21801','46346','37562','56458','28286', + '47306','99555','69399','26234','47546', + '49661','88601','35943','39936','25632', + '24611','44166','56648','30379','59785', + '11110','14329','93815','52226','71381', + '13842','25612','63294','14664','21077', + '82626','18799','60915','81020','56447', + '76619','11433','13414','42548','92713', + '70467','30884','47484','16072','38936', + '13036','88376','45539','35901','19506', + '65690','73957','71850','49231','14276', + '20005','18384','76615','11635','38177', + '55607','41369','95447','58581','58149', + '91946','33790','76232','75692','95464', + '22246','51061','56692','53121','77209', + '15482','10688','14868','45907','73520', + '72666','25734','17959','24677','66446', + '94627','53535','15560','41967','69297', + '11929','59403','33283','52232','57350', + '43933','40921','36635','10827','71286', + '19736','80619','25251','95042','15526', + '36496','55854','49124','81980','35375', + '49157','63512','28944','14946','36503', + '54010','18767','23969','43905','66979', + '33113','21286','58471','59080','13395', + '79144','70373','67031','38360','26705', + '50906','52406','26066','73146','15884', + '31897','30045','61068','45550','92454', + '13376','14354','19770','22928','97790', + '50723','46081','30202','14410','20223', + '88500','67298','13261','14172','81410', + '93578','83583','46047','94167','82564', + '21156','15799','86709','37931','74703', + '83103','23054','70470','72008','49247', + '91911','69998','20961','70070','63197', + '54853','88191','91830','49521','19454', + '81450','89091','62378','25683','61869', + '51744','36580','85778','36871','48121', + '28810','83712','45486','67393','26935', + '42393','20132','55349','86057','21309', + '80218','10094','11357','48819','39734', + '40758','30432','21204','29467','30214', + '61024','55307','74621','11622','68908', + '33032','52868','99194','99900','84936', + '69036','99149','45013','32895','59004', + '32322','14933','32936','33562','72550', + '27385','58049','58200','16808','21360', + '32961','18586','79307','15492')) + intersect + (select ca_zip + from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt + FROM customer_address, customer + WHERE ca_address_sk = c_current_addr_sk and + c_preferred_cust_flag='Y' + group by ca_zip + having count(*) > 10)A1))A2) V1 + where ss_store_sk = s_store_sk + and ss_sold_date_sk = d_date_sk + and d_qoy = 1 and d_year = 2002 + and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2)) + group by 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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query80.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query80.q b/ql/src/test/queries/clientpositive/perf/cbo_query80.q new file mode 100644 index 0000000..16b1da2 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query80.q @@ -0,0 +1,98 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query80.tpl and seed 1819994127 +explain cbo +with ssr as + (select s_store_id as store_id, + sum(ss_ext_sales_price) as sales, + sum(coalesce(sr_return_amt, 0)) as returns, + sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit + from store_sales left outer join store_returns on + (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number), + date_dim, + store, + item, + promotion + where ss_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ss_store_sk = s_store_sk + and ss_item_sk = i_item_sk + and i_current_price > 50 + and ss_promo_sk = p_promo_sk + and p_channel_tv = 'N' + group by s_store_id) + , + csr as + (select cp_catalog_page_id as catalog_page_id, + sum(cs_ext_sales_price) as sales, + sum(coalesce(cr_return_amount, 0)) as returns, + sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit + from catalog_sales left outer join catalog_returns on + (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number), + date_dim, + catalog_page, + item, + promotion + where cs_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and cs_catalog_page_sk = cp_catalog_page_sk + and cs_item_sk = i_item_sk + and i_current_price > 50 + and cs_promo_sk = p_promo_sk + and p_channel_tv = 'N' +group by cp_catalog_page_id) + , + wsr as + (select web_site_id, + sum(ws_ext_sales_price) as sales, + sum(coalesce(wr_return_amt, 0)) as returns, + sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit + from web_sales left outer join web_returns on + (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number), + date_dim, + web_site, + item, + promotion + where ws_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ws_web_site_sk = web_site_sk + and ws_item_sk = i_item_sk + and i_current_price > 50 + and ws_promo_sk = p_promo_sk + and p_channel_tv = 'N' +group by web_site_id) + select channel + , id + , sum(sales) as sales + , sum(returns) as returns + , sum(profit) as profit + from + (select 'store channel' as channel + , 'store' || store_id as id + , sales + , returns + , profit + from ssr + union all + select 'catalog channel' as channel + , 'catalog_page' || catalog_page_id as id + , sales + , returns + , profit + from csr + union all + select 'web channel' as channel + , 'web_site' || web_site_id as id + , sales + , returns + , profit + from wsr + ) x + group by rollup (channel, id) + order by channel + ,id + limit 100; + +-- end query 1 in stream 0 using template query80.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query81.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query81.q b/ql/src/test/queries/clientpositive/perf/cbo_query81.q new file mode 100644 index 0000000..722bc9e --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query81.q @@ -0,0 +1,33 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query81.tpl and seed 1819994127 +explain cbo +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 + from catalog_returns + ,date_dim + ,customer_address + where cr_returned_date_sk = d_date_sk + and d_year =1998 + and cr_returning_addr_sk = ca_address_sk + group by cr_returning_customer_sk + ,ca_state ) + select c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name + ,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 + from customer_total_return ctr1 + ,customer_address + ,customer + where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 + from customer_total_return ctr2 + where ctr1.ctr_state = ctr2.ctr_state) + and ca_address_sk = c_current_addr_sk + and ca_state = 'IL' + and ctr1.ctr_customer_sk = c_customer_sk + order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name + ,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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query82.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query82.q b/ql/src/test/queries/clientpositive/perf/cbo_query82.q new file mode 100644 index 0000000..932a71b --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query82.q @@ -0,0 +1,19 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query82.tpl and seed 55585014 +explain cbo +select i_item_id + ,i_item_desc + ,i_current_price + from item, inventory, date_dim, store_sales + where i_current_price between 30 and 30+30 + and inv_item_sk = i_item_sk + and d_date_sk=inv_date_sk + and d_date between cast('2002-05-30' as date) and (cast('2002-05-30' as date) + 60 days) + and i_manufact_id in (437,129,727,663) + and inv_quantity_on_hand between 100 and 500 + and ss_item_sk = i_item_sk + group by i_item_id,i_item_desc,i_current_price + order by i_item_id + limit 100; + +-- end query 1 in stream 0 using template query82.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query83.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query83.q b/ql/src/test/queries/clientpositive/perf/cbo_query83.q new file mode 100644 index 0000000..551ea0d --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query83.q @@ -0,0 +1,69 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query83.tpl and seed 1930872976 +explain cbo +with sr_items as + (select i_item_id item_id, + sum(sr_return_quantity) sr_item_qty + from store_returns, + item, + date_dim + where sr_item_sk = i_item_sk + and d_date in + (select d_date + from date_dim + where d_week_seq in + (select d_week_seq + from date_dim + where d_date in ('1998-01-02','1998-10-15','1998-11-10'))) + and sr_returned_date_sk = d_date_sk + group by i_item_id), + cr_items as + (select i_item_id item_id, + sum(cr_return_quantity) cr_item_qty + from catalog_returns, + item, + date_dim + where cr_item_sk = i_item_sk + and d_date in + (select d_date + from date_dim + where d_week_seq in + (select d_week_seq + from date_dim + where d_date in ('1998-01-02','1998-10-15','1998-11-10'))) + and cr_returned_date_sk = d_date_sk + group by i_item_id), + wr_items as + (select i_item_id item_id, + sum(wr_return_quantity) wr_item_qty + from web_returns, + item, + date_dim + where wr_item_sk = i_item_sk + and d_date in + (select d_date + from date_dim + where d_week_seq in + (select d_week_seq + from date_dim + where d_date in ('1998-01-02','1998-10-15','1998-11-10'))) + and wr_returned_date_sk = d_date_sk + group by i_item_id) + select sr_items.item_id + ,sr_item_qty + ,sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev + ,cr_item_qty + ,cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev + ,wr_item_qty + ,wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev + ,(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average + from sr_items + ,cr_items + ,wr_items + where sr_items.item_id=cr_items.item_id + and sr_items.item_id=wr_items.item_id + 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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query84.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query84.q b/ql/src/test/queries/clientpositive/perf/cbo_query84.q new file mode 100644 index 0000000..d36df57 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query84.q @@ -0,0 +1,23 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query84.tpl and seed 1819994127 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query85.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query85.q b/ql/src/test/queries/clientpositive/perf/cbo_query85.q new file mode 100644 index 0000000..e09137b --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query85.q @@ -0,0 +1,86 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query85.tpl and seed 622697896 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query86.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query86.q b/ql/src/test/queries/clientpositive/perf/cbo_query86.q new file mode 100644 index 0000000..a8e9941 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query86.q @@ -0,0 +1,28 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query86.tpl and seed 1819994127 +explain cbo +select + sum(ws_net_paid) as total_sum + ,i_category + ,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 + from + web_sales + ,date_dim d1 + ,item + where + d1.d_month_seq between 1212 and 1212+11 + and d1.d_date_sk = ws_sold_date_sk + and i_item_sk = ws_item_sk + group by rollup(i_category,i_class) + order by + lochierarchy desc, + case when lochierarchy = 0 then i_category end, + 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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query87.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query87.q b/ql/src/test/queries/clientpositive/perf/cbo_query87.q new file mode 100644 index 0000000..79f8437 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query87.q @@ -0,0 +1,25 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query87.tpl and seed 1819994127 +explain cbo +select count(*) +from ((select distinct c_last_name, c_first_name, d_date + from store_sales, date_dim, customer + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_customer_sk = customer.c_customer_sk + and d_month_seq between 1212 and 1212+11) + except + (select distinct c_last_name, c_first_name, d_date + from catalog_sales, date_dim, customer + where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk + and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk + and d_month_seq between 1212 and 1212+11) + except + (select distinct c_last_name, c_first_name, d_date + from web_sales, date_dim, customer + where web_sales.ws_sold_date_sk = date_dim.d_date_sk + and web_sales.ws_bill_customer_sk = customer.c_customer_sk + and d_month_seq between 1212 and 1212+11) +) cool_cust +; + +-- end query 1 in stream 0 using template query87.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query88.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query88.q b/ql/src/test/queries/clientpositive/perf/cbo_query88.q new file mode 100644 index 0000000..09f95b9 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query88.q @@ -0,0 +1,96 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query88.tpl and seed 318176889 +explain cbo +select * +from + (select count(*) h8_30_to_9 + 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 = 3 and household_demographics.hd_vehicle_count<=3+2) or + (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or + (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) + and store.s_store_name = 'ese') s1, + (select count(*) h9_to_9_30 + 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 = 9 + and time_dim.t_minute < 30 + and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or + (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or + (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) + and store.s_store_name = 'ese') s2, + (select count(*) h9_30_to_10 + 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 = 9 + and time_dim.t_minute >= 30 + and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or + (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or + (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) + and store.s_store_name = 'ese') s3, + (select count(*) h10_to_10_30 + 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 = 10 + and time_dim.t_minute < 30 + and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or + (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or + (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) + and store.s_store_name = 'ese') s4, + (select count(*) h10_30_to_11 + 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 = 10 + and time_dim.t_minute >= 30 + and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or + (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or + (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) + and store.s_store_name = 'ese') s5, + (select count(*) h11_to_11_30 + 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 = 11 + and time_dim.t_minute < 30 + and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or + (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or + (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) + and store.s_store_name = 'ese') s6, + (select count(*) h11_30_to_12 + 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 = 11 + and time_dim.t_minute >= 30 + and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or + (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or + (household_demographics.hd_dep_count = 1 and household_demographics.hd_vehicle_count<=1+2)) + and store.s_store_name = 'ese') s7, + (select count(*) h12_to_12_30 + 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 = 12 + and time_dim.t_minute < 30 + and ((household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2) or + (household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or + (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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query89.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query89.q b/ql/src/test/queries/clientpositive/perf/cbo_query89.q new file mode 100644 index 0000000..7682404 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query89.q @@ -0,0 +1,30 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query89.tpl and seed 1719819282 +explain cbo +select * +from( +select i_category, i_class, i_brand, + s_store_name, s_company_name, + d_moy, + sum(ss_sales_price) sum_sales, + avg(sum(ss_sales_price)) over + (partition by i_category, i_brand, s_store_name, s_company_name) + avg_monthly_sales +from item, store_sales, date_dim, store +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') + ) + or (i_category in ('Shoes','Jewelry','Men') and + i_class in ('womens','birdal','pants') + )) +group by i_category, i_class, i_brand, + s_store_name, s_company_name, d_moy) tmp1 +where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1 +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query9.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query9.q b/ql/src/test/queries/clientpositive/perf/cbo_query9.q new file mode 100644 index 0000000..46d8868 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query9.q @@ -0,0 +1,53 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query9.tpl and seed 1490436826 +explain cbo +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) + else (select avg(ss_net_paid_inc_tax) + from store_sales + where ss_quantity between 1 and 20) end bucket1 , + case when (select count(*) + from store_sales + 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) + else (select avg(ss_net_paid_inc_tax) + from store_sales + where ss_quantity between 21 and 40) end bucket2, + case when (select count(*) + from store_sales + where ss_quantity between 41 and 60) > 7887297 + then (select avg(ss_ext_list_price) + from store_sales + where ss_quantity between 41 and 60) + else (select avg(ss_net_paid_inc_tax) + from store_sales + where ss_quantity between 41 and 60) end bucket3, + case when (select count(*) + from store_sales + where ss_quantity between 61 and 80) > 10872978 + then (select avg(ss_ext_list_price) + from store_sales + where ss_quantity between 61 and 80) + else (select avg(ss_net_paid_inc_tax) + from store_sales + where ss_quantity between 61 and 80) end bucket4, + case when (select count(*) + from store_sales + where ss_quantity between 81 and 100) > 43571537 + then (select avg(ss_ext_list_price) + from store_sales + where ss_quantity between 81 and 100) + else (select avg(ss_net_paid_inc_tax) + from store_sales + where ss_quantity between 81 and 100) end bucket5 +from reason +where r_reason_sk = 1 +; + +-- end query 1 in stream 0 using template query9.tpl http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query90.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query90.q b/ql/src/test/queries/clientpositive/perf/cbo_query90.q new file mode 100644 index 0000000..fc75e67 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query90.q @@ -0,0 +1,24 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query90.tpl and seed 2031708268 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query91.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query91.q b/ql/src/test/queries/clientpositive/perf/cbo_query91.q new file mode 100644 index 0000000..7f1f8d8 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query91.q @@ -0,0 +1,33 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query91.tpl and seed 1930872976 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query92.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query92.q b/ql/src/test/queries/clientpositive/perf/cbo_query92.q new file mode 100644 index 0000000..23b32ea --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query92.q @@ -0,0 +1,32 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query92.tpl and seed 2031708268 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query93.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query93.q b/ql/src/test/queries/clientpositive/perf/cbo_query93.q new file mode 100644 index 0000000..c6a7d12 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query93.q @@ -0,0 +1,20 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query93.tpl and seed 1200409435 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query94.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query94.q b/ql/src/test/queries/clientpositive/perf/cbo_query94.q new file mode 100644 index 0000000..4ae01bf --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query94.q @@ -0,0 +1,31 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query94.tpl and seed 2031708268 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query95.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query95.q b/ql/src/test/queries/clientpositive/perf/cbo_query95.q new file mode 100644 index 0000000..58c9be5 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query95.q @@ -0,0 +1,34 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query95.tpl and seed 2031708268 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query96.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query96.q b/ql/src/test/queries/clientpositive/perf/cbo_query96.q new file mode 100644 index 0000000..1561fe3 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query96.q @@ -0,0 +1,18 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query96.tpl and seed 1819994127 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query97.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query97.q b/ql/src/test/queries/clientpositive/perf/cbo_query97.q new file mode 100644 index 0000000..cb754dd --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query97.q @@ -0,0 +1,27 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query97.tpl and seed 1819994127 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query98.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query98.q b/ql/src/test/queries/clientpositive/perf/cbo_query98.q new file mode 100644 index 0000000..a48f81e --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_query98.q @@ -0,0 +1,34 @@ +set hive.mapred.mode=nonstrict; +-- start query 1 in stream 0 using template query98.tpl and seed 345591136 +explain cbo +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/b8299551/ql/src/test/queries/clientpositive/perf/cbo_query99.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/cbo_query99.q b/ql/src/test/queries/clientpositive/perf/cbo_query99.q new file mode 100644 index 0000000..be098fa --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/cbo_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 cbo +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/b8299551/ql/src/test/results/clientpositive/perf/tez/cbo_query1.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/cbo_query1.q.out b/ql/src/test/results/clientpositive/perf/tez/cbo_query1.q.out new file mode 100644 index 0000000..295ba99 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/cbo_query1.q.out @@ -0,0 +1,91 @@ +PREHOOK: query: explain cbo +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 +from store_returns +,date_dim +where sr_returned_date_sk = d_date_sk +and d_year =2000 +group by sr_customer_sk +,sr_store_sk) + select c_customer_id +from customer_total_return ctr1 +,store +,customer +where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 +from customer_total_return ctr2 +where ctr1.ctr_store_sk = ctr2.ctr_store_sk) +and s_store_sk = ctr1.ctr_store_sk +and s_state = 'NM' +and ctr1.ctr_customer_sk = c_customer_sk +order by c_customer_id +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@customer +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@store +PREHOOK: Input: default@store_returns +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain cbo +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 +from store_returns +,date_dim +where sr_returned_date_sk = d_date_sk +and d_year =2000 +group by sr_customer_sk +,sr_store_sk) + select c_customer_id +from customer_total_return ctr1 +,store +,customer +where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 +from customer_total_return ctr2 +where ctr1.ctr_store_sk = ctr2.ctr_store_sk) +and s_store_sk = ctr1.ctr_store_sk +and s_state = 'NM' +and ctr1.ctr_customer_sk = c_customer_sk +order by c_customer_id +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_returns +POSTHOOK: Output: hdfs://### HDFS PATH ### +CBO PLAN: +HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) + HiveProject(c_customer_id=[$1]) + HiveJoin(condition=[AND(=($3, $8), >($4, $7))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($2, $0)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(c_customer_sk=[$0], c_customer_id=[$1]) + HiveFilter(condition=[IS NOT NULL($0)]) + HiveTableScan(table=[[default, customer]], table:alias=[customer]) + HiveJoin(condition=[=($3, $1)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(sr_customer_sk=[$0], sr_store_sk=[$1], $f2=[$2]) + HiveAggregate(group=[{1, 2}], agg#0=[sum($3)]) + HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(sr_returned_date_sk=[$0], sr_customer_sk=[$3], sr_store_sk=[$7], sr_fee=[$14]) + HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7), IS NOT NULL($3))]) + HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns]) + HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER]) + HiveFilter(condition=[AND(=($6, 2000), IS NOT NULL($0))]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + HiveProject(s_store_sk=[$0], s_state=[CAST(_UTF-16LE'NM'):VARCHAR(2147483647) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary"]) + HiveFilter(condition=[AND(=($24, _UTF-16LE'NM'), IS NOT NULL($0))]) + HiveTableScan(table=[[default, store]], table:alias=[store]) + HiveProject(_o__c0=[*(/($1, $2), 1.2)], ctr_store_sk=[$0]) + HiveAggregate(group=[{1}], agg#0=[sum($2)], agg#1=[count($2)]) + HiveProject(sr_customer_sk=[$0], sr_store_sk=[$1], $f2=[$2]) + HiveAggregate(group=[{1, 2}], agg#0=[sum($3)]) + HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(sr_returned_date_sk=[$0], sr_customer_sk=[$3], sr_store_sk=[$7], sr_fee=[$14]) + HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($7))]) + HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns]) + HiveProject(d_date_sk=[$0], d_year=[CAST(2000):INTEGER]) + HiveFilter(condition=[AND(=($6, 2000), IS NOT NULL($0))]) + HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) +
