[ https://issues.apache.org/jira/browse/SPARK-13861?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
JESSE CHEN updated SPARK-13861: ------------------------------- Labels: tpcds-result-mismatch (was: ) > 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: > [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