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

   Part of #177 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `format_number(expr1, expr2)` formats the number `expr1` like 
`'#,###,###.##'`, rounded to `expr2` decimal
   places, and returns a STRING. If `expr2` is 0 the result has no decimal 
point or fractional part. `expr2` may
   instead be a STRING holding a user-supplied `java.text.DecimalFormat` 
pattern; an empty string means the default
   pattern `#,###,###,###,###,###,##0`. Modeled on MySQL's `FORMAT`. Available 
since Spark 1.5.0.
   
   Input types use `ExpectsInputTypes`, so the analyzer does not insert 
implicit casts: `expr1` must be a
   `NumericType` (TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL) and 
`expr2` must be exactly INT or STRING.
   A STRING first argument (`format_number('aa', 4)`) and a non-integral second 
argument (`format_number(4, 6.48173)`)
   both fail analysis. The result is always nullable. It is NULL if either 
argument is NULL or if the integer
   `expr2` is negative.
   
   Formatting always uses `DecimalFormatSymbols(Locale.US)`, so the decimal 
separator is `.` and the grouping
   separator is `,` whatever the JVM locale is (SPARK-13515). Integral inputs 
are formatted as `long`, FLOAT is
   widened to `double`, DOUBLE is formatted as `double`, and DECIMAL is 
formatted as an exact `java.math.BigDecimal`.
   
   ### Examples
   
   ```sql
   SELECT format_number(12332.123456, 4);
   -- 12,332.1235
   SELECT format_number(12332.123456, '##################.###');
   -- 12332.123
   SELECT format_number(12831273.23481D, 3);
   -- 12,831,273.235
   SELECT format_number(12831273.83421D, 0);
   -- 12,831,274
   SELECT format_number(123123324123L, 3);
   -- 123,123,324,123.000
   SELECT format_number(123123324123L, -1);
   -- NULL
   SELECT format_number(CAST(3.1322 AS FLOAT), 4);
   -- 3.1322
   SELECT format_number(CAST(4 AS TINYINT), '##.####');
   -- 4
   SELECT format_number(12831273.83421D, '');
   -- 12,831,274
   SELECT format_number(123123324123L, '###,###,###,###,###.###');
   -- 123,123,324,123
   SELECT format_number(12332.123456, '#,###,###,###,###,###,##0');
   -- 12,332
   ```
   
   (Outputs come from `StringExpressionsSuite` "format_number / FormatNumber", 
`StringFunctionsSuite`
   "number format function", and the `ExpressionDescription`.)
   
   ### Edge cases to test
   
   - NULL in either argument returns NULL (`format_number(CAST(NULL AS INT), 
3)`, `format_number(1, CAST(NULL AS INT))`,
     `format_number(1, CAST(NULL AS STRING))`).
   - A negative integer `expr2` returns NULL. A string `expr2` has no such 
check.
   - `expr2 = 0`: no decimal point.
   - Rounding mode: `DecimalFormat` defaults to `RoundingMode.HALF_EVEN`. From 
the code,
     `format_number(CAST(2.5 AS DECIMAL(2,1)), 0)` gives `2` and 
`format_number(CAST(3.5 AS DECIMAL(2,1)), 0)` gives `4`.
     Test ties for DECIMAL (exact) and DOUBLE separately. A DOUBLE such as 
`1.005` is not exactly representable, and
     `DecimalFormat.format(double)` rounds based on the binary value. Compare 
against Spark rather than assuming
     HALF_UP.
   - FLOAT is widened to double before formatting, so formatting `CAST(0.1 AS 
FLOAT)` with many decimals shows the
     float's binary expansion.
   - Large `expr2`: `DecimalFormat` caps the fraction digits at 340 for 
anything other than `BigDecimal`/`BigInteger`.
     So integral, FLOAT and DOUBLE inputs get at most 340 decimals, while 
DECIMAL inputs get all `expr2` digits.
   - Wide DECIMAL values: the suite formats the Decimal product `123123324123 * 
123123.21234` with `expr2 = 4` as
     `15,159,339,180,002,773.2778`, and with `'###,###,###,###,###.####'` it 
gets the same result. Grouping repeats
     every 3 digits for any number of integer digits.
   - Negative values get a `-` prefix. Check the output for DOUBLE `-0.0` (Java 
prints a signed zero), and for
     `NaN`, `Infinity` and `-Infinity` (Java's US symbols on JDK 17 are `NaN` 
and `∞`).
   - User pattern strings follow `DecimalFormat.applyLocalizedPattern` 
semantics: `#`, `0`, `,`, `.`, `;`
     (negative subpattern), `%`, `‰`, `E` exponent, and quoting with `'`. An 
invalid pattern raises a raw
     `java.lang.IllegalArgumentException`, not a Spark error condition. Decide 
whether to support the full pattern
     language or only the subset `#`, `0`, `,`, `.`. Document whatever you 
choose.
   - `expr2` as a column with different values per row (the Spark kernel caches 
the last pattern, which must not leak
     between rows): `(5L,4),(4L,3),(4L,3),(3L,2)` gives `5.0000, 4.000, 4.000, 
3.00`.
   - Scalar and array for each argument.
   - BIGINT, DECIMAL or DOUBLE second argument: analysis error (no implicit 
cast).
   - Collations (4.0+): the pattern argument accepts any collated STRING, 
including trim collations. The output
     string uses the session default collation 
(`DefaultStringProducingExpression`).
   - ANSI mode has no effect.
   
   ### Differences across Spark versions
   
   - 3.4 / 3.5: `inputTypes = Seq(NumericType, TypeCollection(IntegerType, 
StringType))`, `dataType = StringType`.
   - 4.0+: the pattern argument is 
`StringTypeWithCollation(supportsTrimCollation = true)` and the output uses the
     default string type (collation-aware). Formatting is unchanged.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala`,
     `case class FormatNumber`. Formatting is `java.text.DecimalFormat` with 
`Locale.US` symbols.
   - DataFusion core has no `format_number` or numeric formatting function 
(DataFusion `to_char` is datetime-only),
     so this needs a new kernel. The core work is reproducing `DecimalFormat`: 
HALF_EVEN rounding on the exact
     decimal value (or the binary value for doubles), grouping size 3, minimum 
one integer digit, and the 340-digit
     fraction cap for non-decimal inputs.
   - Use `coerce_types`/the signature to reject non-numeric `expr1` and 
non-INT/STRING `expr2` instead of casting.
   - Belongs in `crates/spark/src/function/string/` (Spark group 
`string_funcs`). Register it in `string/mod.rs`
     like the other functions there.
   - Tests: `crates/sqllogictest/test_files/string/format_number.slt` already 
exists with commented-out queries.
     Enable those and add the cases above. Document with `#[user_doc]` and run 
`dev/update_function_docs.sh`.
   
   ---
   _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