IMPALA-6819: Add new performance test workload - tpcds-unmodified used by Impala Performance Tests
Description: Impala versions prior to 2.5 didn't have Runtimefilters, which made TPC-DS queries run very slow, so queries under tpcds have explicit partition filters to workaround the limitation. Post Impala 2.5 adding tpcds-unmodified which has the unmodified version of the workload to provide more coverage Testing: Ran the performance tests using the new workload and all tests passed Change-Id: I3957621d88b80fffc8fc89fd8104a58137b86e92 Reviewed-on: http://gerrit.cloudera.org:8080/9973 Reviewed-by: David Knupp <dkn...@cloudera.com> Tested-by: Impala Public Jenkins <impala-public-jenk...@cloudera.com> Project: http://git-wip-us.apache.org/repos/asf/impala/repo Commit: http://git-wip-us.apache.org/repos/asf/impala/commit/bb9237d9 Tree: http://git-wip-us.apache.org/repos/asf/impala/tree/bb9237d9 Diff: http://git-wip-us.apache.org/repos/asf/impala/diff/bb9237d9 Branch: refs/heads/master Commit: bb9237d942abc69efcf1b26bfd10f149dd781948 Parents: 4cdb6df Author: njanarthanan <njanartha...@cloudera.com> Authored: Tue Apr 10 10:44:47 2018 -0700 Committer: Impala Public Jenkins <impala-public-jenk...@cloudera.com> Committed: Sun May 13 09:06:06 2018 +0000 ---------------------------------------------------------------------- .../tpcds-unmodified/queries/tpcds-q1.test | 31 +++ .../tpcds-unmodified/queries/tpcds-q10.test | 67 ++++++ .../tpcds-unmodified/queries/tpcds-q11.test | 87 +++++++ .../tpcds-unmodified/queries/tpcds-q12.test | 40 ++++ .../tpcds-unmodified/queries/tpcds-q13.test | 62 +++++ .../tpcds-unmodified/queries/tpcds-q14.test | 159 +++++++++++++ .../tpcds-unmodified/queries/tpcds-q15.test | 26 +++ .../tpcds-unmodified/queries/tpcds-q16.test | 37 +++ .../tpcds-unmodified/queries/tpcds-q17.test | 51 +++++ .../tpcds-unmodified/queries/tpcds-q18.test | 59 +++++ .../tpcds-unmodified/queries/tpcds-q19.test | 31 +++ .../tpcds-unmodified/queries/tpcds-q2.test | 66 ++++++ .../tpcds-unmodified/queries/tpcds-q20.test | 36 +++ .../tpcds-unmodified/queries/tpcds-q21.test | 36 +++ .../tpcds-unmodified/queries/tpcds-q22.test | 48 ++++ .../tpcds-unmodified/queries/tpcds-q23.test | 63 ++++++ .../tpcds-unmodified/queries/tpcds-q25.test | 54 +++++ .../tpcds-unmodified/queries/tpcds-q26.test | 27 +++ .../tpcds-unmodified/queries/tpcds-q27.test | 43 ++++ .../tpcds-unmodified/queries/tpcds-q28.test | 59 +++++ .../tpcds-unmodified/queries/tpcds-q29.test | 53 +++++ .../tpcds-unmodified/queries/tpcds-q3.test | 27 +++ .../tpcds-unmodified/queries/tpcds-q30.test | 37 +++ .../tpcds-unmodified/queries/tpcds-q31.test | 58 +++++ .../tpcds-unmodified/queries/tpcds-q32.test | 34 +++ .../tpcds-unmodified/queries/tpcds-q33.test | 81 +++++++ .../tpcds-unmodified/queries/tpcds-q34.test | 37 +++ .../tpcds-unmodified/queries/tpcds-q35.test | 66 ++++++ .../tpcds-unmodified/queries/tpcds-q36.test | 47 ++++ .../tpcds-unmodified/queries/tpcds-q37.test | 23 ++ .../tpcds-unmodified/queries/tpcds-q38.test | 67 ++++++ .../tpcds-unmodified/queries/tpcds-q39.test | 33 +++ .../tpcds-unmodified/queries/tpcds-q4.test | 122 ++++++++++ .../tpcds-unmodified/queries/tpcds-q40.test | 34 +++ .../tpcds-unmodified/queries/tpcds-q41.test | 62 +++++ .../tpcds-unmodified/queries/tpcds-q42.test | 28 +++ .../tpcds-unmodified/queries/tpcds-q43.test | 25 ++ .../tpcds-unmodified/queries/tpcds-q44.test | 54 +++++ .../tpcds-unmodified/queries/tpcds-q45.test | 40 ++++ .../tpcds-unmodified/queries/tpcds-q46.test | 41 ++++ .../tpcds-unmodified/queries/tpcds-q47.test | 57 +++++ .../tpcds-unmodified/queries/tpcds-q48.test | 27 +++ .../tpcds-unmodified/queries/tpcds-q49.test | 133 +++++++++++ .../tpcds-unmodified/queries/tpcds-q5.test | 140 ++++++++++++ .../tpcds-unmodified/queries/tpcds-q50.test | 65 ++++++ .../tpcds-unmodified/queries/tpcds-q51.test | 51 +++++ .../tpcds-unmodified/queries/tpcds-q52.test | 28 +++ .../tpcds-unmodified/queries/tpcds-q53.test | 34 +++ .../tpcds-unmodified/queries/tpcds-q54.test | 62 +++++ .../tpcds-unmodified/queries/tpcds-q55.test | 20 ++ .../tpcds-unmodified/queries/tpcds-q56.test | 74 ++++++ .../tpcds-unmodified/queries/tpcds-q57.test | 54 +++++ .../tpcds-unmodified/queries/tpcds-q58.test | 71 ++++++ .../tpcds-unmodified/queries/tpcds-q59.test | 50 ++++ .../tpcds-unmodified/queries/tpcds-q6.test | 32 +++ .../tpcds-unmodified/queries/tpcds-q60.test | 84 +++++++ .../tpcds-unmodified/queries/tpcds-q61.test | 50 ++++ .../tpcds-unmodified/queries/tpcds-q62.test | 41 ++++ .../tpcds-unmodified/queries/tpcds-q63.test | 35 +++ .../tpcds-unmodified/queries/tpcds-q64.test | 125 ++++++++++ .../tpcds-unmodified/queries/tpcds-q65.test | 35 +++ .../tpcds-unmodified/queries/tpcds-q66.test | 226 +++++++++++++++++++ .../tpcds-unmodified/queries/tpcds-q68.test | 48 ++++ .../tpcds-unmodified/queries/tpcds-q69.test | 53 +++++ .../tpcds-unmodified/queries/tpcds-q7.test | 27 +++ .../tpcds-unmodified/queries/tpcds-q70.test | 50 ++++ .../tpcds-unmodified/queries/tpcds-q71.test | 46 ++++ .../tpcds-unmodified/queries/tpcds-q72.test | 35 +++ .../tpcds-unmodified/queries/tpcds-q73.test | 34 +++ .../tpcds-unmodified/queries/tpcds-q74.test | 67 ++++++ .../tpcds-unmodified/queries/tpcds-q75.test | 76 +++++++ .../tpcds-unmodified/queries/tpcds-q76.test | 30 +++ .../tpcds-unmodified/queries/tpcds-q77.test | 121 ++++++++++ .../tpcds-unmodified/queries/tpcds-q78.test | 64 ++++++ .../tpcds-unmodified/queries/tpcds-q79.test | 29 +++ .../tpcds-unmodified/queries/tpcds-q8.test | 64 ++++++ .../tpcds-unmodified/queries/tpcds-q80.test | 116 ++++++++++ .../tpcds-unmodified/queries/tpcds-q81.test | 37 +++ .../tpcds-unmodified/queries/tpcds-q82.test | 23 ++ .../tpcds-unmodified/queries/tpcds-q83.test | 73 ++++++ .../tpcds-unmodified/queries/tpcds-q84.test | 27 +++ .../tpcds-unmodified/queries/tpcds-q85.test | 90 ++++++++ .../tpcds-unmodified/queries/tpcds-q86.test | 41 ++++ .../tpcds-unmodified/queries/tpcds-q87.test | 41 ++++ .../tpcds-unmodified/queries/tpcds-q88.test | 100 ++++++++ .../tpcds-unmodified/queries/tpcds-q89.test | 34 +++ .../tpcds-unmodified/queries/tpcds-q9.test | 49 ++++ .../tpcds-unmodified/queries/tpcds-q90.test | 28 +++ .../tpcds-unmodified/queries/tpcds-q91.test | 37 +++ .../tpcds-unmodified/queries/tpcds-q92.test | 36 +++ .../tpcds-unmodified/queries/tpcds-q93.test | 24 ++ .../tpcds-unmodified/queries/tpcds-q94.test | 35 +++ .../tpcds-unmodified/queries/tpcds-q95.test | 38 ++++ .../tpcds-unmodified/queries/tpcds-q96.test | 22 ++ .../tpcds-unmodified/queries/tpcds-q97.test | 31 +++ .../tpcds-unmodified/queries/tpcds-q98.test | 39 ++++ .../tpcds-unmodified/queries/tpcds-q99.test | 41 ++++ .../tpcds-unmodified/tpcds-unmodified_core.csv | 4 + .../tpcds-unmodified_dimensions.csv | 4 + .../tpcds-unmodified_exhaustive.csv | 23 ++ .../tpcds-unmodified_pairwise.csv | 15 ++ 101 files changed, 5297 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q1.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q1.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q1.test new file mode 100644 index 0000000..97ea25e --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q1.test @@ -0,0 +1,31 @@ +==== +---- QUERY: TPCDS-Q1 + +with customer_total_return as +(select sr_customer_sk as ctr_customer_sk +,sr_store_sk as ctr_store_sk +,sum(SR_RETURN_AMT) as ctr_total_return +from store_returns +,date_dim +where sr_returned_date_sk = d_date_sk +and d_year =2002 +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 = 'KS' +and ctr1.ctr_customer_sk = c_customer_sk +order by c_customer_id +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q10.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q10.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q10.test new file mode 100644 index 0000000..6c3ee28 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q10.test @@ -0,0 +1,67 @@ +==== +---- QUERY: TPCDS-Q10 + +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, + (select ss_customer_sk + from store_sales,date_dim + where ss_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 2 and 2+3) ss, + (select ws_bill_customer_sk + from web_sales,date_dim + where ws_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 2 ANd 2+3) ws, + (select cs_ship_customer_sk + from catalog_sales,date_dim + where cs_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 2 and 2+3) cs + where + c.c_current_addr_sk = ca.ca_address_sk and + ca_county in ('McKenzie County','Adams County','Grant County','Saguache County','Waseca County') and + cd_demo_sk = c.c_current_cdemo_sk + and c_customer_sk = ss_customer_sk + and c_customer_sk = ws_bill_customer_sk + and c_customer_sk = cs_ship_customer_sk + + + 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; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q11.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q11.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q11.test new file mode 100644 index 0000000..2127e45 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q11.test @@ -0,0 +1,87 @@ +==== +---- QUERY: TPCDS-Q11 + +with year_total as ( + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,c_preferred_cust_flag customer_preferred_cust_flag + ,c_birth_country customer_birth_country + ,c_login customer_login + ,c_email_address customer_email_address + ,d_year dyear + ,sum(ss_ext_list_price-ss_ext_discount_amt) 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 + group by c_customer_id + ,c_first_name + ,c_last_name + ,c_preferred_cust_flag + ,c_birth_country + ,c_login + ,c_email_address + ,d_year + union all + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,c_preferred_cust_flag customer_preferred_cust_flag + ,c_birth_country customer_birth_country + ,c_login customer_login + ,c_email_address customer_email_address + ,d_year dyear + ,sum(ws_ext_list_price-ws_ext_discount_amt) 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 + group by c_customer_id + ,c_first_name + ,c_last_name + ,c_preferred_cust_flag + ,c_birth_country + ,c_login + ,c_email_address + ,d_year + ) + select + t_s_secyear.customer_id + ,t_s_secyear.customer_first_name + ,t_s_secyear.customer_last_name + ,t_s_secyear.customer_email_address + 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.dyear = 1999 + and t_s_secyear.dyear = 1999+1 + and t_w_firstyear.dyear = 1999 + and t_w_secyear.dyear = 1999+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 0.0 end + > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end + order by t_s_secyear.customer_id + ,t_s_secyear.customer_first_name + ,t_s_secyear.customer_last_name + ,t_s_secyear.customer_email_address +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q12.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q12.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q12.test new file mode 100644 index 0000000..1e61367 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q12.test @@ -0,0 +1,40 @@ +==== +---- QUERY: TPCDS-Q12 + +select i_item_id + ,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 + ws_item_sk = i_item_sk + and i_category in ('Music', 'Electronics', 'Jewelry') + and ws_sold_date_sk = d_date_sk + and d_date between cast('1998-02-15' as timestamp) + and (cast('1998-02-15' as timestamp) + interval 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; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q13.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q13.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q13.test new file mode 100644 index 0000000..77f5a4f --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q13.test @@ -0,0 +1,62 @@ +==== +---- QUERY: TPCDS-Q13 + +select avg(ss_quantity) + ,avg(ss_ext_sales_price) + ,avg(ss_ext_wholesale_cost) + ,sum(ss_ext_wholesale_cost) + from store_sales + ,store + ,customer_demographics + ,household_demographics + ,customer_address + ,date_dim + where s_store_sk = ss_store_sk + and ss_sold_date_sk = d_date_sk and d_year = 2001 + and cd_demo_sk = ss_cdemo_sk + and ss_hdemo_sk=hd_demo_sk + and ss_addr_sk = ca_address_sk +and ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk +and ss_addr_sk = ca_address_sk + and((ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'W' + and cd_education_status = 'Primary' + and ss_sales_price between 100.00 and 150.00 + and hd_dep_count = 3 + )or + (ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'D' + and cd_education_status = '4 yr Degree' + and ss_sales_price between 50.00 and 100.00 + and hd_dep_count = 1 + ) or + (ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'U' + and cd_education_status = 'College' + and ss_sales_price between 150.00 and 200.00 + and hd_dep_count = 1 + )) + and((ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('IL', 'MN', 'ID') + and ss_net_profit between 100 and 200 + ) or + (ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('VA', 'MT', 'OR') + and ss_net_profit between 150 and 300 + ) or + (ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('SC', 'MO', 'IA') + and ss_net_profit between 50 and 250 + )) +; +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q14.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q14.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q14.test new file mode 100644 index 0000000..b767ed9 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q14.test @@ -0,0 +1,159 @@ +==== +---- QUERY: TPCDS-Q14 +with cross_items as +( +select distinct i_item_sk ss_item_sk + from item, +( select t1t2t3.* from ( +select t1t2.* from ( +select t1.* from ( +select distinct iss.i_brand_id brand_id + ,iss.i_class_id class_id + ,iss.i_category_id category_id + from store_sales ,item iss ,date_dim d1 + where ss_item_sk = iss.i_item_sk + and ss_sold_date_sk = d1.d_date_sk + and d1.d_year between 1999 AND 1999 + 2 +) t1 +INNER JOIN ( + select distinct ics.i_brand_id brand_id + ,ics.i_class_id class_id + ,ics.i_category_id category_id + from catalog_sales ,item ics ,date_dim d2 + where cs_item_sk = ics.i_item_sk + and cs_sold_date_sk = d2.d_date_sk + and d2.d_year between 1999 AND 1999 + 2 +) t2 +ON t1.brand_id = t2.brand_id and + t1.class_id = t2.class_id and + t1.category_id = t2.category_id +) t1t2 +INNER JOIN ( + select distinct iws.i_brand_id brand_id + ,iws.i_class_id class_id + ,iws.i_category_id category_id + from web_sales + ,item iws + ,date_dim d3 + where ws_item_sk = iws.i_item_sk + and ws_sold_date_sk = d3.d_date_sk + and d3.d_year between 1999 AND 1999 + 2 +) t3 +ON t1t2.brand_id = t3.brand_id and + t1t2.class_id = t3.class_id and + t1t2.category_id = t3.category_id +) t1t2t3 ) t1t2t3_1 +where i_brand_id = brand_id + and i_class_id = class_id + and i_category_id = category_id +), + avg_sales as + (select avg(quantity*list_price) average_sales + from (select ss_quantity quantity + ,ss_list_price list_price + from store_sales + ,date_dim + where ss_sold_date_sk = d_date_sk + and d_year between 1999 and 2001 + union all + select cs_quantity quantity + ,cs_list_price list_price + from catalog_sales + ,date_dim + where cs_sold_date_sk = d_date_sk + and d_year between 1998 and 2000 + union all + select ws_quantity quantity + ,ws_list_price list_price + from web_sales + ,date_dim + where ws_sold_date_sk = d_date_sk + and d_year between 1998 and 1998 + 2) x) +, + results AS +(select channel, i_brand_id, i_class_id, i_category_id, sum(sales) sum_sales, sum(number_sales) number_sales + from ( +select * from ( + select * from + (select channel, i_brand_id,i_class_id,i_category_id, sales, number_sales from + (select 'store' channel, i_brand_id,i_class_id + ,i_category_id,sum(ss_quantity*ss_list_price) sales + , count(*) number_sales + from store_sales + ,item + ,date_dim + where ss_item_sk in (select ss_item_sk from cross_items) + and ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and d_year = 1998+2 + and d_moy = 11 + group by i_brand_id,i_class_id,i_category_id ) a1 + cross join + (select average_sales from avg_sales) a2 + where a1.sales > a2.average_sales + ) a12 ) a121 + union all + select * from + (select * from + (select channel, i_brand_id,i_class_id,i_category_id, sales, number_sales from + ( + select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum(cs_quantity*cs_list_price) sales, count(*) number_sales + from catalog_sales + ,item + ,date_dim + where cs_item_sk in (select ss_item_sk from cross_items) + and cs_item_sk = i_item_sk + and cs_sold_date_sk = d_date_sk + and d_year = 1998+2 + and d_moy = 11 + group by i_brand_id,i_class_id,i_category_id ) a1 + cross join + (select average_sales from avg_sales) a2 + where a1.sales > a2.average_sales + ) a12 ) a121 + + --having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales) + union all + select * from + (select * from + (select channel, i_brand_id,i_class_id,i_category_id, sales, number_sales from + ( + select 'web' channel, i_brand_id,i_class_id,i_category_id, sum(ws_quantity*ws_list_price) sales , count(*) number_sales + from web_sales + ,item + ,date_dim + where ws_item_sk in (select ss_item_sk from cross_items) + and ws_item_sk = i_item_sk + and ws_sold_date_sk = d_date_sk + and ws_sold_date_sk between 2451850 and 2451879 + and d_year = 1998+2 + and d_moy = 11 + group by i_brand_id,i_class_id,i_category_id ) a1 + cross join + (select average_sales from avg_sales) a2 + where a1.sales > a2.average_sales + ) a12 ) a121 + --having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales) + ) y + group by channel, i_brand_id,i_class_id,i_category_id) + +select channel, i_brand_id, i_class_id, i_category_id, sum_sales, number_sales +from ( + select channel, i_brand_id, i_class_id, i_category_id, sum_sales, number_sales from results + union + select channel, i_brand_id, i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results + group by channel, i_brand_id, i_class_id + union + select channel, i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results + group by channel, i_brand_id + union + select channel, null as i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results + group by channel + union + select null as channel, null as i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results) z +order by channel, i_brand_id, i_class_id, i_category_id +limit 100; +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q15.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q15.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q15.test new file mode 100644 index 0000000..1a8cb36 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q15.test @@ -0,0 +1,26 @@ +==== +---- QUERY: TPCDS-Q15 + +select ca_zip + ,sum(cs_sales_price) + from catalog_sales + ,customer + ,customer_address + ,date_dim + where cs_bill_customer_sk = c_customer_sk + and c_current_addr_sk = ca_address_sk + and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', + '85392', '85460', '80348', '81792') + or ca_state in ('CA','WA','GA') + or cs_sales_price > 500) + and cs_sold_date_sk = d_date_sk + and d_qoy = 2 and d_year = 2002 + group by ca_zip + order by ca_zip + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q16.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q16.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q16.test new file mode 100644 index 0000000..01d9bc4 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q16.test @@ -0,0 +1,37 @@ +==== +---- QUERY: TPCDS-Q16 + +select + count(distinct cs_order_number) as "order count" + ,sum(cs_ext_ship_cost) as "total shipping cost" + ,sum(cs_net_profit) as "total net profit" +from + catalog_sales cs1 + ,date_dim + ,customer_address + ,call_center +where + d_date between '2002-2-01' and + (cast('2002-2-01' as timestamp) + interval 60 days) +and cs1.cs_ship_date_sk = d_date_sk +and cs1.cs_ship_addr_sk = ca_address_sk +and ca_state = 'MO' +and cs1.cs_call_center_sk = cc_call_center_sk +and cc_county in ('Kittitas County','Levy County','Oglethorpe County','Raleigh County', + 'Mesa County' +) +and exists (select * + from catalog_sales cs2 + where cs1.cs_order_number = cs2.cs_order_number + and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk) +and not exists(select * + from catalog_returns cr1 + where cs1.cs_order_number = cr1.cr_order_number) +order by count(distinct cs_order_number) +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q17.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q17.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q17.test new file mode 100644 index 0000000..dcf56ca --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q17.test @@ -0,0 +1,51 @@ +==== +---- QUERY: TPCDS-Q17 + +select i_item_id + ,i_item_desc + ,s_state + ,count(ss_quantity) as store_sales_quantitycount + ,avg(ss_quantity) as store_sales_quantityave + ,stddev_samp(ss_quantity) as store_sales_quantitystdev + ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov + ,count(sr_return_quantity) as store_returns_quantitycount + ,avg(sr_return_quantity) as store_returns_quantityave + ,stddev_samp(sr_return_quantity) as store_returns_quantitystdev + ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov + ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave + ,stddev_samp(cs_quantity) as catalog_sales_quantitystdev + ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov + from store_sales + ,store_returns + ,catalog_sales + ,date_dim d1 + ,date_dim d2 + ,date_dim d3 + ,store + ,item + where d1.d_quarter_name = '2000Q1' + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and ss_customer_sk = sr_customer_sk + and ss_item_sk = sr_item_sk + and ss_ticket_number = sr_ticket_number + and sr_returned_date_sk = d2.d_date_sk + and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3') + and sr_customer_sk = cs_bill_customer_sk + and sr_item_sk = cs_item_sk + and cs_sold_date_sk = d3.d_date_sk + and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3') + group by i_item_id + ,i_item_desc + ,s_state + order by i_item_id + ,i_item_desc + ,s_state +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q18.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q18.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q18.test new file mode 100644 index 0000000..488c788 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q18.test @@ -0,0 +1,59 @@ +==== +---- QUERY: TPCDS-Q18 +with results as + (select i_item_id, + ca_country, + ca_state, + ca_county, + cast(cs_quantity as decimal(12,2)) agg1, + cast(cs_list_price as decimal(12,2)) agg2, + cast(cs_coupon_amt as decimal(12,2)) agg3, + cast(cs_sales_price as decimal(12,2)) agg4, + cast(cs_net_profit as decimal(12,2)) agg5, + cast(c_birth_year as decimal(12,2)) agg6, + cast(cd1.cd_dep_count as decimal(12,2)) agg7 + from catalog_sales, customer_demographics cd1, customer_demographics cd2, customer, customer_address, date_dim, item + where cs_sold_date_sk = d_date_sk and + cs_item_sk = i_item_sk and + cs_bill_cdemo_sk = cd1.cd_demo_sk and + cs_bill_customer_sk = c_customer_sk and + cd1.cd_gender = 'M' and + cd1.cd_education_status = 'Secondary' and + c_current_cdemo_sk = cd2.cd_demo_sk and + c_current_addr_sk = ca_address_sk and + c_birth_month in (8,11,10,5,2,1) and + d_year = 1999 and + ca_state in ('NE','VA','OH','MT','AR','TN','FL') + ) + select i_item_id, ca_country, ca_state, ca_county, agg1, agg2, agg3, agg4, agg5, agg6, agg7 + from ( + select i_item_id, ca_country, ca_state, ca_county, avg(agg1) agg1, + avg(agg2) agg2, avg(agg3) agg3, avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 + from results + group by i_item_id, ca_country, ca_state, ca_county + union all + select i_item_id, ca_country, ca_state, NULL as county, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, + avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 + from results + group by i_item_id, ca_country, ca_state + union all + select i_item_id, ca_country, NULL as ca_state, NULL as county, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, + avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 + from results + group by i_item_id, ca_country + union all + select i_item_id, NULL as ca_country, NULL as ca_state, NULL as county, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, + avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 + from results + group by i_item_id + union all + select NULL AS i_item_id, NULL as ca_country, NULL as ca_state, NULL as county, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, + avg(agg4) agg4, avg(agg5) agg5, avg(agg6) agg6, avg(agg7) agg7 + from results + ) foo + order by ca_country, ca_state, ca_county, i_item_id +limit 100; +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q19.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q19.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q19.test new file mode 100644 index 0000000..e4c0d7d --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q19.test @@ -0,0 +1,31 @@ +==== +---- QUERY: TPCDS-Q19 + +select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, + sum(ss_ext_sales_price) ext_price + from date_dim, store_sales, item,customer,customer_address,store + where d_date_sk = ss_sold_date_sk + and ss_item_sk = i_item_sk + and i_manager_id=2 + and d_moy=12 + and d_year=2001 + and ss_customer_sk = c_customer_sk + and c_current_addr_sk = ca_address_sk + and substr(ca_zip,1,5) <> substr(s_zip,1,5) + and ss_store_sk = s_store_sk + group by i_brand + ,i_brand_id + ,i_manufact_id + ,i_manufact + order by ext_price desc + ,i_brand + ,i_brand_id + ,i_manufact_id + ,i_manufact +limit 100 ; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q2.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q2.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q2.test new file mode 100644 index 0000000..a844d8b --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q2.test @@ -0,0 +1,66 @@ +==== +---- QUERY: TPCDS-Q2 + +with wscs as + (select sold_date_sk + ,sales_price + from (select ws_sold_date_sk sold_date_sk + ,ws_ext_sales_price sales_price + from web_sales + union all + select cs_sold_date_sk sold_date_sk + ,cs_ext_sales_price sales_price + from catalog_sales) x), + wswscs as + (select d_week_seq, + sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales, + sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales, + sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales, + sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales, + sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales, + sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales, + sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales + from wscs + ,date_dim + where d_date_sk = sold_date_sk + group by d_week_seq) + select d_week_seq1 + ,round(sun_sales1/sun_sales2,2) + ,round(mon_sales1/mon_sales2,2) + ,round(tue_sales1/tue_sales2,2) + ,round(wed_sales1/wed_sales2,2) + ,round(thu_sales1/thu_sales2,2) + ,round(fri_sales1/fri_sales2,2) + ,round(sat_sales1/sat_sales2,2) + from + (select wswscs.d_week_seq d_week_seq1 + ,sun_sales sun_sales1 + ,mon_sales mon_sales1 + ,tue_sales tue_sales1 + ,wed_sales wed_sales1 + ,thu_sales thu_sales1 + ,fri_sales fri_sales1 + ,sat_sales sat_sales1 + from wswscs,date_dim + where date_dim.d_week_seq = wswscs.d_week_seq and + d_year = 2001) y, + (select wswscs.d_week_seq d_week_seq2 + ,sun_sales sun_sales2 + ,mon_sales mon_sales2 + ,tue_sales tue_sales2 + ,wed_sales wed_sales2 + ,thu_sales thu_sales2 + ,fri_sales fri_sales2 + ,sat_sales sat_sales2 + from wswscs + ,date_dim + where date_dim.d_week_seq = wswscs.d_week_seq and + d_year = 2001+1) z + where d_week_seq1=d_week_seq2-53 + order by d_week_seq1; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q20.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q20.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q20.test new file mode 100644 index 0000000..9aa1db5 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q20.test @@ -0,0 +1,36 @@ +==== +---- QUERY: TPCDS-Q20 + +select i_item_id + ,i_item_desc + ,i_category + ,i_class + ,i_current_price + ,sum(cs_ext_sales_price) as itemrevenue + ,sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over + (partition by i_class) as revenueratio + from catalog_sales + ,item + ,date_dim + where cs_item_sk = i_item_sk + and i_category in ('Music', 'Electronics', 'Jewelry') + and cs_sold_date_sk = d_date_sk + and d_date between cast('1998-02-15' as timestamp) + and (cast('1998-02-15' as timestamp) + interval 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; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q21.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q21.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q21.test new file mode 100644 index 0000000..4df5858 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q21.test @@ -0,0 +1,36 @@ +==== +---- QUERY: TPCDS-Q21 + +select * + from(select w_warehouse_name + ,i_item_id + ,sum(case when (cast(d_date as timestamp) < cast ('2000-05-30' as timestamp)) + then inv_quantity_on_hand + else 0 end) as inv_before + ,sum(case when (cast(d_date as timestamp) >= cast ('2000-05-30' as timestamp)) + then inv_quantity_on_hand + else 0 end) as inv_after + from inventory + ,warehouse + ,item + ,date_dim + where i_current_price between 0.99 and 1.49 + and i_item_sk = inv_item_sk + and inv_warehouse_sk = w_warehouse_sk + and inv_date_sk = d_date_sk + and d_date between (cast ('2000-05-30' as timestamp) - interval 30 days) + and (cast ('2000-05-30' as timestamp) + interval 30 days) + group by w_warehouse_name, i_item_id) x + where (case when inv_before > 0 + then inv_after / inv_before + else null + end) between 2.0/3.0 and 3.0/2.0 + order by w_warehouse_name + ,i_item_id + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q22.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q22.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q22.test new file mode 100644 index 0000000..fcbe263 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q22.test @@ -0,0 +1,48 @@ +==== +---- QUERY: TPCDS-Q22 +with results as +(select i_product_name + ,i_brand + ,i_class + ,i_category + --,avg(inv_quantity_on_hand) qoh + ,inv_quantity_on_hand qoh +from + inventory, + date_dim, + item, + warehouse +where inv_date_sk=d_date_sk + and inv_item_sk=i_item_sk + and inv_warehouse_sk = w_warehouse_sk + and d_month_seq between 1207 and 1207 + 11 +group by i_product_name,i_brand,i_class,i_category, qoh), +results_rollup as +( +select i_product_name, i_brand, i_class, i_category,avg(qoh) qoh +from results +group by i_product_name,i_brand,i_class,i_category +union all +select i_product_name, i_brand, i_class, null i_category,avg(qoh) qoh +from results +group by i_product_name,i_brand,i_class +union all +select i_product_name, i_brand, null i_class, null i_category,avg(qoh) qoh +from results +group by i_product_name,i_brand +union all +select i_product_name, null i_brand, null i_class, null i_category,avg(qoh) qoh +from results +group by i_product_name +union all +select null i_product_name, null i_brand, null i_class, null i_category,avg(qoh) qoh +from results +) +select i_product_name, i_brand, i_class, i_category,qoh +from results_rollup +order by qoh, i_product_name, i_brand, i_class, i_category +limit 100; +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q23.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q23.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q23.test new file mode 100644 index 0000000..0206859 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q23.test @@ -0,0 +1,63 @@ +==== +---- QUERY: TPCDS-Q23 + +with frequent_ss_items as + (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt + from store_sales + ,date_dim + ,item + where ss_sold_date_sk = d_date_sk + and ss_item_sk = i_item_sk + and d_year in (1999,1999 + 1,1999 + 2,1999 + 3) + group by substr(i_item_desc,1,30),i_item_sk,d_date + having count(*) >4), + max_store_sales as + (select max(csales) tpcds_cmax + from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales + from store_sales + ,customer + ,date_dim + where ss_customer_sk = c_customer_sk + and ss_sold_date_sk = d_date_sk + and d_year in (1999,1999+1,1999+2,1999+3) + group by c_customer_sk) x), + best_ss_customer as + ( +select t1.* from ( +select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales +from store_sales ,customer +where ss_customer_sk = c_customer_sk +group by c_customer_sk) t1, +(select tpcds_cmax * 95/100 as c1 from max_store_sales) t2 +where t1.ssales > t2.c1 +) + select c_last_name,c_first_name,sales + from ((select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales + from catalog_sales + ,customer + ,date_dim + where d_year = 1999 + and d_moy = 3 + and cs_sold_date_sk = d_date_sk + and cs_item_sk in (select distinct item_sk from frequent_ss_items) + and cs_bill_customer_sk in (select distinct c_customer_sk from best_ss_customer) + and cs_bill_customer_sk = c_customer_sk + group by c_last_name,c_first_name) + union all + (select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales + from web_sales + ,customer + ,date_dim + where d_year = 1999 + and d_moy = 3 + and ws_sold_date_sk = d_date_sk + and ws_item_sk in (select distinct item_sk from frequent_ss_items) + and ws_bill_customer_sk in (select distinct c_customer_sk from best_ss_customer) + and ws_bill_customer_sk = c_customer_sk + group by c_last_name,c_first_name)) y + order by c_last_name,c_first_name,sales + limit 100; +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q25.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q25.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q25.test new file mode 100644 index 0000000..f90858f --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q25.test @@ -0,0 +1,54 @@ +==== +---- QUERY: TPCDS-Q25 + +select + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + ,max(ss_net_profit) as store_sales_profit + ,max(sr_net_loss) as store_returns_loss + ,max(cs_net_profit) as catalog_sales_profit + from + store_sales + ,store_returns + ,catalog_sales + ,date_dim d1 + ,date_dim d2 + ,date_dim d3 + ,store + ,item + where + d1.d_moy = 4 + and d1.d_year = 2002 + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and ss_customer_sk = sr_customer_sk + and ss_item_sk = sr_item_sk + and ss_ticket_number = sr_ticket_number + and sr_returned_date_sk = d2.d_date_sk + and d2.d_moy between 4 and 10 + and d2.d_year = 2002 + and sr_customer_sk = cs_bill_customer_sk + and sr_item_sk = cs_item_sk + and cs_sold_date_sk = d3.d_date_sk + and d3.d_moy between 4 and 10 + and d3.d_year = 2002 + group by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + order by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q26.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q26.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q26.test new file mode 100644 index 0000000..ff95118 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q26.test @@ -0,0 +1,27 @@ +==== +---- QUERY: TPCDS-Q26 + +select i_item_id, + avg(cs_quantity) agg1, + avg(cs_list_price) agg2, + avg(cs_coupon_amt) agg3, + avg(cs_sales_price) agg4 + from catalog_sales, customer_demographics, date_dim, item, promotion + where cs_sold_date_sk = d_date_sk and + cs_item_sk = i_item_sk and + cs_bill_cdemo_sk = cd_demo_sk and + cs_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 = 2000 + group by i_item_id + order by i_item_id + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q27.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q27.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q27.test new file mode 100644 index 0000000..349eec2 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q27.test @@ -0,0 +1,43 @@ +==== +---- QUERY: TPCDS-Q27 + +with results as + (select i_item_id, + s_state, 0 as g_state, + ss_quantity agg1, + ss_list_price agg2, + ss_coupon_amt agg3, + ss_sales_price agg4 + from store_sales, customer_demographics, 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 + ss_cdemo_sk = cd_demo_sk and + cd_gender = 'F' and + cd_marital_status = 'W' and + cd_education_status = 'Secondary' and + d_year = 2002 and + s_state in ('OK','MI', 'SC', 'OH', 'TN', 'NC') + ) + + select i_item_id, + s_state, g_state, agg1, agg2, agg3, agg4 + from ( + select i_item_id, s_state, 0 as g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, avg(agg4) agg4 from results + group by i_item_id, s_state + union all + select i_item_id, NULL AS s_state, 1 AS g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, + avg(agg4) agg4 from results + group by i_item_id + union all + select NULL AS i_item_id, NULL as s_state, 1 as g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, + avg(agg4) agg4 from results + ) foo + order by i_item_id, s_state + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q28.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q28.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q28.test new file mode 100644 index 0000000..f9100db --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q28.test @@ -0,0 +1,59 @@ +==== +---- QUERY: TPCDS-Q28 + +select * +from (select avg(ss_list_price) B1_LP + ,count(ss_list_price) B1_CNT + ,count(distinct ss_list_price) B1_CNTD + from store_sales + where ss_quantity between 0 and 5 + and (ss_list_price between 185 and 185+10 + or ss_coupon_amt between 10548 and 10548+1000 + or ss_wholesale_cost between 6 and 6+20)) B1, + (select avg(ss_list_price) B2_LP + ,count(ss_list_price) B2_CNT + ,count(distinct ss_list_price) B2_CNTD + from store_sales + where ss_quantity between 6 and 10 + and (ss_list_price between 28 and 28+10 + or ss_coupon_amt between 6100 and 6100+1000 + or ss_wholesale_cost between 27 and 27+20)) B2, + (select avg(ss_list_price) B3_LP + ,count(ss_list_price) B3_CNT + ,count(distinct ss_list_price) B3_CNTD + from store_sales + where ss_quantity between 11 and 15 + and (ss_list_price between 173 and 173+10 + or ss_coupon_amt between 6371 and 6371+1000 + or ss_wholesale_cost between 32 and 32+20)) B3, + (select avg(ss_list_price) B4_LP + ,count(ss_list_price) B4_CNT + ,count(distinct ss_list_price) B4_CNTD + from store_sales + where ss_quantity between 16 and 20 + and (ss_list_price between 101 and 101+10 + or ss_coupon_amt between 2938 and 2938+1000 + or ss_wholesale_cost between 21 and 21+20)) B4, + (select avg(ss_list_price) B5_LP + ,count(ss_list_price) B5_CNT + ,count(distinct ss_list_price) B5_CNTD + from store_sales + where ss_quantity between 21 and 25 + and (ss_list_price between 8 and 8+10 + or ss_coupon_amt between 5093 and 5093+1000 + or ss_wholesale_cost between 50 and 50+20)) B5, + (select avg(ss_list_price) B6_LP + ,count(ss_list_price) B6_CNT + ,count(distinct ss_list_price) B6_CNTD + from store_sales + where ss_quantity between 26 and 30 + and (ss_list_price between 110 and 110+10 + or ss_coupon_amt between 2276 and 2276+1000 + or ss_wholesale_cost between 36 and 36+20)) B6 +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q29.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q29.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q29.test new file mode 100644 index 0000000..7e212ac --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q29.test @@ -0,0 +1,53 @@ +==== +---- QUERY: TPCDS-Q29 + +select + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + ,max(ss_quantity) as store_sales_quantity + ,max(sr_return_quantity) as store_returns_quantity + ,max(cs_quantity) as catalog_sales_quantity + from + store_sales + ,store_returns + ,catalog_sales + ,date_dim d1 + ,date_dim d2 + ,date_dim d3 + ,store + ,item + where + d1.d_moy = 4 + and d1.d_year = 2000 + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and ss_customer_sk = sr_customer_sk + and ss_item_sk = sr_item_sk + and ss_ticket_number = sr_ticket_number + and sr_returned_date_sk = d2.d_date_sk + and d2.d_moy between 4 and 4 + 3 + and d2.d_year = 2000 + and sr_customer_sk = cs_bill_customer_sk + and sr_item_sk = cs_item_sk + and cs_sold_date_sk = d3.d_date_sk + and d3.d_year in (2000,2000+1,2000+2) + group by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + order by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q3.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q3.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q3.test new file mode 100644 index 0000000..af9e7ec --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q3.test @@ -0,0 +1,27 @@ +==== +---- QUERY: TPCDS-Q3 + +select dt.d_year + ,item.i_brand_id brand_id + ,item.i_brand brand + ,sum(ss_sales_price) sum_agg + from date_dim dt + ,store_sales + ,item + where dt.d_date_sk = store_sales.ss_sold_date_sk + and store_sales.ss_item_sk = item.i_item_sk + and item.i_manufact_id = 808 + and dt.d_moy=12 + group by dt.d_year + ,item.i_brand + ,item.i_brand_id + order by dt.d_year + ,sum_agg desc + ,brand_id + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q30.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q30.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q30.test new file mode 100644 index 0000000..8919688 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q30.test @@ -0,0 +1,37 @@ +==== +---- QUERY: TPCDS-Q30 + +with customer_total_return as + (select wr_returning_customer_sk as ctr_customer_sk + ,ca_state as ctr_state, + sum(wr_return_amt) as ctr_total_return + from web_returns + ,date_dim + ,customer_address + where wr_returned_date_sk = d_date_sk + and d_year =2002 + and wr_returning_addr_sk = ca_address_sk + group by wr_returning_customer_sk + ,ca_state) + select c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag + ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address + ,c_last_review_date,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 = 'VT' + and ctr1.ctr_customer_sk = c_customer_sk + order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag + ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address + ,c_last_review_date,ctr_total_return +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test new file mode 100644 index 0000000..07d519e --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test @@ -0,0 +1,58 @@ +==== +---- QUERY: TPCDS-Q31 + +with ss as + (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales + from store_sales,date_dim,customer_address + where ss_sold_date_sk = d_date_sk + and ss_addr_sk=ca_address_sk + group by ca_county,d_qoy, d_year), + ws as + (select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales + from web_sales,date_dim,customer_address + where ws_sold_date_sk = d_date_sk + and ws_bill_addr_sk=ca_address_sk + group by ca_county,d_qoy, d_year) + select + ss1.ca_county + ,ss1.d_year + ,ws2.web_sales/ws1.web_sales web_q1_q2_increase + ,ss2.store_sales/ss1.store_sales store_q1_q2_increase + ,ws3.web_sales/ws2.web_sales web_q2_q3_increase + ,ss3.store_sales/ss2.store_sales store_q2_q3_increase + from + ss ss1 + ,ss ss2 + ,ss ss3 + ,ws ws1 + ,ws ws2 + ,ws ws3 + where + ss1.d_qoy = 1 + and ss1.d_year = 2002 + and ss1.ca_county = ss2.ca_county + and ss2.d_qoy = 2 + and ss2.d_year = 2002 + and ss2.ca_county = ss3.ca_county + and ss3.d_qoy = 3 + and ss3.d_year = 2002 + and ss1.ca_county = ws1.ca_county + and ws1.d_qoy = 1 + and ws1.d_year = 2002 + and ws1.ca_county = ws2.ca_county + and ws2.d_qoy = 2 + and ws2.d_year = 2002 + and ws1.ca_county = ws3.ca_county + and ws3.d_qoy = 3 + and ws3.d_year =2002 + and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end + > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end + and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end + > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end + order by ss1.d_year; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q32.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q32.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q32.test new file mode 100644 index 0000000..3befd61 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q32.test @@ -0,0 +1,34 @@ +==== +---- QUERY: TPCDS-Q32 + +select sum(cs_ext_discount_amt) as "excess discount amount" +from + catalog_sales + ,item + ,date_dim +where +i_manufact_id = 74 +and i_item_sk = cs_item_sk +and d_date between '2000-01-07' and + (cast('2000-01-07' as timestamp) + interval 90 days) +and d_date_sk = cs_sold_date_sk +and cs_ext_discount_amt + > ( + select + 1.3 * avg(cs_ext_discount_amt) + from + catalog_sales + ,date_dim + where + cs_item_sk = i_item_sk + and d_date between '2000-01-07' and + (cast('2000-01-07' as timestamp) + interval 90 days) + and d_date_sk = cs_sold_date_sk + ) +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q33.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q33.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q33.test new file mode 100644 index 0000000..8f44f47 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q33.test @@ -0,0 +1,81 @@ +==== +---- QUERY: TPCDS-Q33 + +with ss as ( + select + i_manufact_id,sum(ss_ext_sales_price) total_sales + from + store_sales, + date_dim, + customer_address, + item + where + i_manufact_id in (select + i_manufact_id +from + item +where i_category in ('Electronics')) + and ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and d_year = 2001 + and d_moy = 3 + and ss_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_manufact_id), + cs as ( + select + i_manufact_id,sum(cs_ext_sales_price) total_sales + from + catalog_sales, + date_dim, + customer_address, + item + where + i_manufact_id in (select + i_manufact_id +from + item +where i_category in ('Electronics')) + and cs_item_sk = i_item_sk + and cs_sold_date_sk = d_date_sk + and d_year = 2001 + and d_moy = 3 + and cs_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_manufact_id), + ws as ( + select + i_manufact_id,sum(ws_ext_sales_price) total_sales + from + web_sales, + date_dim, + customer_address, + item + where + i_manufact_id in (select + i_manufact_id +from + item +where i_category in ('Electronics')) + and ws_item_sk = i_item_sk + and ws_sold_date_sk = d_date_sk + and d_year = 2001 + and d_moy = 3 + and ws_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_manufact_id) + select i_manufact_id ,sum(total_sales) total_sales + from (select * from ss + union all + select * from cs + union all + select * from ws) tmp1 + group by i_manufact_id + order by total_sales +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q34.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q34.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q34.test new file mode 100644 index 0000000..a0eded9 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q34.test @@ -0,0 +1,37 @@ +==== +---- QUERY: TPCDS-Q34 + +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 3 or date_dim.d_dom between 25 and 28) + and (household_demographics.hd_buy_potential = '>10000' or + household_demographics.hd_buy_potential = '0-500') + 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.2 + and date_dim.d_year in (1999,1999+1,1999+2) + and store.s_county in ('Daviess County','Perry County','Adams County','Raleigh County', + 'Stillwater County','Arthur County','Red River Parish','Karnes County') + group by ss_ticket_number,ss_customer_sk) dn,customer + where ss_customer_sk = c_customer_sk + and cnt between 15 and 20 + order by c_last_name,c_first_name,c_salutation,c_preferred_cust_flag desc; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q35.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q35.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q35.test new file mode 100644 index 0000000..8b8a27d --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q35.test @@ -0,0 +1,66 @@ +==== +---- QUERY: TPCDS-Q35 + +with bla as (select +ss_customer_sk as customer_sk +from +store_sales, +date_dim +where +ss_sold_date_sk = d_date_sk +and d_year = 1999 +and d_qoy < 4 +group by ss_customer_sk union all select +ws_bill_customer_sk as customer_sk +from +web_sales, +date_dim +where +ws_sold_date_sk = d_date_sk +and d_year = 1999 +and d_qoy < 4 +group by ws_bill_customer_sk union all select +cs_ship_customer_sk customer_sk +from +catalog_sales, +date_dim +where +cs_sold_date_sk = d_date_sk +and d_year = 1999 +and d_qoy < 4 +group by cs_ship_customer_sk) +select +ca_state, +cd_gender, +cd_marital_status, +cd_dep_count, +count(*) cnt1, +avg(cd_dep_count), +max(cd_dep_count), +sum(cd_dep_count), +cd_dep_employed_count, +count(*) cnt2, +avg(cd_dep_employed_count), +max(cd_dep_employed_count), +sum(cd_dep_employed_count), +cd_dep_college_count, +count(*) cnt3, +avg(cd_dep_college_count), +max(cd_dep_college_count), +sum(cd_dep_college_count) +from +customer c, +customer_address ca, +customer_demographics cd, +bla +where +c.c_current_addr_sk = ca.ca_address_sk +and cd_demo_sk = c.c_current_cdemo_sk +and c.c_customer_sk = bla.customer_sk +group by ca_state , cd_gender , cd_marital_status , cd_dep_count , cd_dep_employed_count , cd_dep_college_count +order by ca_state , cd_gender , cd_marital_status , cd_dep_count , cd_dep_employed_count , cd_dep_college_count +limit 100; +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q36.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q36.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q36.test new file mode 100644 index 0000000..fe015ae --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q36.test @@ -0,0 +1,47 @@ +==== +---- QUERY: TPCDS-Q36 + + with results as + (select + sum(ss_net_profit) as ss_net_profit, sum(ss_ext_sales_price) as ss_ext_sales_price, + sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin + ,i_category + ,i_class + ,0 as g_category, 0 as g_class + from + store_sales + ,date_dim d1 + ,item + ,store + where + d1.d_year = 2001 + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and s_state in ('AL','SD','TN','SD', + 'SD','SD','TN','SD') + group by i_category,i_class) + , + results_rollup as + (select gross_margin ,i_category ,i_class,0 as t_category, 0 as t_class, 0 as lochierarchy from results + union + select sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin, + i_category, NULL AS i_class, 0 as t_category, 1 as t_class, 1 as lochierarchy from results group by i_category + union + select sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin, + NULL AS i_category ,NULL AS i_class, 1 as t_category, 1 as t_class, 2 as lochierarchy from results) + select + gross_margin ,i_category ,i_class, lochierarchy,rank() over ( + partition by lochierarchy, case when t_class = 0 then i_category end + order by gross_margin asc) as rank_within_parent + from results_rollup + order by + lochierarchy desc + ,case when lochierarchy = 0 then i_category end + ,rank_within_parent + limit 100; + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q37.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q37.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q37.test new file mode 100644 index 0000000..4c4b9e7 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q37.test @@ -0,0 +1,23 @@ +==== +---- QUERY: TPCDS-Q37 + +select i_item_id + ,i_item_desc + ,i_current_price + from item, inventory, date_dim, catalog_sales + where i_current_price between 42 and 42 + 30 + and inv_item_sk = i_item_sk + and d_date_sk=inv_date_sk + and d_date between cast('1998-01-04' as timestamp) and (cast('1998-01-04' as timestamp) + interval 60 days) + and i_manufact_id in (867,919,754,825) + and inv_quantity_on_hand between 100 and 500 + and cs_item_sk = i_item_sk + group by i_item_id,i_item_desc,i_current_price + order by i_item_id + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q38.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q38.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q38.test new file mode 100644 index 0000000..84e3f94 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q38.test @@ -0,0 +1,67 @@ +==== +---- QUERY: TPCDS-Q38 + + +select +count(*) + from + (select c_last_name,c_first_name,d_date, sum(q18.c3) c3,count(*) c4 + from + ( + select + c_last_name,c_first_name,d_date,1 as c3 + from + (select c_last_name,c_first_name,d_date + from + (select c_last_name, c_first_name, d_date, q14.c3 c3, q14.c4 c4 + from + (select + c_last_name,c_first_name,d_date,sum(q13.c3) c3,count(*) c4 + from + ( + select c_last_name,c_first_name,d_date,1 as c3 + from + customer, date_dim, store_sales + where + (d_month_seq between 1215 and 1226) and + (ss_customer_sk = c_customer_sk) and + (ss_sold_date_sk = d_date_sk) + union all + select c_last_name,c_first_name,d_date,-1 as c3 + from + customer, date_dim, catalog_sales + where + (d_month_seq between 1215 and 1226) and + (cs_bill_customer_sk = c_customer_sk) and + (cs_sold_date_sk = d_date_sk) + ) as q13 + group by + c_last_name, + c_first_name, + d_date + ) as q14 + where + ((q14.c4 - case when (q14.c3 >= 0) then q14.c3 else -(q14.c3) end) >= 2) + ) as q15 + ) as q16 + union all + select c_last_name,c_first_name,d_date,-1 as c3 + from + customer,date_dim,web_sales + where + (d_month_seq between 1215 and 1226) and + (ws_bill_customer_sk = c_customer_sk) and + (ws_sold_date_sk = d_date_sk) + ) as q18 + group by + c_last_name, + c_first_name, + d_date + ) as q19 + where + ((q19.c4 - case when (q19.c3 >= 0) then q19.c3 else -(q19.c3) end) >= 2) +; +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q39.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q39.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q39.test new file mode 100644 index 0000000..5846957 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q39.test @@ -0,0 +1,33 @@ +==== +---- QUERY: TPCDS-Q39 + +with inv as +(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy + ,stdev,mean, case mean when 0 then null else stdev/mean end cov + from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy + ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean + from inventory + ,item + ,warehouse + ,date_dim + where inv_item_sk = i_item_sk + and inv_warehouse_sk = w_warehouse_sk + and inv_date_sk = d_date_sk + and d_year =2000 + group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo + where case mean when 0 then 0 else stdev/mean end > 1) +select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov + ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov +from inv inv1,inv inv2 +where inv1.i_item_sk = inv2.i_item_sk + and inv1.w_warehouse_sk = inv2.w_warehouse_sk + and inv1.d_moy=2 + and inv2.d_moy=2+1 +order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov + ,inv2.d_moy,inv2.mean, inv2.cov +; + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q4.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q4.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q4.test new file mode 100644 index 0000000..947fa3a --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q4.test @@ -0,0 +1,122 @@ +==== +---- QUERY: TPCDS-Q4 + +with year_total as ( + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,c_preferred_cust_flag customer_preferred_cust_flag + ,c_birth_country customer_birth_country + ,c_login customer_login + ,c_email_address customer_email_address + ,d_year dyear + ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) 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 + group by c_customer_id + ,c_first_name + ,c_last_name + ,c_preferred_cust_flag + ,c_birth_country + ,c_login + ,c_email_address + ,d_year + union all + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,c_preferred_cust_flag customer_preferred_cust_flag + ,c_birth_country customer_birth_country + ,c_login customer_login + ,c_email_address customer_email_address + ,d_year dyear + ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total + ,'c' sale_type + from customer + ,catalog_sales + ,date_dim + where c_customer_sk = cs_bill_customer_sk + and cs_sold_date_sk = d_date_sk + group by c_customer_id + ,c_first_name + ,c_last_name + ,c_preferred_cust_flag + ,c_birth_country + ,c_login + ,c_email_address + ,d_year +union all + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,c_preferred_cust_flag customer_preferred_cust_flag + ,c_birth_country customer_birth_country + ,c_login customer_login + ,c_email_address customer_email_address + ,d_year dyear + ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) 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 + group by c_customer_id + ,c_first_name + ,c_last_name + ,c_preferred_cust_flag + ,c_birth_country + ,c_login + ,c_email_address + ,d_year + ) + select + t_s_secyear.customer_id + ,t_s_secyear.customer_first_name + ,t_s_secyear.customer_last_name + ,t_s_secyear.customer_email_address + from year_total t_s_firstyear + ,year_total t_s_secyear + ,year_total t_c_firstyear + ,year_total t_c_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_c_secyear.customer_id + and t_s_firstyear.customer_id = t_c_firstyear.customer_id + and t_s_firstyear.customer_id = t_w_firstyear.customer_id + and t_s_firstyear.customer_id = t_w_secyear.customer_id + and t_s_firstyear.sale_type = 's' + and t_c_firstyear.sale_type = 'c' + and t_w_firstyear.sale_type = 'w' + and t_s_secyear.sale_type = 's' + and t_c_secyear.sale_type = 'c' + and t_w_secyear.sale_type = 'w' + and t_s_firstyear.dyear = 1999 + and t_s_secyear.dyear = 1999+1 + and t_c_firstyear.dyear = 1999 + and t_c_secyear.dyear = 1999+1 + and t_w_firstyear.dyear = 1999 + and t_w_secyear.dyear = 1999+1 + and t_s_firstyear.year_total > 0 + and t_c_firstyear.year_total > 0 + and t_w_firstyear.year_total > 0 + and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_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 + and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end + > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end + order by t_s_secyear.customer_id + ,t_s_secyear.customer_first_name + ,t_s_secyear.customer_last_name + ,t_s_secyear.customer_email_address +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q40.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q40.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q40.test new file mode 100644 index 0000000..8106b3c --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q40.test @@ -0,0 +1,34 @@ +==== +---- QUERY: TPCDS-Q40 + +select + w_state + ,i_item_id + ,sum(case when (cast(d_date as timestamp) < cast ('2000-05-30' as timestamp)) + then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before + ,sum(case when (cast(d_date as timestamp) >= cast ('2000-05-30' as timestamp)) + then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after + from + catalog_sales left outer join catalog_returns on + (cs_order_number = cr_order_number + and cs_item_sk = cr_item_sk) + ,warehouse + ,item + ,date_dim + where + i_current_price between 0.99 and 1.49 + and i_item_sk = cs_item_sk + and cs_warehouse_sk = w_warehouse_sk + and cs_sold_date_sk = d_date_sk + and d_date between (cast ('2000-05-30' as timestamp) - interval 30 days) + and (cast ('2000-05-30' as timestamp) + interval 30 days) + group by + w_state,i_item_id + order by w_state,i_item_id +limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q41.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q41.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q41.test new file mode 100644 index 0000000..f03af63 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q41.test @@ -0,0 +1,62 @@ +==== +---- QUERY: TPCDS-Q41 + +select distinct(i_product_name) + from item i1 +JOIN + -- and +(select i_manufact, count(*) as item_cnt + from item + where ( + ((i_category = 'Women' and + (i_color = 'navajo' or i_color = 'bisque') and + (i_units = 'Box' or i_units = 'Lb') and + (i_size = 'medium' or i_size = 'small') + ) or + (i_category = 'Women' and + (i_color = 'spring' or i_color = 'dim') and + (i_units = 'Dram' or i_units = 'Gross') and + (i_size = 'N/A' or i_size = 'petite') + ) or + (i_category = 'Men' and + (i_color = 'burlywood' or i_color = 'brown') and + (i_units = 'Tsp' or i_units = 'Gram') and + (i_size = 'extra large' or i_size = 'economy') + ) or + (i_category = 'Men' and + (i_color = 'forest' or i_color = 'lime') and + (i_units = 'Ounce' or i_units = 'Case') and + (i_size = 'medium' or i_size = 'small') + ))) or + ( + ((i_category = 'Women' and + (i_color = 'honeydew' or i_color = 'cornflower') and + (i_units = 'Pallet' or i_units = 'Bunch') and + (i_size = 'medium' or i_size = 'small') + ) or + (i_category = 'Women' and + (i_color = 'deep' or i_color = 'peru') and + (i_units = 'Tbl' or i_units = 'Ton') and + (i_size = 'N/A' or i_size = 'petite') + ) or + (i_category = 'Men' and + (i_color = 'floral' or i_color = 'violet') and + (i_units = 'Cup' or i_units = 'Unknown') and + (i_size = 'extra large' or i_size = 'economy') + ) or + (i_category = 'Men' and + (i_color = 'plum' or i_color = 'green') and + (i_units = 'Each' or i_units = 'Carton') and + (i_size = 'medium' or i_size = 'small') + ))) + group by i_manufact) i2 +ON i1.i_manufact = i2.i_manufact +where i1.i_manufact_id between 835 and 835+40 +and i2.item_cnt > 0 + order by i_product_name + limit 100; + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q42.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q42.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q42.test new file mode 100644 index 0000000..762414d --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q42.test @@ -0,0 +1,28 @@ +==== +---- QUERY: TPCDS-Q42 + +select dt.d_year + ,item.i_category_id + ,item.i_category + ,sum(ss_ext_sales_price) + from date_dim dt + ,store_sales + ,item + where dt.d_date_sk = store_sales.ss_sold_date_sk + and store_sales.ss_item_sk = item.i_item_sk + and item.i_manager_id = 1 + and dt.d_moy=12 + and dt.d_year=2000 + group by dt.d_year + ,item.i_category_id + ,item.i_category + order by sum(ss_ext_sales_price) desc,dt.d_year + ,item.i_category_id + ,item.i_category +limit 100 ; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q43.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q43.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q43.test new file mode 100644 index 0000000..c208b05 --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q43.test @@ -0,0 +1,25 @@ +==== +---- QUERY: TPCDS-Q43 + +select s_store_name, s_store_id, + sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales, + sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales, + sum(case when (d_day_name='Tuesday') then ss_sales_price else null end) tue_sales, + sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales, + sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales, + sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales, + sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales + from date_dim, store_sales, store + where d_date_sk = ss_sold_date_sk and + s_store_sk = ss_store_sk and + s_gmt_offset = -5 and + d_year = 2000 + group by s_store_name, s_store_id + order by s_store_name, s_store_id,sun_sales,mon_sales,tue_sales,wed_sales,thu_sales,fri_sales,sat_sales + limit 100; + + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q44.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q44.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q44.test new file mode 100644 index 0000000..601acdb --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q44.test @@ -0,0 +1,54 @@ +==== +---- QUERY: TPCDS-Q44 + +select asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing +from(select * + from (select item_sk,rank() over (order by rank_col asc) rnk + from ( + select item_sk, rank_col from + (select ss_item_sk item_sk,avg(ss_net_profit) rank_col + from store_sales ss1 + where ss_store_sk = 218 + group by ss_item_sk) a1 cross join + ( select avg(ss_net_profit) * 0.9 cmp_col + from store_sales + where ss_store_sk = 218 + and ss_hdemo_sk is null + group by ss_store_sk limit 1 + ) a2 + where a1.rank_col > a2.cmp_col + ) + V1)V11 + where rnk < 11) asceding, + (select * + from (select item_sk,rank() over (order by rank_col desc) rnk + from + ( + select item_sk, rank_col from + (select ss_item_sk item_sk,avg(ss_net_profit) rank_col + from store_sales ss1 + where ss_store_sk = 218 + group by ss_item_sk) a1 + cross join + (select avg(ss_net_profit) * 0.9 cmp_col + from store_sales + where ss_store_sk = 218 + and ss_hdemo_sk is null + group by ss_store_sk limit 1 + ) a2 + where a1.rank_col > a2.cmp_col + ) + V1)V11 + where rnk < 11) descending, +item i1, +item i2 +where asceding.rnk = descending.rnk + and i1.i_item_sk=asceding.item_sk + and i2.i_item_sk=descending.item_sk +order by asceding.rnk +limit 100; + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q45.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q45.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q45.test new file mode 100644 index 0000000..3d5460c --- /dev/null +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q45.test @@ -0,0 +1,40 @@ +==== +---- QUERY: TPCDS-Q45 + +select t1.ca_zip, t1.ca_city, sum(t1.ws_sales_price) from ( +( +select ca_zip, ca_city, ws_sales_price + from web_sales, customer , customer_address, date_dim + where (ws_bill_customer_sk = c_customer_sk + and c_current_addr_sk = ca_address_sk + and substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') + and ws_sold_date_sk = d_date_sk + and d_qoy = 2 and d_year = 2002) + group by ca_zip, ca_city, ws_sales_price + order by ca_zip, ca_city, ws_sales_price +) +union +( +select ca_zip, ca_city, ws_sales_price +from web_sales, customer , customer_address, item, date_dim +where (ws_bill_customer_sk = c_customer_sk + and c_current_addr_sk = ca_address_sk + and ws_item_sk = i_item_sk + and i_item_id in (select i_item_id + from item + where i_item_sk in (2, 3, 5, 7, 11, 13, 17, 19, 23, 29) + ) + and ws_sold_date_sk = d_date_sk + and d_qoy = 2 and d_year = 2002) + group by ca_zip, ca_city, ws_sales_price + order by ca_zip, ca_city, ws_sales_price +) +) t1 + group by ca_zip, ca_city + order by ca_zip, ca_city +; + +---- RESULTS +---- TYPES +INT, INT, STRING, DECIMAL +====