IMPALA-5946,IMPALA-5956: add TPC-DS q31,q59,q89 Q31: the substitution variables didn't match the TPC-DS spec. After fixing this, the results match up to 4 digits of rounding (there is some error introduced in intermediate calculations). Q59: the results match the reference results up to rounding. Q89: the results match up to 5 digits of rounding.
I verified the matches by using a spreadsheet comparing reference and actual results. https://docs.google.com/spreadsheets/d/1MNEqkfYRRAd3xqY6m20tTHquqjtCThDaGdizzRAQ8co/edit?usp=sharing *https://github.com/gregrahn/tpcds-kit/blob/master/specification/TPC-DS_v2.10.0.pdf ^https://github.com/gregrahn/tpcds-kit/tree/master/answer_sets Change-Id: I49634e8f63066773c9c78dd5585a0ee69daf720a Reviewed-on: http://gerrit.cloudera.org:8080/11845 Reviewed-by: Impala Public Jenkins <impala-public-jenk...@cloudera.com> Tested-by: Impala Public Jenkins <impala-public-jenk...@cloudera.com> Project: http://git-wip-us.apache.org/repos/asf/impala/repo Commit: http://git-wip-us.apache.org/repos/asf/impala/commit/c8c06dd4 Tree: http://git-wip-us.apache.org/repos/asf/impala/tree/c8c06dd4 Diff: http://git-wip-us.apache.org/repos/asf/impala/diff/c8c06dd4 Branch: refs/heads/branch-3.1.0 Commit: c8c06dd43a7a215a5d6b654a087c2e82013efbc3 Parents: 7616083 Author: Tim Armstrong <tarmstr...@cloudera.com> Authored: Wed Oct 31 12:05:42 2018 -0700 Committer: Zoltan Borok-Nagy <borokna...@cloudera.com> Committed: Tue Nov 13 12:50:23 2018 +0100 ---------------------------------------------------------------------- .../tpcds-unmodified/queries/tpcds-q31.test | 69 +++++- .../tpcds-unmodified/queries/tpcds-q59.test | 227 +++++++++++++++---- .../tpcds-unmodified/queries/tpcds-q89.test | 119 +++++++++- tests/query_test/test_tpcds_queries.py | 9 + 4 files changed, 358 insertions(+), 66 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/impala/blob/c8c06dd4/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test index 07d519e..bc74dfa 100644 --- a/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q31.test @@ -29,30 +29,79 @@ with ss as ,ws ws3 where ss1.d_qoy = 1 - and ss1.d_year = 2002 + and ss1.d_year = 2000 and ss1.ca_county = ss2.ca_county and ss2.d_qoy = 2 - and ss2.d_year = 2002 + and ss2.d_year = 2000 and ss2.ca_county = ss3.ca_county and ss3.d_qoy = 3 - and ss3.d_year = 2002 + and ss3.d_year = 2000 and ss1.ca_county = ws1.ca_county and ws1.d_qoy = 1 - and ws1.d_year = 2002 + and ws1.d_year = 2000 and ws1.ca_county = ws2.ca_county and ws2.d_qoy = 2 - and ws2.d_year = 2002 + and ws2.d_year = 2000 and ws1.ca_county = ws3.ca_county and ws3.d_qoy = 3 - and ws3.d_year =2002 + and ws3.d_year =2000 and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end - order by ss1.d_year; - - + order by ss1.ca_county; ---- RESULTS +'Atchison County',2000,0.800221,0.242964,11.944560,3.162954 +'Bacon County',2000,1.168826,0.394038,0.968800,0.510743 +'Bourbon County',2000,1.913115,0.981928,3.364802,1.380123 +'Boyd County',2000,1.086324,0.811507,1.168972,0.742057 +'Bradley County',2000,1.489632,0.575785,1.344390,0.998919 +'Buchanan County',2000,1.195667,0.746049,3.332904,2.239788 +'Carter County',2000,3.953739,1.151033,2.119285,1.844491 +'Cass County',2000,2.398720,1.190582,2.257169,0.847801 +'Corson County',2000,0.560309,0.175081,4.807409,3.227141 +'Crockett County',2000,1.637106,0.360468,2.134042,1.832451 +'Culpeper County',2000,0.661751,0.619014,1.659248,1.224169 +'Edmonson County',2000,0.732442,0.299753,1.602897,1.491246 +'Ferry County',2000,0.701373,0.341010,4.002919,2.603464 +'Fillmore County',2000,0.507769,0.349921,2.443162,1.301136 +'Forest County',2000,0.644745,0.342367,5.771027,1.881074 +'Gaston County',2000,0.763774,0.455496,3.955611,2.141572 +'Grant County',2000,0.693336,0.622870,1.788644,1.722197 +'Green County',2000,0.763667,0.321499,4.694152,4.207034 +'Harlan County',2000,1.670355,1.590115,2.471959,2.107294 +'Harris County',2000,2.337569,0.333126,2.417058,1.025794 +'Heard County',2000,4.102554,1.266947,3.500228,1.127804 +'Houston County',2000,2.045326,1.038983,1.965051,1.421193 +'Ingham County',2000,0.574390,0.384114,1.306517,0.992061 +'Lake County',2000,1.255077,0.745928,1.515147,1.265662 +'Lamar County',2000,0.749358,0.745617,4.269036,2.029584 +'Lincoln County',2000,1.019137,0.944845,2.335960,1.776928 +'Marion County',2000,1.158999,0.916511,2.445040,1.851073 +'Mercer County',2000,0.738342,0.601678,3.012450,2.724470 +'Meriwether County',2000,0.365748,0.300417,2.772270,0.787769 +'Miller County',2000,2.575745,1.318273,2.191968,0.982218 +'Mitchell County',2000,4.439233,1.161335,1.394036,1.256074 +'Mora County',2000,1.183261,0.635654,2.513120,0.918567 +'Nantucket County',2000,1.437897,0.722692,1.175488,0.962304 +'New Kent County',2000,0.602616,0.399062,2.869386,2.625895 +'Nicholas County',2000,2.165116,2.056274,6.021300,1.262577 +'Otero County',2000,2.754655,1.246299,2.976111,2.245835 +'Oxford County',2000,0.973143,0.757240,4.012687,1.640751 +'Perry County',2000,1.580780,0.764453,2.153370,1.802410 +'Prince William County',2000,3.376373,0.630787,1.706967,0.934324 +'Refugio County',2000,1.812976,0.586732,1.301984,1.269604 +'Rice County',2000,1.134698,0.733018,2.378149,1.986402 +'Richmond County',2000,1.571665,1.294015,2.309592,1.778016 +'Sheridan County',2000,1.386030,1.250657,1.575936,0.537886 +'Smith County',2000,0.636936,0.427882,5.744845,4.477958 +'Stark County',2000,7.338220,1.417589,1.863840,1.227366 +'Steele County',2000,1.377413,0.766513,1.247973,0.931195 +'Stone County',2000,1.900042,0.811920,3.699361,1.521662 +'Tooele County',2000,6.590303,0.768910,1.788637,0.340067 +'Vernon County',2000,0.974454,0.915946,1.368804,1.041712 +'Williamson County',2000,2.985102,0.391418,5.805965,4.396699 +'Wright County',2000,5.029335,1.970810,4.076529,1.966472 ---- TYPES -INT, INT, STRING, DECIMAL +STRING, INT, DECIMAL, DECIMAL, DECIMAL, DECIMAL ==== http://git-wip-us.apache.org/repos/asf/impala/blob/c8c06dd4/testdata/workloads/tpcds-unmodified/queries/tpcds-q59.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q59.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q59.test index 6a9b431..a67ba3f 100644 --- a/testdata/workloads/tpcds-unmodified/queries/tpcds-q59.test +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q59.test @@ -1,50 +1,187 @@ ==== ---- 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 1185 and 1185 + 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 1185+ 12 and 1185 + 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; - - +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.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5271,1.362652,3.086396,0.322089,0.480424,1.141632,0.759841,1.845486 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5272,1.096896,0.758348,0.680007,0.987386,1.003437,1.333548,0.992338 +'able','AAAAAAAACAAAAAAA',5273,1.197285,1.027151,0.474952,0.632463,0.708915,1.492329,0.574006 +'able','AAAAAAAACAAAAAAA',5273,1.197285,1.027151,0.474952,0.632463,0.708915,1.492329,0.574006 ---- TYPES -INT, INT, STRING, DECIMAL +STRING, STRING, INT, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL ==== http://git-wip-us.apache.org/repos/asf/impala/blob/c8c06dd4/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test b/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test index e665976..6fa8080 100644 --- a/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test +++ b/testdata/workloads/tpcds-unmodified/queries/tpcds-q89.test @@ -1,7 +1,6 @@ ==== ---- QUERY: TPCDS-Q89 - -select * +select * from( select i_category, i_class, i_brand, s_store_name, s_company_name, @@ -14,21 +13,119 @@ from item, store_sales, date_dim, store where ss_item_sk = i_item_sk and ss_sold_date_sk = d_date_sk and ss_store_sk = s_store_sk and - d_year in (2000) and - ((i_category in ('Jewelry','Women','Home') and - i_class in ('mens watch','swimwear','kids') + d_year in (1999) and + ((i_category in ('Books','Electronics','Sports') and + i_class in ('computers','stereo','football') ) - or (i_category in ('Men','Sports','Music') and - i_class in ('accessories','football','rock') + or (i_category in ('Men','Jewelry','Women') and + i_class in ('shirts','birdal','dresses') )) group by i_category, i_class, i_brand, s_store_name, s_company_name, d_moy) tmp1 where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1 order by sum_sales - avg_monthly_sales, s_store_name -limit 100; - - +limit 100 ---- RESULTS +'Men','shirts','importoimporto #1','ese','Unknown',3,2070.65,5492.817500 +'Men','shirts','importoimporto #1','ought','Unknown',6,2152.15,5045.758333 +'Men','shirts','importoimporto #1','able','Unknown',2,2333.43,5194.818333 +'Men','shirts','importoimporto #1','ese','Unknown',6,2691.33,5492.817500 +'Men','shirts','importoimporto #1','ought','Unknown',2,2253.70,5045.758333 +'Women','dresses','amalgamalg #1','able','Unknown',6,1761.81,4507.240000 +'Men','shirts','importoimporto #1','ation','Unknown',3,2672.68,5410.916667 +'Men','shirts','importoimporto #1','ation','Unknown',5,2677.81,5410.916667 +'Men','shirts','importoimporto #1','eing','Unknown',5,2356.88,5074.076667 +'Men','shirts','importoimporto #1','ese','Unknown',4,2784.11,5492.817500 +'Women','dresses','amalgamalg #1','ought','Unknown',7,1926.67,4619.707500 +'Women','dresses','amalgamalg #1','ought','Unknown',1,2065.94,4619.707500 +'Men','shirts','importoimporto #1','ation','Unknown',6,2881.34,5410.916667 +'Men','shirts','importoimporto #1','able','Unknown',3,2674.91,5194.818333 +'Women','dresses','amalgamalg #1','ation','Unknown',2,2192.31,4690.890000 +'Men','shirts','importoimporto #1','bar','Unknown',5,2475.90,4973.509167 +'Women','dresses','amalgamalg #1','ese','Unknown',2,2146.52,4642.990833 +'Men','shirts','importoimporto #1','eing','Unknown',2,2588.02,5074.076667 +'Women','dresses','amalgamalg #1','bar','Unknown',7,2428.75,4914.118333 +'Women','dresses','amalgamalg #1','bar','Unknown',3,2451.51,4914.118333 +'Men','shirts','importoimporto #1','ation','Unknown',7,2951.67,5410.916667 +'Men','shirts','importoimporto #1','able','Unknown',1,2765.75,5194.818333 +'Women','dresses','amalgamalg #1','able','Unknown',2,2082.54,4507.240000 +'Men','shirts','importoimporto #1','ation','Unknown',4,2988.08,5410.916667 +'Men','shirts','importoimporto #1','ought','Unknown',7,2667.92,5045.758333 +'Women','dresses','amalgamalg #1','bar','Unknown',4,2549.27,4914.118333 +'Women','dresses','amalgamalg #1','ese','Unknown',7,2307.00,4642.990833 +'Women','dresses','amalgamalg #1','eing','Unknown',4,2204.15,4528.822500 +'Men','shirts','importoimporto #1','able','Unknown',7,2880.34,5194.818333 +'Men','shirts','importoimporto #1','ese','Unknown',5,3210.74,5492.817500 +'Women','dresses','amalgamalg #1','ation','Unknown',4,2418.39,4690.890000 +'Men','shirts','importoimporto #1','eing','Unknown',4,2833.40,5074.076667 +'Women','dresses','amalgamalg #1','bar','Unknown',2,2692.79,4914.118333 +'Men','shirts','importoimporto #1','eing','Unknown',6,2854.62,5074.076667 +'Women','dresses','amalgamalg #1','eing','Unknown',6,2314.71,4528.822500 +'Men','shirts','importoimporto #1','ought','Unknown',5,2834.94,5045.758333 +'Women','dresses','amalgamalg #1','ation','Unknown',5,2480.25,4690.890000 +'Women','dresses','amalgamalg #1','able','Unknown',7,2302.21,4507.240000 +'Women','dresses','amalgamalg #1','ation','Unknown',6,2501.15,4690.890000 +'Men','shirts','importoimporto #1','ese','Unknown',2,3307.78,5492.817500 +'Men','shirts','importoimporto #1','able','Unknown',5,3016.43,5194.818333 +'Men','shirts','importoimporto #1','able','Unknown',4,3040.23,5194.818333 +'Women','dresses','amalgamalg #1','eing','Unknown',5,2420.31,4528.822500 +'Women','dresses','amalgamalg #1','ese','Unknown',5,2535.80,4642.990833 +'Women','dresses','amalgamalg #1','ought','Unknown',2,2521.70,4619.707500 +'Men','shirts','importoimporto #1','bar','Unknown',3,2877.96,4973.509167 +'Women','dresses','amalgamalg #1','ese','Unknown',3,2577.15,4642.990833 +'Women','dresses','amalgamalg #1','ation','Unknown',7,2637.27,4690.890000 +'Men','shirts','importoimporto #1','ation','Unknown',1,3380.16,5410.916667 +'Women','dresses','amalgamalg #1','eing','Unknown',7,2524.32,4528.822500 +'Men','shirts','importoimporto #1','bar','Unknown',4,2986.70,4973.509167 +'Women','dresses','amalgamalg #1','ought','Unknown',4,2648.80,4619.707500 +'Men','shirts','importoimporto #1','bar','Unknown',7,3026.41,4973.509167 +'Women','dresses','amalgamalg #1','ation','Unknown',3,2751.11,4690.890000 +'Men','shirts','importoimporto #1','bar','Unknown',2,3073.45,4973.509167 +'Men','shirts','importoimporto #1','eing','Unknown',3,3179.96,5074.076667 +'Women','dresses','amalgamalg #1','ation','Unknown',1,2797.07,4690.890000 +'Women','dresses','amalgamalg #1','able','Unknown',1,2621.05,4507.240000 +'Women','dresses','amalgamalg #1','ese','Unknown',4,2808.36,4642.990833 +'Women','dresses','amalgamalg #2','bar','Unknown',6,680.47,2506.963333 +'Men','shirts','importoimporto #1','ation','Unknown',2,3591.65,5410.916667 +'Women','dresses','amalgamalg #1','able','Unknown',3,2706.15,4507.240000 +'Women','dresses','amalgamalg #2','eing','Unknown',2,773.08,2569.346667 +'Men','shirts','importoimporto #1','bar','Unknown',6,3199.08,4973.509167 +'Men','shirts','importoimporto #1','ese','Unknown',7,3754.33,5492.817500 +'Women','dresses','amalgamalg #1','bar','Unknown',5,3190.77,4914.118333 +'Women','dresses','amalgamalg #1','eing','Unknown',2,2841.88,4528.822500 +'Men','shirts','importoimporto #1','able','Unknown',6,3538.25,5194.818333 +'Women','dresses','amalgamalg #1','ese','Unknown',6,3006.94,4642.990833 +'Women','dresses','amalgamalg #1','bar','Unknown',1,3284.93,4914.118333 +'Women','dresses','amalgamalg #2','bar','Unknown',5,878.42,2506.963333 +'Women','dresses','amalgamalg #1','able','Unknown',5,2891.95,4507.240000 +'Men','shirts','importoimporto #1','eing','Unknown',7,3465.77,5074.076667 +'Women','dresses','amalgamalg #2','eing','Unknown',3,964.29,2569.346667 +'Women','dresses','amalgamalg #1','bar','Unknown',6,3324.49,4914.118333 +'Men','shirts','importoimporto #1','ought','Unknown',3,3467.09,5045.758333 +'Women','dresses','amalgamalg #1','ought','Unknown',5,3049.25,4619.707500 +'Women','dresses','amalgamalg #1','eing','Unknown',3,2978.07,4528.822500 +'Men','shirts','importoimporto #1','ought','Unknown',4,3544.97,5045.758333 +'Men','shirts','importoimporto #1','bar','Unknown',1,3490.62,4973.509167 +'Men','shirts','importoimporto #2','able','Unknown',2,628.63,2095.470833 +'Men','shirts','importoimporto #1','eing','Unknown',1,3628.21,5074.076667 +'Women','dresses','amalgamalg #1','able','Unknown',4,3082.17,4507.240000 +'Men','shirts','importoimporto #2','eing','Unknown',5,765.86,2137.656667 +'Women','dresses','amalgamalg #2','ation','Unknown',4,1064.70,2431.226667 +'Women','dresses','amalgamalg #2','ought','Unknown',3,991.89,2355.441667 +'Women','dresses','amalgamalg #2','eing','Unknown',5,1211.13,2569.346667 +'Women','dresses','amalgamalg #2','ation','Unknown',7,1078.41,2431.226667 +'Men','shirts','importoimporto #2','ation','Unknown',4,807.67,2141.060000 +'Women','dresses','amalgamalg #2','ation','Unknown',2,1102.89,2431.226667 +'Women','dresses','amalgamalg #2','eing','Unknown',6,1247.86,2569.346667 +'Women','dresses','amalgamalg #2','able','Unknown',4,1020.39,2337.278333 +'Men','shirts','importoimporto #2','able','Unknown',7,780.91,2095.470833 +'Women','dresses','amalgamalg #2','ese','Unknown',2,904.34,2200.820833 +'Women','dresses','amalgamalg #2','ought','Unknown',7,1061.87,2355.441667 +'Women','dresses','amalgamalg #2','able','Unknown',3,1054.94,2337.278333 +'Men','shirts','importoimporto #2','ought','Unknown',6,633.96,1908.056667 +'Men','shirts','importoimporto #2','ese','Unknown',6,853.37,2113.573333 +'Women','dresses','amalgamalg #2','able','Unknown',5,1084.94,2337.278333 +'Women','dresses','amalgamalg #2','eing','Unknown',4,1317.08,2569.346667 ---- TYPES -INT, INT, STRING, DECIMAL +STRING, STRING, STRING, STRING, STRING, INT, DECIMAL, DECIMAL ==== http://git-wip-us.apache.org/repos/asf/impala/blob/c8c06dd4/tests/query_test/test_tpcds_queries.py ---------------------------------------------------------------------- diff --git a/tests/query_test/test_tpcds_queries.py b/tests/query_test/test_tpcds_queries.py index b371bf4..54d884f 100644 --- a/tests/query_test/test_tpcds_queries.py +++ b/tests/query_test/test_tpcds_queries.py @@ -548,8 +548,17 @@ class TestTpcdsUnmodified(ImpalaTestSuite): cls.ImpalaTestMatrix.add_constraint(lambda v: v.get_value('exec_option')['batch_size'] == 0) + def test_tpcds_q31(self, vector): + self.run_test_case('tpcds-q31', vector) + def test_tpcds_q35a(self, vector): self.run_test_case('tpcds-q35a', vector) def test_tpcds_q48(self, vector): self.run_test_case('tpcds-q48', vector) + + def test_tpcds_q59(self, vector): + self.run_test_case('tpcds-q59', vector) + + def test_tpcds_q89(self, vector): + self.run_test_case('tpcds-q89', vector)