Varun Bhandary created SPARK-60202:
--------------------------------------
Summary: Preserve floating-point boundaries in scaled floor and
ceil
Key: SPARK-60202
URL: https://issues.apache.org/jira/browse/SPARK-60202
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.1.2, 4.0.2
Reporter: Varun Bhandary
h3. Problem
The two-argument {{floor}} and {{ceil}} can return a value on the wrong side of
their floating-point input. They implicitly convert Double to
{{decimal(30,15)}} and Float to {{decimal(14,7)}} before applying directional
rounding. The conversion can round across the very boundary that floor/ceil
should preserve.
h3. Reproduction
For example, with explicit Double inputs:
{code:sql}
SELECT
floor(CAST('0.9999999999999999' AS DOUBLE)) AS unary_floor,
floor(CAST('0.9999999999999999' AS DOUBLE), 0) AS scaled_floor,
ceil(CAST('0.9000000000000001' AS DOUBLE), 1) AS scaled_ceil;
{code}
The current results are {{(0, 1, 0.9)}}. The expected numerical results are
{{(0, 0, 1.0)}}. The unary result remains a long and the scaled results remain
decimals; this report does not propose making their types identical.
|| Explicit Double expression || Current || Expected ||
| {{floor(0.8999999999999999, 1)}} | 0.9 | 0.8 |
| {{floor(1.7999999999999998, 1)}} | 1.8 | 1.7 |
| {{floor(0.9999999999999999, 0)}} | 1 | 0 |
| {{ceil(0.9000000000000001, 1)}} | 0.9 | 1.0 |
This affects directional rounding and interval/bucket boundaries, including
values obtained from calculations rather than SQL literals. Runtime-column
regressions reproduce the problem without relying on constant folding.
h3. Cause and proposed correction
The cause is in Catalyst's [RoundFloor and RoundCeil
implementation|https://github.com/apache/spark/blob/f04e6d60ebf9131fd83f306627697370ef52a309/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala]:
both require decimal input, so analysis inserts the lossy cast before
rounding. This is distinct from ordinary binary floating-point representation
error: the supplied Double values are already on the correct side of the
boundary, but the intermediate cast moves them across it.
The proposed correction is local to scaled floor/ceil: accept Float/Double
directly and defer rounding to the requested output scale. Preserve existing
decimal result precision/scale, explicit user casts, integral/decimal input
behavior, and established null, non-finite and ANSI overflow behavior. Do not
change global numeric-to-decimal coercion.
Float results can also change. For example, {{3.1415f}} represents
{{3.141499996185302734375}}, so floor at scale 4 should produce {{3.1414}}, not
{{3.1415}}. The proposal does not expand the existing maximum output scales or
promise exact binary arithmetic for every input; it retains Spark's existing
Double-to-Decimal conversion convention while removing the premature scale
reduction.
h3. Related work
The decimal return type is intentional, as discussed in
[SPARK-38604|https://issues.apache.org/jira/browse/SPARK-38604] and [the
original scale
implementation|https://github.com/apache/spark/pull/34729#discussion_r789493919].
[SPARK-59373|https://issues.apache.org/jira/browse/SPARK-59373] clarified that
contract without changing behavior. This precision problem is separate from the
overflow work in
[SPARK-58052|https://issues.apache.org/jira/browse/SPARK-58052].
h3. Validation
Recorded Catalyst reproductions cover released 4.0.2 and 4.1.2. On master
{{f04e6d60ebf9131fd83f306627697370ef52a309}}, two new regressions fail before
the local fix and pass afterward. Four relevant suites pass (242 tests),
including SQL runtime columns with code generation enabled/disabled and ANSI
enabled/disabled. Local builds required a session-only Netty 4.2.18 override
because the configured proxy rejects master's 4.2.19 artifacts as "version too
new"; validation with the exact upstream dependency set remains outstanding.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
