Repository navigation
Description
Describe the bug
Under ANSI mode, a string whose integral part has up to 38 digits but whose value does not fit the target decimal type raises NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION in Spark. The native cast raises CAST_INVALID_INPUT, which calls the value malformed, when the difference between the target scale and the parsed scale is more than 38. parse_string_to_decimal returns Ok(None) for that case ("scale is too high to fit i128"), and the cast reports Ok(None) as an invalid input under ANSI. Outside ANSI both engines return NULL, so only the error differs.
| Input, cast under ANSI | Spark 4.1.3 | Comet |
|---|---|---|
'1e37' to DECIMAL(10,2) |
NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION |
CAST_INVALID_INPUT |
'1e30' to DECIMAL(38,10) |
NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION |
CAST_INVALID_INPUT |
'1e29' to DECIMAL(38,10) |
NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION |
CAST_INVALID_INPUT |
'1e30' to DECIMAL(10,2) (scale adjustment 32, for comparison) |
NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION |
NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION |
'6E+38' to DECIMAL(10,2), with spark.sql.legacy.allowNegativeScaleOfDecimal=true |
NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION |
CAST_INVALID_INPUT |
Steps to reproduce
SET spark.sql.ansi.enabled=true;
CREATE TABLE t (s STRING) USING parquet;
INSERT INTO t VALUES ('1e37');
SELECT CAST(s AS DECIMAL(10, 2)) FROM t;
-- Spark: [NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION] 1E+37 cannot be represented as Decimal(10, 2). ...
-- Comet: [CAST_INVALID_INPUT] The value '1e37' 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
The native cast raises NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION for a string that parses but does not fit the target type, as Spark does.
Additional context
- The cause is the
return Ok(None)forscale_adjustment > 38inparse_string_to_decimal. Returning the sameNumericValueOutOfRangeerror as the other overflow paths would match Spark, and NULL outside ANSI is unchanged. - It is separate from the 38-integral-digit limit in Native string to decimal cast raises the wrong error under ANSI for values with more than 38 integral digits #7188, where Spark raises
NUMERIC_OUT_OF_SUPPORTED_RANGEbefore it looks at the target type. These strings have up to 38 integral digits. - With
spark.sql.legacy.allowNegativeScaleOfDecimal=true, Spark does not apply that limit, and fix: raise NUMERIC_OUT_OF_SUPPORTED_RANGE for a string with more than 38 integral digits cast to decimal #7578 skips it too. A string with more than 38 integral digits such as'6E+38'then reaches this path, and its scale adjustment toDECIMAL(10,2)is 40. A negative-scale target such asDECIMAL(10,-30)has a scale adjustment of 8, so it is not affected. - A string with more digits than an
i128holds is a different limit, tracked in Native string to decimal cast fails on a string with more than 38 digits: NULL orCAST_INVALID_INPUTwhere Spark returns a value #7581. Spark returns a value for some of those strings, so it is not only a wrong error.
Willingness to contribute
I can contribute a fix for this bug independently.
Activity
docs: say which strings still report CAST_INVALID_INPUT with the lega…
fix: raise NUMERIC_OUT_OF_SUPPORTED_RANGE for a string with more than…