snmvaughan opened a new issue, #7188:
URL: https://github.com/apache/datafusion-comet/issues/7188
### Describe the bug
Under ANSI mode, Spark rejects a string whose value has more than 38
integral digits before converting it, with `NUMERIC_OUT_OF_SUPPORTED_RANGE`.
`Decimal.fromStringANSI` does this on every Spark version Comet supports,
unless `spark.sql.legacy.allowNegativeScaleOfDecimal=true`.
The native cast has no such check, so the error depends on where its own
parsing overflows. It raises `CAST_INVALID_INPUT`, which calls the value
malformed, when the mantissa or the scale arithmetic overflows, and
`NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION` when the rescaled value fails the
precision check. Outside ANSI mode both engines return NULL, so only the error
differs.
| Input, cast to `DECIMAL(10,2)` under ANSI | Spark | Comet |
|---|---|---|
| `'6E+38'` | `NUMERIC_OUT_OF_SUPPORTED_RANGE` | `CAST_INVALID_INPUT` |
| `'1'` and 39 zeros (40 digits) | `NUMERIC_OUT_OF_SUPPORTED_RANGE` |
`CAST_INVALID_INPUT` |
| `'1'` and 38 zeros (39 digits) | `NUMERIC_OUT_OF_SUPPORTED_RANGE` |
`NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION` |
| `'12345678901'` (11 digits, for comparison) |
`NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION` |
`NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION` |
### Steps to reproduce
```sql
SET spark.sql.ansi.enabled=true;
CREATE TABLE t (s STRING) USING parquet;
INSERT INTO t VALUES ('6E+38');
SELECT CAST(s AS DECIMAL(10, 2)) FROM t;
-- Spark: [NUMERIC_OUT_OF_SUPPORTED_RANGE] The value 6E+38 cannot be
interpreted as a numeric since it has more than 38 digits.
-- Comet: [CAST_INVALID_INPUT] The value '6E+38' of the type "STRING" cannot
be cast to "DECIMAL(10,2)" because it is malformed. ...
```
The value comes from a table so that the optimizer doesn't fold the cast
before Comet sees it.
### Expected behavior
Under ANSI, the native cast raises `NUMERIC_OUT_OF_SUPPORTED_RANGE` for a
string with more than 38 integral digits, as Spark does. Outside ANSI it keeps
returning NULL.
### Additional context
- Spark counts the integral digits as `precision - scale` of the parsed
`java.math.BigDecimal`, in the fast-fail check from SPARK-35841 and
SPARK-37451. Since SPARK-60118 (Spark 4.0.5, 4.1.4 and 4.2.1) it computes that
in a long, so an exponent near the int limit such as `'1e2147483647'`, which
used to overflow the check, also raises `NUMERIC_OUT_OF_SUPPORTED_RANGE` there.
The native cast raises `CAST_INVALID_INPUT` for it too.
- With `spark.sql.legacy.allowNegativeScaleOfDecimal=true`, Spark skips the
check, so the native cast would need that setting to choose the same error.
- The native cast already raises `NUMERIC_OUT_OF_SUPPORTED_RANGE` for a zero
with too many integral digits, such as `'0e38'` (since #3248), but not for
other values.
- The cast compatibility guide says the native string to decimal cast
matches Spark. I couldn't find a test that casts such a string under ANSI.
- #7187 is about the same function, `parse_string_to_decimal`.
### Willingness to contribute
I can contribute a fix for this bug independently.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
