You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Sean Owen (JIRA)" <ji...@apache.org> on 2016/03/14 18:59:33 UTC
[jira] [Commented] (SPARK-13862) TPCDS query 49 returns wrong
results compared to TPC official result set
[ https://issues.apache.org/jira/browse/SPARK-13862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15193767#comment-15193767 ]
Sean Owen commented on SPARK-13862:
-----------------------------------
These JIRAs might be a case where an Epic really does make sense, since they are closely related but have separate resolutions (I imagine). At least you can tie them together with a label.
> TPCDS query 49 returns wrong results compared to TPC official result set
> -------------------------------------------------------------------------
>
> Key: SPARK-13862
> URL: https://issues.apache.org/jira/browse/SPARK-13862
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 1.6.0
> Reporter: JESSE CHEN
>
> 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:
> [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]
> Expected results:
> +---------+------------------+--------------+-------------+
> | 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 |
> +---------+------------------+--------------+-------------+
> Query used:
> -- 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;
--
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