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 +====
