You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by ar...@apache.org on 2018/05/14 18:24:55 UTC
[4/5] impala git commit: IMPALA-6819: Add new performance test
workload - tpcds-unmodified used by Impala Performance Tests
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 <dk...@cloudera.com>
Tested-by: Impala Public Jenkins <im...@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 <nj...@cloudera.com>
Authored: Tue Apr 10 10:44:47 2018 -0700
Committer: Impala Public Jenkins <im...@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
+====