spark-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "JESSE CHEN (JIRA)" <j...@apache.org>
Subject [jira] [Closed] (SPARK-13861) TPCDS query 40 returns wrong results compared to TPC official result set
Date Tue, 22 Mar 2016 18:55:25 GMT

     [ https://issues.apache.org/jira/browse/SPARK-13861?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

JESSE CHEN closed SPARK-13861.
------------------------------
    Resolution: Duplicate

Fixed all schema discrepancies. 

> TPCDS query 40 returns wrong results compared to TPC official result set 
> -------------------------------------------------------------------------
>
>                 Key: SPARK-13861
>                 URL: https://issues.apache.org/jira/browse/SPARK-13861
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 1.6.0
>            Reporter: JESSE CHEN
>              Labels: tpcds-result-mismatch
>
> Testing Spark SQL using TPC queries. Query 40 returns wrong results compared to official
result set. This is at 1GB SF (validation run).
> SparkSQL missing at least one row (grep for AAAAAAAAABBDAAAA) ; I believe 5 rows are
missing in total.
> Actual results:
> {noformat}
> [TN,AAAAAAAAAABDAAAA,0.0,-82.0999960899353]
> [TN,AAAAAAAAAACDAAAA,-216.54000234603882,158.0399932861328]
> [TN,AAAAAAAAAAHDAAAA,186.54999542236328,0.0]
> [TN,AAAAAAAAAALAAAAA,0.0,48.22999954223633]
> [TN,AAAAAAAAACGCAAAA,63.67999863624573,0.0]
> [TN,AAAAAAAAACHCAAAA,102.68000030517578,51.88999938964844]
> [TN,AAAAAAAAACKCAAAA,128.92999935150146,44.81999969482422]
> [TN,AAAAAAAAACLDAAAA,205.43999433517456,-948.619930267334]
> [TN,AAAAAAAAACOBAAAA,207.32000732421875,24.889999389648438]
> [TN,AAAAAAAAACPDAAAA,87.75,53.9900016784668]
> [TN,AAAAAAAAADGBAAAA,44.310001373291016,222.4800033569336]
> [TN,AAAAAAAAADKBAAAA,0.0,-471.8699951171875]
> [TN,AAAAAAAAAEADAAAA,58.2400016784668,0.0]
> [TN,AAAAAAAAAEOCAAAA,19.90999984741211,214.70000076293945]
> [TN,AAAAAAAAAFACAAAA,271.8199977874756,163.1699981689453]
> [TN,AAAAAAAAAFADAAAA,2.3499999046325684,28.31999969482422]
> [TN,AAAAAAAAAFDCAAAA,-378.0499496459961,-303.26999282836914]
> [TN,AAAAAAAAAGIDAAAA,307.6099967956543,-19.290000915527344]
> [TN,AAAAAAAAAHDEAAAA,80.5799994468689,-476.7200012207031]
> [TN,AAAAAAAAAHHAAAAA,8.270000457763672,155.10000276565552]
> [TN,AAAAAAAAAHJBAAAA,39.23999857902527,0.0]
> [TN,AAAAAAAAAIECAAAA,82.39999675750732,3.9100000858306885]
> [TN,AAAAAAAAAIEEAAAA,20.399999618530273,-151.08999633789062]
> [TN,AAAAAAAAAIMCAAAA,24.469999313354492,-150.3000030517578]
> [TN,AAAAAAAAAJACAAAA,49.09000015258789,82.0999984741211]
> [TN,AAAAAAAAAJCAAAAA,121.18000221252441,63.779998779296875]
> [TN,AAAAAAAAAJKBAAAA,27.940000534057617,8.970000267028809]
> [TN,AAAAAAAAALBEAAAA,88.2599983215332,30.229999542236328]
> [TN,AAAAAAAAALCEAAAA,93.52000045776367,92.01999998092651]
> [TN,AAAAAAAAALECAAAA,64.1999979019165,15.15999984741211]
> [TN,AAAAAAAAALNBAAAA,4.199999809265137,148.27000427246094]
> [TN,AAAAAAAAAMBEAAAA,28.440000534057617,0.0]
> [TN,AAAAAAAAAMPBAAAA,0.0,131.92999839782715]
> [TN,AAAAAAAAANFEAAAA,0.0,-137.3400115966797]
> [TN,AAAAAAAAAOIBAAAA,150.40999603271484,254.2800008058548]
> [TN,AAAAAAAAAPJBAAAA,45.27000045776367,334.4000082015991]
> [TN,AAAAAAAAAPLAAAAA,50.20000076293945,29.150001049041748]
> [TN,AAAAAAAAAPLDAAAA,0.0,32.38999938964844]
> [TN,AAAAAAAABAPDAAAA,93.41999816894531,145.8699951171875]
> [TN,AAAAAAAABBIDAAAA,296.7700004577637,30.959999084472656]
> [TN,AAAAAAAABDCEAAAA,-1771.0800704956055,-54.779998779296875]
> [TN,AAAAAAAABDDDAAAA,111.12000274658203,280.5899963378906]
> [TN,AAAAAAAABDJAAAAA,0.0,79.54999923706055]
> [TN,AAAAAAAABEFDAAAA,0.0,3.4299999475479126]
> [TN,AAAAAAAABEODAAAA,269.8999938964844,297.5800061225891]
> [TN,AAAAAAAABFMBAAAA,110.82999801635742,-941.4000930786133]
> [TN,AAAAAAAABFNAAAAA,47.86000061035156,0.0]
> [TN,AAAAAAAABFOCAAAA,46.34000015258789,83.52000045776367]
> [TN,AAAAAAAABHPCAAAA,27.3700008392334,77.61999893188477]
> [TN,AAAAAAAABIDBAAAA,196.6199951171875,5.570000171661377]
> [TN,AAAAAAAABIGBAAAA,425.3399963378906,0.0]
> [TN,AAAAAAAABIJBAAAA,209.6300048828125,0.0]
> [TN,AAAAAAAABJFEAAAA,7.329999923706055,55.15999984741211]
> [TN,AAAAAAAABKFAAAAA,0.0,138.14000129699707]
> [TN,AAAAAAAABKMCAAAA,27.170000076293945,54.970001220703125]
> [TN,AAAAAAAABLDEAAAA,170.28999400138855,0.0]
> [TN,AAAAAAAABNHBAAAA,58.05999994277954,-337.8899841308594]
> [TN,AAAAAAAABNIDAAAA,54.400001525878906,35.019999504089355]
> [TN,AAAAAAAABNLAAAAA,0.0,168.37999629974365]
> [TN,AAAAAAAABNLDAAAA,0.0,96.40999984741211]
> [TN,AAAAAAAABNMCAAAA,202.40999698638916,49.52999830245972]
> [TN,AAAAAAAABOCCAAAA,4.730000019073486,69.83999633789062]
> [TN,AAAAAAAABOMBAAAA,63.66999816894531,163.49000668525696]
> [TN,AAAAAAAACAAAAAAA,121.91000366210938,0.0]
> [TN,AAAAAAAACAADAAAA,-1107.6099338531494,0.0]
> [TN,AAAAAAAACAJCAAAA,115.80999946594238,173.05000019073486]
> [TN,AAAAAAAACBCDAAAA,18.940000534057617,226.38000106811523]
> [TN,AAAAAAAACBFAAAAA,0.0,97.41000366210938]
> [TN,AAAAAAAACBIAAAAA,2.140000104904175,84.66000366210938]
> [TN,AAAAAAAACBPBAAAA,95.44000244140625,26.68000030517578]
> [TN,AAAAAAAACCABAAAA,160.43000602722168,135.86000061035156]
> [TN,AAAAAAAACCHDAAAA,0.0,121.62000274658203]
> [TN,AAAAAAAACCMDAAAA,-115.87000274658203,124.37999820709229]
> [TN,AAAAAAAACDBCAAAA,16.6200008392334,3.3999999910593033]
> [TN,AAAAAAAACDECAAAA,-3114.599931716919,0.0]
> [TN,AAAAAAAACEEAAAAA,34.68000030517578,26.40999984741211]
> [TN,AAAAAAAACELAAAAA,130.58999633789062,154.6300048828125]
> [TN,AAAAAAAACELDAAAA,0.0,181.07000732421875]
> [TN,AAAAAAAACFEAAAAA,3.7799999713897705,-315.13000106811523]
> [TN,AAAAAAAACGFDAAAA,-386.8699951171875,96.92000102996826]
> [TN,AAAAAAAACHHDAAAA,143.17000675201416,251.63999938964844]
> [TN,AAAAAAAACHPCAAAA,0.17000000178813934,198.29000091552734]
> [TN,AAAAAAAACJCBAAAA,-918.6500339508057,270.9600028991699]
> [TN,AAAAAAAACJDCAAAA,0.0,130.14999771118164]
> [TN,AAAAAAAACJLAAAAA,63.959999084472656,91.27000045776367]
> [TN,AAAAAAAACKFCAAAA,-540.5899600982666,35.63999938964844]
> [TN,AAAAAAAACKHAAAAA,204.52000045776367,110.61000061035156]
> [TN,AAAAAAAACKIAAAAA,18.43000030517578,-63.649983406066895]
> [TN,AAAAAAAACLAEAAAA,116.07000350952148,0.0]
> [TN,AAAAAAAACLGAAAAA,108.10000228881836,111.14000475406647]
> [TN,AAAAAAAACLKAAAAA,143.0499973297119,19.59000015258789]
> [TN,AAAAAAAACLLBAAAA,0.0,178.10000610351562]
> [TN,AAAAAAAACLOBAAAA,-2200.7200317382812,14.130000114440918]
> [TN,AAAAAAAACMADAAAA,71.41999864578247,-13.639997482299805]
> [TN,AAAAAAAACMJAAAAA,0.0,358.30999755859375]
> [TN,AAAAAAAACMLAAAAA,37.31999969482422,4.059999942779541]
> [TN,AAAAAAAACMPDAAAA,64.20000171661377,0.0]
> [TN,AAAAAAAACNABAAAA,374.32999324798584,46.88999938964844]
> [TN,AAAAAAAACNBBAAAA,0.0,30.350000381469727]
> [TN,AAAAAAAACNEBAAAA,84.13999843597412,0.0]
> {noformat}
> Expected results:
> {noformat}
> +---------+------------------+--------------+-------------+
> | W_STATE | I_ITEM_ID        | SALES_BEFORE | SALES_AFTER |
> +---------+------------------+--------------+-------------+
> | TN      | AAAAAAAAAABDAAAA |       .00000 |   -82.10000 |
> | TN      | AAAAAAAAAACDAAAA |   -216.54000 |   158.04000 |
> | TN      | AAAAAAAAAAHDAAAA |    186.55000 |      .00000 |
> | TN      | AAAAAAAAAALAAAAA |       .00000 |    48.23000 |
> | TN      | AAAAAAAAABBDAAAA |     98.61000 |   332.71000 |
> | TN      | AAAAAAAAABDAAAAA |       .00000 |   213.64000 |
> | TN      | AAAAAAAAACGCAAAA |     63.68000 |      .00000 |
> | TN      | AAAAAAAAACHCAAAA |    102.68000 |    51.89000 |
> | TN      | AAAAAAAAACKCAAAA |    128.93000 |    44.82000 |
> | TN      | AAAAAAAAACLDAAAA |    205.44000 |  -948.62000 |
> | TN      | AAAAAAAAACOBAAAA |    207.32000 |    24.89000 |
> | TN      | AAAAAAAAACPDAAAA |     87.75000 |    53.99000 |
> | TN      | AAAAAAAAADGBAAAA |     44.31000 |   222.48000 |
> | TN      | AAAAAAAAADKBAAAA |       .00000 |  -471.87000 |
> | TN      | AAAAAAAAAEADAAAA |     58.24000 |      .00000 |
> | TN      | AAAAAAAAAEOCAAAA |     19.91000 |   214.70000 |
> | TN      | AAAAAAAAAFACAAAA |    271.82000 |   163.17000 |
> | TN      | AAAAAAAAAFADAAAA |      2.35000 |    28.32000 |
> | TN      | AAAAAAAAAFDCAAAA |   -378.05000 |  -303.27000 |
> | TN      | AAAAAAAAAGIDAAAA |    307.61000 |   -19.29000 |
> | TN      | AAAAAAAAAHDEAAAA |     80.58000 |  -476.72000 |
> | TN      | AAAAAAAAAHHAAAAA |      8.27000 |   155.10000 |
> | TN      | AAAAAAAAAHJBAAAA |     39.24000 |      .00000 |
> | TN      | AAAAAAAAAIECAAAA |     82.40000 |     3.91000 |
> | TN      | AAAAAAAAAIEEAAAA |     20.40000 |  -151.09000 |
> | TN      | AAAAAAAAAIMCAAAA |     24.47000 |  -150.30000 |
> | TN      | AAAAAAAAAJACAAAA |     49.09000 |    82.10000 |
> | TN      | AAAAAAAAAJCAAAAA |    121.18000 |    63.78000 |
> | TN      | AAAAAAAAAJKBAAAA |     27.94000 |     8.97000 |
> | TN      | AAAAAAAAALBEAAAA |     88.26000 |    30.23000 |
> | TN      | AAAAAAAAALCEAAAA |     93.52000 |    92.02000 |
> | TN      | AAAAAAAAALECAAAA |     64.20000 |    15.16000 |
> | TN      | AAAAAAAAALNBAAAA |      4.20000 |   148.27000 |
> | TN      | AAAAAAAAAMBEAAAA |     28.44000 |      .00000 |
> | TN      | AAAAAAAAAMPBAAAA |       .00000 |   131.93000 |
> | TN      | AAAAAAAAANFEAAAA |       .00000 |  -137.34000 |
> | TN      | AAAAAAAAAOBBAAAA |       .00000 |    55.62000 |
> | TN      | AAAAAAAAAOIBAAAA |    150.41000 |   254.28000 |
> | TN      | AAAAAAAAAPBAAAAA |     70.40000 |      .00000 |
> | TN      | AAAAAAAAAPJBAAAA |     45.27000 |   334.40000 |
> | TN      | AAAAAAAAAPLAAAAA |     50.20000 |    29.15000 |
> | TN      | AAAAAAAAAPLDAAAA |       .00000 |    32.39000 |
> | TN      | AAAAAAAABAPDAAAA |     93.42000 |   145.87000 |
> | TN      | AAAAAAAABBIDAAAA |    296.77000 |    30.96000 |
> | TN      | AAAAAAAABDCEAAAA |  -1771.08000 |   -54.78000 |
> | TN      | AAAAAAAABDDDAAAA |    111.12000 |   280.59000 |
> | TN      | AAAAAAAABDJAAAAA |       .00000 |    79.55000 |
> | TN      | AAAAAAAABEFDAAAA |       .00000 |     3.43000 |
> | TN      | AAAAAAAABEODAAAA |    269.90000 |   297.58000 |
> | TN      | AAAAAAAABFMBAAAA |    110.83000 |  -941.40000 |
> | TN      | AAAAAAAABFNAAAAA |     47.86000 |      .00000 |
> | TN      | AAAAAAAABFOCAAAA |     46.34000 |    83.52000 |
> | TN      | AAAAAAAABHPCAAAA |     27.37000 |    77.62000 |
> | TN      | AAAAAAAABIDBAAAA |    196.62000 |     5.57000 |
> | TN      | AAAAAAAABIGBAAAA |    425.34000 |      .00000 |
> | TN      | AAAAAAAABIJBAAAA |    209.63000 |      .00000 |
> | TN      | AAAAAAAABJFEAAAA |      7.33000 |    55.16000 |
> | TN      | AAAAAAAABKFAAAAA |       .00000 |   138.14000 |
> | TN      | AAAAAAAABKMCAAAA |     27.17000 |    54.97000 |
> | TN      | AAAAAAAABLDEAAAA |    170.29000 |      .00000 |
> | TN      | AAAAAAAABNHBAAAA |     58.06000 |  -337.89000 |
> | TN      | AAAAAAAABNIDAAAA |     54.40000 |    35.02000 |
> | TN      | AAAAAAAABNLAAAAA |       .00000 |   168.38000 |
> | TN      | AAAAAAAABNLDAAAA |       .00000 |    96.41000 |
> | TN      | AAAAAAAABNMCAAAA |    202.41000 |    49.53000 |
> | TN      | AAAAAAAABOCCAAAA |      4.73000 |    69.84000 |
> | TN      | AAAAAAAABOMBAAAA |     63.67000 |   163.49000 |
> | TN      | AAAAAAAACAAAAAAA |    121.91000 |      .00000 |
> | TN      | AAAAAAAACAADAAAA |  -1107.61000 |      .00000 |
> | TN      | AAAAAAAACAJCAAAA |    115.81000 |   173.05000 |
> | TN      | AAAAAAAACBCDAAAA |     18.94000 |   226.38000 |
> | TN      | AAAAAAAACBFAAAAA |       .00000 |    97.41000 |
> | TN      | AAAAAAAACBIAAAAA |      2.14000 |    84.66000 |
> | TN      | AAAAAAAACBPBAAAA |     95.44000 |    26.68000 |
> | TN      | AAAAAAAACCABAAAA |    160.43000 |   135.86000 |
> | TN      | AAAAAAAACCHDAAAA |       .00000 |   121.62000 |
> | TN      | AAAAAAAACCMDAAAA |   -115.87000 |   124.38000 |
> | TN      | AAAAAAAACDBCAAAA |     16.62000 |     3.40000 |
> | TN      | AAAAAAAACDECAAAA |  -3114.60000 |      .00000 |
> | TN      | AAAAAAAACEEAAAAA |     34.68000 |    26.41000 |
> | TN      | AAAAAAAACELAAAAA |    130.59000 |   154.63000 |
> | TN      | AAAAAAAACELDAAAA |       .00000 |   181.07000 |
> | TN      | AAAAAAAACFEAAAAA |      3.78000 |  -315.13000 |
> | TN      | AAAAAAAACFHDAAAA |       .00000 |     1.80000 |
> | TN      | AAAAAAAACGFDAAAA |   -386.87000 |    96.92000 |
> | TN      | AAAAAAAACHHDAAAA |    143.17000 |   251.64000 |
> | TN      | AAAAAAAACHPCAAAA |       .17000 |   198.29000 |
> | TN      | AAAAAAAACJCBAAAA |   -918.65000 |   270.96000 |
> | TN      | AAAAAAAACJDCAAAA |       .00000 |   130.15000 |
> | TN      | AAAAAAAACJLAAAAA |     63.96000 |    91.27000 |
> | TN      | AAAAAAAACKFCAAAA |   -540.59000 |    35.64000 |
> | TN      | AAAAAAAACKHAAAAA |    204.52000 |   110.61000 |
> | TN      | AAAAAAAACKIAAAAA |     18.43000 |   -63.65000 |
> | TN      | AAAAAAAACLAEAAAA |    116.07000 |      .00000 |
> | TN      | AAAAAAAACLGAAAAA |    108.10000 |   111.14000 |
> | TN      | AAAAAAAACLKAAAAA |    143.05000 |    19.59000 |
> | TN      | AAAAAAAACLLBAAAA |       .00000 |   178.10000 |
> | TN      | AAAAAAAACLOBAAAA |  -2200.72000 |    14.13000 |
> | TN      | AAAAAAAACMADAAAA |     71.42000 |   -13.64000 |
> | TN      | AAAAAAAACMJAAAAA |       .00000 |   358.31000 |
> +---------+------------------+--------------+-------------+
> {noformat}
> Query used:
> {noformat}
> -- start query 40 in stream 0 using template query40.tpl and seed QUALIFICATION
>   select  
>    w_state
>   ,i_item_id
>   ,sum(case when (cast(d_date as date) < cast ('2000-03-11' as date)) 
>  		then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_before
>   ,sum(case when (cast(d_date as date) >= cast ('2000-03-11' as date)) 
>  		then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after
>  from
>    catalog_sales left outer join catalog_returns on
>        (cs_order_number = cr_order_number 
>         and cs_item_sk = cr_item_sk)
>   ,warehouse 
>   ,item
>   ,date_dim
>  where
>      i_current_price between 0.99 and 1.49
>  and i_item_sk          = cs_item_sk
>  and cs_warehouse_sk    = w_warehouse_sk 
>  and cs_sold_date_sk    = d_date_sk
>  and d_date between date_sub(cast ('2000-03-11' as date), 30) 
>                 and date_add(cast ('2000-03-11' as date), 30) 
>  group by
>     w_state,i_item_id
>  order by w_state,i_item_id
>   limit 100;
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org


Mime
View raw message