JESSE CHEN created SPARK-13862:
----------------------------------
Summary: 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-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
