impala-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject [3/9] incubator-impala git commit: IMPALA-5376: Implement all TPCDS test cases or alternates for Impala.
Date Tue, 24 Oct 2017 18:51:38 GMT
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q78.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q78.test b/testdata/workloads/tpcds/queries/tpcds-q78.test
new file mode 100644
index 0000000..2610218
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q78.test
@@ -0,0 +1,163 @@
+====
+---- QUERY: TPCDS-Q78
+-- RESULT MISMATCH FROM ORIGINAL
+-- FIXED. TAKE ACTUAL RESULT AS EXPECTED
+with ws as
+  (select d_year AS ws_sold_year, ws_item_sk,
+    ws_bill_customer_sk ws_customer_sk,
+    sum(ws_quantity) ws_qty,
+    sum(ws_wholesale_cost) ws_wc,
+    sum(ws_sales_price) ws_sp
+   from web_sales
+   left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
+   join date_dim on ws_sold_date_sk = d_date_sk
+   where wr_order_number is null
+   group by d_year, ws_item_sk, ws_bill_customer_sk
+   ),
+cs as
+  (select d_year AS cs_sold_year, cs_item_sk,
+    cs_bill_customer_sk cs_customer_sk,
+    sum(cs_quantity) cs_qty,
+    sum(cs_wholesale_cost) cs_wc,
+    sum(cs_sales_price) cs_sp
+   from catalog_sales
+   left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
+   join date_dim on cs_sold_date_sk = d_date_sk
+   where cr_order_number is null
+   group by d_year, cs_item_sk, cs_bill_customer_sk
+   ),
+ss as
+  (select d_year AS ss_sold_year, ss_item_sk,
+    ss_customer_sk,
+    sum(ss_quantity) ss_qty,
+    sum(ss_wholesale_cost) ss_wc,
+    sum(ss_sales_price) ss_sp
+   from store_sales
+   left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
+   join date_dim on ss_sold_date_sk = d_date_sk
+   where sr_ticket_number is null
+   group by d_year, ss_item_sk, ss_customer_sk
+   )
+ select
+ss_sold_year, ss_item_sk, ss_customer_sk,
+round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from ss
+left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
+left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)
+where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2002
+order by
+  ss_sold_year, ss_item_sk, ss_customer_sk,
+  ss_qty desc, ss_wc desc, ss_sp desc,
+  other_chan_qty,
+  other_chan_wholesale_cost,
+  other_chan_sales_price,
+  round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)
+limit 100;
+---- RESULTS
+2002,9,5646,0.43,20,7.84,11.54,47,30.81,85.03
+2002,57,97133,6.36,70,40.21,43.02,11,5.16,11.10
+2002,129,108,26.00,26,10.48,2.37,1,87.68,47.14
+2002,321,41960,0.17,7,90.35,105.67,41,39.05,24.69
+2002,511,14899,0.25,21,72.09,108.17,84,89.30,103.17
+2002,690,2510,0.54,37,79.26,68.23,69,56.38,22.82
+2002,721,403,1.67,95,61.68,65.23,57,52.67,50.53
+2002,729,46247,2.09,73,91.24,89.89,35,80.58,35.77
+2002,765,37957,0.06,5,84.16,14.01,90,12.45,5.09
+2002,1059,25758,1.38,47,86.96,91.23,34,32.27,5.76
+2002,1236,41346,1.59,43,28.56,40.90,27,7.18,2.01
+2002,1260,46874,1.11,98,5.18,3.01,88,50.18,1.29
+2002,1335,76341,2.94,97,86.95,3.30,33,68.26,73.11
+2002,1347,76414,0.01,1,5.00,NULL,79,45.08,13.14
+2002,1464,89045,1.03,100,95.99,NULL,97,38.98,27.96
+2002,1477,17492,2.12,89,38.91,37.74,42,38.00,39.46
+2002,1767,16602,0.76,16,94.71,47.81,21,12.73,15.51
+2002,1983,47178,2.53,76,63.03,75.63,30,90.09,116.43
+2002,1986,6198,0.72,31,71.27,97.49,43,44.13,79.14
+2002,2071,24159,0.84,63,84.99,55.68,75,89.63,120.08
+2002,2245,73119,0.09,6,10.04,2.59,65,61.47,44.42
+2002,2293,27432,10.57,74,47.06,59.52,7,5.27,7.03
+2002,2353,92698,1.41,58,85.54,11.20,41,41.20,86.32
+2002,2761,38357,0.34,32,49.23,9.98,94,37.74,37.64
+2002,2845,7790,1.73,76,85.75,30.56,44,93.91,0.00
+2002,2959,47472,1.97,75,77.31,92.67,38,82.28,83.75
+2002,3045,20740,0.51,25,97.93,88.86,49,70.74,157.56
+2002,3129,26451,0.09,6,31.93,10.29,65,62.81,90.30
+2002,3204,35986,1.88,32,12.65,3.84,17,64.95,44.36
+2002,3219,29526,1.46,82,42.31,65.89,56,9.90,15.19
+2002,3357,71540,0.11,9,50.44,22.46,81,53.38,74.94
+2002,3399,72,2.61,81,4.58,3.13,31,63.52,40.03
+2002,3462,88673,0.17,11,40.93,43.54,66,39.83,35.68
+2002,3627,71405,0.24,21,61.60,20.18,86,30.17,2.98
+2002,3732,41263,0.35,33,63.24,71.03,93,64.70,123.62
+2002,3942,31747,0.28,8,14.21,22.64,29,55.38,6.42
+2002,4117,54024,0.57,41,66.75,72.00,72,71.80,96.35
+2002,4242,37012,0.78,47,15.86,6.80,60,97.79,170.03
+2002,4302,64566,1.55,82,6.26,2.15,53,50.12,55.86
+2002,4447,74056,0.48,27,16.86,11.21,56,89.56,144.94
+2002,4686,76933,1.00,15,75.29,54.19,15,7.49,6.75
+2002,4717,79946,0.89,57,39.12,67.27,64,7.08,1.71
+2002,4723,36380,3.62,47,65.36,15.68,13,48.79,8.87
+2002,4819,62327,0.77,63,44.48,52.39,82,68.44,88.28
+2002,4957,36983,2.33,70,38.96,14.99,30,34.89,22.13
+2002,4992,62746,0.48,15,17.23,11.02,31,60.22,97.22
+2002,5004,31716,3.00,78,30.61,21.16,26,73.11,50.70
+2002,5133,97233,0.58,49,59.82,43.19,84,85.44,4.07
+2002,5245,45142,0.49,30,1.10,0.37,61,36.53,44.48
+2002,5251,73991,3.20,96,51.64,93.16,30,27.34,8.36
+2002,5449,95871,0.19,16,18.71,8.24,85,12.35,5.29
+2002,5472,24589,0.24,8,34.74,26.11,33,42.48,35.38
+2002,5503,34938,0.69,9,98.38,93.49,13,49.87,54.85
+2002,5569,64962,0.50,50,NULL,5.68,100,93.35,78.15
+2002,5583,92620,0.72,56,97.86,50.80,78,93.99,1.40
+2002,5727,60671,1.14,49,43.70,1.73,43,86.92,100.16
+2002,5737,39389,2.71,46,59.71,2.72,17,95.38,25.03
+2002,5749,62425,1.10,100,39.91,16.04,91,88.00,54.33
+2002,5841,67686,0.33,22,54.47,86.52,67,62.11,7.52
+2002,5937,84826,1.59,86,97.93,75.53,54,75.03,58.06
+2002,6015,91432,2.81,73,95.72,98.89,26,14.50,16.96
+2002,6060,4560,0.20,15,8.54,13.22,75,62.88,117.71
+2002,6139,45325,1.08,92,55.42,6.16,85,58.04,52.46
+2002,6195,65834,1.33,61,88.07,88.10,46,95.23,15.42
+2002,6471,70702,0.26,12,59.51,50.98,46,61.85,26.39
+2002,6576,13149,1.08,13,11.27,2.64,12,63.38,111.35
+2002,6609,27877,9.00,90,66.94,22.75,10,82.77,144.84
+2002,6633,42874,0.93,82,31.20,35.97,88,44.86,6.40
+2002,6855,15315,0.31,25,58.32,89.27,80,78.76,14.83
+2002,6912,52490,1.13,85,56.88,35.84,75,40.18,0.58
+2002,6931,79419,5.00,10,27.63,18.85,2,65.44,132.47
+2002,7033,66414,0.26,13,94.05,1.16,50,67.17,107.46
+2002,7063,21112,0.77,27,49.27,79.81,35,77.32,93.24
+2002,7215,31653,0.42,32,57.07,1.72,77,92.67,171.36
+2002,7561,51218,1.00,68,35.23,21.28,68,59.73,129.01
+2002,7782,49798,1.04,53,83.78,112.52,51,76.63,63.44
+2002,7801,88304,0.48,12,96.02,41.33,25,95.64,20.64
+2002,7815,66823,0.94,72,25.50,25.58,77,53.59,76.78
+2002,7957,96021,0.44,43,79.68,30.03,98,73.48,90.26
+2002,8049,60811,0.39,12,81.72,20.26,31,37.82,50.91
+2002,8227,91553,0.87,60,87.92,111.61,69,31.14,25.90
+2002,8337,8072,0.20,12,63.82,50.02,61,98.28,5.38
+2002,8349,57239,0.25,22,3.17,0.78,89,20.00,18.55
+2002,8451,86351,0.42,40,83.30,109.88,96,77.69,52.56
+2002,8505,27133,0.19,5,30.40,21.53,27,88.89,7.57
+2002,8646,73272,0.72,23,13.59,6.28,32,17.75,13.76
+2002,8761,53153,0.88,37,2.97,4.64,42,58.26,55.88
+2002,8898,57830,0.57,47,30.69,17.17,83,16.40,36.64
+2002,8979,50230,0.50,15,12.02,1.03,30,36.85,34.45
+2002,8983,80488,0.18,7,46.94,49.14,39,51.17,61.25
+2002,8989,69606,0.45,22,99.16,93.14,49,73.63,37.31
+2002,9165,45023,3.67,22,32.15,14.95,6,28.47,38.94
+2002,9193,88085,0.84,61,34.73,34.78,73,30.59,9.83
+2002,9307,30830,0.01,1,53.62,23.51,72,74.60,55.05
+2002,9327,65904,0.97,38,79.26,122.00,39,10.33,14.31
+2002,9385,42467,0.52,27,64.95,23.69,52,86.42,2.37
+2002,9387,87700,0.88,14,85.04,132.39,16,99.04,64.37
+2002,9519,47703,1.49,85,7.64,11.98,57,83.37,144.89
+2002,9541,38364,0.36,27,14.91,11.36,76,2.16,1.32
+2002,9918,45262,1.09,98,3.02,1.91,90,90.79,154.11
+---- TYPES
+INT, BIGINT, INT, DOUBLE, BIGINT, DECIMAL, DECIMAL, BIGINT, DECIMAL, DECIMAL
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q8.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q8.test b/testdata/workloads/tpcds/queries/tpcds-q8.test
index 00ac280..91e41d2 100644
--- a/testdata/workloads/tpcds/queries/tpcds-q8.test
+++ b/testdata/workloads/tpcds/queries/tpcds-q8.test
@@ -6,7 +6,7 @@ select
 from
   store_sales
   join store on (store_sales.ss_store_sk = store.s_store_sk)
