http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q51a.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q51a.test b/testdata/workloads/tpcds/queries/tpcds-q51a.test new file mode 100644 index 0000000..89e0c48 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q51a.test @@ -0,0 +1,166 @@ +==== +---- QUERY: TPCDS-Q51A +WITH web_tv as ( +select + ws_item_sk item_sk, d_date, sum(ws_sales_price) sumws, + row_number() + over (partition by ws_item_sk order by d_date) rk +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 +), +web_v1 as ( +select v1.item_sk, v1.d_date, v1.sumws, sum(v2.sumws) cume_sales +from web_tv v1, web_tv v2 +where v1.item_sk = v2.item_sk and v1.rk >= v2.rk +group by v1.item_sk, v1.d_date, v1.sumws +), +store_tv as ( +select + ss_item_sk item_sk, d_date, sum(ss_sales_price) sumss, + row_number() + over (partition by ss_item_sk order by d_date) rk +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 +), +store_v1 as ( +select v1.item_sk, v1.d_date, v1.sumss, sum(v2.sumss) cume_sales +from store_tv v1, store_tv v2 +where v1.item_sk = v2.item_sk and v1.rk >= v2.rk +group by v1.item_sk, v1.d_date, v1.sumss +), +v as ( +select item_sk + ,d_date + ,web_sales + ,store_sales + ,row_number() over (partition by item_sk order by d_date) rk + 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 +) + select * +from( + select v1.item_sk, v1.d_date, v1.web_sales, v1.store_sales, max(v2.web_sales) web_cumulative, max(v2.store_sales) store_cumulative + from v v1, v v2 + where v1.item_sk = v2.item_sk and v1.rk >= v2.rk + group by v1.item_sk, v1.d_date, v1.web_sales, v1.store_sales +)x +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 +====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q54.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q54.test b/testdata/workloads/tpcds/queries/tpcds-q54.test new file mode 100644 index 0000000..57f3647 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q54.test @@ -0,0 +1,60 @@ +==== +---- QUERY: TPCDS-Q54 +with my_customers as ( + select distinct c_customer_sk + , c_current_addr_sk + from + ( select cs_sold_date_sk sold_date_sk, + cs_bill_customer_sk customer_sk, + cs_item_sk item_sk + from catalog_sales + union all + select ws_sold_date_sk sold_date_sk, + ws_bill_customer_sk customer_sk, + ws_item_sk item_sk + from web_sales + ) cs_or_ws_sales, + item, + date_dim, + customer + where sold_date_sk = d_date_sk + and item_sk = i_item_sk + and i_category = 'Women' + and i_class = 'maternity' + and c_customer_sk = cs_or_ws_sales.customer_sk + and d_moy = 12 + and d_year = 1998 + ) + , my_revenue as ( + select c_customer_sk, + sum(ss_ext_sales_price) as revenue + from my_customers, + store_sales, + customer_address, + store, + date_dim + where c_current_addr_sk = ca_address_sk + and ca_county = s_county + and ca_state = s_state + and ss_sold_date_sk = d_date_sk + and c_customer_sk = ss_customer_sk + and d_month_seq between (select distinct d_month_seq+1 + from date_dim where d_year = 1998 and d_moy = 12 limit 1) + and (select distinct d_month_seq+3 + from date_dim where d_year = 1998 and d_moy = 12 limit 1) + group by c_customer_sk + ) + , segments as + (select cast((revenue/50) as int) as segment + from my_revenue + ) + select segment, count(*) as num_customers, segment*50 as segment_base + from segments + group by segment + order by segment, num_customers + limit 100; +---- RESULTS +11860,1,593000 +---- TYPES +INT, BIGINT, BIGINT +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q56.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q56.test b/testdata/workloads/tpcds/queries/tpcds-q56.test new file mode 100644 index 0000000..7d35eb8 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q56.test @@ -0,0 +1,171 @@ +==== +---- QUERY: TPCDS-Q56 +with ss as ( + select i_item_id,sum(ss_ext_sales_price) total_sales + from + store_sales, + date_dim, + customer_address, + item + where i_item_id in (select + i_item_id +from item +where i_color in ('slate','blanched','burnished')) + and ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and d_year = 2001 + and d_moy = 2 + and ss_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_item_id), + cs as ( + select i_item_id,sum(cs_ext_sales_price) total_sales + from + catalog_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from item +where i_color in ('slate','blanched','burnished')) + and cs_item_sk = i_item_sk + and cs_sold_date_sk = d_date_sk + and d_year = 2001 + and d_moy = 2 + and cs_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_item_id), + ws as ( + select i_item_id,sum(ws_ext_sales_price) total_sales + from + web_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from item +where i_color in ('slate','blanched','burnished')) + and ws_item_sk = i_item_sk + and ws_sold_date_sk = d_date_sk + and d_year = 2001 + and d_moy = 2 + and ws_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_item_id) + select i_item_id ,sum(total_sales) total_sales + from (select * from ss + union all + select * from cs + union all + select * from ws) tmp1 + group by i_item_id + order by total_sales, i_item_id + limit 100; +---- RESULTS +'AAAAAAAACENDAAAA',0.00 +'AAAAAAAAEPDEAAAA',0.00 +'AAAAAAAAIGBEAAAA',0.00 +'AAAAAAAAFBGBAAAA',3.52 +'AAAAAAAAINHBAAAA',7.28 +'AAAAAAAAMBGAAAAA',8.52 +'AAAAAAAAEOIDAAAA',14.02 +'AAAAAAAAALIBAAAA',48.62 +'AAAAAAAAEDDAAAAA',48.80 +'AAAAAAAAHOBDAAAA',49.50 +'AAAAAAAAMGCCAAAA',51.84 +'AAAAAAAAGBEAAAAA',54.53 +'AAAAAAAACKAEAAAA',66.30 +'AAAAAAAAKHOAAAAA',72.72 +'AAAAAAAANFPBAAAA',74.48 +'AAAAAAAAOFMBAAAA',83.22 +'AAAAAAAAKHMCAAAA',105.44 +'AAAAAAAAFOCEAAAA',105.98 +'AAAAAAAAENCBAAAA',109.20 +'AAAAAAAANGIBAAAA',111.00 +'AAAAAAAADGDEAAAA',121.74 +'AAAAAAAAEPADAAAA',126.08 +'AAAAAAAAINHAAAAA',127.92 +'AAAAAAAAHKJCAAAA',129.84 +'AAAAAAAAOLFBAAAA',132.16 +'AAAAAAAAPLEBAAAA',135.34 +'AAAAAAAAOHKDAAAA',136.36 +'AAAAAAAAEHOAAAAA',153.54 +'AAAAAAAAGJABAAAA',172.50 +'AAAAAAAAOCCBAAAA',200.93 +'AAAAAAAAGGFAAAAA',235.20 +'AAAAAAAAKIKBAAAA',236.95 +'AAAAAAAAMIOBAAAA',238.92 +'AAAAAAAAIPODAAAA',240.96 +'AAAAAAAACPDCAAAA',265.33 +'AAAAAAAAMKCEAAAA',268.37 +'AAAAAAAAKMPAAAAA',283.53 +'AAAAAAAACIBAAAAA',297.76 +'AAAAAAAAEIACAAAA',332.80 +'AAAAAAAAOGEEAAAA',339.65 +'AAAAAAAAMFMDAAAA',351.12 +'AAAAAAAAGHBCAAAA',359.90 +'AAAAAAAAIGDCAAAA',371.79 +'AAAAAAAACHLCAAAA',410.56 +'AAAAAAAAGMBDAAAA',418.46 +'AAAAAAAAIJMCAAAA',422.10 +'AAAAAAAAEJLBAAAA',442.50 +'AAAAAAAANDHCAAAA',460.07 +'AAAAAAAAAFNBAAAA',460.32 +'AAAAAAAAKDGDAAAA',479.88 +'AAAAAAAAKKNCAAAA',494.48 +'AAAAAAAAJAJBAAAA',518.30 +'AAAAAAAACCOBAAAA',522.92 +'AAAAAAAAAEKAAAAA',525.52 +'AAAAAAAAKJBDAAAA',527.15 +'AAAAAAAAGHEDAAAA',538.85 +'AAAAAAAAACGCAAAA',562.68 +'AAAAAAAAAEGAAAAA',572.32 +'AAAAAAAAEKLBAAAA',572.32 +'AAAAAAAAAHCBAAAA',604.38 +'AAAAAAAALFADAAAA',606.67 +'AAAAAAAAKFNDAAAA',617.96 +'AAAAAAAAOEKAAAAA',619.39 +'AAAAAAAAEEBEAAAA',626.40 +'AAAAAAAAKMBCAAAA',628.95 +'AAAAAAAAJHGDAAAA',631.81 +'AAAAAAAAOMLDAAAA',631.89 +'AAAAAAAAGNDDAAAA',645.99 +'AAAAAAAAEADAAAAA',648.20 +'AAAAAAAAKPKCAAAA',651.42 +'AAAAAAAAAKHCAAAA',657.04 +'AAAAAAAAOEIDAAAA',660.24 +'AAAAAAAAMKAEAAAA',691.02 +'AAAAAAAABLKAAAAA',691.26 +'AAAAAAAADEIBAAAA',726.72 +'AAAAAAAAKBHCAAAA',730.20 +'AAAAAAAADNJAAAAA',731.92 +'AAAAAAAAOFPBAAAA',737.28 +'AAAAAAAACPIBAAAA',737.64 +'AAAAAAAAEPPBAAAA',759.36 +'AAAAAAAALNHDAAAA',761.60 +'AAAAAAAAGKPDAAAA',773.56 +'AAAAAAAAKBCAAAAA',775.30 +'AAAAAAAAIBOCAAAA',777.48 +'AAAAAAAAOJBEAAAA',777.84 +'AAAAAAAAKFKBAAAA',780.41 +'AAAAAAAAPJCCAAAA',783.00 +'AAAAAAAACAKBAAAA',800.27 +'AAAAAAAAOFNDAAAA',806.19 +'AAAAAAAAGLIDAAAA',843.75 +'AAAAAAAAGDBAAAAA',868.77 +'AAAAAAAAOGFDAAAA',887.30 +'AAAAAAAACOLBAAAA',918.96 +'AAAAAAAAFFNCAAAA',923.35 +'AAAAAAAACCJDAAAA',955.08 +'AAAAAAAAMMCAAAAA',959.76 +'AAAAAAAACCPDAAAA',971.77 +'AAAAAAAAKNCEAAAA',998.60 +'AAAAAAAACNACAAAA',1010.70 +'AAAAAAAAKDEAAAAA',1019.52 +---- TYPES +STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q59.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q59.test b/testdata/workloads/tpcds/queries/tpcds-q59.test deleted file mode 100644 index 5f51f01..0000000 --- a/testdata/workloads/tpcds/queries/tpcds-q59.test +++ /dev/null @@ -1,187 +0,0 @@ -==== ----- QUERY: TPCDS-Q59 -with - wss as - (select - d_week_seq, - ss_store_sk, - sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end) sun_sales, - sum(case when (d_day_name = 'Monday') then ss_sales_price else null end) mon_sales, - sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end) tue_sales, - sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end) wed_sales, - sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end) thu_sales, - sum(case when (d_day_name = 'Friday') then ss_sales_price else null end) fri_sales, - sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end) sat_sales - from - store_sales, - date_dim - where - d_date_sk = ss_sold_date_sk - group by - d_week_seq, - ss_store_sk - ) -select - s_store_name1, - s_store_id1, - d_week_seq1, - sun_sales1 / sun_sales2, - mon_sales1 / mon_sales2, - tue_sales1 / tue_sales2, - wed_sales1 / wed_sales2, - thu_sales1 / thu_sales2, - fri_sales1 / fri_sales2, - sat_sales1 / sat_sales2 -from - (select - s_store_name s_store_name1, - wss.d_week_seq d_week_seq1, - s_store_id s_store_id1, - sun_sales sun_sales1, - mon_sales mon_sales1, - tue_sales tue_sales1, - wed_sales wed_sales1, - thu_sales thu_sales1, - fri_sales fri_sales1, - sat_sales sat_sales1 - from - wss, - store, - date_dim d - where - d.d_week_seq = wss.d_week_seq - and ss_store_sk = s_store_sk - and d_month_seq between 1212 and 1212 + 11 - ) y, - (select - s_store_name s_store_name2, - wss.d_week_seq d_week_seq2, - s_store_id s_store_id2, - sun_sales sun_sales2, - mon_sales mon_sales2, - tue_sales tue_sales2, - wed_sales wed_sales2, - thu_sales thu_sales2, - fri_sales fri_sales2, - sat_sales sat_sales2 - from - wss, - store, - date_dim d - where - d.d_week_seq = wss.d_week_seq - and ss_store_sk = s_store_sk - and d_month_seq between 1212 + 12 and 1212 + 23 - ) x -where - s_store_id1 = s_store_id2 - and d_week_seq1 = d_week_seq2 - 52 -order by - s_store_name1, - s_store_id1, - d_week_seq1 -limit 100; ----- RESULTS -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5271,1.36,3.08,0.32,0.48,1.14,0.75,1.84 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5272,1.09,0.75,0.68,0.98,1.00,1.33,0.99 -'able','AAAAAAAACAAAAAAA',5273,1.19,1.02,0.47,0.63,0.70,1.49,0.57 -'able','AAAAAAAACAAAAAAA',5273,1.19,1.02,0.47,0.63,0.70,1.49,0.57 ----- TYPES -STRING, STRING, INT, DECIMAL, DECIMAL, DECIMAL, DECIMAL, 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-q60.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q60.test b/testdata/workloads/tpcds/queries/tpcds-q60.test new file mode 100644 index 0000000..07cd0d5 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q60.test @@ -0,0 +1,181 @@ +==== +---- QUERY: TPCDS-Q60 +with ss as ( + select + i_item_id,sum(ss_ext_sales_price) total_sales + from + store_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from + item +where i_category in ('Music')) + and ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and d_year = 1998 + and d_moy = 9 + and ss_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_item_id), + cs as ( + select + i_item_id,sum(cs_ext_sales_price) total_sales + from + catalog_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from + item +where i_category in ('Music')) + and cs_item_sk = i_item_sk + and cs_sold_date_sk = d_date_sk + and d_year = 1998 + and d_moy = 9 + and cs_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_item_id), + ws as ( + select + i_item_id,sum(ws_ext_sales_price) total_sales + from + web_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from + item +where i_category in ('Music')) + and ws_item_sk = i_item_sk + and ws_sold_date_sk = d_date_sk + and d_year = 1998 + and d_moy = 9 + and ws_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -5 + group by i_item_id) + select + i_item_id +,sum(total_sales) total_sales + from (select * from ss + union all + select * from cs + union all + select * from ws) tmp1 + group by i_item_id + order by i_item_id + ,total_sales + limit 100; +---- RESULTS +'AAAAAAAAAABBAAAA',6287.84 +'AAAAAAAAAABEAAAA',1297.26 +'AAAAAAAAAADCAAAA',8936.88 +'AAAAAAAAAADEAAAA',20945.84 +'AAAAAAAAAAHAAAAA',7227.38 +'AAAAAAAAAANDAAAA',17117.56 +'AAAAAAAAAAOCAAAA',4287.89 +'AAAAAAAAABADAAAA',499.72 +'AAAAAAAAABBCAAAA',22704.66 +'AAAAAAAAABCEAAAA',22499.76 +'AAAAAAAAABDEAAAA',5527.74 +'AAAAAAAAABFBAAAA',4953.67 +'AAAAAAAAABFEAAAA',12523.22 +'AAAAAAAAABHCAAAA',20093.49 +'AAAAAAAAABIBAAAA',382.30 +'AAAAAAAAABICAAAA',1271.62 +'AAAAAAAAABKCAAAA',7355.85 +'AAAAAAAAABLBAAAA',786.05 +'AAAAAAAAABNAAAAA',8385.94 +'AAAAAAAAACACAAAA',11564.85 +'AAAAAAAAACBEAAAA',13046.23 +'AAAAAAAAACCDAAAA',14533.50 +'AAAAAAAAACDCAAAA',820.49 +'AAAAAAAAACLBAAAA',6761.17 +'AAAAAAAAACNCAAAA',19023.15 +'AAAAAAAAACODAAAA',7322.65 +'AAAAAAAAADACAAAA',4696.41 +'AAAAAAAAADBAAAAA',988.80 +'AAAAAAAAADDCAAAA',5661.21 +'AAAAAAAAADDEAAAA',1846.56 +'AAAAAAAAADEEAAAA',15327.48 +'AAAAAAAAADGEAAAA',4511.21 +'AAAAAAAAADHDAAAA',27866.80 +'AAAAAAAAADICAAAA',15624.49 +'AAAAAAAAADLCAAAA',399.00 +'AAAAAAAAADNDAAAA',6403.89 +'AAAAAAAAADPCAAAA',4246.28 +'AAAAAAAAAEBAAAAA',1946.34 +'AAAAAAAAAEBCAAAA',2021.69 +'AAAAAAAAAEBDAAAA',6664.64 +'AAAAAAAAAECCAAAA',11363.72 +'AAAAAAAAAECEAAAA',10198.96 +'AAAAAAAAAEFCAAAA',62.04 +'AAAAAAAAAEGDAAAA',9547.35 +'AAAAAAAAAEIBAAAA',3804.21 +'AAAAAAAAAEICAAAA',7097.51 +'AAAAAAAAAEKCAAAA',20954.40 +'AAAAAAAAAEPDAAAA',2162.31 +'AAAAAAAAAFBEAAAA',10916.01 +'AAAAAAAAAFCDAAAA',14657.18 +'AAAAAAAAAFDCAAAA',794.73 +'AAAAAAAAAFFAAAAA',9979.09 +'AAAAAAAAAFHCAAAA',2060.61 +'AAAAAAAAAFIAAAAA',705.60 +'AAAAAAAAAFJCAAAA',13594.18 +'AAAAAAAAAFMDAAAA',2354.86 +'AAAAAAAAAFNBAAAA',17475.06 +'AAAAAAAAAGBBAAAA',2052.41 +'AAAAAAAAAGCCAAAA',8040.70 +'AAAAAAAAAGCDAAAA',16455.28 +'AAAAAAAAAGDCAAAA',2495.73 +'AAAAAAAAAGDEAAAA',6750.35 +'AAAAAAAAAGFAAAAA',5985.82 +'AAAAAAAAAGHAAAAA',17532.15 +'AAAAAAAAAGHDAAAA',1220.67 +'AAAAAAAAAGJCAAAA',7564.93 +'AAAAAAAAAGKAAAAA',14082.90 +'AAAAAAAAAGKDAAAA',1891.00 +'AAAAAAAAAGNBAAAA',7856.78 +'AAAAAAAAAGOCAAAA',14920.31 +'AAAAAAAAAGPCAAAA',7986.28 +'AAAAAAAAAHCBAAAA',15074.94 +'AAAAAAAAAHECAAAA',11667.69 +'AAAAAAAAAHHDAAAA',2491.48 +'AAAAAAAAAHJDAAAA',4056.95 +'AAAAAAAAAHMAAAAA',15866.43 +'AAAAAAAAAHNDAAAA',7289.53 +'AAAAAAAAAIACAAAA',2603.30 +'AAAAAAAAAIBCAAAA',9.09 +'AAAAAAAAAIBEAAAA',6979.53 +'AAAAAAAAAIDAAAAA',32.70 +'AAAAAAAAAIDCAAAA',15307.29 +'AAAAAAAAAIIAAAAA',11268.14 +'AAAAAAAAAIIDAAAA',6837.36 +'AAAAAAAAAIJCAAAA',28318.16 +'AAAAAAAAAIMAAAAA',4013.06 +'AAAAAAAAAINBAAAA',7691.92 +'AAAAAAAAAINCAAAA',13190.42 +'AAAAAAAAAIODAAAA',17489.85 +'AAAAAAAAAIPDAAAA',6096.92 +'AAAAAAAAAJAEAAAA',30169.62 +'AAAAAAAAAJCCAAAA',17608.54 +'AAAAAAAAAJGBAAAA',14.10 +'AAAAAAAAAJJCAAAA',2763.55 +'AAAAAAAAAJMBAAAA',1013.43 +'AAAAAAAAAJMCAAAA',12220.00 +'AAAAAAAAAJNBAAAA',8076.70 +'AAAAAAAAAJODAAAA',5113.02 +'AAAAAAAAAKBAAAAA',2413.44 +'AAAAAAAAAKDAAAAA',15712.05 +---- TYPES +STRING, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q61.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q61.test b/testdata/workloads/tpcds/queries/tpcds-q61.test index 4ab922c..79198d5 100644 --- a/testdata/workloads/tpcds/queries/tpcds-q61.test +++ b/testdata/workloads/tpcds/queries/tpcds-q61.test @@ -1,6 +1,8 @@ ==== ---- QUERY: TPCDS-Q61 -select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100 +-- RESULT MISMATCH FROM ORIGINAL in DECIMAL value +-- FIXED. CAST RESULT QUOTIENT TO DECIMAL(15, 4), TAKE ACTUAL RESULT AS EXPECTED +select promotions,total,cast(promotions*100.00/total as decimal(15,4)) from (select sum(ss_ext_sales_price) promotions from store_sales @@ -41,7 +43,7 @@ from and d_moy = 1) all_sales order by promotions, total ---- RESULTS -969445.96,1772143.56,54.70470800909605765700 +969445.96,1772143.56,54.7047 ---- TYPES 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-q62.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q62.test b/testdata/workloads/tpcds/queries/tpcds-q62.test new file mode 100644 index 0000000..c961004 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q62.test @@ -0,0 +1,138 @@ +==== +---- QUERY: TPCDS-Q62 +select + substr(w_warehouse_name,1,20) + ,sm_type + ,web_name + ,sum(case when (ws_ship_date_sk - ws_sold_date_sk <= 30 ) then 1 else 0 end) as "30 days" + ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 30) and + (ws_ship_date_sk - ws_sold_date_sk <= 60) then 1 else 0 end ) as "31-60 days" + ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 60) and + (ws_ship_date_sk - ws_sold_date_sk <= 90) then 1 else 0 end) as "61-90 days" + ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 90) and + (ws_ship_date_sk - ws_sold_date_sk <= 120) then 1 else 0 end) as "91-120 days" + ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 120) then 1 else 0 end) as ">120 days" +from + web_sales + ,warehouse + ,ship_mode + ,web_site + ,date_dim +where + d_month_seq between 1200 and 1200 + 11 +and ws_ship_date_sk = d_date_sk +and ws_warehouse_sk = w_warehouse_sk +and ws_ship_mode_sk = sm_ship_mode_sk +and ws_web_site_sk = web_site_sk +group by + substr(w_warehouse_name,1,20) + ,sm_type + ,web_name +order by substr(w_warehouse_name,1,20) + ,sm_type + ,web_name +limit 100; +---- RESULTS +'Bad cards must make.','EXPRESS','site_0',251,248,268,277,0 +'Bad cards must make.','EXPRESS','site_1',262,304,290,267,0 +'Bad cards must make.','EXPRESS','site_2',287,290,293,286,0 +'Bad cards must make.','EXPRESS','site_3',283,260,267,290,0 +'Bad cards must make.','EXPRESS','site_4',281,310,309,321,0 +'Bad cards must make.','LIBRARY','site_0',254,265,226,231,0 +'Bad cards must make.','LIBRARY','site_1',198,231,211,201,0 +'Bad cards must make.','LIBRARY','site_2',181,172,190,176,0 +'Bad cards must make.','LIBRARY','site_3',196,214,173,226,0 +'Bad cards must make.','LIBRARY','site_4',241,241,247,236,0 +'Bad cards must make.','NEXT DAY','site_0',259,255,279,260,0 +'Bad cards must make.','NEXT DAY','site_1',287,334,295,300,0 +'Bad cards must make.','NEXT DAY','site_2',269,309,306,272,0 +'Bad cards must make.','NEXT DAY','site_3',281,322,308,277,0 +'Bad cards must make.','NEXT DAY','site_4',273,295,275,281,0 +'Bad cards must make.','OVERNIGHT','site_0',221,216,201,213,0 +'Bad cards must make.','OVERNIGHT','site_1',250,228,257,235,0 +'Bad cards must make.','OVERNIGHT','site_2',255,223,230,238,0 +'Bad cards must make.','OVERNIGHT','site_3',195,217,199,211,0 +'Bad cards must make.','OVERNIGHT','site_4',175,165,169,164,0 +'Bad cards must make.','REGULAR','site_0',176,239,220,233,0 +'Bad cards must make.','REGULAR','site_1',187,163,200,198,0 +'Bad cards must make.','REGULAR','site_2',190,231,195,208,0 +'Bad cards must make.','REGULAR','site_3',227,241,231,252,0 +'Bad cards must make.','REGULAR','site_4',258,245,260,245,0 +'Bad cards must make.','TWO DAY','site_0',222,235,235,223,0 +'Bad cards must make.','TWO DAY','site_1',264,248,237,227,0 +'Bad cards must make.','TWO DAY','site_2',181,230,231,207,0 +'Bad cards must make.','TWO DAY','site_3',182,158,209,185,0 +'Bad cards must make.','TWO DAY','site_4',203,218,198,221,0 +'Conventional childr','EXPRESS','site_0',311,308,336,287,0 +'Conventional childr','EXPRESS','site_1',250,269,273,260,0 +'Conventional childr','EXPRESS','site_2',255,280,235,276,0 +'Conventional childr','EXPRESS','site_3',269,292,303,278,0 +'Conventional childr','EXPRESS','site_4',275,324,268,330,0 +'Conventional childr','LIBRARY','site_0',212,211,225,250,0 +'Conventional childr','LIBRARY','site_1',218,249,244,204,0 +'Conventional childr','LIBRARY','site_2',203,215,222,205,0 +'Conventional childr','LIBRARY','site_3',163,181,180,193,0 +'Conventional childr','LIBRARY','site_4',189,208,207,191,0 +'Conventional childr','NEXT DAY','site_0',253,269,285,258,0 +'Conventional childr','NEXT DAY','site_1',289,244,254,269,0 +'Conventional childr','NEXT DAY','site_2',341,290,310,315,0 +'Conventional childr','NEXT DAY','site_3',309,294,328,270,0 +'Conventional childr','NEXT DAY','site_4',312,311,275,291,0 +'Conventional childr','OVERNIGHT','site_0',180,193,167,211,0 +'Conventional childr','OVERNIGHT','site_1',196,210,238,194,0 +'Conventional childr','OVERNIGHT','site_2',218,236,287,220,0 +'Conventional childr','OVERNIGHT','site_3',235,253,228,246,0 +'Conventional childr','OVERNIGHT','site_4',211,199,201,201,0 +'Conventional childr','REGULAR','site_0',209,262,232,207,0 +'Conventional childr','REGULAR','site_1',214,218,222,227,0 +'Conventional childr','REGULAR','site_2',166,193,184,183,0 +'Conventional childr','REGULAR','site_3',195,191,221,219,0 +'Conventional childr','REGULAR','site_4',248,236,244,256,0 +'Conventional childr','TWO DAY','site_0',192,225,232,225,0 +'Conventional childr','TWO DAY','site_1',248,239,233,251,0 +'Conventional childr','TWO DAY','site_2',230,214,237,201,0 +'Conventional childr','TWO DAY','site_3',236,190,190,222,0 +'Conventional childr','TWO DAY','site_4',197,159,202,176,0 +'Doors canno','EXPRESS','site_0',303,312,304,319,0 +'Doors canno','EXPRESS','site_1',321,278,313,297,0 +'Doors canno','EXPRESS','site_2',267,277,265,257,0 +'Doors canno','EXPRESS','site_3',263,245,258,285,0 +'Doors canno','EXPRESS','site_4',282,313,319,323,0 +'Doors canno','LIBRARY','site_0',201,210,201,200,0 +'Doors canno','LIBRARY','site_1',239,217,208,206,0 +'Doors canno','LIBRARY','site_2',229,233,244,241,0 +'Doors canno','LIBRARY','site_3',216,198,182,222,0 +'Doors canno','LIBRARY','site_4',176,163,170,216,0 +'Doors canno','NEXT DAY','site_0',289,287,289,311,0 +'Doors canno','NEXT DAY','site_1',253,293,256,274,0 +'Doors canno','NEXT DAY','site_2',275,242,265,261,0 +'Doors canno','NEXT DAY','site_3',347,299,296,278,0 +'Doors canno','NEXT DAY','site_4',280,300,325,334,0 +'Doors canno','OVERNIGHT','site_0',235,232,202,221,0 +'Doors canno','OVERNIGHT','site_1',189,177,179,174,0 +'Doors canno','OVERNIGHT','site_2',230,186,228,207,0 +'Doors canno','OVERNIGHT','site_3',231,251,228,242,0 +'Doors canno','OVERNIGHT','site_4',226,247,237,227,0 +'Doors canno','REGULAR','site_0',235,224,237,214,0 +'Doors canno','REGULAR','site_1',203,252,226,247,0 +'Doors canno','REGULAR','site_2',227,193,204,222,0 +'Doors canno','REGULAR','site_3',157,176,169,189,0 +'Doors canno','REGULAR','site_4',232,200,217,199,0 +'Doors canno','TWO DAY','site_0',190,165,199,200,0 +'Doors canno','TWO DAY','site_1',226,224,209,196,0 +'Doors canno','TWO DAY','site_2',229,236,220,235,0 +'Doors canno','TWO DAY','site_3',242,197,248,238,0 +'Doors canno','TWO DAY','site_4',204,213,230,221,0 +'Important issues liv','EXPRESS','site_0',240,250,239,260,0 +'Important issues liv','EXPRESS','site_1',291,287,283,311,0 +'Important issues liv','EXPRESS','site_2',274,312,300,293,0 +'Important issues liv','EXPRESS','site_3',282,298,307,285,0 +'Important issues liv','EXPRESS','site_4',285,271,268,290,0 +'Important issues liv','LIBRARY','site_0',218,230,209,201,0 +'Important issues liv','LIBRARY','site_1',165,160,153,182,0 +'Important issues liv','LIBRARY','site_2',213,209,234,222,0 +'Important issues liv','LIBRARY','site_3',237,258,219,238,0 +'Important issues liv','LIBRARY','site_4',224,243,277,237,0 +---- TYPES +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-q63.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q63.test b/testdata/workloads/tpcds/queries/tpcds-q63.test deleted file mode 100644 index a01e6a1..0000000 --- a/testdata/workloads/tpcds/queries/tpcds-q63.test +++ /dev/null @@ -1,140 +0,0 @@ -==== ----- QUERY: TPCDS-Q63 -select - * -from - (select - i_manager_id, - sum(ss_sales_price) sum_sales, - avg(sum(ss_sales_price)) over (partition by i_manager_id) 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_month_seq in (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11) - and ((i_category in ('Books', 'Children', 'Electronics') - and i_class in ('personal', 'portable', 'refernece', 'self-help') - and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) - or (i_category in ('Women', 'Music', 'Men') - and i_class in ('accessories', 'classical', 'fragrances', 'pants') - and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) - group by - i_manager_id, - 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 - i_manager_id, - avg_monthly_sales, - sum_sales -limit 100; ----- RESULTS -1,189.04,531.91 -1,203.73,531.91 -1,355.17,531.91 -1,359.53,531.91 -1,365.84,531.91 -1,373.26,531.91 -1,430.03,531.91 -1,698.60,531.91 -1,909.18,531.91 -1,995.02,531.91 -1,998.95,531.91 -2,34.79,625.03 -2,143.64,625.03 -2,253.85,625.03 -2,359.05,625.03 -2,445.59,625.03 -2,499.99,625.03 -2,538.36,625.03 -2,780.07,625.03 -2,1487.82,625.03 -2,1660.48,625.03 -3,270.10,988.29 -3,367.94,988.29 -3,491.20,988.29 -3,529.42,988.29 -3,629.34,988.29 -3,757.70,988.29 -3,1161.47,988.29 -3,1234.30,988.29 -3,1251.01,988.29 -3,1522.49,988.29 -3,2624.04,988.29 -5,353.24,1169.48 -5,581.28,1169.48 -5,694.79,1169.48 -5,696.66,1169.48 -5,740.99,1169.48 -5,890.86,1169.48 -5,955.61,1169.48 -5,1359.61,1169.48 -5,1712.35,1169.48 -5,1794.66,1169.48 -5,2080.16,1169.48 -5,2173.58,1169.48 -6,644.61,1772.99 -6,851.23,1772.99 -6,868.62,1772.99 -6,1056.06,1772.99 -6,1150.40,1772.99 -6,1196.50,1772.99 -6,1551.15,1772.99 -6,2576.64,1772.99 -6,2875.54,1772.99 -6,3299.70,1772.99 -6,3309.13,1772.99 -7,638.30,1499.65 -7,663.54,1499.65 -7,779.65,1499.65 -7,800.25,1499.65 -7,811.54,1499.65 -7,987.78,1499.65 -7,1046.24,1499.65 -7,1667.92,1499.65 -7,2750.32,1499.65 -7,2970.80,1499.65 -7,3524.00,1499.65 -8,803.53,1618.95 -8,819.18,1618.95 -8,933.76,1618.95 -8,944.19,1618.95 -8,1019.24,1618.95 -8,1159.06,1618.95 -8,1290.94,1618.95 -8,2168.17,1618.95 -8,2264.18,1618.95 -8,3033.41,1618.95 -8,3213.77,1618.95 -9,315.31,1509.97 -9,559.82,1509.97 -9,728.72,1509.97 -9,784.15,1509.97 -9,923.36,1509.97 -9,933.68,1509.97 -9,1239.49,1509.97 -9,1848.85,1509.97 -9,2172.04,1509.97 -9,2178.07,1509.97 -9,3165.98,1509.97 -9,3270.26,1509.97 -10,314.30,1125.25 -10,365.57,1125.25 -10,591.30,1125.25 -10,760.81,1125.25 -10,776.91,1125.25 -10,959.65,1125.25 -10,975.25,1125.25 -10,1294.20,1125.25 -10,1342.05,1125.25 -10,1914.64,1125.25 -10,3105.53,1125.25 ----- TYPES -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-q64.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q64.test b/testdata/workloads/tpcds/queries/tpcds-q64.test new file mode 100644 index 0000000..53d454f --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q64.test @@ -0,0 +1,144 @@ +==== +---- QUERY: TPCDS-Q64 +-- RESULT MISMATCH FROM ORIGINAL +-- ADDED ORDER BY COLUMNS. +with cs_ui as + (select cs_item_sk + ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund + from catalog_sales + ,catalog_returns + where cs_item_sk = cr_item_sk + and cs_order_number = cr_order_number + group by cs_item_sk + having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)), +cross_sales as + (select i_product_name product_name + ,i_item_sk item_sk + ,s_store_name store_name + ,s_zip store_zip + ,ad1.ca_street_number b_street_number + ,ad1.ca_street_name b_street_name + ,ad1.ca_city b_city + ,ad1.ca_zip b_zip + ,ad2.ca_street_number c_street_number + ,ad2.ca_street_name c_street_name + ,ad2.ca_city c_city + ,ad2.ca_zip c_zip + ,d1.d_year as syear + ,d2.d_year as fsyear + ,d3.d_year s2year + ,count(*) cnt + ,sum(ss_wholesale_cost) s1 + ,sum(ss_list_price) s2 + ,sum(ss_coupon_amt) s3 + FROM store_sales + ,store_returns + ,cs_ui + ,date_dim d1 + ,date_dim d2 + ,date_dim d3 + ,store + ,customer + ,customer_demographics cd1 + ,customer_demographics cd2 + ,promotion + ,household_demographics hd1 + ,household_demographics hd2 + ,customer_address ad1 + ,customer_address ad2 + ,income_band ib1 + ,income_band ib2 + ,item + WHERE ss_store_sk = s_store_sk AND + ss_sold_date_sk = d1.d_date_sk AND + ss_customer_sk = c_customer_sk AND + ss_cdemo_sk= cd1.cd_demo_sk AND + ss_hdemo_sk = hd1.hd_demo_sk AND + ss_addr_sk = ad1.ca_address_sk and + ss_item_sk = i_item_sk and + ss_item_sk = sr_item_sk and + ss_ticket_number = sr_ticket_number and + ss_item_sk = cs_ui.cs_item_sk and + c_current_cdemo_sk = cd2.cd_demo_sk AND + c_current_hdemo_sk = hd2.hd_demo_sk AND + c_current_addr_sk = ad2.ca_address_sk and + c_first_sales_date_sk = d2.d_date_sk and + c_first_shipto_date_sk = d3.d_date_sk and + ss_promo_sk = p_promo_sk and + hd1.hd_income_band_sk = ib1.ib_income_band_sk and + hd2.hd_income_band_sk = ib2.ib_income_band_sk and + cd1.cd_marital_status <> cd2.cd_marital_status and + i_color in ('purple','burlywood','indian','spring','floral','medium') and + i_current_price between 64 and 64 + 10 and + i_current_price between 64 + 1 and 64 + 15 +group by i_product_name + ,i_item_sk + ,s_store_name + ,s_zip + ,ad1.ca_street_number + ,ad1.ca_street_name + ,ad1.ca_city + ,ad1.ca_zip + ,ad2.ca_street_number + ,ad2.ca_street_name + ,ad2.ca_city + ,ad2.ca_zip + ,d1.d_year + ,d2.d_year + ,d3.d_year +) +select cs1.product_name + ,cs1.store_name + ,cs1.store_zip + ,cs1.b_street_number + ,cs1.b_street_name + ,cs1.b_city + ,cs1.b_zip + ,cs1.c_street_number + ,cs1.c_street_name + ,cs1.c_city + ,cs1.c_zip + ,cs1.syear + ,cs1.cnt + ,cs1.s1 as s11 + ,cs1.s2 as s21 + ,cs1.s3 as s31 + ,cs2.s1 as s12 + ,cs2.s2 as s22 + ,cs2.s3 as s32 + ,cs2.syear + ,cs2.cnt +from cross_sales cs1,cross_sales cs2 +where cs1.item_sk=cs2.item_sk and + cs1.syear = 1999 and + cs2.syear = 1999 + 1 and + cs2.cnt <= cs1.cnt and + cs1.store_name = cs2.store_name and + cs1.store_zip = cs2.store_zip +order by cs1.product_name + ,cs1.store_name + ,cs2.cnt + -- Added to fix result ordering + ,b_street_number desc + ,b_street_name desc + ,b_zip desc + ,s11 desc + ,s21 desc + ,s31 desc + ,s12 desc + ,s22 desc + ,s32 desc; +---- RESULTS +'n stableableantiought','able','31904','987','Hillcrest ','Fairbanks','46653','216','3rd ','Reno','40344',1999,1,55.42,80.91,954.53,59.08,76.80,0.00,2000,1 +'n stableableantiought','ation','31904','425','Green ','Enterprise','11757','772','Valley ','NULL','NULL',1999,1,16.24,25.17,0.00,44.27,58.43,0.00,2000,1 +'n stableableantiought','ation','31904','425','Green ','Enterprise','11757','772','Valley ','NULL','NULL',1999,1,16.24,25.17,0.00,7.92,11.80,0.00,2000,1 +'n stableableantiought','ation','31904','316','Valley Tenth','Pine Grove','74593','806','Wilson Main','Jackson','59583',1999,1,49.63,78.41,14.22,44.27,58.43,0.00,2000,1 +'n stableableantiought','ation','31904','316','Valley Tenth','Pine Grove','74593','806','Wilson Main','Jackson','59583',1999,1,49.63,78.41,14.22,7.92,11.80,0.00,2000,1 +'n stableableantiought','ation','31904','173','Park Maple','Sulphur Springs','68354','232','Franklin ','Fairfield','66192',1999,1,60.78,85.09,0.00,44.27,58.43,0.00,2000,1 +'n stableableantiought','ation','31904','173','Park Maple','Sulphur Springs','68354','232','Franklin ','Fairfield','66192',1999,1,60.78,85.09,0.00,7.92,11.80,0.00,2000,1 +'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,58.19,85.53,0.00,2000,1 +'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,30.28,49.65,0.00,2000,1 +'n stableableantiought','eing','35709','928','First Oak','Summit','40499','178','Johnson Hillcrest','Oakdale','59584',1999,1,63.82,121.25,0.00,10.25,12.09,0.00,2000,1 +---- TYPES +STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, INT, BIGINT, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, INT, BIGINT +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q67a.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q67a.test b/testdata/workloads/tpcds/queries/tpcds-q67a.test new file mode 100644 index 0000000..f66c1f2 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q67a.test @@ -0,0 +1,175 @@ +==== +---- QUERY: TPCDS-Q67A +with results as +( select i_category ,i_class ,i_brand ,i_product_name ,d_year ,d_qoy ,d_moy ,s_store_id + ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales + from store_sales ,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 d_month_seq between 1200 and 1200 + 11 + group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id) + , + results_rollup as + (select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id, sumsales + from results + union all + select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, null s_store_id, sum(sumsales) sumsales + from results + group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy + union all + select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales + from results + group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy + union all + select i_category, i_class, i_brand, i_product_name, d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales + from results + group by i_category, i_class, i_brand, i_product_name, d_year + union all + select i_category, i_class, i_brand, i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales + from results + group by i_category, i_class, i_brand, i_product_name + union all + select i_category, i_class, i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales + from results + group by i_category, i_class, i_brand + union all + select i_category, i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales + from results + group by i_category, i_class + union all + select i_category, null i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales + from results + group by i_category + union all + select null i_category, null i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales + from results) + + select * +from (select i_category + ,i_class + ,i_brand + ,i_product_name + ,d_year + ,d_qoy + ,d_moy + ,s_store_id + ,sumsales + ,rank() over (partition by i_category order by sumsales desc) rk + from results_rollup) dw2 +where rk <= 100 +order by i_category + ,i_class + ,i_brand + ,i_product_name + ,d_year + ,d_qoy + ,d_moy + ,s_store_id + ,sumsales + ,rk +limit 100; +---- RESULTS +'Books','arts','amalgmaxi #12','NULL',NULL,NULL,NULL,'NULL',1577322.20,26 +'Books','arts','amalgmaxi #2','NULL',NULL,NULL,NULL,'NULL',757558.15,93 +'Books','arts','amalgmaxi #3','NULL',NULL,NULL,NULL,'NULL',889848.65,79 +'Books','arts','amalgmaxi #9','NULL',NULL,NULL,NULL,'NULL',816219.08,89 +'Books','arts','NULL','NULL',NULL,NULL,NULL,'NULL',5188162.15,17 +'Books','business','importomaxi #12','NULL',NULL,NULL,NULL,'NULL',1288307.29,42 +'Books','business','importomaxi #2','NULL',NULL,NULL,NULL,'NULL',985887.08,68 +'Books','business','importomaxi #8','NULL',NULL,NULL,NULL,'NULL',1067725.65,61 +'Books','business','importomaxi #9','NULL',NULL,NULL,NULL,'NULL',1269598.91,45 +'Books','business','NULL','NULL',NULL,NULL,NULL,'NULL',5905977.34,10 +'Books','computers','exportimaxi #12','NULL',NULL,NULL,NULL,'NULL',1456918.73,28 +'Books','computers','exportimaxi #2','NULL',NULL,NULL,NULL,'NULL',1626664.04,24 +'Books','computers','exportimaxi #3','NULL',NULL,NULL,NULL,'NULL',875667.64,82 +'Books','computers','exportimaxi #6','NULL',NULL,NULL,NULL,'NULL',847659.03,86 +'Books','computers','exportimaxi #8','NULL',NULL,NULL,NULL,'NULL',1189646.65,49 +'Books','computers','exportimaxi #9','NULL',NULL,NULL,NULL,'NULL',1059845.98,63 +'Books','computers','NULL','NULL',NULL,NULL,NULL,'NULL',7061536.03,7 +'Books','cooking','amalgunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1097730.00,58 +'Books','cooking','amalgunivamalg #3','NULL',NULL,NULL,NULL,'NULL',978431.47,69 +'Books','cooking','amalgunivamalg #6','NULL',NULL,NULL,NULL,'NULL',858562.34,84 +'Books','cooking','amalgunivamalg #8','NULL',NULL,NULL,NULL,'NULL',917692.00,74 +'Books','cooking','amalgunivamalg #9','NULL',NULL,NULL,NULL,'NULL',881045.30,80 +'Books','cooking','NULL','NULL',NULL,NULL,NULL,'NULL',5357549.49,16 +'Books','entertainments','edu packmaxi #12','NULL',NULL,NULL,NULL,'NULL',962267.01,70 +'Books','entertainments','edu packmaxi #2','NULL',NULL,NULL,NULL,'NULL',750249.81,94 +'Books','entertainments','edu packmaxi #3','NULL',NULL,NULL,NULL,'NULL',1325140.63,39 +'Books','entertainments','edu packmaxi #6','NULL',NULL,NULL,NULL,'NULL',874454.87,83 +'Books','entertainments','edu packmaxi #8','NULL',NULL,NULL,NULL,'NULL',894416.62,78 +'Books','entertainments','NULL','NULL',NULL,NULL,NULL,'NULL',5465761.48,14 +'Books','fiction','scholarunivamalg #12','NULL',NULL,NULL,NULL,'NULL',957012.63,71 +'Books','fiction','scholarunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1798976.95,20 +'Books','fiction','scholarunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1288272.89,43 +'Books','fiction','scholarunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1031571.39,65 +'Books','fiction','scholarunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1444468.85,30 +'Books','fiction','scholarunivamalg #9','NULL',NULL,NULL,NULL,'NULL',1333004.22,38 +'Books','fiction','NULL','NULL',NULL,NULL,NULL,'NULL',7867516.44,3 +'Books','history','scholarmaxi #12','NULL',NULL,NULL,NULL,'NULL',1407800.96,34 +'Books','history','scholarmaxi #2','NULL',NULL,NULL,NULL,'NULL',1601550.87,25 +'Books','history','scholarmaxi #3','NULL',NULL,NULL,NULL,'NULL',1181491.20,50 +'Books','history','scholarmaxi #6','NULL',NULL,NULL,NULL,'NULL',1175904.65,51 +'Books','history','scholarmaxi #8','NULL',NULL,NULL,NULL,'NULL',1205167.13,48 +'Books','history','scholarmaxi #9','NULL',NULL,NULL,NULL,'NULL',1873407.99,18 +'Books','history','NULL','NULL',NULL,NULL,NULL,'NULL',8458815.11,2 +'Books','home repair','importounivamalg #12','NULL',NULL,NULL,NULL,'NULL',929871.46,72 +'Books','home repair','importounivamalg #2','NULL',NULL,NULL,NULL,'NULL',1672201.56,22 +'Books','home repair','importounivamalg #3','NULL',NULL,NULL,NULL,'NULL',1321000.94,40 +'Books','home repair','importounivamalg #6','NULL',NULL,NULL,NULL,'NULL',717528.04,96 +'Books','home repair','importounivamalg #8','NULL',NULL,NULL,NULL,'NULL',1669033.95,23 +'Books','home repair','importounivamalg #9','NULL',NULL,NULL,NULL,'NULL',1066519.11,62 +'Books','home repair','NULL','NULL',NULL,NULL,NULL,'NULL',7388154.74,4 +'Books','mystery','corpunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1146116.05,54 +'Books','mystery','corpunivamalg #2','NULL',NULL,NULL,NULL,'NULL',747999.99,95 +'Books','mystery','corpunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1145257.96,55 +'Books','mystery','corpunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1098189.71,57 +'Books','mystery','corpunivamalg #8','NULL',NULL,NULL,NULL,'NULL',759890.03,92 +'Books','mystery','NULL','NULL',NULL,NULL,NULL,'NULL',5559086.76,13 +'Books','parenting','corpmaxi #12','NULL',NULL,NULL,NULL,'NULL',1022503.60,66 +'Books','parenting','corpmaxi #2','NULL',NULL,NULL,NULL,'NULL',908607.31,76 +'Books','parenting','corpmaxi #6','NULL',NULL,NULL,NULL,'NULL',1717275.15,21 +'Books','parenting','corpmaxi #9','NULL',NULL,NULL,NULL,'NULL',895829.36,77 +'Books','parenting','NULL','NULL',NULL,NULL,NULL,'NULL',5905648.45,11 +'Books','reference','brandmaxi #12','NULL',NULL,NULL,NULL,'NULL',1418372.80,33 +'Books','reference','brandmaxi #2','NULL',NULL,NULL,NULL,'NULL',1154379.77,53 +'Books','reference','brandmaxi #3','NULL',NULL,NULL,NULL,'NULL',1168210.15,52 +'Books','reference','brandmaxi #6','NULL',NULL,NULL,NULL,'NULL',1349780.84,37 +'Books','reference','brandmaxi #8','NULL',NULL,NULL,NULL,'NULL',1429502.89,31 +'Books','reference','brandmaxi #9','NULL',NULL,NULL,NULL,'NULL',714094.43,97 +'Books','reference','NULL','NULL',NULL,NULL,NULL,'NULL',7245435.64,6 +'Books','romance','namelessmaxi #12','NULL',NULL,NULL,NULL,'NULL',1356568.45,36 +'Books','romance','namelessmaxi #2','NULL',NULL,NULL,NULL,'NULL',1236630.16,47 +'Books','romance','namelessmaxi #3','NULL',NULL,NULL,NULL,'NULL',1866764.20,19 +'Books','romance','namelessmaxi #6','NULL',NULL,NULL,NULL,'NULL',1086707.92,59 +'Books','romance','namelessmaxi #8','NULL',NULL,NULL,NULL,'NULL',825762.40,88 +'Books','romance','namelessmaxi #9','NULL',NULL,NULL,NULL,'NULL',876869.83,81 +'Books','romance','NULL','NULL',NULL,NULL,NULL,'NULL',7252274.41,5 +'Books','science','maximaxi #2','NULL',NULL,NULL,NULL,'NULL',702171.30,98 +'Books','science','maximaxi #3','NULL',NULL,NULL,NULL,'NULL',777021.93,90 +'Books','science','maximaxi #6','NULL',NULL,NULL,NULL,'NULL',1446589.43,29 +'Books','science','maximaxi #8','NULL',NULL,NULL,NULL,'NULL',1004042.04,67 +'Books','science','maximaxi #9','NULL',NULL,NULL,NULL,'NULL',849422.20,85 +'Books','science','NULL','NULL',NULL,NULL,NULL,'NULL',5359276.19,15 +'Books','self-help','exportiunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1283148.53,44 +'Books','self-help','exportiunivamalg #3','NULL',NULL,NULL,NULL,'NULL',770181.59,91 +'Books','self-help','exportiunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1391623.86,35 +'Books','self-help','exportiunivamalg #8','NULL',NULL,NULL,NULL,'NULL',695644.62,100 +'Books','self-help','exportiunivamalg #9','NULL',NULL,NULL,NULL,'NULL',1427999.91,32 +'Books','self-help','NULL','NULL',NULL,NULL,NULL,'NULL',6261649.72,8 +'Books','sports','edu packunivamalg #12','NULL',NULL,NULL,NULL,'NULL',928301.94,73 +'Books','sports','edu packunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1503681.79,27 +'Books','sports','edu packunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1263974.84,46 +'Books','sports','edu packunivamalg #9','NULL',NULL,NULL,NULL,'NULL',840684.29,87 +'Books','sports','NULL','NULL',NULL,NULL,NULL,'NULL',5610649.06,12 +'Books','travel','univunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1135250.45,56 +'Books','travel','univunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1053533.86,64 +'Books','travel','univunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1085484.15,60 +'Books','travel','univunivamalg #6','NULL',NULL,NULL,NULL,'NULL',696575.97,99 +'Books','travel','univunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1310411.80,41 +'Books','travel','univunivamalg #9','NULL',NULL,NULL,NULL,'NULL',917487.24,75 +'Books','travel','NULL','NULL',NULL,NULL,NULL,'NULL',6200882.35,9 +'Books','NULL','NULL','NULL',NULL,NULL,NULL,'NULL',102246971.86,1 +---- TYPES +STRING, STRING, STRING, STRING, INT, INT, INT, STRING, DECIMAL, BIGINT +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q69.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q69.test b/testdata/workloads/tpcds/queries/tpcds-q69.test new file mode 100644 index 0000000..32c943f --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q69.test @@ -0,0 +1,150 @@ +==== +---- QUERY: TPCDS-Q69 +select + cd_gender, + cd_marital_status, + cd_education_status, + count(*) cnt1, + cd_purchase_estimate, + count(*) cnt2, + cd_credit_rating, + count(*) cnt3 + from + customer c,customer_address ca,customer_demographics + where + c.c_current_addr_sk = ca.ca_address_sk and + ca_state in ('KY','GA','NM') and + cd_demo_sk = c.c_current_cdemo_sk and + exists (select * + from store_sales,date_dim + where c.c_customer_sk = ss_customer_sk and + ss_sold_date_sk = d_date_sk and + d_year = 2001 and + d_moy between 4 and 4+2) and + (not exists (select * + from web_sales,date_dim + where c.c_customer_sk = ws_bill_customer_sk and + ws_sold_date_sk = d_date_sk and + d_year = 2001 and + d_moy between 4 and 4+2) and + not exists (select * + from catalog_sales,date_dim + where c.c_customer_sk = cs_ship_customer_sk and + cs_sold_date_sk = d_date_sk and + d_year = 2001 and + d_moy between 4 and 4+2)) + group by cd_gender, + cd_marital_status, + cd_education_status, + cd_purchase_estimate, + cd_credit_rating + order by cd_gender, + cd_marital_status, + cd_education_status, + cd_purchase_estimate, + cd_credit_rating + limit 100; +---- RESULTS +'F','D','2 yr Degree',1,2500,1,'Low Risk',1 +'F','D','2 yr Degree',1,4500,1,'Good',1 +'F','D','2 yr Degree',2,4500,2,'Low Risk',2 +'F','D','2 yr Degree',1,9000,1,'Good',1 +'F','D','2 yr Degree',1,9500,1,'Good',1 +'F','D','4 yr Degree',1,1000,1,'Low Risk',1 +'F','D','4 yr Degree',1,1500,1,'Low Risk',1 +'F','D','4 yr Degree',1,1500,1,'Unknown',1 +'F','D','4 yr Degree',1,2000,1,'Good',1 +'F','D','4 yr Degree',1,2500,1,'Unknown',1 +'F','D','4 yr Degree',1,4000,1,'Unknown',1 +'F','D','4 yr Degree',1,5500,1,'Good',1 +'F','D','4 yr Degree',1,7000,1,'Good',1 +'F','D','4 yr Degree',1,7000,1,'High Risk',1 +'F','D','4 yr Degree',1,8000,1,'High Risk',1 +'F','D','4 yr Degree',1,8500,1,'Unknown',1 +'F','D','Advanced Degree',1,1000,1,'Good',1 +'F','D','Advanced Degree',1,3500,1,'Good',1 +'F','D','Advanced Degree',1,3500,1,'High Risk',1 +'F','D','Advanced Degree',1,4000,1,'High Risk',1 +'F','D','Advanced Degree',1,5000,1,'High Risk',1 +'F','D','Advanced Degree',1,5500,1,'Good',1 +'F','D','Advanced Degree',1,5500,1,'Low Risk',1 +'F','D','Advanced Degree',1,6500,1,'Low Risk',1 +'F','D','Advanced Degree',1,7000,1,'High Risk',1 +'F','D','Advanced Degree',1,8000,1,'Low Risk',1 +'F','D','Advanced Degree',1,8500,1,'High Risk',1 +'F','D','Advanced Degree',1,9500,1,'Low Risk',1 +'F','D','Advanced Degree',1,9500,1,'Unknown',1 +'F','D','Advanced Degree',1,10000,1,'Low Risk',1 +'F','D','College',1,1500,1,'Low Risk',1 +'F','D','College',1,2500,1,'Good',1 +'F','D','College',1,4000,1,'High Risk',1 +'F','D','College',1,6000,1,'Good',1 +'F','D','College',1,6000,1,'High Risk',1 +'F','D','College',1,9000,1,'High Risk',1 +'F','D','Primary',1,3000,1,'High Risk',1 +'F','D','Primary',1,4000,1,'Low Risk',1 +'F','D','Primary',1,4000,1,'Unknown',1 +'F','D','Primary',1,4500,1,'Unknown',1 +'F','D','Primary',1,5000,1,'High Risk',1 +'F','D','Primary',1,5000,1,'Unknown',1 +'F','D','Primary',1,6000,1,'Good',1 +'F','D','Primary',1,6000,1,'Unknown',1 +'F','D','Primary',1,8500,1,'Unknown',1 +'F','D','Primary',1,9000,1,'Unknown',1 +'F','D','Primary',1,9500,1,'High Risk',1 +'F','D','Primary',1,9500,1,'Unknown',1 +'F','D','Primary',1,10000,1,'Good',1 +'F','D','Primary',2,10000,2,'Low Risk',2 +'F','D','Secondary',1,500,1,'Low Risk',1 +'F','D','Secondary',1,1000,1,'High Risk',1 +'F','D','Secondary',1,3000,1,'Good',1 +'F','D','Secondary',1,3500,1,'Good',1 +'F','D','Secondary',1,6000,1,'Low Risk',1 +'F','D','Secondary',1,6500,1,'Unknown',1 +'F','D','Secondary',1,8500,1,'High Risk',1 +'F','D','Secondary',1,10000,1,'Low Risk',1 +'F','D','Unknown',1,1500,1,'High Risk',1 +'F','D','Unknown',1,3000,1,'Good',1 +'F','D','Unknown',1,3000,1,'High Risk',1 +'F','D','Unknown',1,6000,1,'Good',1 +'F','D','Unknown',1,6500,1,'High Risk',1 +'F','D','Unknown',2,8000,2,'Low Risk',2 +'F','D','Unknown',1,10000,1,'Unknown',1 +'F','M','2 yr Degree',1,2500,1,'Low Risk',1 +'F','M','2 yr Degree',3,4000,3,'Unknown',3 +'F','M','2 yr Degree',1,4500,1,'Low Risk',1 +'F','M','2 yr Degree',1,8500,1,'High Risk',1 +'F','M','4 yr Degree',1,1000,1,'Good',1 +'F','M','4 yr Degree',1,1500,1,'Good',1 +'F','M','4 yr Degree',1,4000,1,'Low Risk',1 +'F','M','4 yr Degree',1,4000,1,'Unknown',1 +'F','M','4 yr Degree',1,5000,1,'Unknown',1 +'F','M','4 yr Degree',1,5500,1,'Low Risk',1 +'F','M','4 yr Degree',1,6000,1,'Good',1 +'F','M','4 yr Degree',1,8000,1,'High Risk',1 +'F','M','Advanced Degree',1,500,1,'Low Risk',1 +'F','M','Advanced Degree',1,500,1,'Unknown',1 +'F','M','Advanced Degree',1,2500,1,'Good',1 +'F','M','Advanced Degree',1,3500,1,'High Risk',1 +'F','M','Advanced Degree',2,5500,2,'Unknown',2 +'F','M','Advanced Degree',1,6500,1,'Good',1 +'F','M','Advanced Degree',1,6500,1,'Unknown',1 +'F','M','Advanced Degree',1,8000,1,'Low Risk',1 +'F','M','Advanced Degree',1,9500,1,'Good',1 +'F','M','Advanced Degree',1,10000,1,'Low Risk',1 +'F','M','College',1,1000,1,'Unknown',1 +'F','M','College',1,3000,1,'Good',1 +'F','M','College',2,5000,2,'High Risk',2 +'F','M','College',1,6000,1,'Low Risk',1 +'F','M','College',1,7000,1,'High Risk',1 +'F','M','College',1,8000,1,'Good',1 +'F','M','College',1,9000,1,'Good',1 +'F','M','College',2,9000,2,'High Risk',2 +'F','M','College',1,9500,1,'High Risk',1 +'F','M','College',1,10000,1,'High Risk',1 +'F','M','Primary',1,1000,1,'Low Risk',1 +'F','M','Primary',1,1500,1,'High Risk',1 +'F','M','Primary',1,7000,1,'Good',1 +---- TYPES +STRING, STRING, STRING, BIGINT, INT, BIGINT, STRING, BIGINT +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q70a.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q70a.test b/testdata/workloads/tpcds/queries/tpcds-q70a.test new file mode 100644 index 0000000..8804007 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q70a.test @@ -0,0 +1,49 @@ +==== +---- QUERY: TPCDS-Q70A +with results as +( select + sum(ss_net_profit) as total_sum ,s_state ,s_county, 0 as gstate, 0 as g_county + from + store_sales + ,date_dim d1 + ,store + where + d1.d_month_seq between 1200 and 1200 + 11 + and d1.d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + and s_state in + ( select s_state + from (select s_state as s_state, + rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking + from store_sales, store, date_dim + where d_month_seq between 1200 and 1200 + 11 + and d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + group by s_state + ) tmp1 + where ranking <= 5) + group by s_state,s_county) , + results_rollup as +(select total_sum ,s_state ,s_county, 0 as g_state, 0 as g_county, 0 as lochierarchy from results + union + select sum(total_sum) as total_sum,s_state, NULL as s_county, 0 as g_state, 1 as g_county, 1 as lochierarchy from results group by s_state + union + select sum(total_sum) as total_sum ,NULL as s_state ,NULL as s_county, 1 as g_state, 1 as g_county, 2 as lochierarchy from results) + select total_sum ,s_state ,s_county, lochierarchy + ,rank() over ( + partition by lochierarchy, + case when g_county = 0 then s_state end + order by total_sum desc) as rank_within_parent + from results_rollup + order by + lochierarchy desc + ,case when lochierarchy = 0 then s_state end + ,rank_within_parent + limit 100; +---- RESULTS +-440986113.22,'NULL','NULL',2,1 +-440986113.22,'TN','NULL',1,1 +-440986113.22,'TN','Williamson County',0,1 +---- TYPES +DECIMAL, STRING, STRING, TINYINT, BIGINT +====
