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:53 UTC
[2/5] impala git commit: IMPALA-6819: Add new performance test
workload - tpcds-unmodified used by Impala Performance Tests
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q8.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q8.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q8.test
new file mode 100644
index 0000000..950cb37
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q8.test
@@ -0,0 +1,64 @@
+====
+---- QUERY: TPCDS-Q8
+
+select s_store_name
+ ,sum(ss_net_profit)
+ from store_sales
+ ,date_dim
+ ,store,
+ (select distinct a01.ca_zip
+ from
+ (SELECT substr(ca_zip,1,5) ca_zip
+ FROM customer_address
+ WHERE substr(ca_zip,1,5) IN ('89436', '30868', '65085', '22977', '83927', '77557', '58429', '40697', '80614', '10502', '32779',
+ '91137', '61265', '98294', '17921', '18427', '21203', '59362', '87291', '84093', '21505', '17184', '10866', '67898', '25797',
+ '28055', '18377', '80332', '74535', '21757', '29742', '90885', '29898', '17819', '40811', '25990', '47513', '89531', '91068',
+ '10391', '18846', '99223', '82637', '41368', '83658', '86199', '81625', '26696', '89338', '88425', '32200', '81427', '19053',
+ '77471', '36610', '99823', '43276', '41249', '48584', '83550', '82276', '18842', '78890', '14090', '38123', '40936', '34425',
+ '19850', '43286', '80072', '79188', '54191', '11395', '50497', '84861', '90733', '21068', '57666', '37119', '25004', '57835',
+ '70067', '62878', '95806', '19303', '18840', '19124', '29785', '16737', '16022', '49613', '89977', '68310', '60069', '98360',
+ '48649', '39050', '41793', '25002', '27413', '39736', '47208', '16515', '94808', '57648', '15009', '80015', '42961', '63982',
+ '21744', '71853', '81087', '67468', '34175', '64008', '20261', '11201', '51799', '48043', '45645', '61163', '48375', '36447',
+ '57042', '21218', '41100', '89951', '22745', '35851', '83326', '61125', '78298', '80752', '49858', '52940', '96976', '63792',
+ '11376', '53582', '18717', '90226', '50530', '94203', '99447', '27670', '96577', '57856', '56372', '16165', '23427', '54561',
+ '28806', '44439', '22926', '30123', '61451', '92397', '56979', '92309', '70873', '13355', '21801', '46346', '37562', '56458',
+ '28286', '47306', '99555', '69399', '26234', '47546', '49661', '88601', '35943', '39936', '25632', '24611', '44166', '56648',
+ '30379', '59785', '11110', '14329', '93815', '52226', '71381', '13842', '25612', '63294', '14664', '21077', '82626', '18799',
+ '60915', '81020', '56447', '76619', '11433', '13414', '42548', '92713', '70467', '30884', '47484', '16072', '38936', '13036',
+ '88376', '45539', '35901', '19506', '65690', '73957', '71850', '49231', '14276', '20005', '18384', '76615', '11635', '38177',
+ '55607', '41369', '95447', '58581', '58149', '91946', '33790', '76232', '75692', '95464', '22246', '51061', '56692', '53121',
+ '77209', '15482', '10688', '14868', '45907', '73520', '72666', '25734', '17959', '24677', '66446', '94627', '53535', '15560',
+ '41967', '69297', '11929', '59403', '33283', '52232', '57350', '43933', '40921', '36635', '10827', '71286', '19736', '80619',
+ '25251', '95042', '15526', '36496', '55854', '49124', '81980', '35375', '49157', '63512', '28944', '14946', '36503', '54010',
+ '18767', '23969', '43905', '66979', '33113', '21286', '58471', '59080', '13395', '79144', '70373', '67031', '38360', '26705',
+ '50906', '52406', '26066', '73146', '15884', '31897', '30045', '61068', '45550', '92454', '13376', '14354', '19770', '22928',
+ '97790', '50723', '46081', '30202', '14410', '20223', '88500', '67298', '13261', '14172', '81410', '93578', '83583', '46047',
+ '94167', '82564', '21156', '15799', '86709', '37931', '74703', '83103', '23054', '70470', '72008', '35709', '91911', '69998',
+ '20961', '70070', '63197', '54853', '88191', '91830', '49521', '19454', '81450', '89091', '62378', '31904', '61869', '51744',
+ '36580', '85778', '36871', '48121', '28810', '83712', '45486', '67393', '26935', '42393', '20132', '55349', '86057', '21309',
+ '80218', '10094', '11357', '48819', '39734', '40758', '30432', '21204', '29467', '30214', '61024', '55307', '74621', '11622',
+ '68908', '33032', '52868', '99194', '99900', '84936', '69036', '99149', '45013', '32895', '59004', '32322', '14933', '32936',
+ '33562', '72550', '27385', '58049', '58200', '16808', '21360', '32961', '18586', '79307', '15492'
+ )) a01
+ inner join
+ (select ca_zip
+ from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt
+ FROM customer_address, customer
+ WHERE ca_address_sk = c_current_addr_sk and
+ c_preferred_cust_flag='Y'
+ group by ca_zip
+ having count(*) > 10)A1
+ ) b11
+ on (a01.ca_zip = b11.ca_zip )) A2
+ where ss_store_sk = s_store_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_qoy = 2 and d_year = 1999
+ and (substr(s_zip,1,2) = substr(a2.ca_zip,1,2))
+ group by s_store_name
+ order by 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-q80.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q80.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q80.test
new file mode 100644
index 0000000..def4a6a
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q80.test
@@ -0,0 +1,116 @@
+====
+---- QUERY: TPCDS-Q80
+
+ with ssr as
+ (select s_store_id as store_id,
+ sum(ss_ext_sales_price) as sales,
+ sum(coalesce(sr_return_amt, 0)) as sreturns,
+ sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit
+ from store_sales left outer join store_returns on
+ (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number),
+ date_dim,
+ store,
+ item,
+ promotion
+ where ss_sold_date_sk = d_date_sk
+ and d_date between cast('2000-08-10' as timestamp)
+ and (cast('2000-08-10' as timestamp) + interval 30 days)
+ and ss_store_sk = s_store_sk
+ and ss_item_sk = i_item_sk
+ and i_current_price > 50
+ and ss_promo_sk = p_promo_sk
+ and p_channel_tv = 'N'
+ group by s_store_id)
+ ,
+ csr as
+ (select cp_catalog_page_id as catalog_page_id,
+ sum(cs_ext_sales_price) as sales,
+ sum(coalesce(cr_return_amount, 0)) as sreturns,
+ sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit
+ from catalog_sales left outer join catalog_returns on
+ (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number),
+ date_dim,
+ catalog_page,
+ item,
+ promotion
+ where cs_sold_date_sk = d_date_sk
+ and d_date between cast('2000-08-10' as timestamp)
+ and (cast('2000-08-10' as timestamp) + interval 30 days)
+ and cs_catalog_page_sk = cp_catalog_page_sk
+ and cs_item_sk = i_item_sk
+ and i_current_price > 50
+ and cs_promo_sk = p_promo_sk
+ and p_channel_tv = 'N'
+group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select web_site_id,
+ sum(ws_ext_sales_price) as sales,
+ sum(coalesce(wr_return_amt, 0)) as sreturns,
+ sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit
+ from web_sales left outer join web_returns on
+ (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number),
+ date_dim,
+ web_site,
+ item,
+ promotion
+ where ws_sold_date_sk = d_date_sk
+ and d_date between cast('2000-08-10' as timestamp)
+ and (cast('2000-08-10' as timestamp) + interval 30 days)
+ and ws_web_site_sk = web_site_sk
+ and ws_item_sk = i_item_sk
+ and i_current_price > 50
+ and ws_promo_sk = p_promo_sk
+ and p_channel_tv = 'N'
+group by web_site_id)
+,
+results as
+ (select channel
+ , id
+ , sum(sales) as sales
+ , sum(sreturns) as sreturns
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , concat('store' , store_id) as id
+ , sales
+ , sreturns
+ , profit
+ from ssr
+ union all
+ select 'catalog channel' as channel
+ , concat('catalog_page' , catalog_page_id) as id
+ , sales
+ , sreturns
+ , profit
+ from csr
+ union all
+ select 'web channel' as channel
+ , concat('web_site' , web_site_id) as id
+ , sales
+ , sreturns
+ , profit
+ from wsr
+ ) x
+ group by channel, id)
+
+ select channel
+ , id
+ , sales
+ , sreturns
+ , profit
+ from (
+ select channel, id, sales, sreturns, profit from results
+ union
+ select channel, NULL AS id, sum(sales) as sales, sum(sreturns) as sreturns, sum(profit) as profit from results group by channel
+ union
+ select NULL AS channel, NULL AS id, sum(sales) as sales, sum(sreturns) as sreturns, sum(profit) as profit from results
+ ) foo
+ order by channel, 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-q81.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q81.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q81.test
new file mode 100644
index 0000000..b2c2971
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q81.test
@@ -0,0 +1,37 @@
+====
+---- QUERY: TPCDS-Q81
+
+with customer_total_return as
+ (select cr_returning_customer_sk as ctr_customer_sk
+ ,ca_state as ctr_state,
+ sum(cr_return_amt_inc_tax) as ctr_total_return
+ from catalog_returns
+ ,date_dim
+ ,customer_address
+ where cr_returned_date_sk = d_date_sk
+ and d_year =2000
+ and cr_returning_addr_sk = ca_address_sk
+ group by cr_returning_customer_sk
+ ,ca_state )
+ select c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
+ ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
+ ,ca_location_type,ctr_total_return
+ from customer_total_return ctr1
+ ,customer_address
+ ,customer
+ where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
+ from customer_total_return ctr2
+ where ctr1.ctr_state = ctr2.ctr_state)
+ and ca_address_sk = c_current_addr_sk
+ and ca_state = 'VT'
+ and ctr1.ctr_customer_sk = c_customer_sk
+ order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
+ ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
+ ,ca_location_type,ctr_total_return
+ limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q82.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q82.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q82.test
new file mode 100644
index 0000000..9f2690f
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q82.test
@@ -0,0 +1,23 @@
+====
+---- QUERY: TPCDS-Q82
+
+select i_item_id
+ ,i_item_desc
+ ,i_current_price
+ from item, inventory, date_dim, store_sales
+ where i_current_price between 16 and 16+30
+ and inv_item_sk = i_item_sk
+ and d_date_sk=inv_date_sk
+ and d_date between cast('1999-05-20' as timestamp) and (cast('1999-05-20' as timestamp) + interval 60 days)
+ and i_manufact_id in (24,52,75,394)
+ and inv_quantity_on_hand between 100 and 500
+ and ss_item_sk = i_item_sk
+ group by i_item_id,i_item_desc,i_current_price
+ order by i_item_id
+ limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q83.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q83.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q83.test
new file mode 100644
index 0000000..d4deff3
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q83.test
@@ -0,0 +1,73 @@
+====
+---- QUERY: TPCDS-Q83
+
+with sr_items as
+ (select i_item_id item_id,
+ sum(sr_return_quantity) sr_item_qty
+ from store_returns,
+ item,
+ date_dim
+ where sr_item_sk = i_item_sk
+ and d_date in
+ (select d_date
+ from date_dim
+ where d_week_seq in
+ (select d_week_seq
+ from date_dim
+ where d_date in ('2000-01-24','2000-10-20','2000-11-05')))
+ and sr_returned_date_sk = d_date_sk
+ group by i_item_id),
+ cr_items as
+ (select i_item_id item_id,
+ sum(cr_return_quantity) cr_item_qty
+ from catalog_returns,
+ item,
+ date_dim
+ where cr_item_sk = i_item_sk
+ and d_date in
+ (select d_date
+ from date_dim
+ where d_week_seq in
+ (select d_week_seq
+ from date_dim
+ where d_date in ('2000-01-24','2000-10-20','2000-11-05')))
+ and cr_returned_date_sk = d_date_sk
+ group by i_item_id),
+ wr_items as
+ (select i_item_id item_id,
+ sum(wr_return_quantity) wr_item_qty
+ from web_returns,
+ item,
+ date_dim
+ where wr_item_sk = i_item_sk
+ and d_date in
+ (select d_date
+ from date_dim
+ where d_week_seq in
+ (select d_week_seq
+ from date_dim
+ where d_date in ('2000-01-24','2000-10-20','2000-11-05')))
+ and wr_returned_date_sk = d_date_sk
+ group by i_item_id)
+ select sr_items.item_id
+ ,sr_item_qty
+ ,sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev
+ ,cr_item_qty
+ ,cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev
+ ,wr_item_qty
+ ,wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev
+ ,(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average
+ from sr_items
+ ,cr_items
+ ,wr_items
+ where sr_items.item_id=cr_items.item_id
+ and sr_items.item_id=wr_items.item_id
+ order by sr_items.item_id
+ ,sr_item_qty
+ limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q84.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q84.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q84.test
new file mode 100644
index 0000000..e9eb791
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q84.test
@@ -0,0 +1,27 @@
+====
+---- QUERY: TPCDS-Q84
+
+select c_customer_id as customer_id
+ ,concat(c_last_name, ', ', c_first_name) as customername
+ from customer
+ ,customer_address
+ ,customer_demographics
+ ,household_demographics
+ ,income_band
+ ,store_returns
+ where ca_city = 'Mount Zion'
+ and c_current_addr_sk = ca_address_sk
+ and ib_lower_bound >= 16807
+ and ib_upper_bound <= 16807 + 50000
+ and ib_income_band_sk = hd_income_band_sk
+ and cd_demo_sk = c_current_cdemo_sk
+ and hd_demo_sk = c_current_hdemo_sk
+ and sr_cdemo_sk = cd_demo_sk
+ order by c_customer_id
+ limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q85.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q85.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q85.test
new file mode 100644
index 0000000..14c95df
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q85.test
@@ -0,0 +1,90 @@
+====
+---- QUERY: TPCDS-Q85
+
+select substr(r_reason_desc,1,20)
+ ,avg(ws_quantity)
+ ,avg(wr_refunded_cash)
+ ,avg(wr_fee)
+ from web_sales, web_returns, web_page, customer_demographics cd1,
+ customer_demographics cd2, customer_address, date_dim, reason
+ where ws_web_page_sk = wp_web_page_sk
+ and ws_item_sk = wr_item_sk
+ and ws_order_number = wr_order_number
+ and ws_sold_date_sk = d_date_sk and d_year = 2000
+ and cd1.cd_demo_sk = wr_refunded_cdemo_sk
+ and cd2.cd_demo_sk = wr_returning_cdemo_sk
+ and ca_address_sk = wr_refunded_addr_sk
+ and r_reason_sk = wr_reason_sk
+ and
+ (
+ (
+ cd1.cd_marital_status = 'D'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = 'Primary'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 100.00 and 150.00
+ )
+ or
+ (
+ cd1.cd_marital_status = 'M'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = '4 yr Degree'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 50.00 and 100.00
+ )
+ or
+ (
+ cd1.cd_marital_status = 'W'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = 'Secondary'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 150.00 and 200.00
+ )
+ )
+ and
+ (
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('VT', 'NJ', 'FL')
+ and ws_net_profit between 100 and 200
+ )
+ or
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('MA', 'IA', 'MS')
+ and ws_net_profit between 150 and 300
+ )
+ or
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('WI', 'TX', 'VA')
+ and ws_net_profit between 50 and 250
+ )
+ )
+group by r_reason_desc
+order by substr(r_reason_desc,1,20)
+ ,avg(ws_quantity)
+ ,avg(wr_refunded_cash)
+ ,avg(wr_fee)
+limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q86.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q86.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q86.test
new file mode 100644
index 0000000..ddb187c
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q86.test
@@ -0,0 +1,41 @@
+====
+---- QUERY: TPCDS-Q86
+
+with results as
+( select sum(ws_net_paid) as total_sum, i_category, i_class, 0 as g_category, 0 as g_class
+ from
+ web_sales
+ ,date_dim d1
+ ,item
+ where
+ d1.d_month_seq between 1176 and 1176+11
+ and d1.d_date_sk = ws_sold_date_sk
+ and i_item_sk = ws_item_sk
+ group by i_category,i_class
+ ) ,
+
+ results_rollup as
+( select total_sum ,i_category ,i_class, g_category, g_class, 0 as lochierarchy from results
+ union
+ select sum(total_sum) as total_sum, i_category, NULL as i_class, 0 as g_category, 1 as g_class, 1 as lochierarchy from results group by i_category
+ union
+ select sum(total_sum) as total_sum, NULL as i_category, NULL as i_class, 1 as g_category, 1 as g_class, 2 as lochierarchy from results)
+ select
+ total_sum ,i_category ,i_class, lochierarchy
+ ,rank() over (
+ partition by lochierarchy,
+ case when g_class = 0 then i_category end
+ order by total_sum desc) 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-q87.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q87.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q87.test
new file mode 100644
index 0000000..4ed00d1
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q87.test
@@ -0,0 +1,41 @@
+====
+---- QUERY: TPCDS-Q87
+select count(*)
+from (select distinct c_last_name as l1, c_first_name as f1, d_date as d1
+ from store_sales
+ JOIN date_dim ON store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ JOIN customer ON store_sales.ss_customer_sk = customer.c_customer_sk
+ where
+ d_month_seq between 1193 and 1193+11
+ ) t1
+ LEFT OUTER JOIN
+ ( select distinct c_last_name as l2, c_first_name as f2, d_date as d2
+ from catalog_sales
+ JOIN date_dim ON catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
+ JOIN customer ON catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
+ where
+ d_month_seq between 1193 and 1193+11
+ ) t2
+ ON t1.l1 = t2.l2 and
+ t1.f1 = t2.f2 and
+ t1.d1 = t2.d2
+ LEFT OUTER JOIN
+ (select distinct c_last_name as l3, c_first_name as f3, d_date as d3
+ from web_sales
+ JOIN date_dim ON web_sales.ws_sold_date_sk = date_dim.d_date_sk
+ JOIN customer ON web_sales.ws_bill_customer_sk = customer.c_customer_sk
+ where
+ d_month_seq between 1193 and 1193+11
+ ) t3
+ ON t1.l1 = t3.l3 and
+ t1.f1 = t3.f3 and
+ t1.d1 = t3.d3
+WHERE
+ l2 is null and
+ l3 is null ;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q88.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q88.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q88.test
new file mode 100644
index 0000000..03e91f1
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q88.test
@@ -0,0 +1,100 @@
+====
+---- QUERY: TPCDS-Q88
+
+select *
+from
+ (select count(*) h8_30_to_9
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 8
+ and time_dim.t_minute >= 30
+ and ((household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
+ (household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s1,
+ (select count(*) h9_to_9_30
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 9
+ and time_dim.t_minute < 30
+ and ((household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
+ (household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s2,
+ (select count(*) h9_30_to_10
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 9
+ and time_dim.t_minute >= 30
+ and ((household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
+ (household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s3,
+ (select count(*) h10_to_10_30
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 10
+ and time_dim.t_minute < 30
+ and ((household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
+ (household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s4,
+ (select count(*) h10_30_to_11
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 10
+ and time_dim.t_minute >= 30
+ and ((household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
+ (household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s5,
+ (select count(*) h11_to_11_30
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 11
+ and time_dim.t_minute < 30
+ and ((household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
+ (household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s6,
+ (select count(*) h11_30_to_12
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 11
+ and time_dim.t_minute >= 30
+ and ((household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
+ (household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s7,
+ (select count(*) h12_to_12_30
+ from store_sales, household_demographics , time_dim, store
+ where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 12
+ and time_dim.t_minute < 30
+ and ((household_demographics.hd_dep_count = 0 and household_demographics.hd_vehicle_count<=0+2) or
+ (household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
+ (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+ and store.s_store_name = 'ese') s8
+;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test
new file mode 100644
index 0000000..e665976
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test
@@ -0,0 +1,34 @@
+====
+---- QUERY: TPCDS-Q89
+
+select *
+from(
+select i_category, i_class, i_brand,
+ s_store_name, s_company_name,
+ d_moy,
+ sum(ss_sales_price) sum_sales,
+ avg(sum(ss_sales_price)) over
+ (partition by i_category, i_brand, s_store_name, s_company_name)
+ avg_monthly_sales
+from item, store_sales, date_dim, store
+where ss_item_sk = i_item_sk and
+ ss_sold_date_sk = d_date_sk and
+ ss_store_sk = s_store_sk and
+ d_year in (2000) and
+ ((i_category in ('Jewelry','Women','Home') and
+ i_class in ('mens watch','swimwear','kids')
+ )
+ or (i_category in ('Men','Sports','Music') and
+ i_class in ('accessories','football','rock')
+ ))
+group by i_category, i_class, i_brand,
+ s_store_name, s_company_name, d_moy) tmp1
+where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1
+order by sum_sales - avg_monthly_sales, s_store_name
+limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q9.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q9.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q9.test
new file mode 100644
index 0000000..eafd4cd
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q9.test
@@ -0,0 +1,49 @@
+====
+---- QUERY: TPCDS-Q9
+
+select bucket1, bucket2, bucket3, bucket4, bucket5
+from
+(select case when count1 > 62316685 then then1 else else1 end bucket1
+from (
+select count(*) count1, avg(ss_ext_sales_price) then1, avg(ss_net_paid_inc_tax) else1
+from store_sales
+where ss_quantity between 1 and 20
+) A1) B1
+CROSS JOIN
+(select case when count2 > 19045798 then then2 else else2 end bucket2
+from (
+select count(*) count2, avg(ss_ext_sales_price) then2, avg(ss_net_paid_inc_tax) else2
+from store_sales
+where ss_quantity between 21 and 40
+) A2) B2
+CROSS JOIN
+(select case when count3 > 365541424 then then3 else else3 end bucket3
+from (
+select count(*) count3, avg(ss_ext_sales_price) then3, avg(ss_net_paid_inc_tax) else3
+from store_sales
+where ss_quantity between 41 and 60
+) A3) B3
+CROSS JOIN
+(select case when count4 > 216357808 then then4 else else4 end bucket4
+from (
+select count(*) count4, avg(ss_ext_sales_price) then4, avg(ss_net_paid_inc_tax) else4
+from store_sales
+where ss_quantity between 61 and 80
+) A4) B4
+CROSS JOIN
+(select case when count5 > 184483884 then then5 else else5 end bucket5
+from (
+select count(*) count5, avg(ss_ext_sales_price) then5, avg(ss_net_paid_inc_tax) else5
+from store_sales
+where ss_quantity between 81 and 100
+) A5) B5
+CROSS JOIN
+reason
+where r_reason_sk = 1
+;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q90.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q90.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q90.test
new file mode 100644
index 0000000..edceef2
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q90.test
@@ -0,0 +1,28 @@
+====
+---- QUERY: TPCDS-Q90
+
+select cast(amc as decimal(15,4))/cast(pmc as decimal(15,4)) am_pm_ratio
+ from ( select count(*) amc
+ from web_sales, household_demographics , time_dim, web_page
+ where ws_sold_time_sk = time_dim.t_time_sk
+ and ws_ship_hdemo_sk = household_demographics.hd_demo_sk
+ and ws_web_page_sk = web_page.wp_web_page_sk
+ and time_dim.t_hour between 6 and 6+1
+ and household_demographics.hd_dep_count = 3
+ and web_page.wp_char_count between 5000 and 5200) at1,
+ ( select count(*) pmc
+ from web_sales, household_demographics , time_dim, web_page
+ where ws_sold_time_sk = time_dim.t_time_sk
+ and ws_ship_hdemo_sk = household_demographics.hd_demo_sk
+ and ws_web_page_sk = web_page.wp_web_page_sk
+ and time_dim.t_hour between 17 and 17+1
+ and household_demographics.hd_dep_count = 3
+ and web_page.wp_char_count between 5000 and 5200) pt
+ order by am_pm_ratio
+ 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-q91.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q91.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q91.test
new file mode 100644
index 0000000..c69a4bf
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q91.test
@@ -0,0 +1,37 @@
+====
+---- QUERY: TPCDS-Q91
+
+select
+ cc_call_center_id Call_Center,
+ cc_name Call_Center_Name,
+ cc_manager Manager,
+ sum(cr_net_loss) Returns_Loss
+from
+ call_center,
+ catalog_returns,
+ date_dim,
+ customer,
+ customer_address,
+ customer_demographics,
+ household_demographics
+where
+ cr_call_center_sk = cc_call_center_sk
+and cr_returned_date_sk = d_date_sk
+and cr_returning_customer_sk= c_customer_sk
+and cd_demo_sk = c_current_cdemo_sk
+and hd_demo_sk = c_current_hdemo_sk
+and ca_address_sk = c_current_addr_sk
+and d_year = 2001
+and d_moy = 12
+and ( (cd_marital_status = 'M' and cd_education_status = 'Unknown')
+ or(cd_marital_status = 'W' and cd_education_status = 'Advanced Degree'))
+and hd_buy_potential like '>10000%'
+and ca_gmt_offset = -7
+group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status
+order by sum(cr_net_loss) desc;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q92.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q92.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q92.test
new file mode 100644
index 0000000..e7fc00a
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q92.test
@@ -0,0 +1,36 @@
+====
+---- QUERY: TPCDS-Q92
+
+select
+ sum(ws_ext_discount_amt) as "Excess Discount Amount"
+from
+ web_sales
+ ,item
+ ,date_dim
+where
+i_manufact_id = 74
+and i_item_sk = ws_item_sk
+and d_date between '2000-01-07' and
+ (cast('2000-01-07' as timestamp) + interval 90 days)
+and d_date_sk = ws_sold_date_sk
+and ws_ext_discount_amt
+ > (
+ SELECT
+ 1.3 * avg(ws_ext_discount_amt)
+ FROM
+ web_sales
+ ,date_dim
+ WHERE
+ ws_item_sk = i_item_sk
+ and d_date between '2000-01-07' and
+ (cast('2000-01-07' as timestamp) + interval 90 days)
+ and d_date_sk = ws_sold_date_sk
+ )
+order by sum(ws_ext_discount_amt)
+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-q93.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q93.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q93.test
new file mode 100644
index 0000000..d02962f
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q93.test
@@ -0,0 +1,24 @@
+====
+---- QUERY: TPCDS-Q93
+
+select ss_customer_sk
+ ,sum(act_sales) sumsales
+ from (select ss_item_sk
+ ,ss_ticket_number
+ ,ss_customer_sk
+ ,case when sr_return_quantity is not null then (ss_quantity-sr_return_quantity)*ss_sales_price
+ else (ss_quantity*ss_sales_price) end act_sales
+ from store_sales left outer join store_returns on (sr_item_sk = ss_item_sk
+ and sr_ticket_number = ss_ticket_number)
+ ,reason
+ where sr_reason_sk = r_reason_sk
+ and r_reason_desc = 'Did not like the color') t
+ group by ss_customer_sk
+ order by sumsales, ss_customer_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-q94.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q94.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q94.test
new file mode 100644
index 0000000..68d4b13
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q94.test
@@ -0,0 +1,35 @@
+====
+---- QUERY: TPCDS-Q94
+
+select
+ count(distinct ws_order_number) as "order count"
+ ,sum(ws_ext_ship_cost) as "total shipping cost"
+ ,sum(ws_net_profit) as "total net profit"
+from
+ web_sales ws1
+ ,date_dim
+ ,customer_address
+ ,web_site
+where
+ d_date between '2000-3-01' and
+ (cast('2000-3-01' as timestamp) + interval 60 days)
+and ws1.ws_ship_date_sk = d_date_sk
+and ws1.ws_ship_addr_sk = ca_address_sk
+and ca_state = 'GA'
+and ws1.ws_web_site_sk = web_site_sk
+and web_company_name = 'pri'
+and exists (select *
+ from web_sales ws2
+ where ws1.ws_order_number = ws2.ws_order_number
+ and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
+and not exists(select *
+ from web_returns wr1
+ where ws1.ws_order_number = wr1.wr_order_number)
+order by count(distinct ws_order_number)
+limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q95.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q95.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q95.test
new file mode 100644
index 0000000..5d177e5
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q95.test
@@ -0,0 +1,38 @@
+====
+---- QUERY: TPCDS-Q95
+
+with ws_wh as
+(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2
+ from web_sales ws1,web_sales ws2
+ where ws1.ws_order_number = ws2.ws_order_number
+ and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
+ select
+ count(distinct ws_order_number) as "order count"
+ ,sum(ws_ext_ship_cost) as "total shipping cost"
+ ,sum(ws_net_profit) as "total net profit"
+from
+ web_sales ws1
+ ,date_dim
+ ,customer_address
+ ,web_site
+where
+ d_date between '2000-3-01' and
+ (cast('2000-3-01' as timestamp) + interval 60 days)
+and ws1.ws_ship_date_sk = d_date_sk
+and ws1.ws_ship_addr_sk = ca_address_sk
+and ca_state = 'GA'
+and ws1.ws_web_site_sk = web_site_sk
+and web_company_name = 'pri'
+and ws1.ws_order_number in (select ws_order_number
+ from ws_wh)
+and ws1.ws_order_number in (select wr_order_number
+ from web_returns,ws_wh
+ where wr_order_number = ws_wh.ws_order_number)
+order by count(distinct ws_order_number)
+limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q96.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q96.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q96.test
new file mode 100644
index 0000000..96c40fe
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q96.test
@@ -0,0 +1,22 @@
+====
+---- QUERY: TPCDS-Q96
+
+select count(*)
+from store_sales
+ ,household_demographics
+ ,time_dim, store
+where ss_sold_time_sk = time_dim.t_time_sk
+ and ss_hdemo_sk = household_demographics.hd_demo_sk
+ and ss_store_sk = s_store_sk
+ and time_dim.t_hour = 15
+ and time_dim.t_minute >= 30
+ and household_demographics.hd_dep_count = 7
+ and store.s_store_name = 'ese'
+order by 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-q97.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q97.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q97.test
new file mode 100644
index 0000000..2660f4c
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q97.test
@@ -0,0 +1,31 @@
+====
+---- QUERY: TPCDS-Q97
+
+with ssci as (
+select ss_customer_sk customer_sk
+ ,ss_item_sk item_sk
+from store_sales,date_dim
+where ss_sold_date_sk = d_date_sk
+ and d_month_seq between 1176 and 1176 + 11
+group by ss_customer_sk
+ ,ss_item_sk),
+csci as(
+ select cs_bill_customer_sk customer_sk
+ ,cs_item_sk item_sk
+from catalog_sales,date_dim
+where cs_sold_date_sk = d_date_sk
+ and d_month_seq between 1176 and 1176 + 11
+group by cs_bill_customer_sk
+ ,cs_item_sk)
+ select sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) store_only
+ ,sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) catalog_only
+ ,sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) store_and_catalog
+from ssci full outer join csci on (ssci.customer_sk=csci.customer_sk
+ and ssci.item_sk = csci.item_sk)
+limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q98.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q98.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q98.test
new file mode 100644
index 0000000..5085c35
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q98.test
@@ -0,0 +1,39 @@
+====
+---- QUERY: TPCDS-Q98
+
+select i_item_id
+ ,i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,sum(ss_ext_sales_price) as itemrevenue
+ ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over
+ (partition by i_class) as revenueratio
+from
+ store_sales
+ ,item
+ ,date_dim
+where
+ ss_item_sk = i_item_sk
+ and i_category in ('Music', 'Electronics', 'Jewelry')
+ and ss_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;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/queries/tpcds-q99.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q99.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q99.test
new file mode 100644
index 0000000..2313aa7
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q99.test
@@ -0,0 +1,41 @@
+====
+---- QUERY: TPCDS-Q99
+
+select
+ substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,cc_name
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk <= 30 ) then 1 else 0 end) as "30 days"
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 30) and
+ (cs_ship_date_sk - cs_sold_date_sk <= 60) then 1 else 0 end ) as "31-60 days"
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 60) and
+ (cs_ship_date_sk - cs_sold_date_sk <= 90) then 1 else 0 end) as "61-90 days"
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 90) and
+ (cs_ship_date_sk - cs_sold_date_sk <= 120) then 1 else 0 end) as "91-120 days"
+ ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 120) then 1 else 0 end) as ">120 days"
+from
+ catalog_sales
+ ,warehouse
+ ,ship_mode
+ ,call_center
+ ,date_dim
+where
+ d_month_seq between 1176 and 1176 + 11
+and cs_ship_date_sk = d_date_sk
+and cs_warehouse_sk = w_warehouse_sk
+and cs_ship_mode_sk = sm_ship_mode_sk
+and cs_call_center_sk = cc_call_center_sk
+group by
+ substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,cc_name
+order by substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,cc_name
+limit 100;
+
+
+---- RESULTS
+---- TYPES
+INT, INT, STRING, DECIMAL
+====
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/tpcds-unmodified_core.csv
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/tpcds-unmodified_core.csv b/testdata/workloads/tpcds-unmodified/tpcds-unmodified_core.csv
new file mode 100644
index 0000000..94b4b22
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/tpcds-unmodified_core.csv
@@ -0,0 +1,4 @@
+# Generated File.
+file_format: text, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: seq, dataset: tpcds, compression_codec: snap, compression_type: block
+file_format: parquet, dataset: tpcds, compression_codec: none, compression_type: none
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/tpcds-unmodified_dimensions.csv
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/tpcds-unmodified_dimensions.csv b/testdata/workloads/tpcds-unmodified/tpcds-unmodified_dimensions.csv
new file mode 100644
index 0000000..8137b7a
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/tpcds-unmodified_dimensions.csv
@@ -0,0 +1,4 @@
+file_format: text,seq,rc,avro,parquet
+dataset: tpcds
+compression_codec: none,def,gzip,bzip,snap,lzo
+compression_type: none,block,record
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/tpcds-unmodified_exhaustive.csv
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/tpcds-unmodified_exhaustive.csv b/testdata/workloads/tpcds-unmodified/tpcds-unmodified_exhaustive.csv
new file mode 100644
index 0000000..c4b4f99
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/tpcds-unmodified_exhaustive.csv
@@ -0,0 +1,23 @@
+# Generated File.
+file_format: text, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: text, dataset: tpcds, compression_codec: lzo, compression_type: block
+file_format: seq, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: seq, dataset: tpcds, compression_codec: def, compression_type: block
+file_format: seq, dataset: tpcds, compression_codec: def, compression_type: record
+file_format: seq, dataset: tpcds, compression_codec: gzip, compression_type: block
+file_format: seq, dataset: tpcds, compression_codec: gzip, compression_type: record
+file_format: seq, dataset: tpcds, compression_codec: bzip, compression_type: block
+file_format: seq, dataset: tpcds, compression_codec: bzip, compression_type: record
+file_format: seq, dataset: tpcds, compression_codec: snap, compression_type: block
+file_format: seq, dataset: tpcds, compression_codec: snap, compression_type: record
+file_format: rc, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: rc, dataset: tpcds, compression_codec: def, compression_type: block
+file_format: rc, dataset: tpcds, compression_codec: gzip, compression_type: block
+file_format: rc, dataset: tpcds, compression_codec: bzip, compression_type: block
+file_format: rc, dataset: tpcds, compression_codec: snap, compression_type: block
+file_format: avro, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: avro, dataset: tpcds, compression_codec: def, compression_type: block
+file_format: avro, dataset: tpcds, compression_codec: snap, compression_type: block
+file_format: parquet, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: parquet, dataset: tpcds, compression_codec: def, compression_type: block
+file_format: parquet, dataset: tpcds, compression_codec: snap, compression_type: block
http://git-wip-us.apache.org/repos/asf/impala/blob/bb9237d9/testdata/workloads/tpcds-unmodified/tpcds-unmodified_pairwise.csv
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds-unmodified/tpcds-unmodified_pairwise.csv b/testdata/workloads/tpcds-unmodified/tpcds-unmodified_pairwise.csv
new file mode 100644
index 0000000..e643495
--- /dev/null
+++ b/testdata/workloads/tpcds-unmodified/tpcds-unmodified_pairwise.csv
@@ -0,0 +1,15 @@
+# Generated File.
+file_format: text, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: seq, dataset: tpcds, compression_codec: def, compression_type: block
+file_format: rc, dataset: tpcds, compression_codec: gzip, compression_type: block
+file_format: avro, dataset: tpcds, compression_codec: snap, compression_type: block
+file_format: parquet, dataset: tpcds, compression_codec: snap, compression_type: block
+file_format: parquet, dataset: tpcds, compression_codec: def, compression_type: block
+file_format: avro, dataset: tpcds, compression_codec: def, compression_type: block
+file_format: rc, dataset: tpcds, compression_codec: bzip, compression_type: block
+file_format: seq, dataset: tpcds, compression_codec: snap, compression_type: record
+file_format: text, dataset: tpcds, compression_codec: lzo, compression_type: block
+file_format: rc, dataset: tpcds, compression_codec: def, compression_type: block
+file_format: avro, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: parquet, dataset: tpcds, compression_codec: none, compression_type: none
+file_format: rc, dataset: tpcds, compression_codec: none, compression_type: none