-  join 
+  join
   (select
     a.ca_zip
   from
@@ -22,8 +22,8 @@ from
       ca_zip
     having
       count(*) > 10
-    ) a 
-    left semi join 
+    ) a
+    left semi join
     (select
       substr(ca_zip, 1, 5) ca_zip
     from
@@ -66,7 +66,7 @@ where
 group by
   s_store_name
 order by
-  s_store_name 
+  s_store_name
 limit 100
 ---- RESULTS
 ---- TYPES

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q80a.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q80a.test b/testdata/workloads/tpcds/queries/tpcds-q80a.test
new file mode 100644
index 0000000..1d15914
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q80a.test
@@ -0,0 +1,213 @@
+====
+---- QUERY: TPCDS-Q80A
+with ssr as
+ (select  s_store_id as store_id,
+          sum(ss_ext_sales_price) as sales,
+          sum(coalesce(sr_return_amt, 0)) as return_amt,
+          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-23' as timestamp)
+                  and (cast('2000-08-23' 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 return_amt,
+          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-23' as timestamp)
+                  and (cast('2000-08-23' 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 return_amt,
+          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-23' as timestamp)
+                  and (cast('2000-08-23' 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(return_amt) as return_amt
+        , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+        , concat('store', store_id) as id
+        , sales
+        , return_amt
+        , profit
+ from   ssr
+ union all
+ select 'catalog channel' as channel
+        , concat('catalog_page', catalog_page_id) as id
+        , sales
+        , return_amt
+        , profit
+ from  csr
+ union all
+ select 'web channel' as channel
+        , concat('web_site', web_site_id) as id
+        , sales
+        , return_amt
+        , profit
+ from   wsr
+ ) x
+ group by channel, id)
+
+  select  channel
+        , id
+        , sales
+        , return_amt
+        , profit
+ from (
+   select channel, id, sales, return_amt, profit from  results
+   union
+   select channel, NULL AS id, sum(sales) as sales, sum(return_amt) as return_amt, sum(profit) as profit from  results group by channel
+   union
+   select NULL AS channel, NULL AS id, sum(sales) as sales, sum(return_amt) as return_amt, sum(profit) as profit from  results
+ ) foo
+ order by channel, id
+ limit 100;
+---- RESULTS
+'catalog channel','catalog_pageAAAAAAAAAAABAAAA',19965.69,3132.48,-16011.54
+'catalog channel','catalog_pageAAAAAAAAABABAAAA',14376.88,380.90,-3715.45
+'catalog channel','catalog_pageAAAAAAAAACABAAAA',4427.58,0.00,-6520.01
+'catalog channel','catalog_pageAAAAAAAAADABAAAA',8488.29,0.00,-3247.97
+'catalog channel','catalog_pageAAAAAAAAADCBAAAA',16052.65,0.00,-5900.88
+'catalog channel','catalog_pageAAAAAAAAAEABAAAA',10314.44,0.00,-7096.25
+'catalog channel','catalog_pageAAAAAAAAAECBAAAA',2857.44,2548.08,-136.42
+'catalog channel','catalog_pageAAAAAAAAAFABAAAA',5184.92,0.00,-507.04
+'catalog channel','catalog_pageAAAAAAAAAFCBAAAA',594.44,0.00,-1518.44
+'catalog channel','catalog_pageAAAAAAAAAGABAAAA',1107.03,190.82,-897.30
+'catalog channel','catalog_pageAAAAAAAAAGCBAAAA',3059.65,0.00,-5088.38
+'catalog channel','catalog_pageAAAAAAAAAHABAAAA',13134.30,0.00,2118.90
+'catalog channel','catalog_pageAAAAAAAAAHCBAAAA',16181.17,0.00,4761.95
+'catalog channel','catalog_pageAAAAAAAAAJCBAAAA',2448.69,0.00,557.49
+'catalog channel','catalog_pageAAAAAAAAAKCBAAAA',1005.27,0.00,154.35
+'catalog channel','catalog_pageAAAAAAAAAKPAAAAA',8445.19,0.00,-4147.63
+'catalog channel','catalog_pageAAAAAAAAALCBAAAA',11511.46,0.00,1173.29
+'catalog channel','catalog_pageAAAAAAAAALPAAAAA',22038.68,94.24,2354.44
+'catalog channel','catalog_pageAAAAAAAAAMPAAAAA',24071.71,5664.86,-6484.42
+'catalog channel','catalog_pageAAAAAAAAANCBAAAA',8352.06,0.00,4237.49
+'catalog channel','catalog_pageAAAAAAAAANPAAAAA',14597.62,134.64,-15964.47
+'catalog channel','catalog_pageAAAAAAAAAOCBAAAA',373.75,0.00,-1085.83
+'catalog channel','catalog_pageAAAAAAAAAOPAAAAA',23808.03,0.00,5548.05
+'catalog channel','catalog_pageAAAAAAAAAPCBAAAA',9531.26,0.00,581.49
+'catalog channel','catalog_pageAAAAAAAAAPPAAAAA',33119.11,55.44,7393.54
+'catalog channel','catalog_pageAAAAAAAABAABAAAA',4272.46,501.30,-9581.06
+'catalog channel','catalog_pageAAAAAAAABBABAAAA',1080.28,0.00,-4305.79
+'catalog channel','catalog_pageAAAAAAAABCABAAAA',33.90,0.00,-278.65
+'catalog channel','catalog_pageAAAAAAAABDABAAAA',5872.30,88.38,-480.99
+'catalog channel','catalog_pageAAAAAAAABDCBAAAA',5743.76,0.00,1462.64
+'catalog channel','catalog_pageAAAAAAAABEABAAAA',2049.28,0.00,-4059.03
+'catalog channel','catalog_pageAAAAAAAABECBAAAA',18997.60,0.00,-8490.71
+'catalog channel','catalog_pageAAAAAAAABFABAAAA',31503.86,0.00,7136.80
+'catalog channel','catalog_pageAAAAAAAABFCBAAAA',25421.67,8976.00,10129.52
+'catalog channel','catalog_pageAAAAAAAABGABAAAA',10594.99,367.65,-2926.53
+'catalog channel','catalog_pageAAAAAAAABHABAAAA',9000.33,1145.50,-12055.00
+'catalog channel','catalog_pageAAAAAAAABICBAAAA',1094.54,0.00,-202.67
+'catalog channel','catalog_pageAAAAAAAABJCBAAAA',2178.01,0.00,979.05
+'catalog channel','catalog_pageAAAAAAAABKCBAAAA',9189.93,0.00,4352.16
+'catalog channel','catalog_pageAAAAAAAABKPAAAAA',9637.79,1986.96,-19295.74
+'catalog channel','catalog_pageAAAAAAAABLCBAAAA',13969.57,0.00,5111.08
+'catalog channel','catalog_pageAAAAAAAABLPAAAAA',11655.71,3796.68,-15600.79
+'catalog channel','catalog_pageAAAAAAAABMCBAAAA',11936.10,14.70,1200.76
+'catalog channel','catalog_pageAAAAAAAABMPAAAAA',14866.48,0.00,-662.88
+'catalog channel','catalog_pageAAAAAAAABNCBAAAA',5440.20,0.00,-3049.20
+'catalog channel','catalog_pageAAAAAAAABNPAAAAA',4186.13,188.37,-16115.36
+'catalog channel','catalog_pageAAAAAAAABOCBAAAA',3112.32,0.00,-2559.36
+'catalog channel','catalog_pageAAAAAAAABOPAAAAA',2020.48,0.00,-192.04
+'catalog channel','catalog_pageAAAAAAAABPCBAAAA',7148.02,0.00,1781.91
+'catalog channel','catalog_pageAAAAAAAABPPAAAAA',17674.89,0.00,345.58
+'catalog channel','catalog_pageAAAAAAAACAABAAAA',17255.36,0.00,-3926.28
+'catalog channel','catalog_pageAAAAAAAACBABAAAA',2470.13,1545.21,-1544.35
+'catalog channel','catalog_pageAAAAAAAACCABAAAA',6361.92,0.00,3790.08
+'catalog channel','catalog_pageAAAAAAAACDCBAAAA',1526.49,0.00,-3403.89
+'catalog channel','catalog_pageAAAAAAAACEABAAAA',10732.94,0.00,-984.47
+'catalog channel','catalog_pageAAAAAAAACECBAAAA',648.84,0.00,170.04
+'catalog channel','catalog_pageAAAAAAAACFABAAAA',6745.24,0.00,-3013.83
+'catalog channel','catalog_pageAAAAAAAACGABAAAA',5143.41,0.00,1545.93
+'catalog channel','catalog_pageAAAAAAAACGCBAAAA',1284.52,0.00,-144.08
+'catalog channel','catalog_pageAAAAAAAACHABAAAA',10246.18,0.00,-6015.51
+'catalog channel','catalog_pageAAAAAAAACHCBAAAA',10416.70,0.00,19.49
+'catalog channel','catalog_pageAAAAAAAACICBAAAA',396.76,0.00,154.44
+'catalog channel','catalog_pageAAAAAAAACJCBAAAA',281.26,0.00,-5252.32
+'catalog channel','catalog_pageAAAAAAAACKCBAAAA',163.02,0.00,-46.83
+'catalog channel','catalog_pageAAAAAAAACKPAAAAA',12857.24,730.99,-2517.04
+'catalog channel','catalog_pageAAAAAAAACLCBAAAA',5470.63,0.00,1082.94
+'catalog channel','catalog_pageAAAAAAAACLPAAAAA',21549.31,0.00,6695.34
+'catalog channel','catalog_pageAAAAAAAACMCBAAAA',11329.50,0.00,5202.60
+'catalog channel','catalog_pageAAAAAAAACMPAAAAA',7546.59,294.84,-2582.73
+'catalog channel','catalog_pageAAAAAAAACNPAAAAA',23904.66,0.00,488.70
+'catalog channel','catalog_pageAAAAAAAACOPAAAAA',19014.47,648.01,-8700.92
+'catalog channel','catalog_pageAAAAAAAACPCBAAAA',1148.55,0.00,-932.52
+'catalog channel','catalog_pageAAAAAAAACPPAAAAA',25230.53,0.00,-741.80
+'catalog channel','catalog_pageAAAAAAAADAABAAAA',20305.27,0.00,972.58
+'catalog channel','catalog_pageAAAAAAAADBABAAAA',1390.65,0.00,-4835.48
+'catalog channel','catalog_pageAAAAAAAADCABAAAA',8296.22,4011.70,-5197.32
+'catalog channel','catalog_pageAAAAAAAADDABAAAA',6289.02,0.00,1457.49
+'catalog channel','catalog_pageAAAAAAAADDCBAAAA',14137.81,0.00,479.91
+'catalog channel','catalog_pageAAAAAAAADEABAAAA',7572.00,77.80,2937.68
+'catalog channel','catalog_pageAAAAAAAADECBAAAA',4207.12,0.00,-2457.02
+'catalog channel','catalog_pageAAAAAAAADFABAAAA',23991.62,3.15,1216.83
+'catalog channel','catalog_pageAAAAAAAADFCBAAAA',6537.84,0.00,-6173.37
+'catalog channel','catalog_pageAAAAAAAADGABAAAA',9050.23,3062.68,-2935.52
+'catalog channel','catalog_pageAAAAAAAADGCBAAAA',7789.42,0.00,-3868.94
+'catalog channel','catalog_pageAAAAAAAADHABAAAA',25558.98,0.00,-6817.76
+'catalog channel','catalog_pageAAAAAAAADHCBAAAA',5245.91,65.34,-1734.29
+'catalog channel','catalog_pageAAAAAAAADICBAAAA',0.00,0.00,-2246.10
+'catalog channel','catalog_pageAAAAAAAADJCBAAAA',7721.15,0.00,3313.85
+'catalog channel','catalog_pageAAAAAAAADKCBAAAA',5463.32,1677.60,874.45
+'catalog channel','catalog_pageAAAAAAAADKPAAAAA',30068.71,0.00,-8011.27
+'catalog channel','catalog_pageAAAAAAAADLCBAAAA',1146.19,0.00,-1287.95
+'catalog channel','catalog_pageAAAAAAAADLPAAAAA',35314.26,0.00,-5055.96
+'catalog channel','catalog_pageAAAAAAAADMCBAAAA',2420.23,0.00,-1610.01
+'catalog channel','catalog_pageAAAAAAAADMPAAAAA',32509.00,737.40,-14350.75
+'catalog channel','catalog_pageAAAAAAAADNCBAAAA',9986.56,5534.72,1208.35
+'catalog channel','catalog_pageAAAAAAAADNPAAAAA',1953.82,0.00,220.42
+'catalog channel','catalog_pageAAAAAAAADOCBAAAA',3356.59,62.99,-4084.30
+'catalog channel','catalog_pageAAAAAAAADOPAAAAA',5090.89,0.00,-2319.86
+'catalog channel','catalog_pageAAAAAAAADPCBAAAA',7004.85,78.75,-1243.37
+'catalog channel','catalog_pageAAAAAAAADPPAAAAA',22004.30,2309.27,3234.13
+---- TYPES
+STRING, STRING, DECIMAL, DECIMAL, DECIMAL
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q81.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q81.test b/testdata/workloads/tpcds/queries/tpcds-q81.test
new file mode 100644
index 0000000..fc72fb9
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q81.test
@@ -0,0 +1,134 @@
+====
+---- 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 = 'GA'
+       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
+'AAAAAAAAABBEAAAA','Dr.','Oscar','Fox','183','15th ','Ln','Suite P','Greenwood','Clay County','GA','38828','United States',-5.00,'condo',4607.92
+'AAAAAAAAADCEBAAA','Sir','Billy','Bell','690','4th Fifth','Ct.','Suite 390','Hillcrest','Spalding County','GA','33003','United States',-5.00,'condo',5567.44
+'AAAAAAAAAFBGAAAA','Mrs.','Erika','Buford','527','1st ','Ln','Suite 380','Lakewood','Jasper County','GA','38877','United States',-5.00,'single family',5412.16
+'AAAAAAAAAFLABAAA','Mr.','Richard','James','40','2nd Miller','Road','Suite 190','Arlington','Morgan County','GA','36557','United States',-5.00,'apartment',2249.10
+'AAAAAAAAAHOEAAAA','Mr.','Michael','Yates','349','Elm South','ST','Suite 30','Blanchard','Talbot County','GA','35985','United States',-5.00,'single family',1810.13
+'AAAAAAAAAIEHBAAA','Miss','Tamara','Chestnut','934','13th Cherry','Blvd','Suite O','Antioch','Haralson County','GA','38605','United States',-5.00,'condo',1639.26
+'AAAAAAAAAJLNAAAA','Dr.','Sheree','Bragg','664','Elm 4th','Ave','Suite 320','Glenwood','McDuffie County','GA','33511','United States',-5.00,'condo',3012.45
+'AAAAAAAAAJNEBAAA','Dr.','David','Hamilton','108','Spring Wilson','Ct.','Suite F','Glenwood','Macon County','GA','33511','United States',-5.00,'apartment',2567.79
+'AAAAAAAAAKJOAAAA','Dr.','Gus','Stewart','883','Valley ','Drive','Suite J','Macedonia','Lanier County','GA','31087','United States',-5.00,'single family',3530.28
+'AAAAAAAAAMFBBAAA','Mr.','Jackson','NULL','177','8th Walnut','Ct.','Suite 300','Riverview','Jenkins County','GA','39003','United States',-5.00,'single family',4875.86
+'AAAAAAAAAMPKAAAA','Ms.','Elizabeth','Booth','344','Jackson North','Drive','Suite 100','Woodville','Fayette County','GA','34289','United States',-5.00,'single family',2372.60
+'AAAAAAAAANCLAAAA','Mr.','Raymond','Dixon','189','Jackson 7th','Parkway','Suite 360','Lakewood','Dade County','GA','38877','United States',-5.00,'single family',4655.74
+'AAAAAAAAANJIAAAA','Dr.','Dennis','Roper','600','Walnut ','Pkwy','Suite 140','Plainview','Barrow County','GA','33683','United States',-5.00,'apartment',3500.40
+'AAAAAAAAANLABAAA','Sir','Thomas','Zimmerman','496','Forest ','ST','Suite L','Saratoga','Lumpkin County','GA','32123','United States',-5.00,'apartment',9193.95
+'AAAAAAAAAODFAAAA','Mrs.','Kathy','Villegas','156','4th Sunset','Cir.','Suite I','Red Hill','Appling County','GA','34338','United States',-5.00,'single family',2106.00
+'AAAAAAAAAOEBBAAA','Sir','Benjamin','Parker','698','6th ','Drive','Suite O','Plainview','Screven County','GA','33683','United States',-5.00,'apartment',3891.38
+'AAAAAAAAAOEBBAAA','Sir','Benjamin','Parker','698','6th ','Drive','Suite O','Plainview','Screven County','GA','33683','United States',-5.00,'apartment',9787.55
+'AAAAAAAAAPBBBAAA','Sir','Donald','Littlejohn','467','Lake ','Ln','Suite 20','Riverside','Turner County','GA','39231','United States',-5.00,'single family',4831.52
+'AAAAAAAABABBBAAA','Mr.','David','Roe','521','Jefferson ','Parkway','Suite 120','Enterprise','Wilcox County','GA','31757','United States',-5.00,'apartment',4079.80
+'AAAAAAAABAJHAAAA','Mrs.','Doris','Gillespie','179','Washington Chestnut','Dr.','Suite W','Jackson','Columbia County','GA','39583','United States',-5.00,'condo',4617.58
+'AAAAAAAABANGBAAA','Mr.','Ralph','Condon','755','Davis ','Blvd','Suite 280','Antioch','Pike County','GA','38605','United States',-5.00,'condo',14966.24
+'AAAAAAAABBHKAAAA','Mrs.','Kristin','Grant','870','Ash Park','Ave','Suite 300','Lakeview','Harris County','GA','38579','United States',-5.00,'single family',3580.44
+'AAAAAAAABBOBBAAA','Mr.','Herman','Rivera','396','Walnut ','Cir.','Suite P','Athens','Baldwin County','GA','36796','United States',-5.00,'single family',4608.96
+'AAAAAAAABCAOAAAA','Dr.','Brenda','Bell','562','7th Park','Road','Suite 320','Edgewood','Troup County','GA','30069','United States',-5.00,'apartment',3721.03
+'AAAAAAAABCIMAAAA','Dr.','Becky','Seals','174','Park Park','Ct.','Suite H','Green Acres','Schley County','GA','37683','United States',-5.00,'single family',2980.15
+'AAAAAAAABDHFAAAA','Mr.','Charles','Miller','531','Church 14th','Circle','Suite 60','Ashland','Franklin County','GA','34244','United States',-5.00,'apartment',3241.85
+'AAAAAAAABEHABAAA','Dr.','Gregory','NULL','391','Seventh ','Road','Suite Q','Oakwood','Liberty County','GA','30169','United States',-5.00,'single family',3520.33
+'AAAAAAAABGDGBAAA','Sir','Brian','Adams','915','Jackson ','RD','Suite O','Concord','Columbia County','GA','34107','United States',-5.00,'apartment',2912.27
+'AAAAAAAABGLCBAAA','Dr.','Joseph','Torres','103','Highland 7th','Court','Suite I','Wilson','Jenkins County','GA','36971','United States',-5.00,'single family',1842.89
+'AAAAAAAABHOFAAAA','Ms.','Anthony','NULL','518','Meadow Smith','Ct.','Suite C','Riverdale','Bleckley County','GA','39391','United States',-5.00,'single family',1878.44
+'AAAAAAAABIMMAAAA','Dr.','Shanna','Mcrae','764','Walnut ','Street','Suite B','Brownsville','Lumpkin County','GA','39310','United States',-5.00,'condo',7237.05
+'AAAAAAAABINIAAAA','Dr.','Jose','Reynolds','571','Main ','Ave','Suite B','Mount Zion','Troup County','GA','38054','United States',-5.00,'apartment',2463.65
+'AAAAAAAABJMEAAAA','Miss','Deborah','Sheehan','450','Willow ','Cir.','Suite G','Marion','Franklin County','GA','30399','United States',-5.00,'condo',6623.88
+'AAAAAAAABKAIAAAA','Miss','Ruth','Rodriguez','706','Williams ','Pkwy','Suite Y','Antioch','Lanier County','GA','38605','United States',-5.00,'single family',3025.05
+'AAAAAAAABMBLAAAA','Ms.','Ava','Jackson','601','Fourth Washington','Way','Suite 440','Glendale','Cherokee County','GA','33951','United States',-5.00,'apartment',1637.46
+'AAAAAAAABMBLAAAA','Ms.','Ava','Jackson','601','Fourth Washington','Way','Suite 440','Glendale','Cherokee County','GA','33951','United States',-5.00,'apartment',2373.14
+'AAAAAAAABMFDAAAA','Mrs.','Megan','Webster','124','11th ','Pkwy','Suite K','Shady Grove','Miller County','GA','32812','United States',-5.00,'single family',3059.27
+'AAAAAAAABNMGBAAA','Mrs.','Wanda','Shumate','422','Pine 3rd','Cir.','Suite 170','Crossroads','Berrien County','GA','30534','United States',-5.00,'single family',1699.41
+'AAAAAAAABNMIAAAA','Miss','Bobbi','Erickson','731','Jefferson ','Parkway','Suite H','Mountain View','Taylor County','GA','34466','United States',-5.00,'apartment',2925.34
+'AAAAAAAABOAPAAAA','Ms.','Karen','Burt','794','Sunset Sunset','Drive','Suite 470','Glendale','Calhoun County','GA','33951','United States',-5.00,'condo',8807.07
+'AAAAAAAABOCDBAAA','Ms.','Flora','Messer','608','10th ','Court','Suite A','Highland Park','Dooly County','GA','36534','United States',-5.00,'single family',3255.56
+'AAAAAAAABPIMAAAA','Dr.','Steve','Cano','711','Park ','Ln','Suite N','Smith','Gwinnett County','GA','37317','United States',-5.00,'single family',2086.39
+'AAAAAAAACABFAAAA','Sir','Steven','Lamb','726','Cherry Hill','ST','Suite E','Woodville','Worth County','GA','34289','United States',-5.00,'apartment',2361.71
+'AAAAAAAACBDDAAAA','Sir','Terry','Seiler','960','Eigth ','Way','Suite 240','Mountain View','Putnam County','GA','34466','United States',-5.00,'apartment',3222.09
+'AAAAAAAACCBOAAAA','Sir','Patrick','Miller','577','Church ','Ave','Suite 110','Bethel','White County','GA','35281','United States',-5.00,'single family',2927.35
+'AAAAAAAACDOPAAAA','Mrs.','Carolyn','Singer','399','Second ','Boulevard','Suite O','White Oak','Wayne County','GA','36668','United States',-5.00,'condo',4011.31
+'AAAAAAAACFADBAAA','NULL','NULL','NULL','502','Willow Park','Cir.','Suite E','Dewey','Colquitt County','GA','31160','United States',-5.00,'apartment',3732.03
+'AAAAAAAACIHABAAA','Ms.','Arlene','Baker','216','Maple Walnut','Ave','Suite 310','Greenfield','Glascock County','GA','35038','United States',-5.00,'single family',2857.28
+'AAAAAAAACJCHAAAA','Mr.','Wilbur','Mullins','119','Eigth ','Dr.','Suite W','Fairview','Wilkes County','GA','35709','United States',-5.00,'single family',4861.83
+'AAAAAAAACJJKAAAA','Mr.','Ramon','Zaragoza','934','Park ','Lane','Suite K','Lakewood','Muscogee County','GA','38877','United States',-5.00,'condo',9022.65
+'AAAAAAAACKHEAAAA','Mr.','Richard','Leblanc','18','Green View','Court','Suite J','Woodville','Butts County','GA','34289','United States',-5.00,'single family',3787.49
+'AAAAAAAACODJAAAA','Dr.','Neal','Nash','95','NULL','Street','Suite 110','Spring Hill','Murray County','GA','36787','NULL',-5.00,'NULL',2144.31
+'AAAAAAAACOFIBAAA','Mr.','Fred','Morales','687','5th Park','Cir.','Suite X','Fairview','Murray County','GA','35709','United States',-5.00,'condo',1936.70
+'AAAAAAAACOHIAAAA','Mr.','Roger','Grider','191','Williams Second','Avenue','Suite 420','Brownsville','Coweta County','GA','39310','United States',-5.00,'single family',5782.43
+'AAAAAAAACPBABAAA','Miss','Lucile','Anderson','149','View Williams','Ct.','Suite L','Mount Pleasant','Washington County','GA','31933','United States',-5.00,'single family',2600.59
+'AAAAAAAADANFBAAA','Mrs.','Ann','Nadeau','247','Maple ','Avenue','Suite 260','Hamilton','Elbert County','GA','32808','United States',-5.00,'single family',4103.32
+'AAAAAAAADBAHAAAA','Dr.','Lucille','Hill','668','West Laurel','Pkwy','Suite M','Mount Olive','Quitman County','GA','38059','United States',-5.00,'single family',2033.22
+'AAAAAAAADBEIBAAA','Miss','Erica','Hill','396','Maple Johnson','Avenue','Suite R','Crossroads','Hall County','GA','30534','United States',-5.00,'condo',4500.01
+'AAAAAAAADBEOAAAA','Miss','Loretta','Smith','149','Maple ','ST','Suite 350','Fairview','Cherokee County','GA','35709','United States',-5.00,'apartment',3810.28
+'AAAAAAAADBFHBAAA','Mr.','Charles','Robinson','731','Ridge ','Lane','Suite 160','New Hope','Bartow County','GA','39431','United States',-5.00,'apartment',4042.46
+'AAAAAAAADCAEAAAA','Dr.','Joanna','Walker','665','15th Maple','Avenue','Suite Y','Camden','McDuffie County','GA','37119','United States',-5.00,'single family',4067.57
+'AAAAAAAADCLBAAAA','Mrs.','Evelyn','Hammett','302','1st Adams','Cir.','Suite 370','Five Points','Pulaski County','GA','36098','United States',-5.00,'single family',2187.12
+'AAAAAAAADDENAAAA','Sir','Alejandro','Doyle','916','Cherry Elm','ST','Suite 110','Providence','Clay County','GA','36614','United States',-5.00,'single family',3357.92
+'AAAAAAAADEGFAAAA','Sir','Jesse','Johnson','725','Laurel First','Parkway','Suite O','Pleasant Valley','Candler County','GA','32477','United States',-5.00,'condo',4969.77
+'AAAAAAAADGCCAAAA','Miss','Loretta','Moss','274','3rd Walnut','Ave','Suite L','Arlington','Lee County','GA','36557','United States',-5.00,'condo',1891.74
+'AAAAAAAADHFLAAAA','Mr.','George','Schneider','579','Johnson Ash','Way','Suite 110','Concord','Seminole County','GA','34107','United States',-5.00,'condo',4011.59
+'AAAAAAAADHOIAAAA','Miss','Andrew','James','139','Smith Lincoln','Lane','Suite 360','Stringtown','Coweta County','GA','30162','United States',-5.00,'condo',2834.91
+'AAAAAAAADJABAAAA','Mrs.','Charity','Arroyo','158','Highland Elm','Ln','Suite K','Woodland','Pickens County','GA','34854','United States',-5.00,'apartment',2443.75
+'AAAAAAAADJJMAAAA','Dr.','Joseph','Burns','339','12th ','Blvd','Suite S','Hamilton','Banks County','GA','32808','United States',-5.00,'condo',2670.84
+'AAAAAAAADJODAAAA','Dr.','Larry','Phillips','612','Mill Church','Blvd','Suite 0','Sunnyside','Fannin County','GA','31952','United States',-5.00,'single family',7260.82
+'AAAAAAAADKKGAAAA','Sir','Robert','Patrick','638','Fourth ','Cir.','Suite 250','Lebanon','Clay County','GA','32898','United States',-5.00,'condo',9049.72
+'AAAAAAAADMDHAAAA','Mr.','Samuel','Wells','877','Highland East','Road','Suite I','Valley View','Bartow County','GA','35124','United States',-5.00,'single family',4490.95
+'AAAAAAAADNIBBAAA','Miss','Sharon','Prince','955','Walnut Ridge','Boulevard','Suite W','Cedar Grove','Troup County','GA','30411','United States',-5.00,'condo',2158.11
+'AAAAAAAADNOGBAAA','Dr.','David','Lofton','327','11th Central','Parkway','Suite D','White Oak','Glynn County','GA','36668','United States',-5.00,'single family',1971.52
+'AAAAAAAADOBCBAAA','Dr.','Benjamin','Pham','300','Willow Sycamore','Blvd','Suite 380','Liberty','Twiggs County','GA','33451','United States',-5.00,'apartment',1787.84
+'AAAAAAAADOFNAAAA','Mrs.','Sebrina','Jacobs','611','Center ','Avenue','Suite 210','Glenwood','Ware County','GA','33511','United States',-5.00,'condo',5738.75
+'AAAAAAAAEAFJAAAA','Mr.','Gregory','Quigley','932','Maple ','Avenue','Suite 300','Glendale','Butts County','GA','33951','United States',-5.00,'single family',2621.58
+'AAAAAAAAEAJGBAAA','Miss','Helen','Flora','38','Park ','Circle','Suite 360','Florence','Pike County','GA','33394','United States',-5.00,'apartment',2804.13
+'AAAAAAAAEBDFAAAA','Mr.','Ed','Chavis','276','Pine Hill','Boulevard','Suite 150','Shiloh','Lincoln County','GA','39275','United States',-5.00,'single family',1800.04
+'AAAAAAAAECIHAAAA','Sir','Gilberto','Mcdonald','615','10th ','Blvd','Suite 150','Oakdale','Glynn County','GA','39584','United States',-5.00,'apartment',6023.68
+'AAAAAAAAECOGBAAA','Dr.','David','Lamb','436','Second Elm','Way','Suite 30','Green Acres','Laurens County','GA','37683','United States',-5.00,'single family',4778.71
+'AAAAAAAAEDOAAAAA','Dr.','NULL','Finley','899','Willow Pine','ST','Suite 160','Clinton','Peach County','GA','38222','United States',-5.00,'condo',18954.78
+'AAAAAAAAEEFEBAAA','Mr.','Gordon','Lee','444','Park Jackson','Boulevard','Suite 150','Kingston','Mitchell County','GA','34975','United States',-5.00,'single family',2499.41
+'AAAAAAAAEFMHBAAA','Ms.','Anne','Busby','724','Second ','ST','Suite 20','Union Hill','Pulaski County','GA','37746','United States',-5.00,'condo',2866.95
+'AAAAAAAAEGOJAAAA','Miss','Caroline','Miller','719','Smith ','RD','Suite M','Empire','Oglethorpe County','GA','34145','United States',-5.00,'single family',7530.99
+'AAAAAAAAEHCIAAAA','Ms.','Laura','Wright','816','Main ','Ln','Suite 300','Sunnyside','Emanuel County','GA','31952','United States',-5.00,'apartment',2767.59
+'AAAAAAAAEHPBAAAA','Dr.','Paula','Bearden','932','Williams Forest','Ln','Suite 10','Allentown','Long County','GA','31838','United States',-5.00,'single family',12021.15
+'AAAAAAAAEJFNAAAA','Dr.','Chad','Baldwin','404','Cedar ','Ct.','Suite Y','Enterprise','Treutlen County','GA','31757','United States',-5.00,'apartment',1737.56
+'AAAAAAAAEJJDAAAA','Ms.','Katherine','Hernandez','741','Park Jefferson','ST','Suite 270','Oak Ridge','McIntosh County','GA','38371','United States',-5.00,'single family',6714.64
+'AAAAAAAAEKGABAAA','Sir','Steven','Thurman','638','Park Green','Ct.','Suite 270','Lakeside','Wayne County','GA','39532','United States',-5.00,'single family',1662.14
+'AAAAAAAAEKJGBAAA','Sir','Kurt','Jenkins','656','8th ','Dr.','Suite E','Franklin','Heard County','GA','39101','United States',-5.00,'apartment',1607.08
+'AAAAAAAAELCGBAAA','Sir','Harold','Randolph','535','Lee ','RD','Suite 160','Sutton','Bacon County','GA','35413','United States',-5.00,'apartment',4737.51
+'AAAAAAAAELJAAAAA','Miss','Gwendolyn','Johnson','482','Sunset Spring','Ave','Suite J','Mount Olive','Madison County','GA','38059','United States',-5.00,'apartment',7387.08
+'AAAAAAAAENFHBAAA','Dr.','Janice','Ojeda','127','Williams 5th','Ave','Suite 280','Walnut Grove','Burke County','GA','37752','United States',-5.00,'condo',4838.93
+'AAAAAAAAEOBHAAAA','Miss','Janet','Mcculloch','379','Spring Eigth','Street','Suite R','Cedar Grove','Wilcox County','GA','30411','United States',-5.00,'single family',7581.20
+'AAAAAAAAEOGGAAAA','Sir','Christopher','Garrett','308','Broadway 4th','Lane','Suite 360','Sunnyside','Coffee County','GA','31952','United States',-5.00,'apartment',3279.85
+'AAAAAAAAEPBDBAAA','Dr.','Curtis','Briscoe','142','Meadow ','Road','Suite 460','Woodville','Montgomery County','GA','34289','United States',-5.00,'single family',5239.12
+'AAAAAAAAFAHCAAAA','Ms.','Anna','Koch','482','Sunset Spring','Ave','Suite J','Mount Olive','Madison County','GA','38059','United States',-5.00,'apartment',1858.37
+'AAAAAAAAFANDAAAA','Dr.','Daniel','Wilson','586','Pine Cherry','Street','Suite U','Deerfield','Muscogee County','GA','39840','United States',-5.00,'condo',2774.69
+'AAAAAAAAFANDAAAA','Dr.','Daniel','Wilson','586','Pine Cherry','Street','Suite U','Deerfield','Muscogee County','GA','39840','United States',-5.00,'condo',2946.52
+---- TYPES
+STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, DECIMAL, STRING, DECIMAL
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q82.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q82.test b/testdata/workloads/tpcds/queries/tpcds-q82.test
new file mode 100644
index 0000000..be2a417
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q82.test
@@ -0,0 +1,22 @@
+====
+---- 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 62 and 62+30
+ and inv_item_sk = i_item_sk
+ and d_date_sk=inv_date_sk
+ and cast(d_date as timestamp) between cast('2000-05-25' as timestamp) and (cast('2000-05-25' as timestamp) + interval 60 days)
+ and i_manufact_id in (129,270,821,423)
+ 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
+'AAAAAAAAECMCAAAA','Rather vast companies pose quiet, actual carers. Close times take only simple possibilities. Current events might say only on a foundation',67.28
+'AAAAAAAALIHCAAAA','Things select increased views. Tools imagine for example; ever likely developments live so scottish benefits. Soft, short-term services should give indeed good illu',86.90
+---- TYPES
+STRING, STRING, DECIMAL
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q84.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q84.test b/testdata/workloads/tpcds/queries/tpcds-q84.test
new file mode 100644
index 0000000..d556ab9
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q84.test
@@ -0,0 +1,49 @@
+====
+---- QUERY: TPCDS-Q84
+select  c_customer_id as customer_id
+       ,concat(coalesce(c_last_name,''), ', ', coalesce(c_first_name,'')) as customername
+ from customer
+     ,customer_address
+     ,customer_demographics
+     ,household_demographics
+     ,income_band
+     ,store_returns
+ where ca_city          =  'Edgewood'
+   and c_current_addr_sk = ca_address_sk
+   and ib_lower_bound   >=  38128
+   and ib_upper_bound   <=  38128 + 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
+'AAAAAAAAAIPGAAAA','Carter, Rodney'
+'AAAAAAAAAKMBBAAA','Mcarthur, Emma'
+'AAAAAAAACBNHBAAA','Wells, Ron'
+'AAAAAAAADBMEAAAA','Vera, Tina'
+'AAAAAAAADBMEAAAA','Vera, Tina'
+'AAAAAAAADHKGBAAA','Scott, Pamela'
+'AAAAAAAAEIIBBAAA','Atkins, Susan'
+'AAAAAAAAFKAHAAAA','Batiste, Ernest'
+'AAAAAAAAGHMAAAAA','Mitchell, Gregory'
+'AAAAAAAAIAODBAAA','Murray, Karen'
+'AAAAAAAAIEOKAAAA','Solomon, Clyde'
+'AAAAAAAAIIBOAAAA','Owens, David'
+'AAAAAAAAIPDCAAAA','Wallace, Eric'
+'AAAAAAAAIPIMAAAA','Hayward, Benjamin'
+'AAAAAAAAJCIKAAAA','Ramos, Donald'
+'AAAAAAAAKFJEAAAA','Roberts, Yvonne'
+'AAAAAAAAKPGBBAAA','Moore, '
+'AAAAAAAALCLABAAA','Whitaker, Lettie'
+'AAAAAAAAMGMEAAAA','Sharp, Michael'
+'AAAAAAAAMIGBBAAA','Montgomery, Jesenia'
+'AAAAAAAAMPDKAAAA','Lopez, Isabel'
+'AAAAAAAANEOMAAAA','Powell, Linda'
+'AAAAAAAANKPCAAAA','Shaffer, Sergio'
+'AAAAAAAANOCKAAAA','Vargas, James'
+'AAAAAAAAOGJEBAAA','Owens, Denice'
+---- TYPES
+STRING, STRING
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q86a.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q86a.test b/testdata/workloads/tpcds/queries/tpcds-q86a.test
new file mode 100644
index 0000000..d6abbfe
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q86a.test
@@ -0,0 +1,139 @@
+====
+---- QUERY: TPCDS-Q86A
+-- RESULT MISMATCH FROM ORIGINAL
+-- FIXED. TAKE ACTUAL RESULT AS EXPECTED
+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 1200 and 1200+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
+325552630.64,'NULL','NULL',2,1
+33602545.41,'Shoes','NULL',1,1
+33399717.80,'Music','NULL',1,2
+33061835.62,'Women','NULL',1,3
+32942681.04,'Books','NULL',1,4
+32233369.67,'Men','NULL',1,5
+32135907.22,'Electronics','NULL',1,6
+32027085.12,'Jewelry','NULL',1,7
+31908139.65,'Sports','NULL',1,8
+31877378.15,'Children','NULL',1,9
+31428816.29,'Home','NULL',1,10
+935154.67,'NULL','NULL',1,11
+2981755.34,'Books','history',0,1
+2533681.90,'Books','romance',0,2
+2513406.90,'Books','computers',0,3
+2359621.74,'Books','fiction',0,4
+2220829.36,'Books','home repair',0,5
+2132619.93,'Books','reference',0,6
+2023862.05,'Books','travel',0,7
+1938302.12,'Books','parenting',0,8
+1916049.65,'Books','science',0,9
+1904474.64,'Books','business',0,10
+1903280.30,'Books','sports',0,11
+1819360.79,'Books','self-help',0,12
+1817324.19,'Books','mystery',0,13
+1698653.10,'Books','entertainments',0,14
+1641874.01,'Books','cooking',0,15
+1453516.79,'Books','arts',0,16
+84068.23,'Books','NULL',0,17
+8603692.12,'Children','infants',0,1
+7982811.62,'Children','toddlers',0,2
+7716135.48,'Children','school-uniforms',0,3
+7529560.01,'Children','newborn',0,4
+45178.92,'Children','NULL',0,5
+2625503.90,'Electronics','dvd/vcr players',0,1
+2351244.66,'Electronics','televisions',0,2
+2283231.32,'Electronics','memory',0,3
+2262599.89,'Electronics','stereo',0,4
+2257811.83,'Electronics','karoke',0,5
+2235218.27,'Electronics','monitors',0,6
+2228844.29,'Electronics','scanners',0,7
+1948029.88,'Electronics','wireless',0,8
+1894729.95,'Electronics','disk drives',0,9
+1838653.07,'Electronics','automotive',0,10
+1793728.96,'Electronics','portable',0,11
+1768939.49,'Electronics','cameras',0,12
+1733994.95,'Electronics','musical',0,13
+1730490.24,'Electronics','personal',0,14
+1670511.23,'Electronics','camcorders',0,15
+1512375.29,'Electronics','audio',0,16
+2408305.34,'Home','paint',0,1
+2349779.93,'Home','curtains/drapes',0,2
+2306676.63,'Home','bedding',0,3
+2284168.74,'Home','flatware',0,4
+2269734.86,'Home','glassware',0,5
+2112112.73,'Home','lighting',0,6
+2085612.39,'Home','bathroom',0,7
+1924834.23,'Home','mattresses',0,8
+1868688.89,'Home','tables',0,9
+1820604.22,'Home','furniture',0,10
+1816997.04,'Home','decor',0,11
+1805905.27,'Home','kids',0,12
+1788142.30,'Home','blinds/shades',0,13
+1570699.11,'Home','accent',0,14
+1503088.13,'Home','rugs',0,15
+1457642.85,'Home','wallpaper',0,16
+55823.63,'Home','NULL',0,17
+2754963.88,'Jewelry','jewelry boxes',0,1
+2346470.04,'Jewelry','pendants',0,2
+2308524.13,'Jewelry','rings',0,3
+2216602.19,'Jewelry','custom',0,4
+2141484.71,'Jewelry','gold',0,5
+2105589.88,'Jewelry','estate',0,6
+2105536.74,'Jewelry','womens watch',0,7
+2057556.93,'Jewelry','mens watch',0,8
+1964085.08,'Jewelry','costume',0,9
+1838748.38,'Jewelry','birdal',0,10
+1820456.21,'Jewelry','earings',0,11
+1811297.48,'Jewelry','loose stones',0,12
+1738088.37,'Jewelry','diamonds',0,13
+1636759.38,'Jewelry','semi-precious',0,14
+1625306.26,'Jewelry','bracelets',0,15
+1457032.40,'Jewelry','consignment',0,16
+98583.06,'Jewelry','NULL',0,17
+8968117.21,'Men','shirts',0,1
+8079270.31,'Men','sports-apparel',0,2
+7734264.66,'Men','accessories',0,3
+7451717.49,'Men','pants',0,4
+9196797.97,'Music','classical',0,1
+8755969.85,'Music','pop',0,2
+8444376.89,'Music','country',0,3
+6891047.77,'Music','rock',0,4
+111525.32,'Music','NULL',0,5
+8938639.40,'Shoes','mens',0,1
+8290473.41,'Shoes','kids',0,2
+8241359.70,'Shoes','womens',0,3
+8132072.90,'Shoes','athletic',0,4
+2530419.12,'Sports','golf',0,1
+2513869.92,'Sports','athletic shoes',0,2
+2340416.63,'Sports','outdoor',0,3
+---- TYPES
+DECIMAL, STRING, STRING, TINYINT, BIGINT
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q89.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q89.test b/testdata/workloads/tpcds/queries/tpcds-q89.test
deleted file mode 100644
index a70e102..0000000
--- a/testdata/workloads/tpcds/queries/tpcds-q89.test
+++ /dev/null
@@ -1,131 +0,0 @@
-====
----- 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 (1999) and
-        ((i_category in ('Books','Electronics','Sports') and
-          i_class in ('computers','stereo','football')
-         )
-      or (i_category in ('Men','Jewelry','Women') and
-          i_class in ('shirts','birdal','dresses')
-        ))
-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
-'Men','shirts','importoimporto #1','ese','Unknown',3,2070.65,5492.81
-'Men','shirts','importoimporto #1','ought','Unknown',6,2152.15,5045.75
-'Men','shirts','importoimporto #1','able','Unknown',2,2333.43,5194.81
-'Men','shirts','importoimporto #1','ese','Unknown',6,2691.33,5492.81
-'Men','shirts','importoimporto #1','ought','Unknown',2,2253.70,5045.75
-'Women','dresses','amalgamalg #1','able','Unknown',6,1761.81,4507.24
-'Men','shirts','importoimporto #1','ation','Unknown',3,2672.68,5410.91
-'Men','shirts','importoimporto #1','ation','Unknown',5,2677.81,5410.91
-'Men','shirts','importoimporto #1','eing','Unknown',5,2356.88,5074.07
-'Men','shirts','importoimporto #1','ese','Unknown',4,2784.11,5492.81
-'Women','dresses','amalgamalg #1','ought','Unknown',7,1926.67,4619.70
-'Women','dresses','amalgamalg #1','ought','Unknown',1,2065.94,4619.70
-'Men','shirts','importoimporto #1','ation','Unknown',6,2881.34,5410.91
-'Men','shirts','importoimporto #1','able','Unknown',3,2674.91,5194.81
-'Women','dresses','amalgamalg #1','ation','Unknown',2,2192.31,4690.89
-'Men','shirts','importoimporto #1','bar','Unknown',5,2475.90,4973.50
-'Women','dresses','amalgamalg #1','ese','Unknown',2,2146.52,4642.99
-'Men','shirts','importoimporto #1','eing','Unknown',2,2588.02,5074.07
-'Women','dresses','amalgamalg #1','bar','Unknown',7,2428.75,4914.11
-'Women','dresses','amalgamalg #1','bar','Unknown',3,2451.51,4914.11
-'Men','shirts','importoimporto #1','ation','Unknown',7,2951.67,5410.91
-'Men','shirts','importoimporto #1','able','Unknown',1,2765.75,5194.81
-'Women','dresses','amalgamalg #1','able','Unknown',2,2082.54,4507.24
-'Men','shirts','importoimporto #1','ation','Unknown',4,2988.08,5410.91
-'Men','shirts','importoimporto #1','ought','Unknown',7,2667.92,5045.75
-'Women','dresses','amalgamalg #1','bar','Unknown',4,2549.27,4914.11
-'Women','dresses','amalgamalg #1','ese','Unknown',7,2307.00,4642.99
-'Women','dresses','amalgamalg #1','eing','Unknown',4,2204.15,4528.82
-'Men','shirts','importoimporto #1','able','Unknown',7,2880.34,5194.81
-'Men','shirts','importoimporto #1','ese','Unknown',5,3210.74,5492.81
-'Women','dresses','amalgamalg #1','ation','Unknown',4,2418.39,4690.89
-'Men','shirts','importoimporto #1','eing','Unknown',4,2833.40,5074.07
-'Women','dresses','amalgamalg #1','bar','Unknown',2,2692.79,4914.11
-'Men','shirts','importoimporto #1','eing','Unknown',6,2854.62,5074.07
-'Women','dresses','amalgamalg #1','eing','Unknown',6,2314.71,4528.82
-'Men','shirts','importoimporto #1','ought','Unknown',5,2834.94,5045.75
-'Women','dresses','amalgamalg #1','ation','Unknown',5,2480.25,4690.89
-'Women','dresses','amalgamalg #1','able','Unknown',7,2302.21,4507.24
-'Women','dresses','amalgamalg #1','ation','Unknown',6,2501.15,4690.89
-'Men','shirts','importoimporto #1','ese','Unknown',2,3307.78,5492.81
-'Men','shirts','importoimporto #1','able','Unknown',5,3016.43,5194.81
-'Men','shirts','importoimporto #1','able','Unknown',4,3040.23,5194.81
-'Women','dresses','amalgamalg #1','eing','Unknown',5,2420.31,4528.82
-'Women','dresses','amalgamalg #1','ese','Unknown',5,2535.80,4642.99
-'Women','dresses','amalgamalg #1','ought','Unknown',2,2521.70,4619.70
-'Men','shirts','importoimporto #1','bar','Unknown',3,2877.96,4973.50
-'Women','dresses','amalgamalg #1','ese','Unknown',3,2577.15,4642.99
-'Women','dresses','amalgamalg #1','ation','Unknown',7,2637.27,4690.89
-'Men','shirts','importoimporto #1','ation','Unknown',1,3380.16,5410.91
-'Women','dresses','amalgamalg #1','eing','Unknown',7,2524.32,4528.82
-'Men','shirts','importoimporto #1','bar','Unknown',4,2986.70,4973.50
-'Women','dresses','amalgamalg #1','ought','Unknown',4,2648.80,4619.70
-'Men','shirts','importoimporto #1','bar','Unknown',7,3026.41,4973.50
-'Women','dresses','amalgamalg #1','ation','Unknown',3,2751.11,4690.89
-'Men','shirts','importoimporto #1','bar','Unknown',2,3073.45,4973.50
-'Men','shirts','importoimporto #1','eing','Unknown',3,3179.96,5074.07
-'Women','dresses','amalgamalg #1','ation','Unknown',1,2797.07,4690.89
-'Women','dresses','amalgamalg #1','able','Unknown',1,2621.05,4507.24
-'Women','dresses','amalgamalg #1','ese','Unknown',4,2808.36,4642.99
-'Women','dresses','amalgamalg #2','bar','Unknown',6,680.47,2506.96
-'Men','shirts','importoimporto #1','ation','Unknown',2,3591.65,5410.91
-'Women','dresses','amalgamalg #1','able','Unknown',3,2706.15,4507.24
-'Women','dresses','amalgamalg #2','eing','Unknown',2,773.08,2569.34
-'Men','shirts','importoimporto #1','bar','Unknown',6,3199.08,4973.50
-'Men','shirts','importoimporto #1','ese','Unknown',7,3754.33,5492.81
-'Women','dresses','amalgamalg #1','bar','Unknown',5,3190.77,4914.11
-'Women','dresses','amalgamalg #1','eing','Unknown',2,2841.88,4528.82
-'Men','shirts','importoimporto #1','able','Unknown',6,3538.25,5194.81
-'Women','dresses','amalgamalg #1','ese','Unknown',6,3006.94,4642.99
-'Women','dresses','amalgamalg #1','bar','Unknown',1,3284.93,4914.11
-'Women','dresses','amalgamalg #2','bar','Unknown',5,878.42,2506.96
-'Women','dresses','amalgamalg #1','able','Unknown',5,2891.95,4507.24
-'Men','shirts','importoimporto #1','eing','Unknown',7,3465.77,5074.07
-'Women','dresses','amalgamalg #2','eing','Unknown',3,964.29,2569.34
-'Women','dresses','amalgamalg #1','bar','Unknown',6,3324.49,4914.11
-'Men','shirts','importoimporto #1','ought','Unknown',3,3467.09,5045.75
-'Women','dresses','amalgamalg #1','ought','Unknown',5,3049.25,4619.70
-'Women','dresses','amalgamalg #1','eing','Unknown',3,2978.07,4528.82
-'Men','shirts','importoimporto #1','ought','Unknown',4,3544.97,5045.75
-'Men','shirts','importoimporto #1','bar','Unknown',1,3490.62,4973.50
-'Men','shirts','importoimporto #2','able','Unknown',2,628.63,2095.47
-'Men','shirts','importoimporto #1','eing','Unknown',1,3628.21,5074.07
-'Women','dresses','amalgamalg #1','able','Unknown',4,3082.17,4507.24
-'Men','shirts','importoimporto #2','eing','Unknown',5,765.86,2137.65
-'Women','dresses','amalgamalg #2','ation','Unknown',4,1064.70,2431.22
-'Women','dresses','amalgamalg #2','ought','Unknown',3,991.89,2355.44
-'Women','dresses','amalgamalg #2','eing','Unknown',5,1211.13,2569.34
-'Women','dresses','amalgamalg #2','ation','Unknown',7,1078.41,2431.22
-'Men','shirts','importoimporto #2','ation','Unknown',4,807.67,2141.06
-'Women','dresses','amalgamalg #2','ation','Unknown',2,1102.89,2431.22
-'Women','dresses','amalgamalg #2','eing','Unknown',6,1247.86,2569.34
-'Women','dresses','amalgamalg #2','able','Unknown',4,1020.39,2337.27
-'Men','shirts','importoimporto #2','able','Unknown',7,780.91,2095.47
-'Women','dresses','amalgamalg #2','ese','Unknown',2,904.34,2200.82
-'Women','dresses','amalgamalg #2','ought','Unknown',7,1061.87,2355.44
-'Women','dresses','amalgamalg #2','able','Unknown',3,1054.94,2337.27
-'Men','shirts','importoimporto #2','ought','Unknown',6,633.96,1908.05
-'Men','shirts','importoimporto #2','ese','Unknown',6,853.37,2113.57
-'Women','dresses','amalgamalg #2','able','Unknown',5,1084.94,2337.27
-'Women','dresses','amalgamalg #2','eing','Unknown',4,1317.08,2569.34
----- TYPES
-STRING, STRING, STRING, STRING, STRING, INT, DECIMAL, DECIMAL
-====
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q91.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q91.test b/testdata/workloads/tpcds/queries/tpcds-q91.test
new file mode 100644
index 0000000..c4840b0
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q91.test
@@ -0,0 +1,35 @@
+====
+---- 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                  = 1998
+and     d_moy                   = 11
+and     ( (cd_marital_status       = 'M' and cd_education_status     = 'Unknown')
+        or(cd_marital_status       = 'W' and cd_education_status     = 'Advanced Degree'))
+and     hd_buy_potential like 'Unknown%'
+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
+'AAAAAAAACAAAAAAA','Mid Atlantic','Felipe Perkins',109.74
+---- TYPES
+STRING, STRING, STRING, DECIMAL
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q92.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q92.test b/testdata/workloads/tpcds/queries/tpcds-q92.test
new file mode 100644
index 0000000..f0f6b9f
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q92.test
@@ -0,0 +1,34 @@
+====
+---- QUERY: TPCDS-Q92
+select
+   sum(ws_ext_discount_amt)  as "Excess Discount Amount"
+from
+    web_sales
+   ,item
+   ,date_dim
+where
+i_manufact_id = 350
+and i_item_sk = ws_item_sk
+and cast(d_date as timestamp) between cast('2000-01-27' as timestamp) and
+        (cast('2000-01-27' 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 cast(d_date as timestamp) between cast('2000-01-27' as timestamp) and
+                             (cast('2000-01-27' as timestamp) + interval 90 days)
+          and d_date_sk = ws_sold_date_sk
+      )
+order by sum(ws_ext_discount_amt)
+limit 100;
+---- RESULTS
+39529.71
+---- TYPES
+DECIMAL
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q94.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q94.test b/testdata/workloads/tpcds/queries/tpcds-q94.test
new file mode 100644
index 0000000..899bf66
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q94.test
@@ -0,0 +1,33 @@
+====
+---- 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
+    cast(d_date as timestamp) between cast('1999-02-01' as timestamp) and
+           (cast('1999-02-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 = 'IL'
+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
+33,64554.35,-3979.35
+---- TYPES
+BIGINT, DECIMAL, DECIMAL
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q95.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q95.test b/testdata/workloads/tpcds/queries/tpcds-q95.test
new file mode 100644
index 0000000..a658d06
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q95.test
@@ -0,0 +1,36 @@
+====
+---- 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
+    cast(d_date as timestamp) between cast('1999-02-01' as timestamp) and
+           (cast('1999-02-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 = 'IL'
+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
+73,120440.34,42133.12
+---- TYPES
+BIGINT, DECIMAL, DECIMAL
+====

http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q97.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-q97.test b/testdata/workloads/tpcds/queries/tpcds-q97.test
new file mode 100644
index 0000000..8725c45
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-q97.test
@@ -0,0 +1,29 @@
+====
+---- 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 1200 and 1200 + 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 1200 and 1200 + 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
+540401,286628,174
+---- TYPES
+BIGINT, BIGINT, BIGINT
+====


Mime
View raw message