andygrove opened a new issue, #195:
URL: https://github.com/apache/datafusion-spark/issues/195

   Part of #177 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `to_number(expr, fmt)` parses the string `expr` into a DECIMAL using a 
number format string `fmt`. When the input
   does not match, it throws `INVALID_FORMAT.MISMATCH_INPUT`. 
`try_to_number(expr, fmt)` behaves the same but returns
   NULL on a mismatch. Both are available since Spark 3.3.0 (group 
`string_funcs`).
   
   Both arguments are STRING (`ImplicitCastInputTypes`, so other types are cast 
to string). `fmt` must be foldable,
   otherwise analysis fails with `DATATYPE_MISMATCH.NON_FOLDABLE_INPUT`. `fmt` 
is upper-cased with `Locale.ROOT`
   before parsing, so letters are case-insensitive. The result type comes from 
the format alone:
   `DECIMAL(precision, scale)`, where precision is the count of `0`/`9` digits 
and scale is the count of digits after
   `.`/`D`. If `fmt` is a NULL literal, the type is `DECIMAL(10,0)` and every 
result is NULL. The function is
   null-intolerant. `try_to_number` is always nullable.
   
   Format tokens: `0`/`9` are digits. Before the decimal point, a group 
starting with `0` must match exactly that
   many digits; a group starting with `9`, and any group after the point, 
matches at most that many. `.`/`D` marks
   the decimal point (at most one; optional in the input). `,`/`G` is a 
grouping separator with digits on both
   sides. `$` is a currency sign (at most one; before digits and the point). 
`S` is an optional `+`/`-` and `MI` is
   an optional `-`; each appears at most once, at the start or end. `PR` (end 
only) means `<...>` marks a negative
   value. The format grammar is
   `[MI|S] [$] [0|9|G|,]* [.|D] [0|9]* [$] [PR|MI|S]`.
   
   ### Examples
   
   ```sql
   SELECT to_number('454', '999');
   -- 454
   SELECT to_number('454.00', '000.00');
   -- 454.00
   SELECT to_number('12,454', '99,999');
   -- 12454
   SELECT to_number('$78.12', '$99.99');
   -- 78.12
   SELECT to_number('12,454.8-', '99,999.9S');
   -- -12454.8
   SELECT to_number('<00,454.8>', '00,000.9PR');
   -- -454.8                    (type decimal(6,1))
   SELECT to_number('0.00001-', '9.999999S');
   -- -0.000010                 (type decimal(7,6))
   SELECT to_number('.0', '99999999.99999999');
   -- 0.00000000                (type decimal(16,8))
   SELECT to_number('34,50', '999,99');
   -- 3450
   SELECT to_number('-34,338,492', '99G999G999');
   -- error INVALID_FORMAT.MISMATCH_INPUT (inputType "STRING", input 
-34,338,492, format 99G999G999)
   SELECT try_to_number('-34,338,492', '99G999G999');
   -- NULL
   SELECT to_number('123,000', '999G');
   -- analysis error INVALID_FORMAT.CONT_THOUSANDS_SEPS
   ```
   
   (From the `ExpressionDescription`, `string-functions.sql.out`, 
`postgreSQL/numeric.sql.out` and
   `StringExpressionsSuite`.)
   
   ### Edge cases to test
   
   - NULL `expr` gives NULL. A NULL literal `fmt` gives NULL (SPARK-41118) with 
type `DECIMAL(10,0)`.
   - Format validation happens at analysis and applies to `try_to_number` too. 
Subclasses of `INVALID_FORMAT`:
     `EMPTY` (`''`), `WRONG_NUM_DIGIT` (`'$'`), `CUR_MUST_BEFORE_DIGIT` 
(`'9$99'`), `CUR_MUST_BEFORE_DEC` (`'.$99'`),
     `CONT_THOUSANDS_SEPS` (`'999G'`), `THOUSANDS_SEPS_MUST_BEFORE_DEC` 
(`'99G999G999D999G999'`),
     `WRONG_NUM_TOKEN` (`'99.99.99'`, `'$$99'`, `'SS$99'`, `'MI$99MI'`, 
`'999PRPR'`) and `UNEXPECTED_TOKEN`
     (`'999@'`, `'999M'`, `'999P'`, `'$9MI9'`, `'PR$99'`). The suite checks the 
exact message parameters.
   - A non-foldable `fmt` (column) fails with 
