[
https://issues.apache.org/jira/browse/SPARK-13858?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
JESSE CHEN updated SPARK-13858:
-------------------------------
Description:
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:
[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]
Expected results:
+----------------------+------------------+------------+-----------+
| 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 |
+----------------------+------------------+------------+-----------+
query used:
-- 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
was:
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:
[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]
Expected results:
+----------------------+------------------+------------+-----------+
| 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 |
+----------------------+------------------+------------+-----------+
> 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:
> [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]
> Expected results:
> +----------------------+------------------+------------+-----------+
> | 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 |
> +----------------------+------------------+------------+-----------+
> query used:
> -- 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
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
