[
https://issues.apache.org/jira/browse/SPARK-60202?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Varun Bhandary updated SPARK-60202:
-----------------------------------
Description:
h3. Problem and reproduction
I found that scaled {{floor}} and {{ceil}} can round Double inputs in the wrong
direction while investigating rounding behavior:
{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}
Baseline numerical results: {{(0, 1, 0.9)}}. Expected: {{(0, 0, 1.0)}}. The
first input is below one; the last is above 0.9.
Catalyst casts Double to {{decimal(30,15)}} and Float to {{decimal(14,7)}}
before applying scaled floor/ceil. This fixed-scale conversion can cross a
rounding boundary before the operation runs. Runtime columns also reproduce the
issue. It affects the shared SQL implementation used by Classic and Connect,
and can change bucket or interval assignments.
h3. Proposed behavior
* Avoid the premature fixed-scale cast in scaled floor/ceil. Retain existing
decimal result precision/scale, output-scale caps and input-range checks; unary
floor/ceil remain unchanged.
* Follow [Spark's existing round/bround conversion
convention|https://github.com/apache/spark/blob/f04e6d60ebf9131fd83f306627697370ef52a309/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala]:
widen Float to Double, then use its canonical decimal representation before
directional rounding. This is not an exact-binary arithmetic contract.
* For result overflow, raise an error with ANSI enabled and return null with
ANSI disabled, consistently for Float, Double and Decimal inputs to scaled
floor/ceil. This deliberately includes correcting existing Decimal
result-overflow behavior in these functions. Changes to round/bround/truncate
are outside this proposal.
h3. Compatibility and current status
The Float policy changes ordinary inputs too: {{ceil(CAST('0.1' AS FLOAT), 1)}}
changes from {{0.1}} to {{0.2}}; the Double form remains {{0.1}}. This follows
the conversion convention above and needs compatibility review. SQL standards
and other engines do not establish one required conversion policy for Spark's
scaled, decimal-returning overload.
For {{ceil(CAST('1e-20' AS DOUBLE), -38)}}, the old cast loses the input to
zero. Preserving it exposes a result of {{1e38}}, outside {{decimal(38,0)}}.
The patch applies the ANSI setting: an error when enabled, null when disabled.
For overflowing Decimal inputs at negative scales, the ANSI-mode error
condition changes from {{NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION}} to
{{NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION}}.
The defect was reproduced in 4.0.2, 4.1.2 and master
{{f04e6d60ebf9131fd83f306627697370ef52a309}}. Two new Double regressions failed
before the fix and passed afterward. [PR
#59408|https://github.com/apache/spark/pull/59408] records implementation and
validation. The PR includes the scoped overflow change, independent
compatibility/schema regressions and migration documentation. The Float
conversion policy and overflow scope are proposed for maintainer review; CI
status is tracked on the PR. No new public API, setting or dependency is
proposed.
was:
h3. Problem and reproduction
I found that scaled {{floor}} and {{ceil}} can round Double inputs in the wrong
direction while investigating rounding behavior:
{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}
Baseline numerical results: {{(0, 1, 0.9)}}. Expected: {{(0, 0, 1.0)}}. The
first input is below one; the last is above 0.9.
Catalyst casts Double to {{decimal(30,15)}} and Float to {{decimal(14,7)}}
before applying scaled floor/ceil. This fixed-scale conversion can cross a
rounding boundary before the operation runs. Runtime columns also reproduce the
issue. It affects the shared SQL implementation used by Classic and Connect,
and can change bucket or interval assignments.
h3. Proposed behavior
* Avoid the premature fixed-scale cast in scaled floor/ceil. Retain existing
decimal result precision/scale, output-scale caps and input-range checks; unary
floor/ceil remain unchanged.
* Follow [Spark's existing round/bround conversion
convention|https://github.com/apache/spark/blob/f04e6d60ebf9131fd83f306627697370ef52a309/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala]:
widen Float to Double, then use its canonical decimal representation before
directional rounding. This is not an exact-binary arithmetic contract.
* For result overflow, raise an error with ANSI enabled and return null with
ANSI disabled, consistently for Float, Double and Decimal inputs to scaled
floor/ceil. This deliberately includes correcting existing Decimal
result-overflow behavior in these functions. Changes to round/bround/truncate
are outside this proposal.
h3. Compatibility and current status
The Float policy changes ordinary inputs too: {{ceil(CAST('0.1' AS FLOAT), 1)}}
changes from {{0.1}} to {{0.2}}; the Double form remains {{0.1}}. This follows
the conversion convention above and needs compatibility review. SQL standards
and other engines do not establish one required conversion policy for Spark's
scaled, decimal-returning overload.
For {{ceil(CAST('1e-20' AS DOUBLE), -38)}}, the old cast loses the input to
zero. Preserving it exposes a result of {{1e38}}, outside {{decimal(38,0)}}.
The current patch throws in both ANSI modes; the proposed ANSI-disabled null
behavior is *not implemented yet*.
The defect was reproduced in 4.0.2, 4.1.2 and master
{{f04e6d60ebf9131fd83f306627697370ef52a309}}. Two new Double regressions failed
before the fix and passed afterward. [PR
#59408|https://github.com/apache/spark/pull/59408] records implementation and
validation. The overflow change, additional compatibility/schema tests and
migration documentation remain pending; the contribution is not yet ready to
merge. No new public API, setting or dependency is proposed.
> 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.0.2, 4.1.2
> Reporter: Varun Bhandary
> Priority: Major
> Labels: correctness, pull-request-available
>
> h3. Problem and reproduction
> I found that scaled {{floor}} and {{ceil}} can round Double inputs in the
> wrong direction while investigating rounding behavior:
> {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}
> Baseline numerical results: {{(0, 1, 0.9)}}. Expected: {{(0, 0, 1.0)}}. The
> first input is below one; the last is above 0.9.
> Catalyst casts Double to {{decimal(30,15)}} and Float to {{decimal(14,7)}}
> before applying scaled floor/ceil. This fixed-scale conversion can cross a
> rounding boundary before the operation runs. Runtime columns also reproduce
> the issue. It affects the shared SQL implementation used by Classic and
> Connect, and can change bucket or interval assignments.
> h3. Proposed behavior
> * Avoid the premature fixed-scale cast in scaled floor/ceil. Retain existing
> decimal result precision/scale, output-scale caps and input-range checks;
> unary floor/ceil remain unchanged.
> * Follow [Spark's existing round/bround conversion
> convention|https://github.com/apache/spark/blob/f04e6d60ebf9131fd83f306627697370ef52a309/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala]:
> widen Float to Double, then use its canonical decimal representation before
> directional rounding. This is not an exact-binary arithmetic contract.
> * For result overflow, raise an error with ANSI enabled and return null with
> ANSI disabled, consistently for Float, Double and Decimal inputs to scaled
> floor/ceil. This deliberately includes correcting existing Decimal
> result-overflow behavior in these functions. Changes to round/bround/truncate
> are outside this proposal.
> h3. Compatibility and current status
> The Float policy changes ordinary inputs too: {{ceil(CAST('0.1' AS FLOAT),
> 1)}} changes from {{0.1}} to {{0.2}}; the Double form remains {{0.1}}. This
> follows the conversion convention above and needs compatibility review. SQL
> standards and other engines do not establish one required conversion policy
> for Spark's scaled, decimal-returning overload.
> For {{ceil(CAST('1e-20' AS DOUBLE), -38)}}, the old cast loses the input to
> zero. Preserving it exposes a result of {{1e38}}, outside {{decimal(38,0)}}.
> The patch applies the ANSI setting: an error when enabled, null when
> disabled. For overflowing Decimal inputs at negative scales, the ANSI-mode
> error condition changes from
> {{NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION}} to
> {{NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION}}.
> The defect was reproduced in 4.0.2, 4.1.2 and master
> {{f04e6d60ebf9131fd83f306627697370ef52a309}}. Two new Double regressions
> failed before the fix and passed afterward. [PR
> #59408|https://github.com/apache/spark/pull/59408] records implementation and
> validation. The PR includes the scoped overflow change, independent
> compatibility/schema regressions and migration documentation. The Float
> conversion policy and overflow scope are proposed for maintainer review; CI
> status is tracked on the PR. No new public API, setting or dependency is
> proposed.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
