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]