`DATATYPE_MISMATCH.NON_FOLDABLE_INPUT`.
   - More than 38 format digits: `DecimalType` construction fails with 
`DECIMAL_PRECISION_EXCEEDS_MAX_PRECISION`. The
     suite parses `'123,456,789,123,456,789,123'` with 
`'999,999,999,999,999,999,999'` to `123456789123456789123`
     (beyond long range).
   - Mismatches (error for `to_number`, NULL for `try_to_number`), all from the 
suite: `('45','0,9')`, `('4','09')`,
     `('454','09')`, `('454','99')`, `('45','$99')`, `('45>','99PR')`, 
`('<45','99PR')`, `('454+','999MI')`,
     `('<<454>>','999PR')`, `('$345','S$099,099.99')`, `('4D5','0D9')` (the 
letter `D` is not accepted in input),
     `('123456','999G999')`.
   - Accepted inputs (suite): `('  454 ','9099')` gives 454 (whitespace inside 
a digit run is skipped),
     `('454.','099.99')` gives 454, `('$045','S$999,099.99')` gives 45,
     `('+$89,1,2,3,45.123','S$999,0,0,0,999.00000')` gives 8912345.123, 
`('-$54','MI$99')` gives -54, `('454','999PR')`
     gives 454, `('454-','999MI')` gives -454.
   - Scale padding: the result always has the scale from the format 
(`to_number('0', '99.99')` gives `0.00`).
   - Input with no digits at all (for example all whitespace) is a mismatch in 
4.1+ (see versions).
   - Character classes come from Java: whitespace is `Character.isWhitespace`, 
and digits are `Character.isDigit`
     (which also accepts non-ASCII Unicode decimal digits, which 
`java.math.BigDecimal` then parses). Decide whether
     to match this and test it. Input is walked by UTF-16 unit; any other 
character outside the format tokens causes a mismatch.
   - Lower-case formats (`'s999'`, `'99d99'`, `'99g999'`, `'999mi'`, `'999pr'`) 
behave like upper case. The
     `format` parameter in the error message is the upper-cased string.
   - ANSI mode (`spark.sql.ansi.enabled`) has no effect. `to_number` always 
throws on a mismatch.
   - Collations (4.0+): both arguments accept any collated STRING, including 
trim collations.
   - Scalar and array `expr`. `fmt` is always a scalar.
   
   ### Differences across Spark versions
   
   - 3.4: the mismatch error is `_LEGACY_ERROR_TEMP_2278`
     (`The input <valueType> '<input>' does not match the given number format: 
'<format>'.`).
     3.5+ uses `INVALID_FORMAT.MISMATCH_INPUT` with `inputType` = `"STRING"`.
   - 3.4 / 3.5: `inputTypes = Seq(StringType, StringType)`. 4.0+ uses 
collation-aware string types.
   - 4.1+: an input that consumes no digits (for example all whitespace) is a 
format mismatch, so it throws, or
     returns NULL for `try_to_number`. In 4.0 and earlier the code goes on to 
build a `BigDecimal` from the empty
     digit string. That gives `0` with the format's scale when scale > 0, or a 
raw `NumberFormatException` when
     scale = 0 (from reading the code).
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/numberFormatExpressions.scala`
     (`ToNumberBase`, `ToNumber`, `TryToNumber`). The parser and validator are 
in
     
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/ToNumberParser.scala`.
   - DataFusion core has no `to_number` or similar function, so this needs a 
new kernel. Port `ToNumberParser`
     (tokenize, validate, parse) as a shared module. The same parser's `format` 
method backs `to_char`/`to_varchar`
     on numeric input (separate issue), so write it so both functions can reuse 
it.
   - The return type depends on the literal format, so compute it in 
`return_field_from_args` from the scalar
     argument. Produce `Decimal128` (precision is at most 38).
   - Belongs in `crates/spark/src/function/string/`. 
`crates/spark/src/function/string/to_binary.rs` is a nearby
     example of a function with a literal format argument and a `try_` variant.
   - Tests: `crates/sqllogictest/test_files/string/to_number.slt` and 
`try_to_number.slt` already exist with
     commented-out queries. Enable them and add the cases above. Document both 
with `#[user_doc]`.
   
   ---
   _This issue was generated by an LLM (Claude) from the Apache Spark source 
code and tests. Examples come from Spark's `ExpressionDescription` examples, 
test suites and `.sql.out` golden files, but please verify them against Spark 
before relying on them._


-- 
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]

Reply via email to