[
https://issues.apache.org/jira/browse/SPARK-13858?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
JESSE CHEN closed SPARK-13858.
------------------------------
Resolution: Not A Bug
Schema updates generated correct results in both spark 1.6 and 2.0. Good to
close.
> TPCDS query 21 returns wrong results compared to TPC official result set
> -------------------------------------------------------------------------
>
> Key: SPARK-13858
> URL: https://issues.apache.org/jira/browse/SPARK-13858
> 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 21 returns wrong results compared
> to official result set. This is at 1GB SF (validation run).
> SparkSQL missing at least one row (grep for AAAAAAAAABDAAAAA) ; I believe 2
> other rows are missing as well.
> Actual results:
> {noformat}
> [null,AAAAAAAAAABDAAAA,2565,1922]
> [null,AAAAAAAAAAHDAAAA,2956,2052]
> [null,AAAAAAAAAALAAAAA,2042,1793]
> [null,AAAAAAAAACGCAAAA,2373,1771]
> [null,AAAAAAAAACKCAAAA,2321,1856]
> [null,AAAAAAAAACOBAAAA,1504,1397]
> [null,AAAAAAAAADKBAAAA,1820,2163]
> [null,AAAAAAAAAEADAAAA,2631,1965]
> [null,AAAAAAAAAEOCAAAA,1659,1798]
> [null,AAAAAAAAAFACAAAA,1965,1705]
> [null,AAAAAAAAAFADAAAA,1769,1313]
> [null,AAAAAAAAAHDEAAAA,2700,1985]
> [null,AAAAAAAAAHHAAAAA,1578,1082]
> [null,AAAAAAAAAIECAAAA,1756,1804]
> [null,AAAAAAAAAIMCAAAA,3603,2951]
> [null,AAAAAAAAAJACAAAA,2109,1989]
> [null,AAAAAAAAAJKBAAAA,2573,3540]
> [null,AAAAAAAAALBEAAAA,3458,2992]
> [null,AAAAAAAAALCEAAAA,1720,1810]
> [null,AAAAAAAAALECAAAA,2569,1946]
> [null,AAAAAAAAALNBAAAA,2552,1750]
> [null,AAAAAAAAANFEAAAA,2022,2269]
> [null,AAAAAAAAAOIBAAAA,2982,2540]
> [null,AAAAAAAAAPJBAAAA,2344,2593]
> [null,AAAAAAAABAPDAAAA,2182,2787]
> [null,AAAAAAAABDCEAAAA,2844,2069]
> [null,AAAAAAAABDDDAAAA,2417,2537]
> [null,AAAAAAAABDJAAAAA,1584,1666]
> [null,AAAAAAAABEODAAAA,2141,2649]
> [null,AAAAAAAABFCCAAAA,2745,2020]
> [null,AAAAAAAABFMBAAAA,1642,1364]
> [null,AAAAAAAABHPCAAAA,1923,1780]
> [null,AAAAAAAABIDBAAAA,1956,2836]
> [null,AAAAAAAABIGBAAAA,2023,2344]
> [null,AAAAAAAABIJBAAAA,1977,2728]
> [null,AAAAAAAABJFEAAAA,1891,2390]
> [null,AAAAAAAABLDEAAAA,1983,1797]
> [null,AAAAAAAABNIDAAAA,2485,2324]
> [null,AAAAAAAABNLDAAAA,2385,2786]
> [null,AAAAAAAABOMBAAAA,2291,2092]
> [null,AAAAAAAACAAAAAAA,2233,2560]
> [null,AAAAAAAACBCDAAAA,1540,2012]
> [null,AAAAAAAACBIAAAAA,2394,2122]
> [null,AAAAAAAACBPBAAAA,1790,1661]
> [null,AAAAAAAACCMDAAAA,2654,2691]
> [null,AAAAAAAACDBCAAAA,1804,2072]
> [null,AAAAAAAACFEAAAAA,1941,1567]
> [null,AAAAAAAACGFDAAAA,2123,2265]
> [null,AAAAAAAACHPCAAAA,2933,2174]
> [null,AAAAAAAACIGDAAAA,2618,2399]
> [null,AAAAAAAACJCBAAAA,2728,2367]
> [null,AAAAAAAACJLAAAAA,1350,1732]
> [null,AAAAAAAACLAEAAAA,2578,2329]
> [null,AAAAAAAACLGAAAAA,1842,1588]
> [null,AAAAAAAACLLBAAAA,3418,2657]
> [null,AAAAAAAACLOBAAAA,3115,2560]
> [null,AAAAAAAACMADAAAA,1991,2243]
> [null,AAAAAAAACMJAAAAA,1261,1855]
> [null,AAAAAAAACMLAAAAA,3288,2753]
> [null,AAAAAAAACMPDAAAA,1320,1676]
> [null,AAAAAAAACNGBAAAA,2340,2118]
> [null,AAAAAAAACNHDAAAA,3519,3348]
> [null,AAAAAAAACNPCAAAA,2561,1948]
> [null,AAAAAAAADCPCAAAA,2664,2627]
> [null,AAAAAAAADDHAAAAA,1313,1926]
> [null,AAAAAAAADDNDAAAA,1109,835]
> [null,AAAAAAAADEAAAAAA,2141,1847]
> [null,AAAAAAAADEJAAAAA,3142,2723]
> [null,AAAAAAAADFKBAAAA,1470,1650]
> [null,AAAAAAAADGCCAAAA,2113,2331]
> [null,AAAAAAAADGFCAAAA,2201,2928]
> [null,AAAAAAAADHPAAAAA,2467,2133]
> [null,AAAAAAAADMBAAAAA,3085,2087]
> [null,AAAAAAAADPABAAAA,3494,3081]
> [null,AAAAAAAAEAECAAAA,2133,2148]
> [null,AAAAAAAAEAPAAAAA,1560,1275]
> [null,AAAAAAAAECGCAAAA,2815,3307]
> [null,AAAAAAAAEDPDAAAA,2731,1883]
> [null,AAAAAAAAEEECAAAA,2024,1902]
> [null,AAAAAAAAEEMCAAAA,2624,2387]
> [null,AAAAAAAAEFFAAAAA,2047,1878]
> [null,AAAAAAAAEGJAAAAA,2403,2633]
> [null,AAAAAAAAEGMAAAAA,2784,2772]
> [null,AAAAAAAAEGOCAAAA,2389,1753]
> [null,AAAAAAAAEHFDAAAA,1940,1420]
> [null,AAAAAAAAEHLBAAAA,2320,2057]
> [null,AAAAAAAAEHPAAAAA,1898,1853]
> [null,AAAAAAAAEIPBAAAA,2930,2326]
> [null,AAAAAAAAEJAEAAAA,2582,1836]
> [null,AAAAAAAAEJIBAAAA,2257,1681]
> [null,AAAAAAAAEJJAAAAA,2791,1941]
> [null,AAAAAAAAEJJDAAAA,3410,2405]
> [null,AAAAAAAAEJNCAAAA,2472,2067]
> [null,AAAAAAAAEJPDAAAA,1219,1229]
> [null,AAAAAAAAEKEBAAAA,2047,1713]
> [null,AAAAAAAAEMEAAAAA,2502,1897]
> [null,AAAAAAAAEMKCAAAA,2362,2042]
> [null,AAAAAAAAENACAAAA,2011,1909]
> [null,AAAAAAAAENFBAAAA,2507,2162]
> [null,AAAAAAAAENODAAAA,3371,2709]
> {noformat}
> Expected results:
> {noformat}
> +----------------------+------------------+------------+-----------+
> | W_WAREHOUSE_NAME | I_ITEM_ID | INV_BEFORE | INV_AFTER |
> +----------------------+------------------+------------+-----------+
> | Bad cards must make. | AAAAAAAAAACDAAAA | 1889 | 2168 |
> | Bad cards must make. | AAAAAAAAAAHDAAAA | 2739 | 2039 |
> | Bad cards must make. | AAAAAAAAABDAAAAA | 1717 | 1782 |
> | Bad cards must make. | AAAAAAAAACGCAAAA | 2296 | 2276 |
> | Bad cards must make. | AAAAAAAAACKCAAAA | 2443 | 1878 |
> | Bad cards must make. | AAAAAAAAACOBAAAA | 2705 | 2428 |
> | Bad cards must make. | AAAAAAAAADGBAAAA | 2242 | 2759 |
> | Bad cards must make. | AAAAAAAAADKBAAAA | 2138 | 2456 |
> | Bad cards must make. | AAAAAAAAAEADAAAA | 2914 | 2237 |
> | Bad cards must make. | AAAAAAAAAEOCAAAA | 1797 | 2073 |
> | Bad cards must make. | AAAAAAAAAFACAAAA | 2058 | 2734 |
> | Bad cards must make. | AAAAAAAAAFADAAAA | 2173 | 2515 |
> | Bad cards must make. | AAAAAAAAAFDCAAAA | 2309 | 2277 |
> | Bad cards must make. | AAAAAAAAAGIDAAAA | 1864 | 2197 |
> | Bad cards must make. | AAAAAAAAAHDEAAAA | 2483 | 2077 |
> | Bad cards must make. | AAAAAAAAAHJBAAAA | 2904 | 3312 |
> | Bad cards must make. | AAAAAAAAAIECAAAA | 2239 | 2526 |
> | Bad cards must make. | AAAAAAAAAIMCAAAA | 1948 | 2693 |
> | Bad cards must make. | AAAAAAAAAJKBAAAA | 2184 | 2190 |
> | Bad cards must make. | AAAAAAAAALECAAAA | 2539 | 3052 |
> | Bad cards must make. | AAAAAAAAALNBAAAA | 1406 | 1728 |
> | Bad cards must make. | AAAAAAAAAMPBAAAA | 3450 | 3033 |
> | Bad cards must make. | AAAAAAAAANFEAAAA | 2255 | 1517 |
> | Bad cards must make. | AAAAAAAAAPLAAAAA | 1465 | 1227 |
> | Bad cards must make. | AAAAAAAABBIDAAAA | 2822 | 2125 |
> | Bad cards must make. | AAAAAAAABDCEAAAA | 2068 | 2187 |
> | Bad cards must make. | AAAAAAAABEFDAAAA | 1023 | 1510 |
> | Bad cards must make. | AAAAAAAABFNAAAAA | 1962 | 2719 |
> | Bad cards must make. | AAAAAAAABFOCAAAA | 2392 | 2520 |
> | Bad cards must make. | AAAAAAAABHPCAAAA | 2945 | 3058 |
> | Bad cards must make. | AAAAAAAABIDBAAAA | 1746 | 1706 |
> | Bad cards must make. | AAAAAAAABJFEAAAA | 2458 | 2647 |
> | Bad cards must make. | AAAAAAAABKFAAAAA | 3152 | 2529 |
> | Bad cards must make. | AAAAAAAABLDEAAAA | 2439 | 2899 |
> | Bad cards must make. | AAAAAAAABNHBAAAA | 2254 | 2117 |
> | Bad cards must make. | AAAAAAAABNLAAAAA | 2552 | 2608 |
> | Bad cards must make. | AAAAAAAABNLDAAAA | 3103 | 2489 |
> | Bad cards must make. | AAAAAAAABNMCAAAA | 1752 | 2078 |
> | Bad cards must make. | AAAAAAAABOCCAAAA | 1697 | 1547 |
> | Bad cards must make. | AAAAAAAACAAAAAAA | 2736 | 1937 |
> | Bad cards must make. | AAAAAAAACAJCAAAA | 1921 | 1806 |
> | Bad cards must make. | AAAAAAAACBFAAAAA | 1237 | 1460 |
> | Bad cards must make. | AAAAAAAACBIAAAAA | 2157 | 1915 |
> | Bad cards must make. | AAAAAAAACBPBAAAA | 3220 | 2970 |
> | Bad cards must make. | AAAAAAAACCABAAAA | 1167 | 1704 |
> | Bad cards must make. | AAAAAAAACCMDAAAA | 2057 | 2563 |
> | Bad cards must make. | AAAAAAAACDBCAAAA | 2017 | 1494 |
> | Bad cards must make. | AAAAAAAACGFDAAAA | 2811 | 3027 |
> | Bad cards must make. | AAAAAAAACHHDAAAA | 2589 | 1935 |
> | Bad cards must make. | AAAAAAAACJDCAAAA | 1299 | 1807 |
> | Bad cards must make. | AAAAAAAACKHAAAAA | 1484 | 2158 |
> | Bad cards must make. | AAAAAAAACLKAAAAA | 2743 | 2910 |
> | Bad cards must make. | AAAAAAAACMJAAAAA | 1345 | 1776 |
> | Bad cards must make. | AAAAAAAACMLAAAAA | 1869 | 2372 |
> | Bad cards must make. | AAAAAAAACMPDAAAA | 1480 | 2177 |
> | Bad cards must make. | AAAAAAAACNBBAAAA | 2914 | 2373 |
> | Bad cards must make. | AAAAAAAACNEBAAAA | 2170 | 2456 |
> | Bad cards must make. | AAAAAAAACNFAAAAA | 2854 | 2071 |
> | Bad cards must make. | AAAAAAAACNGBAAAA | 1830 | 2659 |
> | Bad cards must make. | AAAAAAAACNHDAAAA | 1507 | 1643 |
> | Bad cards must make. | AAAAAAAACNPCAAAA | 2756 | 2402 |
> | Bad cards must make. | AAAAAAAADCBEAAAA | 2403 | 2684 |
> | Bad cards must make. | AAAAAAAADCPCAAAA | 2112 | 1881 |
> | Bad cards must make. | AAAAAAAADDNDAAAA | 3404 | 2652 |
> | Bad cards must make. | AAAAAAAADEJDAAAA | 1719 | 1822 |
> | Bad cards must make. | AAAAAAAADFLAAAAA | 3242 | 2344 |
> | Bad cards must make. | AAAAAAAADGCCAAAA | 2435 | 2159 |
> | Bad cards must make. | AAAAAAAADGFCAAAA | 2350 | 1867 |
> | Bad cards must make. | AAAAAAAADGGBAAAA | 2686 | 3567 |
> | Bad cards must make. | AAAAAAAADGKAAAAA | 2927 | 2595 |
> | Bad cards must make. | AAAAAAAADHPAAAAA | 2639 | 2216 |
> | Bad cards must make. | AAAAAAAADKPDAAAA | 2476 | 3512 |
> | Bad cards must make. | AAAAAAAADMBAAAAA | 2472 | 2301 |
> | Bad cards must make. | AAAAAAAADNLBAAAA | 2792 | 2077 |
> | Bad cards must make. | AAAAAAAADPABAAAA | 1719 | 1667 |
> | Bad cards must make. | AAAAAAAAEAICAAAA | 1420 | 2094 |
> | Bad cards must make. | AAAAAAAAEAPAAAAA | 1875 | 2244 |
> | Bad cards must make. | AAAAAAAAEBCEAAAA | 2241 | 1924 |
> | Bad cards must make. | AAAAAAAAEBPCAAAA | 2544 | 2281 |
> | Bad cards must make. | AAAAAAAAECGCAAAA | 2039 | 1848 |
> | Bad cards must make. | AAAAAAAAEDOCAAAA | 2401 | 1770 |
> | Bad cards must make. | AAAAAAAAEDPDAAAA | 3041 | 2119 |
> | Bad cards must make. | AAAAAAAAEGGBAAAA | 2717 | 2032 |
> | Bad cards must make. | AAAAAAAAEGJAAAAA | 2996 | 2092 |
> | Bad cards must make. | AAAAAAAAEGMAAAAA | 3051 | 2982 |
> | Bad cards must make. | AAAAAAAAEGOCAAAA | 2086 | 1958 |
> | Bad cards must make. | AAAAAAAAEHECAAAA | 1993 | 1498 |
> | Bad cards must make. | AAAAAAAAEHFDAAAA | 2217 | 2151 |
> | Bad cards must make. | AAAAAAAAEHLBAAAA | 1417 | 2034 |
> | Bad cards must make. | AAAAAAAAEIDEAAAA | 1804 | 1474 |
> | Bad cards must make. | AAAAAAAAEJAEAAAA | 2287 | 2006 |
> | Bad cards must make. | AAAAAAAAEJJDAAAA | 2080 | 1660 |
> | Bad cards must make. | AAAAAAAAEJPDAAAA | 1788 | 2188 |
> | Bad cards must make. | AAAAAAAAEKCAAAAA | 2451 | 2182 |
> | Bad cards must make. | AAAAAAAAENFBAAAA | 2123 | 2294 |
> | Bad cards must make. | AAAAAAAAEONAAAAA | 2706 | 1887 |
> | Bad cards must make. | AAAAAAAAEPHCAAAA | 1332 | 1209 |
> | Bad cards must make. | AAAAAAAAEPKCAAAA | 2445 | 1824 |
> | Bad cards must make. | AAAAAAAAFAKBAAAA | 843 | 1256 |
> | Bad cards must make. | AAAAAAAAFALDAAAA | 2391 | 2019 |
> +----------------------+------------------+------------+-----------+
> {noformat}
> query used:
> {noformat}
> -- start query 21 in stream 0 using template query21.tpl and seed
> QUALIFICATION
> select *
> from(select w_warehouse_name
> ,i_item_id
> ,sum(case when (cast(d_date as date) < cast ('2000-03-11' as
> date))
> then inv_quantity_on_hand
> else 0 end) as inv_before
> ,sum(case when (cast(d_date as date) >= cast ('2000-03-11' as
> date))
> then inv_quantity_on_hand
> else 0 end) as inv_after
> from inventory
> ,warehouse
> ,item
> ,date_dim
> where i_current_price between 0.99 and 1.49
> and i_item_sk = inv_item_sk
> and inv_warehouse_sk = w_warehouse_sk
> and inv_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_warehouse_name, i_item_id) x
> where (case when inv_before > 0
> then inv_after / inv_before
> else null
> end) between 2.0/3.0 and 3.0/2.0
> order by w_warehouse_name
> ,i_item_id
> limit 100;
> -- end query 21 in stream 0 using template query21.tpl
> {noformat}
--
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
