http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q22a.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q22a.test b/testdata/workloads/tpcds/queries/tpcds-q22a.test new file mode 100644 index 0000000..3e40c35 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q22a.test @@ -0,0 +1,148 @@ +==== +---- QUERY: TPCDS-Q22A +-- RESULT MISMATCH FROM ORIGINAL in LSD of AVG() values +-- FIXED, HAND_ROUNDED AVG() VALUES IN RESULT SET +set decimal_v2=1; +with results as +(select i_product_name + ,i_brand + ,i_class + ,i_category + ,inv_quantity_on_hand qoh + from inventory + ,date_dim + ,item + ,warehouse + where inv_date_sk=d_date_sk + and inv_item_sk=i_item_sk + and inv_warehouse_sk = w_warehouse_sk + and d_month_seq between 1200 and 1200 + 11 + -- group by i_product_name,i_brand,i_class,i_category +), +results_rollup as +(select i_product_name, i_brand, i_class, i_category,avg(qoh) qoh +from results +group by i_product_name,i_brand,i_class,i_category +union all +select i_product_name, i_brand, i_class, null i_category,avg(qoh) qoh +from results +group by i_product_name,i_brand,i_class +union all +select i_product_name, i_brand, null i_class, null i_category,avg(qoh) qoh +from results +group by i_product_name,i_brand +union all +select i_product_name, null i_brand, null i_class, null i_category,avg(qoh) qoh +from results +group by i_product_name +union all +select null i_product_name, null i_brand, null i_class, null i_category,avg(qoh) qoh +from results) + select i_product_name, i_brand, i_class, i_category, cast(qoh as decimal(12, 2)) + from results_rollup + order by qoh, i_product_name, i_brand, i_class, i_category +limit 100; +---- RESULTS +'ationbarn station','amalgbrand #8','bathroom','Home',430.36 +'ationbarn station','amalgbrand #8','bathroom','NULL',430.36 +'ationbarn station','amalgbrand #8','NULL','NULL',430.36 +'ationbarn station','NULL','NULL','NULL',430.36 +'ationoughtn stn st','edu packimporto #2','sports-apparel','Men',435.27 +'ationoughtn stn st','edu packimporto #2','sports-apparel','NULL',435.27 +'ationoughtn stn st','edu packimporto #2','NULL','NULL',435.27 +'ationoughtn stn st','NULL','NULL','NULL',435.27 +'ationationprin st','amalgexporti #2','newborn','Children',435.51 +'ationationprin st','amalgexporti #2','newborn','NULL',435.51 +'ationationprin st','amalgexporti #2','NULL','NULL',435.51 +'ationationprin st','NULL','NULL','NULL',435.51 +'oughtcallyn stantiought','corpcorp #2','rings','Jewelry',436.49 +'oughtcallyn stantiought','corpcorp #2','rings','NULL',436.49 +'oughtcallyn stantiought','corpcorp #2','NULL','NULL',436.49 +'oughtcallyn stantiought','NULL','NULL','NULL',436.49 +'callyesebaration','namelesscorp #7','mens watch','Jewelry',437.39 +'callyesebaration','namelesscorp #7','mens watch','NULL',437.39 +'callyesebaration','namelesscorp #7','NULL','NULL',437.39 +'callyesebaration','NULL','NULL','NULL',437.39 +'n stprieingationought','namelesscorp #8','mens watch','Jewelry',439.06 +'n stprieingationought','namelesscorp #8','mens watch','NULL',439.06 +'n stprieingationought','namelesscorp #8','NULL','NULL',439.06 +'n stprieingationought','NULL','NULL','NULL',439.06 +'priationn stn st','importoscholar #2','country','Music',439.10 +'priationn stn st','importoscholar #2','country','NULL',439.10 +'priationn stn st','importoscholar #2','NULL','NULL',439.10 +'priationn stn st','NULL','NULL','NULL',439.10 +'n stcallyableoughtought','amalgimporto #2','accessories','Men',439.72 +'n stcallyableoughtought','amalgimporto #2','accessories','NULL',439.72 +'n stcallyableoughtought','amalgimporto #2','NULL','NULL',439.72 +'n stcallyableoughtought','NULL','NULL','NULL',439.72 +'n stbarought','corpbrand #10','rugs','Home',440.31 +'n stbarought','corpbrand #10','rugs','NULL',440.31 +'n stbarought','corpbrand #10','NULL','NULL',440.31 +'n stbarought','NULL','NULL','NULL',440.31 +'n stn stantioughtought','exportimaxi #8','computers','Books',440.93 +'n stn stantioughtought','exportimaxi #8','computers','NULL',440.93 +'n stn stantioughtought','exportimaxi #8','NULL','NULL',440.93 +'n stn stantioughtought','NULL','NULL','NULL',440.93 +'n stcallyesepriought','edu packscholar #2','classical','Music',441.54 +'n stcallyesepriought','edu packscholar #2','classical','NULL',441.54 +'n stcallyesepriought','edu packscholar #2','NULL','NULL',441.54 +'n stcallyesepriought','NULL','NULL','NULL',441.54 +'priantiationation','importoimporto #2','shirts','Men',441.67 +'priantiationation','importoimporto #2','shirts','NULL',441.67 +'priantiationation','importoimporto #2','NULL','NULL',441.67 +'priantiationation','NULL','NULL','NULL',441.67 +'oughtbarantiese','exportiedu pack #2','kids','Shoes',441.75 +'oughtbarantiese','exportiedu pack #2','kids','NULL',441.75 +'oughtbarantiese','exportiedu pack #2','NULL','NULL',441.75 +'oughtbarantiese','NULL','NULL','NULL',441.75 +'oughtbarprin st','edu packscholar #2','classical','Music',442.07 +'oughtbarprin st','edu packscholar #2','classical','NULL',442.07 +'oughtbarprin st','edu packscholar #2','NULL','NULL',442.07 +'oughtbarprin st','NULL','NULL','NULL',442.07 +'ationeingation','amalgexporti #2','newborn','Children',443.78 +'ationeingation','amalgexporti #2','newborn','NULL',443.78 +'ationeingation','amalgexporti #2','NULL','NULL',443.78 +'ationeingation','NULL','NULL','NULL',443.78 +'ationbarn stableought','corpbrand #8','rugs','Home',444.09 +'ationbarn stableought','corpbrand #8','rugs','NULL',444.09 +'ationbarn stableought','corpbrand #8','NULL','NULL',444.09 +'ationbarn stableought','NULL','NULL','NULL',444.09 +'eseationcallyableought','importoexporti #1','infants','Children',444.54 +'eseationcallyableought','importoexporti #1','infants','NULL',444.54 +'eseationcallyableought','importoexporti #1','NULL','NULL',444.54 +'eseationcallyableought','NULL','NULL','NULL',444.54 +'eingableableationought','amalgimporto #1','accessories','Men',444.71 +'eingableableationought','amalgimporto #1','accessories','NULL',444.71 +'eingableableationought','amalgimporto #1','NULL','NULL',444.71 +'eingableableationought','NULL','NULL','NULL',444.71 +'n steseantiation','univmaxi #10','pools','Sports',445.33 +'n steseantiation','univmaxi #10','pools','NULL',445.33 +'n steseantiation','univmaxi #10','NULL','NULL',445.33 +'n steseantiation','NULL','NULL','NULL',445.33 +'antibareingantiought','importoimporto #2','shirts','Men',445.41 +'antibareingantiought','importoimporto #2','shirts','NULL',445.41 +'antibareingantiought','importoimporto #2','NULL','NULL',445.41 +'antibareingantiought','NULL','NULL','NULL',445.41 +'ationantibarationought','univmaxi #8','pools','Sports',445.42 +'ationantibarationought','univmaxi #8','pools','NULL',445.42 +'ationantibarationought','univmaxi #8','NULL','NULL',445.42 +'ationantibarationought','NULL','NULL','NULL',445.42 +'oughtpricallybarought','edu packexporti #2','school-uniforms','Children',445.49 +'oughtpricallybarought','edu packexporti #2','school-uniforms','NULL',445.49 +'oughtpricallybarought','edu packexporti #2','NULL','NULL',445.49 +'oughtpricallybarought','NULL','NULL','NULL',445.49 +'ableationprieing','amalgamalg #1','dresses','Women',446.00 +'ableationprieing','amalgamalg #1','dresses','NULL',446.00 +'ableationprieing','amalgamalg #1','NULL','NULL',446.00 +'ableationprieing','NULL','NULL','NULL',446.00 +'pribarpribarought','edu packimporto #2','sports-apparel','Men',446.04 +'pribarpribarought','edu packimporto #2','sports-apparel','NULL',446.04 +'pribarpribarought','edu packimporto #2','NULL','NULL',446.04 +'pribarpribarought','NULL','NULL','NULL',446.04 +'oughtn stprin st','exportiamalgamalg #8','stereo','Electronics',447.27 +'oughtn stprin st','exportiamalgamalg #8','stereo','NULL',447.27 +'oughtn stprin st','exportiamalgamalg #8','NULL','NULL',447.27 +'oughtn stprin st','NULL','NULL','NULL',447.27 +---- TYPES +STRING, STRING, STRING, STRING, DECIMAL +====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q23-1.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q23-1.test b/testdata/workloads/tpcds/queries/tpcds-q23-1.test deleted file mode 100644 index 7ec3bb4..0000000 --- a/testdata/workloads/tpcds/queries/tpcds-q23-1.test +++ /dev/null @@ -1,60 +0,0 @@ -==== ----- QUERY: TPCDS-Q23 -with frequent_ss_items as - (select substr(i_item_desc,1,30) itemdesc, - i_item_sk item_sk, - d_date solddate, - count(*) cnt - from store_sales, - date_dim, - item - where ss_sold_date_sk = d_date_sk - and ss_item_sk = i_item_sk - and d_year in (2000,2000+1,2000+2,2000+3) - group by substr(i_item_desc,1,30),i_item_sk,d_date - having count(*) > 4), - max_store_sales as - (select max(csales) tpcds_cmax - from (select c_customer_sk, - sum(ss_quantity*ss_sales_price) csales - from store_sales, - customer, - date_dim - where ss_customer_sk = c_customer_sk - and ss_sold_date_sk = d_date_sk - and d_year in (2000,2000+1,2000+2,2000+3) - group by c_customer_sk) x), - best_ss_customer as - (select t1.* from - (select c_customer_sk, - sum(ss_quantity*ss_sales_price) ssales - from store_sales, - customer - where ss_customer_sk = c_customer_sk - group by c_customer_sk) t1, - (select tpcds_cmax * (50/100.0) as c1 from max_store_sales) t2 - where t1.ssales > t2.c1) -select sum(sales) -from (select cs_quantity*cs_list_price sales - from catalog_sales, - date_dim - where d_year = 2000 - and d_moy = 2 - and cs_sold_date_sk = d_date_sk - and cs_item_sk in (select item_sk from frequent_ss_items) - and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) - union all - select ws_quantity*ws_list_price sales - from web_sales, - date_dim - where d_year = 2000 - and d_moy = 2 - and ws_sold_date_sk = d_date_sk - and ws_item_sk in (select item_sk from frequent_ss_items) - and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) x -limit 100; ----- RESULTS -17030.91 ----- TYPES -DECIMAL -==== \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q23-2.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q23-2.test b/testdata/workloads/tpcds/queries/tpcds-q23-2.test deleted file mode 100644 index 3b9e060..0000000 --- a/testdata/workloads/tpcds/queries/tpcds-q23-2.test +++ /dev/null @@ -1,70 +0,0 @@ -==== ----- QUERY: TPCDS-Q23 -with frequent_ss_items as - (select substr(i_item_desc,1,30) itemdesc, - i_item_sk item_sk, - d_date solddate, - count(*) cnt - from store_sales, - date_dim, - item - where ss_sold_date_sk = d_date_sk - and ss_item_sk = i_item_sk - and d_year in (2000,2000 + 1,2000 + 2,2000 + 3) - group by substr(i_item_desc,1,30),i_item_sk,d_date - having count(*) > 4), - max_store_sales as - (select max(csales) tpcds_cmax - from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales - from store_sales, - customer, - date_dim - where ss_customer_sk = c_customer_sk - and ss_sold_date_sk = d_date_sk - and d_year in (2000,2000+1,2000+2,2000+3) - group by c_customer_sk) x), - best_ss_customer as - (select t1.* from - (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales - from store_sales, - customer - where ss_customer_sk = c_customer_sk - group by c_customer_sk) t1, - (select tpcds_cmax * (50/100.0) as c1 from max_store_sales) t2 - where t1.ssales > t2.c1) -select c_last_name,c_first_name,sales -from ((select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales - from catalog_sales, - customer, - date_dim - where d_year = 2000 - and d_moy = 2 - and cs_sold_date_sk = d_date_sk - and cs_item_sk in (select distinct item_sk from frequent_ss_items) - and cs_bill_customer_sk in (select distinct c_customer_sk from best_ss_customer) - and cs_bill_customer_sk = c_customer_sk - group by c_last_name,c_first_name) - union all - (select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales - from web_sales, - customer, - date_dim - where d_year = 2000 - and d_moy = 2 - and ws_sold_date_sk = d_date_sk - and ws_item_sk in (select distinct item_sk from frequent_ss_items) - and ws_bill_customer_sk in (select distinct c_customer_sk from best_ss_customer) - and ws_bill_customer_sk = c_customer_sk - group by c_last_name,c_first_name)) y - order by c_last_name, - c_first_name, - sales -limit 100; ----- RESULTS -'Brown','Monika',6031.52 -'Collins','Gordon',727.57 -'Green','Jesse',9672.96 -'NULL','Robert',598.86 ----- TYPES -STRING, STRING, DECIMAL -==== \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q25.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q25.test b/testdata/workloads/tpcds/queries/tpcds-q25.test new file mode 100644 index 0000000..0f7f41c --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q25.test @@ -0,0 +1,52 @@ +==== +---- QUERY: TPCDS-Q25 +select + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + ,sum(ss_net_profit) as store_sales_profit + ,sum(sr_net_loss) as store_returns_loss + ,sum(cs_net_profit) as catalog_sales_profit + from + store_sales + ,store_returns + ,catalog_sales + ,date_dim d1 + ,date_dim d2 + ,date_dim d3 + ,store + ,item + where + d1.d_moy = 4 + and d1.d_year = 2001 + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and ss_customer_sk = sr_customer_sk + and ss_item_sk = sr_item_sk + and ss_ticket_number = sr_ticket_number + and sr_returned_date_sk = d2.d_date_sk + and d2.d_moy between 4 and 10 + and d2.d_year = 2001 + and sr_customer_sk = cs_bill_customer_sk + and sr_item_sk = cs_item_sk + and cs_sold_date_sk = d3.d_date_sk + and d3.d_moy between 4 and 10 + and d3.d_year = 2001 + group by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + order by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + limit 100; +---- RESULTS +'AAAAAAAADPMBAAAA','Things know alone letters. Flights should tend even jewish fees. Civil plans could not cry also social days; other losses might not pay walls; still able signs should not remove too human ','AAAAAAAAHAAAAAAA','ation',12.84,91.41,-1329.46 +---- TYPES +STRING, STRING, STRING, STRING, DECIMAL, DECIMAL, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q27.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q27.test b/testdata/workloads/tpcds/queries/tpcds-q27.test deleted file mode 100644 index 59a792f..0000000 --- a/testdata/workloads/tpcds/queries/tpcds-q27.test +++ /dev/null @@ -1,139 +0,0 @@ -==== ----- QUERY: TPCDS-Q27 -# TODO: Fix this query up when rollup is implemented -select - i_item_id, - s_state, - -- grouping(s_state) g_state, - avg(ss_quantity) agg1, - avg(ss_list_price) agg2, - avg(ss_coupon_amt) agg3, - avg(ss_sales_price) agg4 -from - store_sales, - customer_demographics, - date_dim, - store, - item -where - ss_sold_date_sk = d_date_sk - and ss_item_sk = i_item_sk - and ss_store_sk = s_store_sk - and ss_cdemo_sk = cd_demo_sk - and cd_gender = 'M' - and cd_marital_status = 'S' - and cd_education_status = 'College' - and d_year = 2002 - and s_state in ('TN','TN', 'TN', 'TN', 'TN', 'TN') -group by - -- rollup (i_item_id, s_state), - i_item_id, - s_state -order by - i_item_id, - s_state -limit 100; ----- RESULTS -'AAAAAAAAAAABAAAA','TN',46,114.72,0.00,32.12 -'AAAAAAAAAAAEAAAA','TN',100,77.97,0.00,57.69 -'AAAAAAAAAABAAAAA','TN',6,48.89,13.61,2.44 -'AAAAAAAAAACAAAAA','TN',62,32.21,0.00,5.50 -'AAAAAAAAAACDAAAA','TN',97,161.78,0.00,55.00 -'AAAAAAAAAADBAAAA','TN',28,136.01,575.76,73.44 -'AAAAAAAAAADCAAAA','TN',64,116.14,0.00,108.01 -'AAAAAAAAAAEBAAAA','TN',9,5.43,0.00,5.21 -'AAAAAAAAAAEDAAAA','TN',45.5,87.63,0.00,51.84 -'AAAAAAAAAAGBAAAA','TN',29,101.94,0.00,100.92 -'AAAAAAAAAAGCAAAA','TN',97,65.27,0.00,21.53 -'AAAAAAAAAAGEAAAA','TN',55,73.87,0.00,71.00 -'AAAAAAAAAAHAAAAA','TN',20,134.30,0.00,119.52 -'AAAAAAAAAAHBAAAA','TN',97,7.40,0.00,6.73 -'AAAAAAAAAAHDAAAA','TN',69,5.62,0.00,0.33 -'AAAAAAAAAAIAAAAA','TN',87,106.98,0.00,27.81 -'AAAAAAAAAAKBAAAA','TN',84.5,66.17,847.83,57.38 -'AAAAAAAAAALAAAAA','TN',6,32.28,0.00,7.42 -'AAAAAAAAAALCAAAA','TN',98,34.31,55.30,1.71 -'AAAAAAAAAALDAAAA','TN',59,129.47,713.90,22.00 -'AAAAAAAAAANAAAAA','TN',74,60.87,0.00,13.20 -'AAAAAAAAAANBAAAA','TN',96,130.79,1864.51,129.48 -'AAAAAAAAAAOAAAAA','TN',85,130.34,0.00,122.51 -'AAAAAAAAAAOCAAAA','TN',14,64.57,0.00,33.97 -'AAAAAAAAAAODAAAA','TN',84,30.50,0.00,28.97 -'AAAAAAAAAAPBAAAA','TN',45,118.89,2380.59,84.71 -'AAAAAAAAABAAAAAA','TN',97,124.75,0.00,74.85 -'AAAAAAAAABABAAAA','TN',31,29.98,17.09,27.58 -'AAAAAAAAABAEAAAA','TN',34,17.21,0.00,13.42 -'AAAAAAAAABCBAAAA','TN',38,131.37,0.00,52.32 -'AAAAAAAAABDAAAAA','TN',31.5,68.34,0.00,63.27 -'AAAAAAAAABDBAAAA','TN',56,103.85,1624.17,59.19 -'AAAAAAAAABDDAAAA','TN',67.33333333333333,119.42,23.18,38.72 -'AAAAAAAAABDEAAAA','TN',61,85.95,2282.36,44.60 -'AAAAAAAAABEDAAAA','TN',61,85.14,0.00,50.23 -'AAAAAAAAABFBAAAA','TN',31,23.55,0.00,13.65 -'AAAAAAAAABFCAAAA','TN',83,15.50,0.00,0.00 -'AAAAAAAAABGAAAAA','TN',64,35.08,0.00,3.15 -'AAAAAAAAABGBAAAA','TN',40,25.07,0.00,16.04 -'AAAAAAAAABGEAAAA','TN',39.5,56.76,14.25,36.00 -'AAAAAAAAABHAAAAA','TN',93,74.80,0.00,21.69 -'AAAAAAAAABHCAAAA','TN',31.5,79.83,181.38,61.93 -'AAAAAAAAABIBAAAA','TN',48,170.89,0.00,140.12 -'AAAAAAAAABJAAAAA','TN',46.5,82.32,0.00,44.98 -'AAAAAAAAABJBAAAA','TN',76,12.40,0.00,3.96 -'AAAAAAAAABJDAAAA','TN',34,118.50,0.00,61.20 -'AAAAAAAAABKAAAAA','TN',65,8.77,0.00,5.18 -'AAAAAAAAABKCAAAA','TN',26,51.57,0.00,41.25 -'AAAAAAAAABLBAAAA','TN',61.5,149.12,0.00,82.54 -'AAAAAAAAABMAAAAA','TN',14,109.97,0.00,10.99 -'AAAAAAAAABMDAAAA','TN',97,104.87,0.00,34.60 -'AAAAAAAAABNAAAAA','TN',13.5,17.39,0.00,2.02 -'AAAAAAAAABOBAAAA','TN',66,112.86,0.00,24.82 -'AAAAAAAAABPAAAAA','TN',19,67.33,268.60,16.83 -'AAAAAAAAABPDAAAA','TN',37,41.16,0.00,20.22 -'AAAAAAAAACACAAAA','TN',53,132.50,0.00,15.90 -'AAAAAAAAACADAAAA','TN',73,76.59,782.56,21.44 -'AAAAAAAAACBBAAAA','TN',82,75.22,277.55,10.24 -'AAAAAAAAACBCAAAA','TN',53,35.46,0.00,21.98 -'AAAAAAAAACBEAAAA','TN',49,113.20,1902.14,75.14 -'AAAAAAAAACCAAAAA','TN',60,111.87,0.00,74.95 -'AAAAAAAAACCEAAAA','TN',89,73.99,0.00,4.43 -'AAAAAAAAACDAAAAA','TN',35,86.57,0.00,22.63 -'AAAAAAAAACDCAAAA','TN',30,68.03,0.00,55.59 -'AAAAAAAAACDDAAAA','TN',67.66666666666667,21.32,0.00,7.83 -'AAAAAAAAACEBAAAA','TN',69.5,81.94,0.00,29.79 -'AAAAAAAAACECAAAA','TN',89,47.10,0.00,20.72 -'AAAAAAAAACFBAAAA','TN',70,14.60,0.00,8.46 -'AAAAAAAAACFDAAAA','TN',53,106.16,0.00,91.29 -'AAAAAAAAACFEAAAA','TN',40.33333333333334,103.41,0.00,18.00 -'AAAAAAAAACGAAAAA','TN',41,10.00,0.00,6.70 -'AAAAAAAAACGDAAAA','TN',4,6.32,0.00,3.72 -'AAAAAAAAACHBAAAA','TN',51,67.85,1521.28,54.99 -'AAAAAAAAACHCAAAA','TN',4,101.18,0.00,97.13 -'AAAAAAAAACKBAAAA','TN',96,123.44,0.00,122.20 -'AAAAAAAAACKCAAAA','TN',44,48.71,0.00,23.86 -'AAAAAAAAACLDAAAA','TN',50,130.80,0.00,13.08 -'AAAAAAAAACMDAAAA','TN',33,91.53,0.00,71.39 -'AAAAAAAAACNCAAAA','TN',27,69.84,0.00,27.93 -'AAAAAAAAACODAAAA','TN',54,57.73,0.00,7.78 -'AAAAAAAAACPAAAAA','TN',36,103.03,0.00,97.87 -'AAAAAAAAADABAAAA','TN',20,194.76,0.00,5.84 -'AAAAAAAAADAEAAAA','TN',53,65.66,0.00,53.84 -'AAAAAAAAADBAAAAA','TN',48,87.49,0.00,74.79 -'AAAAAAAAADBBAAAA','TN',88,48.86,0.00,9.77 -'AAAAAAAAADBDAAAA','TN',41,27.62,380.27,13.25 -'AAAAAAAAADBEAAAA','TN',33.5,112.42,0.00,55.36 -'AAAAAAAAADCAAAAA','TN',59,5.43,0.00,4.88 -'AAAAAAAAADDBAAAA','TN',25,149.67,0.00,125.72 -'AAAAAAAAADDEAAAA','TN',54,11.80,0.00,8.26 -'AAAAAAAAADEAAAAA','TN',23,132.68,0.00,30.51 -'AAAAAAAAADEDAAAA','TN',99,48.89,0.00,48.89 -'AAAAAAAAADFAAAAA','TN',67.5,45.22,0.00,19.09 -'AAAAAAAAADFCAAAA','TN',77,101.79,0.00,39.69 -'AAAAAAAAADFDAAAA','TN',94,79.82,414.86,63.05 -'AAAAAAAAADGCAAAA','TN',19,14.27,0.00,5.56 -'AAAAAAAAADGEAAAA','TN',75.66666666666667,123.31,413.13,51.38 -'AAAAAAAAADHBAAAA','TN',15,179.13,496.44,39.40 -'AAAAAAAAADHDAAAA','TN',98,39.30,0.00,33.79 -'AAAAAAAAADIAAAAA','TN',27,124.51,0.00,107.07 ----- TYPES -STRING, STRING, DOUBLE, DECIMAL, 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-q27a.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q27a.test b/testdata/workloads/tpcds/queries/tpcds-q27a.test deleted file mode 100644 index eb510f1..0000000 --- a/testdata/workloads/tpcds/queries/tpcds-q27a.test +++ /dev/null @@ -1,163 +0,0 @@ -==== ----- QUERY: TPCDS-Q27a -with results as - (select i_item_id, - s_state, - 0 as g_state, - ss_quantity agg1, - ss_list_price agg2, - ss_coupon_amt agg3, - ss_sales_price agg4 - from store_sales, - customer_demographics, - date_dim, - store, - item - where ss_sold_date_sk = d_date_sk and - ss_item_sk = i_item_sk and - ss_store_sk = s_store_sk and - ss_cdemo_sk = cd_demo_sk and - cd_gender = 'M' and - cd_marital_status = 'S' and - cd_education_status = 'College' and - d_year = 2002 and - s_state in ('TN','TN', 'TN', 'TN', 'TN', 'TN')) -select i_item_id, - s_state, - g_state, - agg1, - agg2, - agg3, - agg4 -from (select i_item_id, - s_state, - 0 as g_state, - avg(agg1) agg1, - avg(agg2) agg2, - avg(agg3) agg3, - avg(agg4) agg4 from results - group by i_item_id, - s_state - union all - select i_item_id, - NULL AS s_state, - 1 AS g_state, - avg(agg1) agg1, - avg(agg2) agg2, - avg(agg3) agg3, - avg(agg4) agg4 from results - group by i_item_id - union all - select NULL AS i_item_id, - NULL as s_state, - 1 as g_state, - avg(agg1) agg1, - avg(agg2) agg2, - avg(agg3) agg3, - avg(agg4) agg4 from results) foo -order by i_item_id, s_state -limit 100; ----- RESULTS -'AAAAAAAAAAABAAAA','TN',0,46,114.72,0.00,32.12 -'AAAAAAAAAAABAAAA','NULL',1,46,114.72,0.00,32.12 -'AAAAAAAAAAAEAAAA','TN',0,100,77.97,0.00,57.69 -'AAAAAAAAAAAEAAAA','NULL',1,100,77.97,0.00,57.69 -'AAAAAAAAAABAAAAA','TN',0,6,48.89,13.61,2.44 -'AAAAAAAAAABAAAAA','NULL',1,6,48.89,13.61,2.44 -'AAAAAAAAAACAAAAA','TN',0,62,32.21,0.00,5.50 -'AAAAAAAAAACAAAAA','NULL',1,62,32.21,0.00,5.50 -'AAAAAAAAAACDAAAA','TN',0,97,161.78,0.00,55.00 -'AAAAAAAAAACDAAAA','NULL',1,97,161.78,0.00,55.00 -'AAAAAAAAAADBAAAA','TN',0,28,136.01,575.76,73.44 -'AAAAAAAAAADBAAAA','NULL',1,28,136.01,575.76,73.44 -'AAAAAAAAAADCAAAA','TN',0,64,116.14,0.00,108.01 -'AAAAAAAAAADCAAAA','NULL',1,64,116.14,0.00,108.01 -'AAAAAAAAAAEBAAAA','TN',0,9,5.43,0.00,5.21 -'AAAAAAAAAAEBAAAA','NULL',1,9,5.43,0.00,5.21 -'AAAAAAAAAAEDAAAA','TN',0,45.5,87.63,0.00,51.84 -'AAAAAAAAAAEDAAAA','NULL',1,45.5,87.63,0.00,51.84 -'AAAAAAAAAAGBAAAA','TN',0,29,101.94,0.00,100.92 -'AAAAAAAAAAGBAAAA','NULL',1,29,101.94,0.00,100.92 -'AAAAAAAAAAGCAAAA','TN',0,97,65.27,0.00,21.53 -'AAAAAAAAAAGCAAAA','NULL',1,97,65.27,0.00,21.53 -'AAAAAAAAAAGEAAAA','TN',0,55,73.87,0.00,71.00 -'AAAAAAAAAAGEAAAA','NULL',1,55,73.87,0.00,71.00 -'AAAAAAAAAAHAAAAA','TN',0,20,134.30,0.00,119.52 -'AAAAAAAAAAHAAAAA','NULL',1,20,134.30,0.00,119.52 -'AAAAAAAAAAHBAAAA','TN',0,97,7.40,0.00,6.73 -'AAAAAAAAAAHBAAAA','NULL',1,97,7.40,0.00,6.73 -'AAAAAAAAAAHDAAAA','TN',0,69,5.62,0.00,0.33 -'AAAAAAAAAAHDAAAA','NULL',1,69,5.62,0.00,0.33 -'AAAAAAAAAAIAAAAA','TN',0,87,106.98,0.00,27.81 -'AAAAAAAAAAIAAAAA','NULL',1,87,106.98,0.00,27.81 -'AAAAAAAAAAKBAAAA','TN',0,84.5,66.17,847.83,57.38 -'AAAAAAAAAAKBAAAA','NULL',1,84.5,66.17,847.83,57.38 -'AAAAAAAAAALAAAAA','TN',0,6,32.28,0.00,7.42 -'AAAAAAAAAALAAAAA','NULL',1,6,32.28,0.00,7.42 -'AAAAAAAAAALCAAAA','TN',0,98,34.31,55.30,1.71 -'AAAAAAAAAALCAAAA','NULL',1,98,34.31,55.30,1.71 -'AAAAAAAAAALDAAAA','TN',0,59,129.47,713.90,22.00 -'AAAAAAAAAALDAAAA','NULL',1,59,129.47,713.90,22.00 -'AAAAAAAAAANAAAAA','TN',0,74,60.87,0.00,13.20 -'AAAAAAAAAANAAAAA','NULL',1,74,60.87,0.00,13.20 -'AAAAAAAAAANBAAAA','TN',0,96,130.79,1864.51,129.48 -'AAAAAAAAAANBAAAA','NULL',1,96,130.79,1864.51,129.48 -'AAAAAAAAAAOAAAAA','TN',0,85,130.34,0.00,122.51 -'AAAAAAAAAAOAAAAA','NULL',1,85,130.34,0.00,122.51 -'AAAAAAAAAAOCAAAA','TN',0,14,64.57,0.00,33.97 -'AAAAAAAAAAOCAAAA','NULL',1,14,64.57,0.00,33.97 -'AAAAAAAAAAODAAAA','TN',0,84,30.50,0.00,28.97 -'AAAAAAAAAAODAAAA','NULL',1,84,30.50,0.00,28.97 -'AAAAAAAAAAPBAAAA','TN',0,45,118.89,2380.59,84.71 -'AAAAAAAAAAPBAAAA','NULL',1,45,118.89,2380.59,84.71 -'AAAAAAAAABAAAAAA','TN',0,97,124.75,0.00,74.85 -'AAAAAAAAABAAAAAA','NULL',1,97,124.75,0.00,74.85 -'AAAAAAAAABABAAAA','TN',0,31,29.98,17.09,27.58 -'AAAAAAAAABABAAAA','NULL',1,31,29.98,17.09,27.58 -'AAAAAAAAABAEAAAA','TN',0,34,17.21,0.00,13.42 -'AAAAAAAAABAEAAAA','NULL',1,34,17.21,0.00,13.42 -'AAAAAAAAABCBAAAA','TN',0,38,131.37,0.00,52.32 -'AAAAAAAAABCBAAAA','NULL',1,38,131.37,0.00,52.32 -'AAAAAAAAABDAAAAA','TN',0,31.5,68.34,0.00,63.27 -'AAAAAAAAABDAAAAA','NULL',1,31.5,68.34,0.00,63.27 -'AAAAAAAAABDBAAAA','TN',0,56,103.85,1624.17,59.19 -'AAAAAAAAABDBAAAA','NULL',1,56,103.85,1624.17,59.19 -'AAAAAAAAABDDAAAA','TN',0,67.33333333333333,119.42,23.18,38.72 -'AAAAAAAAABDDAAAA','NULL',1,67.33333333333333,119.42,23.18,38.72 -'AAAAAAAAABDEAAAA','TN',0,61,85.95,2282.36,44.60 -'AAAAAAAAABDEAAAA','NULL',1,61,85.95,2282.36,44.60 -'AAAAAAAAABEDAAAA','TN',0,61,85.14,0.00,50.23 -'AAAAAAAAABEDAAAA','NULL',1,61,85.14,0.00,50.23 -'AAAAAAAAABFBAAAA','TN',0,31,23.55,0.00,13.65 -'AAAAAAAAABFBAAAA','NULL',1,31,23.55,0.00,13.65 -'AAAAAAAAABFCAAAA','TN',0,83,15.50,0.00,0.00 -'AAAAAAAAABFCAAAA','NULL',1,83,15.50,0.00,0.00 -'AAAAAAAAABGAAAAA','TN',0,64,35.08,0.00,3.15 -'AAAAAAAAABGAAAAA','NULL',1,64,35.08,0.00,3.15 -'AAAAAAAAABGBAAAA','TN',0,40,25.07,0.00,16.04 -'AAAAAAAAABGBAAAA','NULL',1,40,25.07,0.00,16.04 -'AAAAAAAAABGEAAAA','TN',0,39.5,56.76,14.25,36.00 -'AAAAAAAAABGEAAAA','NULL',1,39.5,56.76,14.25,36.00 -'AAAAAAAAABHAAAAA','TN',0,93,74.80,0.00,21.69 -'AAAAAAAAABHAAAAA','NULL',1,93,74.80,0.00,21.69 -'AAAAAAAAABHCAAAA','TN',0,31.5,79.83,181.38,61.93 -'AAAAAAAAABHCAAAA','NULL',1,31.5,79.83,181.38,61.93 -'AAAAAAAAABIBAAAA','TN',0,48,170.89,0.00,140.12 -'AAAAAAAAABIBAAAA','NULL',1,48,170.89,0.00,140.12 -'AAAAAAAAABJAAAAA','TN',0,46.5,82.32,0.00,44.98 -'AAAAAAAAABJAAAAA','NULL',1,46.5,82.32,0.00,44.98 -'AAAAAAAAABJBAAAA','TN',0,76,12.40,0.00,3.96 -'AAAAAAAAABJBAAAA','NULL',1,76,12.40,0.00,3.96 -'AAAAAAAAABJDAAAA','TN',0,34,118.50,0.00,61.20 -'AAAAAAAAABJDAAAA','NULL',1,34,118.50,0.00,61.20 -'AAAAAAAAABKAAAAA','TN',0,65,8.77,0.00,5.18 -'AAAAAAAAABKAAAAA','NULL',1,65,8.77,0.00,5.18 -'AAAAAAAAABKCAAAA','TN',0,26,51.57,0.00,41.25 -'AAAAAAAAABKCAAAA','NULL',1,26,51.57,0.00,41.25 -'AAAAAAAAABLBAAAA','TN',0,61.5,149.12,0.00,82.54 -'AAAAAAAAABLBAAAA','NULL',1,61.5,149.12,0.00,82.54 -'AAAAAAAAABMAAAAA','TN',0,14,109.97,0.00,10.99 -'AAAAAAAAABMAAAAA','NULL',1,14,109.97,0.00,10.99 ----- TYPES -STRING,STRING,TINYINT,DOUBLE,DECIMAL,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-q28.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q28.test b/testdata/workloads/tpcds/queries/tpcds-q28.test deleted file mode 100644 index b19dad1..0000000 --- a/testdata/workloads/tpcds/queries/tpcds-q28.test +++ /dev/null @@ -1,57 +0,0 @@ -==== ----- QUERY: TPCDS-Q28 -select * -from (select avg(ss_list_price) B1_LP, - count(ss_list_price) B1_CNT, - count(distinct ss_list_price) B1_CNTD - from store_sales - where ss_quantity between 0 and 5 - and (ss_list_price between 8 and 8+10 - or ss_coupon_amt between 459 and 459+1000 - or ss_wholesale_cost between 57 and 57+20)) B1, - (select avg(ss_list_price) B2_LP, - count(ss_list_price) B2_CNT, - count(distinct ss_list_price) B2_CNTD - from store_sales - where ss_quantity between 6 and 10 - and (ss_list_price between 90 and 90+10 - or ss_coupon_amt between 2323 and 2323+1000 - or ss_wholesale_cost between 31 and 31+20)) B2, - (select avg(ss_list_price) B3_LP, - count(ss_list_price) B3_CNT, - count(distinct ss_list_price) B3_CNTD - from store_sales - where ss_quantity between 11 and 15 - and (ss_list_price between 142 and 142+10 - or ss_coupon_amt between 12214 and 12214+1000 - or ss_wholesale_cost between 79 and 79+20)) B3, - (select avg(ss_list_price) B4_LP, - count(ss_list_price) B4_CNT, - count(distinct ss_list_price) B4_CNTD - from store_sales - where ss_quantity between 16 and 20 - and (ss_list_price between 135 and 135+10 - or ss_coupon_amt between 6071 and 6071+1000 - or ss_wholesale_cost between 38 and 38+20)) B4, - (select avg(ss_list_price) B5_LP, - count(ss_list_price) B5_CNT, - count(distinct ss_list_price) B5_CNTD - from store_sales - where ss_quantity between 21 and 25 - and (ss_list_price between 122 and 122+10 - or ss_coupon_amt between 836 and 836+1000 - or ss_wholesale_cost between 17 and 17+20)) B5, - (select avg(ss_list_price) B6_LP, - count(ss_list_price) B6_CNT, - count(distinct ss_list_price) B6_CNTD - from store_sales - where ss_quantity between 26 and 30 - and (ss_list_price between 154 and 154+10 - or ss_coupon_amt between 7326 and 7326+1000 - or ss_wholesale_cost between 7 and 7+20)) B6 -limit 100; ----- RESULTS -77.71,36142,9210,69.50,35250,6592,134.01,27875,9741,82.55,31422,7678,61.82,36100,8664,39.27,29776,5210 ----- TYPES -DECIMAL,BIGINT,BIGINT,DECIMAL,BIGINT,BIGINT,DECIMAL,BIGINT,BIGINT,DECIMAL,BIGINT,BIGINT,DECIMAL,BIGINT,BIGINT,DECIMAL,BIGINT,BIGINT -==== \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q29.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q29.test b/testdata/workloads/tpcds/queries/tpcds-q29.test new file mode 100644 index 0000000..dca443e --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q29.test @@ -0,0 +1,51 @@ +==== +---- QUERY: TPCDS-Q29 +select + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + ,sum(ss_quantity) as store_sales_quantity + ,sum(sr_return_quantity) as store_returns_quantity + ,sum(cs_quantity) as catalog_sales_quantity + from + store_sales + ,store_returns + ,catalog_sales + ,date_dim d1 + ,date_dim d2 + ,date_dim d3 + ,store + ,item + where + d1.d_moy = 9 + and d1.d_year = 1999 + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and ss_customer_sk = sr_customer_sk + and ss_item_sk = sr_item_sk + and ss_ticket_number = sr_ticket_number + and sr_returned_date_sk = d2.d_date_sk + and d2.d_moy between 9 and 9 + 3 + and d2.d_year = 1999 + and sr_customer_sk = cs_bill_customer_sk + and sr_item_sk = cs_item_sk + and cs_sold_date_sk = d3.d_date_sk + and d3.d_year in (1999,1999+1,1999+2) + group by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + order by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + limit 100; +---- RESULTS +'AAAAAAAABOKDAAAA','Immediate women sell. Slowly sure measures might ensure in order from a duties. Privately aware damages may compensate so for example previous letters','AAAAAAAABAAAAAAA','ought',99,81,22 +---- TYPES +STRING, STRING, STRING, STRING, BIGINT, BIGINT, BIGINT +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q32.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q32.test b/testdata/workloads/tpcds/queries/tpcds-q32.test new file mode 100644 index 0000000..cf1ad03 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q32.test @@ -0,0 +1,32 @@ +==== +---- QUERY: TPCDS-Q32 +select sum(cs_ext_discount_amt) as "excess discount amount" +from + catalog_sales + ,item + ,date_dim +where +i_manufact_id = 977 +and i_item_sk = cs_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 = cs_sold_date_sk +and cs_ext_discount_amt + > ( + select + 1.3 * avg(cs_ext_discount_amt) + from + catalog_sales + ,date_dim + where + cs_item_sk = i_item_sk + and 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 = cs_sold_date_sk + ) +limit 100; +---- RESULTS +28038.14 +---- TYPES +DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q33.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q33.test b/testdata/workloads/tpcds/queries/tpcds-q33.test new file mode 100644 index 0000000..d3f818b --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q33.test @@ -0,0 +1,178 @@ +==== +---- QUERY: TPCDS-Q33 +with ss as ( + select + i_manufact_id,sum(ss_ext_sales_price) total_sales + from + store_sales, + date_dim, + customer_address, + item + where + i_manufact_id in (select + i_manufact_id +from + item +where i_category in ('Electronics')) + and ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and d_year = 1998 + and d_moy = 5 + and ss_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_manufact_id), + cs as ( + select + i_manufact_id,sum(cs_ext_sales_price) total_sales + from + catalog_sales, + date_dim, + customer_address, + item + where + i_manufact_id in (select + i_manufact_id +from + item +where i_category in ('Electronics')) + and cs_item_sk = i_item_sk + and cs_sold_date_sk = d_date_sk + and d_year = 1998 + and d_moy = 5 + and cs_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_manufact_id), + ws as ( + select + i_manufact_id,sum(ws_ext_sales_price) total_sales + from + web_sales, + date_dim, + customer_address, + item + where + i_manufact_id in (select + i_manufact_id +from + item +where i_category in ('Electronics')) + and ws_item_sk = i_item_sk + and ws_sold_date_sk = d_date_sk + and d_year = 1998 + and d_moy = 5 + and ws_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_manufact_id) + select i_manufact_id ,sum(total_sales) total_sales + from (select * from ss + union all + select * from cs + union all + select * from ws) tmp1 + group by i_manufact_id + order by total_sales +limit 100; +---- RESULTS +698,214.34 +929,539.60 +464,641.96 +938,858.22 +697,959.48 +682,1074.64 +741,1195.96 +812,2069.00 +838,2256.80 +910,2291.67 +899,2355.04 +990,2370.68 +963,2380.92 +832,2684.32 +692,2773.42 +738,2945.77 +564,3210.59 +770,3379.72 +821,3446.02 +797,3485.44 +974,3527.17 +704,3574.75 +853,3850.44 +898,3854.56 +937,3968.27 +737,4284.65 +942,4618.19 +798,5017.15 +849,5099.82 +939,5122.92 +947,5138.04 +774,5138.38 +668,5666.30 +607,5770.19 +358,5955.66 +860,5957.46 +951,5973.81 +472,6060.92 +824,6198.72 +766,6237.09 +783,6341.40 +799,6360.64 +794,6474.90 +529,6476.80 +928,6536.81 +946,6817.54 +795,6859.76 +793,6871.33 +423,6878.60 +811,6887.18 +887,6998.14 +961,7098.23 +806,7143.83 +772,7196.00 +880,7202.52 +709,7247.76 +696,7732.80 +710,7783.45 +784,7894.58 +780,7973.56 +476,8166.70 +839,8299.55 +828,8398.05 +968,8461.36 +966,8715.08 +872,8722.84 +432,8779.74 +842,8879.82 +981,8899.15 +567,9226.08 +773,9431.83 +430,9891.62 +445,9913.18 +678,9976.96 +977,10067.34 +705,10202.62 +402,10322.05 +789,10376.28 +733,10653.42 +936,10760.28 +920,10922.38 +658,10926.50 +1000,10992.09 +651,11129.87 +965,11158.41 +844,11348.42 +954,11383.41 +655,11632.71 +99,11785.13 +493,11859.91 +723,12068.58 +623,12388.39 +874,12585.39 +810,12598.51 +299,12649.43 +826,12657.78 +870,12927.40 +385,12992.19 +590,13381.82 +718,13768.98 +---- TYPES +INT, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q37.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q37.test b/testdata/workloads/tpcds/queries/tpcds-q37.test new file mode 100644 index 0000000..5a3c630 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q37.test @@ -0,0 +1,22 @@ +==== +---- QUERY: TPCDS-Q37 +-- start query 1 in stream 0 using template query37.tpl +select i_item_id + ,i_item_desc + ,i_current_price + from item, inventory, date_dim, catalog_sales + where i_current_price between 68 and 68 + 30 + and inv_item_sk = i_item_sk + and d_date_sk=inv_date_sk + and cast(d_date as timestamp) between cast('2000-02-01' as timestamp) and (cast('2000-02-01' as timestamp) + interval 60 days) + and i_manufact_id in (677,940,694,808) + and inv_quantity_on_hand between 100 and 500 + and cs_item_sk = i_item_sk + group by i_item_id,i_item_desc,i_current_price + order by i_item_id + limit 100; +---- RESULTS +'AAAAAAAAHFMBAAAA','Jobs crack at all public countries. Possible, traditional initiatives will make british problems. Fiscal years call extremely just growing pupils. Soon dutch voices',86.11 +---- TYPES +STRING, STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q39-1.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q39-1.test b/testdata/workloads/tpcds/queries/tpcds-q39-1.test new file mode 100644 index 0000000..117440e --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q39-1.test @@ -0,0 +1,278 @@ +==== +---- QUERY: TPCDS-Q39.1 +-- RESULT MISMATCH FROM ORIGINAL +-- ADD ROUND()s TO 4th, 5th, 9th, 10th COLUMNS, TAKE ACTUAL RESULTS AS EXPECTED. +with inv as +(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy + ,stdev,mean, case mean when 0 then null else stdev/mean end cov + from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy + ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean + from inventory + ,item + ,warehouse + ,date_dim + where inv_item_sk = i_item_sk + and inv_warehouse_sk = w_warehouse_sk + and inv_date_sk = d_date_sk + and d_year =2001 + group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo + where case mean when 0 then 0 else stdev/mean end > 1) +select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy + ,round(inv1.mean, 2), round(inv1.cov, 8) + ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy + ,round(inv2.mean, 2), round(inv2.cov, 8) +from inv inv1,inv inv2 +where inv1.i_item_sk = inv2.i_item_sk + and inv1.w_warehouse_sk = inv2.w_warehouse_sk + and inv1.d_moy=1 + and inv2.d_moy=1+1 +order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov + ,inv2.d_moy,inv2.mean, inv2.cov +; +---- RESULTS +1,265,1,324.75,1.24383918,1,265,2,329.00,1.01515813 +1,363,1,499.50,1.03194157,1,363,2,321.00,1.14117668 +1,679,1,373.75,1.09554981,1,679,2,417.50,1.04297099 +1,695,1,450.75,1.08358883,1,695,2,368.75,1.13564941 +1,789,1,357.25,1.03450938,1,789,2,410.00,1.02842219 +1,815,1,216.50,1.17022709,1,815,2,150.50,1.30572815 +1,827,1,271.75,1.10468901,1,827,2,424.75,1.16531986 +1,1041,1,382.50,1.28480840,1,1041,2,424.75,1.00057727 +1,1569,1,212.00,1.63021352,1,1569,2,239.25,1.26415133 +1,1623,1,338.25,1.12854833,1,1623,2,261.33,1.27178090 +1,2581,1,448.50,1.06042904,1,2581,2,476.25,1.03629847 +1,2705,1,246.25,1.01203084,1,2705,2,294.67,1.07421341 +1,3131,1,393.75,1.00376140,1,3131,2,480.50,1.06691450 +1,3291,1,374.50,1.19518983,1,3291,2,265.25,1.57297211 +1,3687,1,279.75,1.42609091,1,3687,2,157.25,1.45343409 +1,4955,1,495.25,1.03182962,1,4955,2,322.50,1.16938423 +1,5627,1,282.75,1.56570324,1,5627,2,297.50,1.20842868 +1,7017,1,175.50,1.04274542,1,7017,2,321.33,1.01833569 +1,7317,1,366.33,1.02546640,1,7317,2,378.00,1.21725132 +1,7569,1,430.50,1.08743969,1,7569,2,360.25,1.04700556 +1,7999,1,166.25,1.79242317,1,7999,2,375.33,1.00809226 +1,8319,1,306.75,1.16153780,1,8319,2,276.00,1.14209964 +1,8443,1,327.75,1.25671837,1,8443,2,332.50,1.00441673 +1,8583,1,319.50,1.02410889,1,8583,2,310.25,1.23588138 +1,8591,1,398.00,1.14781687,1,8591,2,355.75,1.00244721 +1,8611,1,300.50,1.51915452,1,8611,2,243.75,1.23421228 +1,9081,1,367.00,1.08789321,1,9081,2,435.00,1.03305308 +1,9357,1,351.67,1.19029226,1,9357,2,427.00,1.04385830 +1,9449,1,406.25,1.01831831,1,9449,2,175.00,1.05447798 +1,9713,1,242.50,1.10350444,1,9713,2,393.00,1.20847461 +1,9809,1,479.00,1.01896025,1,9809,2,317.50,1.06141421 +1,9993,1,417.75,1.00998327,1,9993,2,204.50,1.55287075 +1,10127,1,239.75,1.05617706,1,10127,2,359.25,1.18579804 +1,11159,1,407.25,1.07855072,1,11159,2,250.00,1.33475791 +1,11277,1,211.25,1.26158583,1,11277,2,330.75,1.08087680 +1,11937,1,344.50,1.08580403,1,11937,2,200.33,1.06385271 +1,12373,1,387.75,1.10149048,1,12373,2,306.00,1.07617444 +1,12471,1,365.25,1.06075702,1,12471,2,327.25,1.05475606 +1,12625,1,279.00,1.30165605,1,12625,2,443.25,1.06049588 +1,12751,1,280.75,1.10833058,1,12751,2,369.33,1.34165044 +1,12779,1,331.00,1.04169021,1,12779,2,359.00,1.02897806 +1,13077,1,367.67,1.34552390,1,13077,2,358.67,1.51324291 +1,13191,1,260.25,1.06356963,1,13191,2,405.00,1.01979992 +1,13561,1,335.25,1.26096170,1,13561,2,240.00,1.05136045 +1,13935,1,311.75,1.03992897,1,13935,2,275.00,1.03675272 +1,14687,1,358.00,1.43693569,1,14687,2,187.00,1.54936315 +1,14719,1,209.00,1.04115096,1,14719,2,489.00,1.37661688 +1,15345,1,148.50,1.52957840,1,15345,2,246.50,1.50879877 +1,15427,1,482.75,1.01242389,1,15427,2,333.25,1.27247701 +1,15647,1,201.67,1.28579319,1,15647,2,249.25,1.36481730 +1,16079,1,280.50,1.24447574,1,16079,2,361.25,1.07378057 +1,16223,1,245.67,1.06011490,1,16223,2,412.67,1.06712311 +1,16289,1,171.00,1.08726331,1,16289,2,316.50,1.36953067 +1,16451,1,300.00,1.25165076,1,16451,2,467.50,1.29623639 +1,17051,1,349.75,1.13464238,1,17051,2,319.75,1.03793549 +2,71,1,221.50,1.56397411,2,71,2,309.00,1.49170579 +2,833,1,363.33,1.04228138,2,833,2,158.75,1.21308917 +2,969,1,282.00,1.13743403,2,969,2,386.00,1.13710012 +2,1401,1,249.00,1.32062377,2,1401,2,296.67,1.28681219 +2,1681,1,337.75,1.05215660,2,1681,2,252.75,1.03179107 +2,1991,1,356.00,1.13367900,2,1991,2,273.25,1.33241964 +2,2031,1,407.50,1.02098768,2,2031,2,418.75,1.00533886 +2,2481,1,319.75,1.11602666,2,2481,2,378.33,1.05971853 +2,2725,1,349.75,1.11487049,2,2725,2,265.67,1.54629721 +2,3073,1,385.00,1.10208458,2,3073,2,231.75,1.08849891 +2,3769,1,334.00,1.19542966,2,3769,2,335.00,1.04946496 +2,4085,1,327.75,1.10054139,2,4085,2,281.75,1.01876081 +2,4995,1,282.25,1.26041833,2,4995,2,248.00,1.25482290 +2,5099,1,418.67,1.06809009,2,5099,2,428.67,1.03523728 +2,5211,1,157.00,1.06955174,2,5211,2,270.25,1.19402008 +2,5769,1,341.25,1.27231327,2,5769,2,267.00,1.18157918 +2,6103,1,194.33,1.51606702,2,6103,2,158.50,1.27436986 +2,6243,1,168.50,1.07321081,2,6243,2,386.75,1.08329026 +2,6489,1,268.00,1.69563724,2,6489,2,389.00,1.41057805 +2,6585,1,183.75,1.11519376,2,6585,2,353.75,1.09397071 +2,6767,1,341.00,1.09316398,2,6767,2,235.00,1.13995439 +2,7411,1,460.00,1.06282933,2,7411,2,297.50,1.00057654 +2,8489,1,126.75,1.34164630,2,8489,2,398.25,1.00411248 +2,8633,1,218.50,1.46451897,2,8633,2,230.25,1.31169630 +2,8807,1,417.25,1.02581129,2,8807,2,356.25,1.10797306 +2,8883,1,249.25,1.11122718,2,8883,2,189.25,1.01007549 +2,9253,1,413.33,1.16066599,2,9253,2,465.00,1.02424458 +2,9467,1,108.25,1.09112239,2,9467,2,107.00,1.03092204 +2,9561,1,190.50,1.03030325,2,9561,2,271.00,1.20583495 +2,9575,1,452.50,1.04944922,2,9575,2,155.75,1.15340567 +2,9903,1,322.75,1.07610801,2,9903,2,387.33,1.26124068 +2,10535,1,324.33,1.05711040,2,10535,2,310.25,1.00406350 +2,10955,1,356.00,1.15828133,2,10955,2,339.00,1.07025321 +2,12089,1,212.00,1.08275011,2,12089,2,173.33,1.07398122 +2,12633,1,402.75,1.00713621,2,12633,2,367.00,1.12705356 +2,12765,1,308.00,1.11884769,2,12765,2,347.00,1.15717446 +2,13077,1,277.00,1.23233608,2,13077,2,223.50,1.13940034 +2,13687,1,361.25,1.01073417,2,13687,2,468.25,1.00984292 +2,14025,1,371.75,1.01501361,2,14025,2,141.00,1.25431703 +2,14117,1,298.50,1.02512380,2,14117,2,220.25,1.41526845 +2,14347,1,366.00,1.03278929,2,14347,2,369.75,1.01857399 +2,14697,1,397.75,1.00812054,2,14697,2,328.75,1.16084956 +2,14987,1,146.75,1.02900406,2,14987,2,436.50,1.04476815 +2,15321,1,527.00,1.21026626,2,15321,2,298.75,1.13090984 +2,15347,1,243.00,1.22262398,2,15347,2,310.75,1.20365849 +2,15533,1,223.00,1.09651113,2,15533,2,330.00,1.49674885 +2,15839,1,353.00,1.50636844,2,15839,2,255.50,1.23623932 +2,16107,1,376.00,1.01332463,2,16107,2,364.75,1.06886780 +2,16749,1,200.00,1.09447095,2,16749,2,438.25,1.03606466 +2,17329,1,321.33,1.30932795,2,17329,2,490.25,1.07565806 +2,17417,1,321.75,1.13067648,2,17417,2,534.00,1.00107252 +3,1049,1,252.50,1.10906411,3,1049,2,457.25,1.02398412 +3,1415,1,247.50,1.13216178,3,1415,2,369.75,1.09546796 +3,2199,1,185.75,1.20281256,3,2199,2,300.75,1.07678451 +3,2403,1,270.75,1.32587057,3,2403,2,384.00,1.09039872 +3,2541,1,364.75,1.07921353,3,2541,2,550.50,1.05455889 +3,2727,1,303.75,1.03016470,3,2727,2,219.25,1.38220918 +3,3207,1,383.75,1.08545880,3,3207,2,258.25,1.21133925 +3,3373,1,404.50,1.01433741,3,3373,2,171.25,1.28566540 +3,3969,1,234.25,1.04228318,3,3969,2,217.25,1.42154907 +3,4407,1,438.50,1.06566635,3,4407,2,330.75,1.02444040 +3,6051,1,289.75,1.26388809,3,6051,2,439.00,1.10457692 +3,6521,1,381.00,1.00156829,3,6521,2,437.75,1.03651493 +3,6691,1,377.00,1.10907373,3,6691,2,380.50,1.05813013 +3,6757,1,282.50,1.08950094,3,6757,2,345.25,1.22638287 +3,6915,1,420.67,1.04683456,3,6915,2,292.50,1.25061711 +3,7207,1,329.67,1.59544822,3,7207,2,414.50,1.01791971 +3,7889,1,318.50,1.28790300,3,7889,2,291.33,1.00429253 +3,8559,1,356.25,1.00651935,3,8559,2,393.50,1.01288317 +3,8829,1,364.25,1.07928523,3,8829,2,350.25,1.11339122 +3,9555,1,377.00,1.13084693,3,9555,2,444.75,1.01715882 +3,9623,1,331.33,1.15512893,3,9623,2,284.67,1.03037244 +3,9813,1,343.75,1.05169538,3,9813,2,384.67,1.08150059 +3,9881,1,351.50,1.17888206,3,9881,2,287.75,1.09093861 +3,10035,1,378.25,1.02163995,3,10035,2,364.25,1.15679227 +3,10509,1,420.25,1.05486269,3,10509,2,368.25,1.18206334 +3,10547,1,182.33,1.53256415,3,10547,2,320.25,1.30244184 +3,10743,1,233.25,1.27930224,3,10743,2,110.25,1.10720463 +3,11477,1,264.25,1.09535300,3,11477,2,278.50,1.03279098 +3,11959,1,358.00,1.06848353,3,11959,2,268.50,1.00898568 +3,12043,1,169.25,1.03309381,3,12043,2,377.25,1.01619625 +3,12157,1,326.33,1.00223187,3,12157,2,396.33,1.02878181 +3,12433,1,199.75,1.30725892,3,12433,2,478.50,1.01647421 +3,12867,1,278.25,1.64038001,3,12867,2,350.75,1.20069333 +3,13499,1,281.00,1.14319655,3,13499,2,446.75,1.03315203 +3,14589,1,266.50,1.11432439,3,14589,2,334.33,1.14531468 +3,15207,1,300.25,1.48446113,3,15207,2,564.00,1.00048087 +3,15395,1,258.25,1.05138769,3,15395,2,307.50,1.13822532 +3,15973,1,477.50,1.20393259,3,15973,2,286.00,1.28722942 +3,16233,1,260.67,1.12042355,3,16233,2,210.50,1.17881433 +3,16267,1,456.00,1.00422635,3,16267,2,364.33,1.48188554 +3,16605,1,394.25,1.11374136,3,16605,2,296.25,1.28858927 +3,16949,1,155.75,1.11861773,3,16949,2,229.50,1.02894692 +3,17319,1,364.75,1.27093644,3,17319,2,359.25,1.15787514 +4,947,1,247.50,1.69331818,4,947,2,203.33,1.20543315 +4,1895,1,354.75,1.23973415,4,1895,2,383.75,1.08930770 +4,3429,1,358.50,1.06655934,4,3429,2,346.00,1.25194893 +4,4559,1,215.00,1.24323299,4,4559,2,360.75,1.03335459 +4,4773,1,328.25,1.05098366,4,4773,2,330.50,1.00625806 +4,4885,1,151.00,1.32055704,4,4885,2,377.25,1.04890352 +4,4915,1,289.25,1.08639295,4,4915,2,368.00,1.06965855 +4,5043,1,476.00,1.01559643,4,5043,2,244.25,1.35628937 +4,5095,1,334.00,1.22588613,4,5095,2,390.25,1.06217881 +4,5193,1,263.33,1.25017095,4,5193,2,323.00,1.09796162 +4,5975,1,434.00,1.02336145,4,5975,2,359.75,1.19793569 +4,6251,1,285.00,1.12153803,4,6251,2,335.50,1.16021925 +4,6389,1,280.75,1.20492361,4,6389,2,259.33,1.48165506 +4,6487,1,452.75,1.07106266,4,6487,2,350.50,1.17164757 +4,6619,1,430.00,1.00072626,4,6619,2,226.25,1.54855745 +4,7191,1,260.75,1.00126668,4,7191,2,195.25,1.53726755 +4,7427,1,239.50,1.41835671,4,7427,2,373.25,1.04297920 +4,8453,1,314.75,1.02597149,4,8453,2,376.75,1.00990239 +4,8781,1,518.50,1.10055000,4,8781,2,521.00,1.12377047 +4,9571,1,314.00,1.21685782,4,9571,2,309.00,1.08921674 +4,9587,1,248.00,1.49818873,4,9587,2,248.00,1.00649878 +4,10333,1,321.00,1.14861071,4,10333,2,317.00,1.04692263 +4,10969,1,186.67,1.10442062,4,10969,2,307.00,1.10947641 +4,10993,1,446.00,1.00923763,4,10993,2,206.67,1.23553335 +4,11571,1,342.50,1.14262840,4,11571,2,304.67,1.03199432 +4,11979,1,225.33,1.46706562,4,11979,2,438.25,1.13899270 +4,13409,1,312.33,1.34275025,4,13409,2,336.25,1.10819783 +4,13443,1,417.75,1.02177961,4,13443,2,334.00,1.30764309 +4,13567,1,271.50,1.00876216,4,13567,2,370.00,1.45576861 +4,13683,1,318.75,1.01498277,4,13683,2,363.50,1.07811546 +4,14143,1,367.00,1.04417994,4,14143,2,290.00,1.19330608 +4,14401,1,342.75,1.18365590,4,14401,2,344.25,1.20670831 +4,14693,1,447.25,1.05166749,4,14693,2,498.00,1.35173826 +4,14877,1,414.33,1.17077239,4,14877,2,282.50,1.58305829 +4,15063,1,378.25,1.11281926,4,15063,2,444.00,1.06327777 +4,15275,1,335.50,1.15363829,4,15275,2,292.67,1.29783137 +4,15509,1,296.33,1.02467405,4,15509,2,317.67,1.44703646 +4,15969,1,423.00,1.04728755,4,15969,2,342.50,1.25117694 +4,16627,1,485.50,1.01514609,4,16627,2,161.25,1.35332527 +4,16641,1,521.50,1.10506621,4,16641,2,221.50,1.03085905 +4,16751,1,549.50,1.08221438,4,16751,2,223.33,1.33778976 +5,75,1,333.00,1.26563188,5,75,2,226.00,1.03580785 +5,125,1,441.67,1.09597146,5,125,2,331.50,1.00811483 +5,431,1,159.00,1.10617948,5,431,2,304.00,1.12242254 +5,671,1,403.33,1.00560141,5,671,2,281.33,1.33668352 +5,745,1,466.75,1.01090655,5,745,2,351.00,1.11472129 +5,1701,1,374.00,1.08644582,5,1701,2,385.00,1.33063087 +5,2521,1,360.75,1.09924823,5,2521,2,265.50,1.17240777 +5,2855,1,406.00,1.40376371,5,2855,2,183.25,1.06622499 +5,3137,1,271.25,1.57545322,5,3137,2,380.00,1.08342034 +5,3279,1,299.50,1.05366972,5,3279,2,276.75,1.44588781 +5,3467,1,294.00,1.14772946,5,3467,2,349.67,1.16742237 +5,4463,1,338.00,1.06993201,5,4463,2,273.50,1.23697973 +5,4719,1,340.50,1.05718578,5,4719,2,423.67,1.02550002 +5,4739,1,281.33,1.02429715,5,4739,2,383.50,1.03343374 +5,4839,1,310.25,1.03590344,5,4839,2,232.00,1.29541938 +5,4979,1,359.50,1.10464081,5,4979,2,323.75,1.17042510 +5,5401,1,219.25,1.27738702,5,5401,2,203.00,1.09156752 +5,5635,1,370.00,1.02270546,5,5635,2,351.67,1.37776645 +5,5797,1,403.00,1.02348823,5,5797,2,457.50,1.00370401 +5,5901,1,360.25,1.12596313,5,5901,2,376.00,1.00950384 +5,6129,1,419.25,1.01691802,5,6129,2,359.50,1.04788894 +5,6213,1,351.67,1.20562125,5,6213,2,361.50,1.11684081 +5,6479,1,301.33,1.27800104,5,6479,2,395.75,1.17797435 +5,7333,1,227.00,1.46113460,5,7333,2,243.75,1.17891625 +5,7923,1,358.00,1.10467709,5,7923,2,342.33,1.16077811 +5,8737,1,217.25,1.07421364,5,8737,2,403.50,1.04420805 +5,8945,1,344.25,1.14148377,5,8945,2,298.00,1.00083670 +5,8993,1,399.00,1.18737229,5,8993,2,371.75,1.13163538 +5,10667,1,288.00,1.34536402,5,10667,2,289.50,1.09522380 +5,10771,1,394.75,1.04806801,5,10771,2,323.25,1.23773488 +5,10949,1,383.00,1.11256774,5,10949,2,378.25,1.12554061 +5,11107,1,446.50,1.26534898,5,11107,2,258.50,1.55361256 +5,11795,1,395.50,1.01044732,5,11795,2,430.75,1.13106259 +5,12017,1,347.75,1.16823794,5,12017,2,308.00,1.15932280 +5,12027,1,456.25,1.02629401,5,12027,2,390.33,1.11427701 +5,13583,1,430.33,1.04238393,5,13583,2,256.25,1.54103483 +5,13651,1,272.33,1.16952312,5,13651,2,330.50,1.19018735 +5,13783,1,419.50,1.05846997,5,13783,2,339.50,1.06809095 +5,13859,1,318.00,1.14222390,5,13859,2,380.25,1.05644962 +5,14537,1,206.00,1.22834400,5,14537,2,357.75,1.03879827 +5,15309,1,276.33,1.29875906,5,15309,2,409.00,1.01024062 +5,15883,1,385.33,1.01247775,5,15883,2,173.25,1.35622382 +5,15935,1,293.50,1.01876594,5,15935,2,271.25,1.40009475 +5,15949,1,241.50,1.14866599,5,15949,2,235.00,1.40728879 +5,16037,1,327.25,1.13076173,5,16037,2,412.67,1.18302002 +5,16291,1,380.50,1.05495950,5,16291,2,411.67,1.03492140 +5,16459,1,370.00,1.27564991,5,16459,2,265.75,1.29881917 +5,16901,1,305.25,1.10430105,5,16901,2,186.50,1.02312693 +5,17199,1,376.50,1.10389698,5,17199,2,374.00,1.08730312 +5,17387,1,298.33,1.38487417,5,17387,2,327.67,1.36708758 +5,17717,1,283.75,1.15571566,5,17717,2,235.25,1.14162394 +5,17927,1,217.50,1.35499643,5,17927,2,250.75,1.07092131 +5,17943,1,209.75,1.37170713,5,17943,2,428.00,1.10907993 +---- TYPES +INT, BIGINT, INT, DOUBLE, DOUBLE, INT, BIGINT, INT, DOUBLE, DOUBLE +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q39-2.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q39-2.test b/testdata/workloads/tpcds/queries/tpcds-q39-2.test new file mode 100644 index 0000000..a7a666a --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q39-2.test @@ -0,0 +1,50 @@ +==== +---- QUERY: TPCDS-Q39.2 +-- RESULT MISMATCH FROM ORIGINAL +-- ADDED ROUND()s TO 4th, 5th, 9th, 10th COLUMNS, TAKE ACTUAL RESULTS AS EXPECTED. +with inv as +(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy + ,stdev,mean, case mean when 0 then null else stdev/mean end cov + from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy + ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean + from inventory + ,item + ,warehouse + ,date_dim + where inv_item_sk = i_item_sk + and inv_warehouse_sk = w_warehouse_sk + and inv_date_sk = d_date_sk + and d_year =2001 + group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo + where case mean when 0 then 0 else stdev/mean end > 1) +select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy + ,round(inv1.mean, 2), round(inv1.cov, 8) + ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy + ,round(inv2.mean, 2), round(inv2.cov, 8) +from inv inv1,inv inv2 +where inv1.i_item_sk = inv2.i_item_sk + and inv1.w_warehouse_sk = inv2.w_warehouse_sk + and inv1.d_moy=1 + and inv2.d_moy=1+1 + and inv1.cov > 1.5 +order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov + ,inv2.d_moy,inv2.mean, inv2.cov +; +---- RESULTS +1,1569,1,212.00,1.63021352,1,1569,2,239.25,1.26415133 +1,5627,1,282.75,1.56570324,1,5627,2,297.50,1.20842868 +1,7999,1,166.25,1.79242317,1,7999,2,375.33,1.00809226 +1,8611,1,300.50,1.51915452,1,8611,2,243.75,1.23421228 +1,15345,1,148.50,1.52957840,1,15345,2,246.50,1.50879877 +2,71,1,221.50,1.56397411,2,71,2,309.00,1.49170579 +2,6103,1,194.33,1.51606702,2,6103,2,158.50,1.27436986 +2,6489,1,268.00,1.69563724,2,6489,2,389.00,1.41057805 +2,15839,1,353.00,1.50636844,2,15839,2,255.50,1.23623932 +3,7207,1,329.67,1.59544822,3,7207,2,414.50,1.01791971 +3,10547,1,182.33,1.53256415,3,10547,2,320.25,1.30244184 +3,12867,1,278.25,1.64038001,3,12867,2,350.75,1.20069333 +4,947,1,247.50,1.69331818,4,947,2,203.33,1.20543315 +5,3137,1,271.25,1.57545322,5,3137,2,380.00,1.08342034 +---- TYPES +INT, BIGINT, INT, DOUBLE, DOUBLE, INT, BIGINT, INT, DOUBLE, DOUBLE +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q40.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q40.test b/testdata/workloads/tpcds/queries/tpcds-q40.test new file mode 100644 index 0000000..4d0f991 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q40.test @@ -0,0 +1,131 @@ +==== +---- QUERY: TPCDS-Q40 +select + w_state + ,i_item_id + ,sum(case when (cast(d_date as timestamp) < cast ('2000-03-11' as timestamp)) + then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before + ,sum(case when (cast(d_date as timestamp) >= cast ('2000-03-11' as timestamp)) + then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after + from + catalog_sales left outer join catalog_returns on + (cs_order_number = cr_order_number + and cs_item_sk = cr_item_sk) + ,warehouse + ,item + ,date_dim + where + i_current_price between 0.99 and 1.49 + and i_item_sk = cs_item_sk + and cs_warehouse_sk = w_warehouse_sk + and cs_sold_date_sk = d_date_sk + and cast(d_date as timestamp) between (cast ('2000-03-11' as timestamp) - interval 30 days) + and (cast ('2000-03-11' as timestamp) + interval 30 days) + group by + w_state,i_item_id + order by w_state,i_item_id +limit 100; +---- RESULTS +'TN','AAAAAAAAAABDAAAA',0.00,-82.10 +'TN','AAAAAAAAAACDAAAA',-216.54,158.04 +'TN','AAAAAAAAAAHDAAAA',186.55,0.00 +'TN','AAAAAAAAAALAAAAA',0.00,48.23 +'TN','AAAAAAAAABBDAAAA',98.61,332.71 +'TN','AAAAAAAAABDAAAAA',0.00,213.64 +'TN','AAAAAAAAACGCAAAA',63.68,0.00 +'TN','AAAAAAAAACHCAAAA',102.68,51.89 +'TN','AAAAAAAAACKCAAAA',128.93,44.82 +'TN','AAAAAAAAACLDAAAA',205.44,-948.62 +'TN','AAAAAAAAACOBAAAA',207.32,24.89 +'TN','AAAAAAAAACPDAAAA',87.75,53.99 +'TN','AAAAAAAAADGBAAAA',44.31,222.48 +'TN','AAAAAAAAADKBAAAA',0.00,-471.87 +'TN','AAAAAAAAAEADAAAA',58.24,0.00 +'TN','AAAAAAAAAEOCAAAA',19.91,214.70 +'TN','AAAAAAAAAFACAAAA',271.82,163.17 +'TN','AAAAAAAAAFADAAAA',2.35,28.32 +'TN','AAAAAAAAAFDCAAAA',-378.05,-303.27 +'TN','AAAAAAAAAGIDAAAA',307.61,-19.29 +'TN','AAAAAAAAAHDEAAAA',80.58,-476.72 +'TN','AAAAAAAAAHHAAAAA',8.27,155.10 +'TN','AAAAAAAAAHJBAAAA',39.24,0.00 +'TN','AAAAAAAAAIECAAAA',82.40,3.91 +'TN','AAAAAAAAAIEEAAAA',20.40,-151.09 +'TN','AAAAAAAAAIMCAAAA',24.47,-150.30 +'TN','AAAAAAAAAJACAAAA',49.09,82.10 +'TN','AAAAAAAAAJCAAAAA',121.18,63.78 +'TN','AAAAAAAAAJKBAAAA',27.94,8.97 +'TN','AAAAAAAAALBEAAAA',88.26,30.23 +'TN','AAAAAAAAALCEAAAA',93.52,92.02 +'TN','AAAAAAAAALECAAAA',64.20,15.16 +'TN','AAAAAAAAALNBAAAA',4.20,148.27 +'TN','AAAAAAAAAMBEAAAA',28.44,0.00 +'TN','AAAAAAAAAMPBAAAA',0.00,131.93 +'TN','AAAAAAAAANFEAAAA',0.00,-137.34 +'TN','AAAAAAAAAOBBAAAA',0.00,55.62 +'TN','AAAAAAAAAOIBAAAA',150.41,254.28 +'TN','AAAAAAAAAPBAAAAA',70.40,0.00 +'TN','AAAAAAAAAPJBAAAA',45.27,334.40 +'TN','AAAAAAAAAPLAAAAA',50.20,29.15 +'TN','AAAAAAAAAPLDAAAA',0.00,32.39 +'TN','AAAAAAAABAPDAAAA',93.42,145.87 +'TN','AAAAAAAABBIDAAAA',296.77,30.96 +'TN','AAAAAAAABDCEAAAA',-1771.08,-54.78 +'TN','AAAAAAAABDDDAAAA',111.12,280.59 +'TN','AAAAAAAABDJAAAAA',0.00,79.55 +'TN','AAAAAAAABEFDAAAA',0.00,3.43 +'TN','AAAAAAAABEODAAAA',269.90,297.58 +'TN','AAAAAAAABFMBAAAA',110.83,-941.40 +'TN','AAAAAAAABFNAAAAA',47.86,0.00 +'TN','AAAAAAAABFOCAAAA',46.34,83.52 +'TN','AAAAAAAABHPCAAAA',27.37,77.62 +'TN','AAAAAAAABIDBAAAA',196.62,5.57 +'TN','AAAAAAAABIGBAAAA',425.34,0.00 +'TN','AAAAAAAABIJBAAAA',209.63,0.00 +'TN','AAAAAAAABJFEAAAA',7.33,55.16 +'TN','AAAAAAAABKFAAAAA',0.00,138.14 +'TN','AAAAAAAABKMCAAAA',27.17,54.97 +'TN','AAAAAAAABLDEAAAA',170.29,0.00 +'TN','AAAAAAAABNHBAAAA',58.06,-337.89 +'TN','AAAAAAAABNIDAAAA',54.40,35.02 +'TN','AAAAAAAABNLAAAAA',0.00,168.38 +'TN','AAAAAAAABNLDAAAA',0.00,96.41 +'TN','AAAAAAAABNMCAAAA',202.41,49.53 +'TN','AAAAAAAABOCCAAAA',4.73,69.84 +'TN','AAAAAAAABOMBAAAA',63.67,163.49 +'TN','AAAAAAAACAAAAAAA',121.91,0.00 +'TN','AAAAAAAACAADAAAA',-1107.61,0.00 +'TN','AAAAAAAACAJCAAAA',115.81,173.05 +'TN','AAAAAAAACBCDAAAA',18.94,226.38 +'TN','AAAAAAAACBFAAAAA',0.00,97.41 +'TN','AAAAAAAACBIAAAAA',2.14,84.66 +'TN','AAAAAAAACBPBAAAA',95.44,26.68 +'TN','AAAAAAAACCABAAAA',160.43,135.86 +'TN','AAAAAAAACCHDAAAA',0.00,121.62 +'TN','AAAAAAAACCMDAAAA',-115.87,124.38 +'TN','AAAAAAAACDBCAAAA',16.62,3.40 +'TN','AAAAAAAACDECAAAA',-3114.60,0.00 +'TN','AAAAAAAACEEAAAAA',34.68,26.41 +'TN','AAAAAAAACELAAAAA',130.59,154.63 +'TN','AAAAAAAACELDAAAA',0.00,181.07 +'TN','AAAAAAAACFEAAAAA',3.78,-315.13 +'TN','AAAAAAAACFHDAAAA',0.00,1.80 +'TN','AAAAAAAACGFDAAAA',-386.87,96.92 +'TN','AAAAAAAACHHDAAAA',143.17,251.64 +'TN','AAAAAAAACHPCAAAA',0.17,198.29 +'TN','AAAAAAAACJCBAAAA',-918.65,270.96 +'TN','AAAAAAAACJDCAAAA',0.00,130.15 +'TN','AAAAAAAACJLAAAAA',63.96,91.27 +'TN','AAAAAAAACKFCAAAA',-540.59,35.64 +'TN','AAAAAAAACKHAAAAA',204.52,110.61 +'TN','AAAAAAAACKIAAAAA',18.43,-63.65 +'TN','AAAAAAAACLAEAAAA',116.07,0.00 +'TN','AAAAAAAACLGAAAAA',108.10,111.14 +'TN','AAAAAAAACLKAAAAA',143.05,19.59 +'TN','AAAAAAAACLLBAAAA',0.00,178.10 +'TN','AAAAAAAACLOBAAAA',-2200.72,14.13 +'TN','AAAAAAAACMADAAAA',71.42,-13.64 +'TN','AAAAAAAACMJAAAAA',0.00,358.31 +---- TYPES +STRING, STRING, DECIMAL, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q41.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q41.test b/testdata/workloads/tpcds/queries/tpcds-q41.test new file mode 100644 index 0000000..e046f08 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q41.test @@ -0,0 +1,59 @@ +==== +---- QUERY: TPCDS-Q41 +select distinct(i_product_name) + from item i1 + where i_manufact_id between 738 and 738+40 + and (select count(*) as item_cnt + from item + where (i_manufact = i1.i_manufact and + ((i_category = 'Women' and + (i_color = 'powder' or i_color = 'khaki') and + (i_units = 'Ounce' or i_units = 'Oz') and + (i_size = 'medium' or i_size = 'extra large') + ) or + (i_category = 'Women' and + (i_color = 'brown' or i_color = 'honeydew') and + (i_units = 'Bunch' or i_units = 'Ton') and + (i_size = 'N/A' or i_size = 'small') + ) or + (i_category = 'Men' and + (i_color = 'floral' or i_color = 'deep') and + (i_units = 'N/A' or i_units = 'Dozen') and + (i_size = 'petite' or i_size = 'large') + ) or + (i_category = 'Men' and + (i_color = 'light' or i_color = 'cornflower') and + (i_units = 'Box' or i_units = 'Pound') and + (i_size = 'medium' or i_size = 'extra large') + ))) or + (i_manufact = i1.i_manufact and + ((i_category = 'Women' and + (i_color = 'midnight' or i_color = 'snow') and + (i_units = 'Pallet' or i_units = 'Pallet') and + (i_size = 'medium' or i_size = 'extra large') + ) or + (i_category = 'Women' and + (i_color = 'cyan' or i_color = 'papaya') and + (i_units = 'Cup' or i_units = 'Dram') and + (i_size = 'N/A' or i_size = 'small') + ) or + (i_category = 'Men' and + (i_color = 'orange' or i_color = 'frosted') and + (i_units = 'Each' or i_units = 'Tbl') and + (i_size = 'petite' or i_size = 'large') + ) or + (i_category = 'Men' and + (i_color = 'forest' or i_color = 'ghost') and + (i_units = 'Lb' or i_units = 'Bundle') and + (i_size = 'medium' or i_size = 'extra large') + )))) > 0 + order by i_product_name + limit 100; +---- RESULTS +'ableationableought' +'anticallyeingese' +'callycallyeingese' +'oughtationableought' +---- TYPES +STRING +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q47.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q47.test b/testdata/workloads/tpcds/queries/tpcds-q47.test deleted file mode 100644 index b65606e..0000000 --- a/testdata/workloads/tpcds/queries/tpcds-q47.test +++ /dev/null @@ -1,154 +0,0 @@ -==== ----- QUERY: TPCDS-Q47 -with v1 as ( - select i_category, i_brand, - s_store_name, s_company_name, - d_year, 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, d_year) - avg_monthly_sales, - rank() over - (partition by i_category, i_brand, - s_store_name, s_company_name - order by d_year, d_moy) rn - 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 = 1999 or - ( d_year = 1999-1 and d_moy =12) or - ( d_year = 1999+1 and d_moy =1) - ) - group by i_category, i_brand, - s_store_name, s_company_name, - d_year, d_moy), - v2 as( - select v1.i_category, v1.i_brand, v1.s_store_name, v1.s_company_name, - v1.d_year, v1.d_moy, - v1.avg_monthly_sales, - v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum - from v1, v1 v1_lag, v1 v1_lead - where v1.i_category = v1_lag.i_category and - v1.i_category = v1_lead.i_category and - v1.i_brand = v1_lag.i_brand and - v1.i_brand = v1_lead.i_brand and - v1.s_store_name = v1_lag.s_store_name and - v1.s_store_name = v1_lead.s_store_name and - v1.s_company_name = v1_lag.s_company_name and - v1.s_company_name = v1_lead.s_company_name and - v1.rn = v1_lag.rn + 1 and - v1.rn = v1_lead.rn - 1) -select * -from v2 -where d_year = 1999 and - avg_monthly_sales > 0 and - 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, 3 -limit 100; ----- RESULTS -'Men','importoimporto #1','ese','Unknown',1999,3,5492.81,2070.65,3307.78,2784.11 -'Music','exportischolar #1','eing','Unknown',1999,2,5134.23,1740.21,4401.89,2721.61 -'Music','edu packscholar #1','ese','Unknown',1999,4,5463.70,2091.07,3391.39,3095.31 -'Music','edu packscholar #1','ought','Unknown',1999,7,5552.30,2226.34,4081.99,7573.33 -'Music','edu packscholar #1','ese','Unknown',1999,2,5463.70,2268.27,4139.47,3391.39 -'Men','edu packimporto #1','ought','Unknown',1999,7,5176.25,1993.73,3542.54,5764.35 -'Children','exportiexporti #1','ese','Unknown',1999,3,5185.89,2019.69,3479.21,2982.05 -'Shoes','amalgedu pack #1','ation','Unknown',1999,6,5152.09,2077.67,3123.43,2368.73 -'Men','edu packimporto #1','eing','Unknown',1999,3,5501.18,2453.88,2683.70,2615.32 -'Children','exportiexporti #1','ation','Unknown',1999,4,5238.35,2232.03,3221.70,3649.52 -'Shoes','importoedu pack #1','eing','Unknown',1999,5,4698.61,1703.27,2687.15,2757.83 -'Music','exportischolar #1','bar','Unknown',1999,7,5318.14,2346.06,3483.88,7658.62 -'Music','edu packscholar #1','ation','Unknown',1999,2,5622.22,2657.68,4207.03,3543.82 -'Children','exportiexporti #1','bar','Unknown',1999,7,5280.66,2324.79,3003.61,5961.39 -'Children','importoexporti #1','eing','Unknown',1999,2,4650.43,1734.45,2341.19,2720.78 -'Shoes','importoedu pack #1','ought','Unknown',1999,4,4537.63,1623.33,2928.34,1905.23 -'Women','edu packamalg #1','ation','Unknown',1999,4,4507.41,1596.92,2865.38,2665.75 -'Men','importoimporto #1','ought','Unknown',1999,6,5045.75,2152.15,2834.94,2667.92 -'Men','edu packimporto #1','eing','Unknown',1999,4,5501.18,2615.32,2453.88,2874.96 -'Shoes','exportiedu pack #1','bar','Unknown',1999,3,5296.08,2410.44,2685.85,3821.39 -'Men','importoimporto #1','able','Unknown',1999,2,5194.81,2333.43,2765.75,2674.91 -'Music','amalgscholar #1','ought','Unknown',1999,2,4565.60,1727.16,3895.09,2606.58 -'Music','edu packscholar #1','eing','Unknown',1999,5,5539.30,2704.16,3862.34,3960.90 -'Shoes','importoedu pack #1','ation','Unknown',1999,4,4690.57,1855.85,3524.69,3050.34 -'Men','edu packimporto #1','eing','Unknown',1999,2,5501.18,2683.70,4304.21,2453.88 -'Women','exportiamalg #1','able','Unknown',1999,2,4123.60,1306.40,2876.61,2238.71 -'Music','exportischolar #1','able','Unknown',1999,5,5090.10,2281.80,2488.24,3304.75 -'Shoes','exportiedu pack #1','ought','Unknown',1999,2,4755.53,1951.00,3949.06,2767.05 -'Men','importoimporto #1','ese','Unknown',1999,6,5492.81,2691.33,3210.74,3754.33 -'Men','amalgimporto #1','ation','Unknown',1999,2,4909.46,2116.43,3187.20,3356.64 -'Men','importoimporto #1','ought','Unknown',1999,2,5045.75,2253.70,4158.86,3467.09 -'Shoes','amalgedu pack #1','ation','Unknown',1999,7,5152.09,2368.73,2077.67,7543.60 -'Men','edu packimporto #1','ese','Unknown',1999,3,4979.36,2201.00,3110.55,3118.30 -'Children','exportiexporti #1','ation','Unknown',1999,2,5238.35,2462.96,3808.48,3221.70 -'Men','amalgimporto #1','ation','Unknown',1999,4,4909.46,2136.23,3356.64,3046.51 -'Children','importoexporti #1','able','Unknown',1999,7,4586.29,1814.46,2643.58,6423.18 -'Men','edu packimporto #1','ation','Unknown',1999,4,5170.36,2407.58,3086.77,2492.73 -'Shoes','amalgedu pack #1','ese','Unknown',1999,7,4392.18,1630.14,2755.91,6185.73 -'Shoes','amalgedu pack #1','able','Unknown',1999,5,4940.22,2187.55,2894.91,3018.65 -'Men','edu packimporto #1','ought','Unknown',1999,4,5176.25,2424.94,4285.78,3286.20 -'Women','amalgamalg #1','able','Unknown',1999,6,4507.24,1761.81,2891.95,2302.21 -'Men','importoimporto #1','ation','Unknown',1999,3,5410.91,2672.68,3591.65,2988.08 -'Men','importoimporto #1','ation','Unknown',1999,5,5410.91,2677.81,2988.08,2881.34 -'Men','edu packimporto #1','bar','Unknown',1999,4,5632.73,2901.64,3202.87,3447.78 -'Children','exportiexporti #1','able','Unknown',1999,5,4955.24,2230.80,2395.57,3003.89 -'Men','importoimporto #1','eing','Unknown',1999,5,5074.07,2356.88,2833.40,2854.62 -'Men','edu packimporto #1','bar','Unknown',1999,2,5632.73,2916.43,3847.05,3202.87 -'Shoes','exportiedu pack #1','ese','Unknown',1999,3,4865.28,2151.76,3212.91,3768.25 -'Men','importoimporto #1','ese','Unknown',1999,4,5492.81,2784.11,2070.65,3210.74 -'Shoes','exportiedu pack #1','bar','Unknown',1999,7,5296.08,2591.12,3012.98,6254.36 -'Shoes','exportiedu pack #1','ation','Unknown',1999,5,4873.51,2170.98,2302.76,3236.50 -'Shoes','amalgedu pack #1','able','Unknown',1999,2,4940.22,2239.16,3495.29,2563.93 -'Children','exportiexporti #1','eing','Unknown',1999,7,5109.65,2410.24,2916.46,6558.23 -'Women','importoamalg #1','able','Unknown',1999,7,4574.74,1881.03,2345.66,6036.28 -'Women','amalgamalg #1','ought','Unknown',1999,7,4619.70,1926.67,3528.98,5162.15 -'Children','importoexporti #1','bar','Unknown',1999,7,4566.77,1879.57,3400.62,6244.92 -'Music','edu packscholar #1','ation','Unknown',1999,6,5622.22,2943.26,3643.42,3847.77 -'Music','exportischolar #1','ation','Unknown',1999,4,4962.65,2283.77,3210.01,2539.84 -'Music','exportischolar #1','ation','Unknown',1999,2,4962.65,2284.23,3250.69,3210.01 -'Men','edu packimporto #1','ation','Unknown',1999,5,5170.36,2492.73,2407.58,3288.94 -'Men','edu packimporto #1','able','Unknown',1999,3,4989.30,2318.98,2618.89,3315.77 -'Music','exportischolar #1','bar','Unknown',1999,4,5318.14,2651.96,2989.10,3649.76 -'Music','exportischolar #1','bar','Unknown',1999,2,5318.14,2656.31,3419.77,2989.10 -'Shoes','amalgedu pack #1','bar','Unknown',1999,6,4805.40,2149.56,2686.70,3098.25 -'Children','exportiexporti #1','bar','Unknown',1999,4,5280.66,2625.99,3301.62,4331.44 -'Music','amalgscholar #1','ation','Unknown',1999,4,4934.50,2280.93,2322.89,2421.13 -'Music','edu packscholar #1','able','Unknown',1999,7,5335.90,2684.15,3543.33,7540.94 -'Shoes','importoedu pack #1','eing','Unknown',1999,2,4698.61,2058.86,3191.74,2812.15 -'Shoes','edu packedu pack #1','ought','Unknown',1999,5,4745.29,2109.27,3203.82,2737.82 -'Shoes','importoedu pack #1','ought','Unknown',1999,5,4537.63,1905.23,1623.33,3170.58 -'Men','edu packimporto #1','able','Unknown',1999,6,4989.30,2357.82,3363.58,3142.81 -'Children','exportiexporti #1','able','Unknown',1999,2,4955.24,2326.67,2746.99,3097.63 -'Men','edu packimporto #1','eing','Unknown',1999,5,5501.18,2874.96,2615.32,3714.00 -'Shoes','exportiedu pack #1','ation','Unknown',1999,7,4873.51,2256.56,3236.50,6245.37 -'Shoes','importoedu pack #1','ese','Unknown',1999,3,4676.74,2060.29,3273.67,2610.86 -'Men','amalgimporto #1','ese','Unknown',1999,6,4764.59,2150.16,3284.27,3475.17 -'Music','amalgscholar #1','ation','Unknown',1999,3,4934.50,2322.89,3197.76,2280.93 -'Shoes','exportiedu pack #1','bar','Unknown',1999,2,5296.08,2685.85,4235.44,2410.44 -'Children','importoexporti #1','able','Unknown',1999,5,4586.29,1982.77,2837.74,2643.58 -'Music','exportischolar #1','able','Unknown',1999,4,5090.10,2488.24,2966.36,2281.80 -'Shoes','importoedu pack #1','able','Unknown',1999,7,4700.91,2100.12,2533.01,5888.57 -'Music','edu packscholar #1','bar','Unknown',1999,2,5484.78,2903.45,3410.13,3024.86 -'Shoes','exportiedu pack #1','ation','Unknown',1999,3,4873.51,2300.45,2797.30,2302.76 -'Shoes','exportiedu pack #1','ation','Unknown',1999,4,4873.51,2302.76,2300.45,2170.98 -'Shoes','importoedu pack #1','bar','Unknown',1999,3,4794.67,2225.70,2756.97,2413.76 -'Music','edu packscholar #1','ought','Unknown',1999,5,5552.30,2985.49,3241.69,4081.99 -'Music','edu packscholar #1','eing','Unknown',1999,3,5539.30,2973.01,3069.18,3862.34 -'Shoes','amalgedu pack #1','eing','Unknown',1999,7,4706.54,2143.18,2458.07,5967.73 -'Children','exportiexporti #1','ought','Unknown',1999,5,5018.27,2458.03,3467.53,2683.61 -'Children','exportiexporti #1','able','Unknown',1999,4,4955.24,2395.57,3097.63,2230.80 -'Children','exportiexporti #1','eing','Unknown',1999,2,5109.65,2550.30,4039.10,2685.10 -'Music','exportischolar #1','ought','Unknown',1999,4,5079.18,2520.64,3233.50,3079.89 -'Women','edu packamalg #1','ation','Unknown',1999,2,4507.41,1951.42,4166.02,2865.38 -'Women','amalgamalg #1','ought','Unknown',1999,1,4619.70,2065.94,9639.59,2521.70 -'Music','importoscholar #1','ought','Unknown',1999,3,4004.44,1456.84,2438.63,2790.03 -'Shoes','edu packedu pack #1','eing','Unknown',1999,5,4664.86,2122.71,3131.02,2852.96 -'Women','importoamalg #1','bar','Unknown',1999,3,4437.21,1895.27,2678.48,2999.04 -'Music','exportischolar #1','able','Unknown',1999,2,5090.10,2550.48,3702.29,2966.36 -'Music','edu packscholar #1','able','Unknown',1999,1,5335.90,2796.97,13360.68,3413.22 -'Children','exportiexporti #1','eing','Unknown',1999,4,5109.65,2574.12,2685.10,2672.73 ----- TYPES -STRING, STRING, STRING, STRING, INT, INT, DECIMAL, DECIMAL, DECIMAL, DECIMAL -==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q50.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q50.test b/testdata/workloads/tpcds/queries/tpcds-q50.test new file mode 100644 index 0000000..525ad7e --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q50.test @@ -0,0 +1,68 @@ +==== +---- QUERY: TPCDS-Q50 +select + s_store_name + ,s_company_id + ,s_street_number + ,s_street_name + ,s_street_type + ,s_suite_number + ,s_city + ,s_county + ,s_state + ,s_zip + ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end) as "30 days" + ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and + (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end ) as "31-60 days" + ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and + (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end) as "61-90 days" + ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and + (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end) as "91-120 days" + ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0 end) as ">120 days" +from + store_sales + ,store_returns + ,store + ,date_dim d1 + ,date_dim d2 +where + d2.d_year = 2001 +and d2.d_moy = 8 +and ss_ticket_number = sr_ticket_number +and ss_item_sk = sr_item_sk +and ss_sold_date_sk = d1.d_date_sk +and sr_returned_date_sk = d2.d_date_sk +and ss_customer_sk = sr_customer_sk +and ss_store_sk = s_store_sk +group by + s_store_name + ,s_company_id + ,s_street_number + ,s_street_name + ,s_street_type + ,s_suite_number + ,s_city + ,s_county + ,s_state + ,s_zip +order by s_store_name + ,s_company_id + ,s_street_number + ,s_street_name + ,s_street_type + ,s_suite_number + ,s_city + ,s_county + ,s_state + ,s_zip +limit 100; +---- RESULTS +'able',1,'255','Sycamore ','Dr.','Suite 410','Midway','Williamson County','TN','31904',67,48,61,66,98 +'ation',1,'811','Lee ','Circle','Suite T','Midway','Williamson County','TN','31904',70,51,50,61,109 +'bar',1,'175','4th ','Court','Suite C','Midway','Williamson County','TN','31904',96,53,55,76,86 +'eing',1,'226','12th ','Lane','Suite D','Fairview','Williamson County','TN','35709',69,63,62,63,114 +'ese',1,'27','Lake ','Ln','Suite 260','Midway','Williamson County','TN','31904',58,57,55,54,106 +'ought',1,'767','Spring ','Wy','Suite 250','Midway','Williamson County','TN','31904',81,63,52,58,103 +---- TYPES +STRING, INT, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q51.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q51.test b/testdata/workloads/tpcds/queries/tpcds-q51.test new file mode 100644 index 0000000..9fe36b0 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q51.test @@ -0,0 +1,148 @@ +==== +---- QUERY: TPCDS-Q51 +WITH web_v1 as ( +select + ws_item_sk item_sk, d_date, + sum(sum(ws_sales_price)) + over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales +from web_sales + ,date_dim +where ws_sold_date_sk=d_date_sk + and d_month_seq between 1200 and 1200+11 + and ws_item_sk is not NULL +group by ws_item_sk, d_date), +store_v1 as ( +select + ss_item_sk item_sk, d_date, + sum(sum(ss_sales_price)) + over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales +from store_sales + ,date_dim +where ss_sold_date_sk=d_date_sk + and d_month_seq between 1200 and 1200+11 + and ss_item_sk is not NULL +group by ss_item_sk, d_date) + select * +from (select item_sk + ,d_date + ,web_sales + ,store_sales + ,max(web_sales) + over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative + ,max(store_sales) + over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative + from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk + ,case when web.d_date is not null then web.d_date else store.d_date end d_date + ,web.cume_sales web_sales + ,store.cume_sales store_sales + from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk + and web.d_date = store.d_date) + )x )y +where web_cumulative > store_cumulative +order by item_sk + ,d_date +limit 100; +---- RESULTS +14,'2000-01-10',176.83,NULL,176.83,73.60 +14,'2000-01-21',NULL,75.29,176.83,75.29 +14,'2000-01-29',222.33,NULL,222.33,75.29 +14,'2000-02-02',224.01,NULL,224.01,75.29 +14,'2000-02-08',NULL,85.07,224.01,85.07 +14,'2000-02-19',NULL,98.60,224.01,98.60 +14,'2000-02-21',241.64,NULL,241.64,98.60 +14,'2000-02-22',NULL,99.83,241.64,99.83 +14,'2000-03-18',NULL,112.82,241.64,112.82 +14,'2000-03-23',251.15,NULL,251.15,112.82 +14,'2000-03-28',260.17,NULL,260.17,112.82 +14,'2000-03-31',370.74,NULL,370.74,112.82 +14,'2000-04-05',NULL,115.94,370.74,115.94 +14,'2000-04-15',445.30,NULL,445.30,115.94 +14,'2000-04-27',NULL,151.48,445.30,151.48 +14,'2000-05-03',NULL,176.89,445.30,176.89 +14,'2000-05-10',451.40,NULL,451.40,176.89 +14,'2000-05-21',NULL,238.39,451.40,238.39 +14,'2000-05-26',596.81,NULL,596.81,238.39 +14,'2000-05-29',NULL,242.51,596.81,242.51 +14,'2000-06-05',NULL,304.64,596.81,304.64 +14,'2000-07-03',623.77,NULL,623.77,304.64 +14,'2000-07-07',NULL,307.77,623.77,307.77 +14,'2000-07-18',NULL,320.04,623.77,320.04 +14,'2000-07-25',673.08,NULL,673.08,320.04 +14,'2000-08-10',NULL,411.48,673.08,411.48 +14,'2000-08-14',NULL,465.85,673.08,465.85 +14,'2000-08-21',NULL,541.45,673.08,541.45 +14,'2000-08-26',NULL,574.56,673.08,574.56 +19,'2000-01-02',56.96,49.68,56.96,49.68 +25,'2000-01-21',97.29,NULL,97.29,4.49 +25,'2000-01-28',192.46,NULL,192.46,4.49 +25,'2000-02-09',NULL,24.23,192.46,24.23 +25,'2000-02-11',NULL,98.99,192.46,98.99 +25,'2000-02-21',NULL,170.60,192.46,170.60 +25,'2000-02-22',NULL,185.05,192.46,185.05 +35,'2000-01-14',NULL,55.24,177.88,55.24 +35,'2000-01-16',NULL,95.92,177.88,95.92 +35,'2000-01-18',NULL,126.45,177.88,126.45 +35,'2000-01-19',NULL,167.07,177.88,167.07 +35,'2000-02-17',NULL,173.97,177.88,173.97 +35,'2000-02-22',270.43,NULL,270.43,173.97 +35,'2000-02-23',NULL,180.61,270.43,180.61 +35,'2000-03-03',NULL,181.99,270.43,181.99 +35,'2000-03-05',NULL,221.24,270.43,221.24 +35,'2000-03-06',NULL,266.41,270.43,266.41 +37,'2000-01-02',31.75,11.89,31.75,11.89 +37,'2000-01-04',NULL,17.15,31.75,17.15 +37,'2000-01-05',34.34,NULL,34.34,17.15 +37,'2000-01-06',NULL,29.67,34.34,29.67 +41,'2000-01-21',NULL,15.54,123.34,15.54 +41,'2000-02-03',NULL,21.04,123.34,21.04 +41,'2000-02-16',NULL,33.46,123.34,33.46 +41,'2000-02-20',NULL,37.46,123.34,37.46 +41,'2000-02-22',NULL,58.57,123.34,58.57 +41,'2000-03-05',NULL,70.06,123.34,70.06 +41,'2000-03-17',178.84,150.76,178.84,150.76 +41,'2000-04-26',263.14,NULL,263.14,254.88 +41,'2000-07-12',474.83,NULL,474.83,393.87 +41,'2000-07-18',NULL,421.23,474.83,421.23 +41,'2000-08-15',NULL,430.77,474.83,430.77 +49,'2000-01-18',NULL,2.51,4.58,2.51 +49,'2000-01-31',72.47,NULL,72.47,13.05 +49,'2000-02-13',NULL,70.68,72.47,70.68 +49,'2000-02-29',NULL,71.86,72.47,71.86 +49,'2000-04-17',225.29,NULL,225.29,219.03 +53,'2000-01-02',12.85,1.13,12.85,1.13 +53,'2000-01-08',119.24,NULL,119.24,1.13 +53,'2000-01-09',126.98,NULL,126.98,1.13 +53,'2000-01-15',NULL,3.20,126.98,3.20 +53,'2000-02-04',NULL,22.89,126.98,22.89 +53,'2000-02-05',NULL,64.45,126.98,64.45 +53,'2000-02-12',NULL,66.06,126.98,66.06 +56,'2000-01-02',41.57,17.31,41.57,17.31 +61,'2000-02-17',421.60,NULL,421.60,344.03 +61,'2000-03-01',NULL,411.33,421.60,411.33 +61,'2000-04-22',600.20,NULL,600.20,573.28 +71,'2000-01-02',13.92,2.88,13.92,2.88 +85,'2000-02-03',NULL,42.30,65.50,42.30 +85,'2000-02-16',NULL,42.95,65.50,42.95 +85,'2000-04-19',335.16,NULL,335.16,247.67 +85,'2000-04-23',NULL,252.83,335.16,252.83 +85,'2000-05-02',NULL,289.65,335.16,289.65 +85,'2000-05-11',NULL,312.62,335.16,312.62 +86,'2000-01-19',31.70,NULL,31.70,25.97 +86,'2000-02-03',151.26,NULL,151.26,91.16 +86,'2000-02-04',NULL,112.15,151.26,112.15 +89,'2000-01-12',NULL,28.84,181.56,28.84 +89,'2000-01-23',NULL,67.19,181.56,67.19 +89,'2000-01-30',NULL,104.65,181.56,104.65 +89,'2000-02-22',NULL,146.96,181.56,146.96 +89,'2000-02-25',NULL,147.02,181.56,147.02 +89,'2000-03-19',NULL,172.85,181.56,172.85 +89,'2000-03-20',191.66,NULL,191.66,172.85 +89,'2000-04-11',295.81,NULL,295.81,172.85 +89,'2000-04-13',NULL,203.86,295.81,203.86 +89,'2000-04-20',373.30,NULL,373.30,203.86 +89,'2000-04-23',NULL,219.74,373.30,219.74 +89,'2000-04-26',NULL,235.97,373.30,235.97 +89,'2000-05-04',NULL,248.05,373.30,248.05 +---- TYPES +BIGINT, STRING, DECIMAL, DECIMAL, DECIMAL, DECIMAL +====
