You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by bo...@apache.org on 2018/11/19 10:45:13 UTC
[06/33] impala git commit: IMPALA-5946, IMPALA-5956: add TPC-DS q31,
q59, q89
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 <im...@cloudera.com>
Tested-by: Impala Public Jenkins <im...@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 <ta...@cloudera.com>
Authored: Wed Oct 31 12:05:42 2018 -0700
Committer: Zoltan Borok-Nagy <bo...@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)