Native string to decimal cast raises the wrong error under ANSI for values with more than 38 integral digits #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.

Steps to reproduce

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 bug: Fix string decimal type throw right exception #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.
• Native string to negative-scale decimal cast doesn't match Spark 4.0.5, 4.1.4 and 4.2.1 (SPARK-60119) #7187 is about the same function, parse_string_to_decimal.

Willingness to contribute

I can contribute a fix for this bug independently.