[SPARK-27768] Infinity, -Infinity, NaN should be recognized in a case insensitive manner

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.0.0
• Component/s: SQL
• Assignee: Dilip Biswal
• Reporter: Xiao Li
• Created: 19/May/19 03:59
• Updated: 29/Dec/19 05:41
• Resolved: 13/Aug/19 23:48

Description

When the inputs contain the constant 'infinity', Spark SQL does not generate the expected results.

SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
FROM (VALUES ('infinity'), ('1')) v(x);
SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
FROM (VALUES ('infinity'), ('infinity')) v(x);
SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
FROM (VALUES ('-infinity'), ('infinity')) v(x);

 The root cause: Spark SQL does not recognize the special constants in a case insensitive way. In PostgreSQL, they are recognized in a case insensitive way. 

Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html 

 

Issue Links

• is duplicated by: SPARK-28060 Float/Double type can not accept some special inputs
• links to: GitHub Pull Request #25331