[SPARK-29638] Spark handles 'NaN' as 0 in sums

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Cannot Reproduce
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Dylan Guedes
• Created: 29/Oct/19 10:32
• Updated: 30/Nov/20 12:01
• Resolved: 30/Nov/20 12:01

Description

Currently, Spark handles 'NaN' as 0 in window functions, such that 3+'NaN'=3. PgSQL, on the other hand, handles the entire result as 'NaN', as in 3+'NaN' = 'NaN'

I experienced this with the query below:

SELECT a, b,
       SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM (VALUES(1,1),(2,2),(3,(cast('nan' as int))),(4,3),(5,4)) t(a,b);