[
https://issues.apache.org/jira/browse/SPARK-60843?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dustin Smith updated SPARK-60843:
---------------------------------
Description:
{{Decimal.roundToInt}}, {{roundToShort}} and {{roundToByte}} check the range of
a BigDecimal-backed value by converting it to a {{Double}}. A value that
truncates to the bound but sits just below the next integer rounds up past the
bound as a double, so the check fails even though the truncated result fits.
Example, with {{spark.sql.ansi.enabled=true}}:
{code:sql}
SELECT CAST(CAST('2147483647.99999999' AS DECIMAL(20,8)) AS INT);
-- fails with CAST_OVERFLOW, should return 2147483647
SELECT try_cast(CAST('2147483647.99999999' AS DECIMAL(20,8)) AS INT);
-- returns NULL, should return 2147483647
{code}
The same happens at {{Int.MinValue}} and at the {{SMALLINT}} and {{TINYINT}}
bounds. Values just below an integer near the bound do not fail but come back
one too high, because the rounded double is converted:
{code:sql}
SELECT CAST(CAST('2147483646.99999999' AS DECIMAL(20,8)) AS INT);
-- returns 2147483647, should return 2147483646 (as the non-ANSI cast does)
{code}
When the same value is held as an unscaled long (precision <= 18), the check
is already exact and the cast succeeds, as does {{roundToLong}}, which
truncates through {{BigInteger}}. So the result of the cast depends on the
internal representation of the value.
Proposed change: truncate BigDecimal-backed values exactly via {{BigInteger}},
as {{roundToLong}} does, and range-check the truncated integer. Values that
used to fail with {{CAST_OVERFLOW}} (or return NULL from {{try_cast}}) now
return the truncated value; values that are actually out of range still
overflow. Add a line to the SQL migration guide.
was:
{{Decimal.roundToInt}}, {{roundToShort}} and {{roundToByte}} check the range of
a BigDecimal-backed value by converting it to a {{Double}}. A value that
truncates to the bound but sits just below the next integer rounds up past the
bound as a double, so the check fails even though the truncated result fits.
Example, with {{spark.sql.ansi.enabled=true}}:
{code:sql}
SELECT CAST(CAST('2147483647.99999999' AS DECIMAL(20,8)) AS INT);
-- fails with CAST_OVERFLOW, should return 2147483647
SELECT try_cast(CAST('2147483647.99999999' AS DECIMAL(20,8)) AS INT);
-- returns NULL, should return 2147483647
{code}
The same happens at {{Int.MinValue}} and at the {{SMALLINT}} and {{TINYINT}}
bounds. When the same value is held as an unscaled long (precision <= 18), the
check is already exact and the cast succeeds, as does {{roundToLong}}, which
truncates through {{BigInteger}}. So the result of the cast depends on the
internal representation of the value.
Proposed change: truncate BigDecimal-backed values exactly via {{BigInteger}},
as {{roundToLong}} does, and range-check the truncated integer. Values that
used to fail with {{CAST_OVERFLOW}} (or return NULL from {{try_cast}}) now
return the truncated value; values that are actually out of range still
overflow. Add a line to the SQL migration guide.
> Truncate decimals exactly when casting to INT, SMALLINT and TINYINT
> -------------------------------------------------------------------
>
> Key: SPARK-60843
> URL: https://issues.apache.org/jira/browse/SPARK-60843
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: Dustin Smith
> Priority: Major
>
> {{Decimal.roundToInt}}, {{roundToShort}} and {{roundToByte}} check the range
> of a BigDecimal-backed value by converting it to a {{Double}}. A value that
> truncates to the bound but sits just below the next integer rounds up past
> the bound as a double, so the check fails even though the truncated result
> fits.
> Example, with {{spark.sql.ansi.enabled=true}}:
> {code:sql}
> SELECT CAST(CAST('2147483647.99999999' AS DECIMAL(20,8)) AS INT);
> -- fails with CAST_OVERFLOW, should return 2147483647
> SELECT try_cast(CAST('2147483647.99999999' AS DECIMAL(20,8)) AS INT);
> -- returns NULL, should return 2147483647
> {code}
> The same happens at {{Int.MinValue}} and at the {{SMALLINT}} and {{TINYINT}}
> bounds. Values just below an integer near the bound do not fail but come back
> one too high, because the rounded double is converted:
> {code:sql}
> SELECT CAST(CAST('2147483646.99999999' AS DECIMAL(20,8)) AS INT);
> -- returns 2147483647, should return 2147483646 (as the non-ANSI cast does)
> {code}
> When the same value is held as an unscaled long (precision <= 18), the check
> is already exact and the cast succeeds, as does {{roundToLong}}, which
> truncates through {{BigInteger}}. So the result of the cast depends on the
> internal representation of the value.
> Proposed change: truncate BigDecimal-backed values exactly via
> {{BigInteger}}, as {{roundToLong}} does, and range-check the truncated
> integer. Values that used to fail with {{CAST_OVERFLOW}} (or return NULL from
> {{try_cast}}) now return the truncated value; values that are actually out of
> range still overflow. Add a line to the SQL migration guide.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
