Philo He created SPARK-58316:
--------------------------------
Summary: ANSI mode does not throw when casting NaN/Infinity float
or double to DECIMAL
Key: SPARK-58316
URL: https://issues.apache.org/jira/browse/SPARK-58316
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 4.3.0
Reporter: Philo He
h3. Summary
Under ANSI mode, casting a floating-point value (float/double) to DECIMAL
returns NULL for {{NaN}}, {{Infinity}}, and {{-Infinity}}, instead of throwing
an error. This is inconsistent with how ANSI mode handles other values that
cannot be represented in the target decimal, which throw an exception.
h3. How to reproduce
{code:sql} SET spark.sql.ansi.enabled=true;
-- Finite value that overflows the target precision/scale: throws (expected
under ANSI) SELECT CAST(CAST(1e38 AS DOUBLE) AS DECIMAL(20, 2)); --
ArithmeticException
-- Non-finite values: return NULL instead of throwing SELECT CAST(CAST('nan' AS
DOUBLE) AS DECIMAL(38, 2)); -- NULL SELECT CAST(CAST('inf' AS DOUBLE) AS
DECIMAL(38, 2)); -- NULL SELECT CAST(CAST('-inf' AS DOUBLE) AS DECIMAL(38, 2));
-- NULL {code}
h3. Expected behavior
Under ANSI mode, casting {{NaN}}, {{Infinity}}, or {{-Infinity}} to DECIMAL
should throw an error (e.g. a cast overflow / invalid value error), consistent
with:
Finite floating-point values that overflow the target decimal precision/scale,
which already throw under ANSI.
Casting {{NaN}}/{{Infinity}} float/double to integral types, which throws under
ANSI.
A DECIMAL can only represent finite numbers, so {{NaN}} and {{Infinity}} have
no valid decimal value. ANSI semantics call for raising a data exception when a
value cannot be represented in the target type, rather than silently returning
NULL.
When ANSI mode is disabled, returning NULL is correct and should not change.
h3. Root cause
In {{Cast.scala}}, {{castToDecimal}} for {{FractionalType}} wraps the
conversion in a try/catch that swallows {{NumberFormatException}} and returns
NULL unconditionally, regardless of the ANSI setting:
{code:scala} case x: FractionalType => val fractional =
PhysicalFractionalType.fractional(x) b => try {
changePrecision(Decimal(fractional.toDouble(b)), target) } catch { case _:
NumberFormatException => null } {code}
{{Decimal(Double.NaN)}} / {{Decimal(Double.PositiveInfinity)}} construct a Java
{{BigDecimal}}, which has no representation for NaN/Infinity and throws
{{NumberFormatException}}. Because this exception is caught before
{{changePrecision}} (the ANSI-aware code that decides throw-vs-null) runs, the
ANSI setting never takes effect for non-finite inputs.
By contrast, the {{IntegralType}} branch has no such catch, so overflow flows
through {{changePrecision}} and throws under ANSI as expected.
h3. Proposed fix
Make the catch clause ANSI-aware: when {{ansiEnabled}} is true, throw a proper
cast error (e.g. {{castingCauseOverflowError}} / an invalid-input cast error)
for non-finite inputs; when ANSI is disabled, return NULL as today. The
try/catch itself should remain, since a raw {{NumberFormatException}} must not
escape.
If the behavior change is a compatibility concern, it could be gated behind a
legacy config so existing queries can opt back into the NULL behavior.
h3. Notes
This was found while implementing float/double → DECIMAL casting in a native
engine (Velox, used by Gluten). The overflow cases align with Spark, but the
NaN/Infinity handling differs; we chose to follow ANSI semantics (throw under
ANSI) and are filing this to check whether Spark intends to align.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
