andygrove opened a new issue, #196:
URL: https://github.com/apache/datafusion-spark/issues/196
Part of #177 and apache/datafusion-comet#7300.
### What the function does
`to_char(expr, format)` and its alias `to_varchar` (since 3.5.0) convert
`expr` to a STRING. `to_char` has
existed since 3.4.0. In Spark 4.0+ the function is an `ExpressionBuilder`
(`ToCharacterBuilder`) that dispatches
on the type of `expr`:
- datetime (DATE, TIMESTAMP, TIMESTAMP_NTZ, and TIME in 4.1+) becomes
`date_format(expr, format)` (`DateFormatClass`, Spark datetime
patterns);
- BINARY with a foldable `format` that, after `trim` and lower-casing, is
`'base64'`, `'hex'` or `'utf-8'` becomes
`base64(expr)`, `hex(expr)` or `decode(expr, format)`;
- anything else becomes `ToCharacter(expr, format)`, the numeric formatter.
**This issue covers only the numeric form (`ToCharacter`).** The datetime
and binary forms just rewrite to other
functions. Each of those should be its own function in datafusion-spark:
`date_format` and `decode` do not exist
yet, while `base64` and `hex` already do. The `to_char` entry point can
dispatch to them once they exist, or a
planner like Comet's can do the rewrite.
`ToCharacter` uses `ImplicitCastInputTypes` with `inputTypes =
Seq(DecimalType, StringType)`. Non-decimal numeric
input is implicitly cast with `DecimalType.forType`: TINYINT to
`DECIMAL(3,0)`, SMALLINT to `(5,0)`, INT to
`(10,0)`, BIGINT to `(20,0)`, FLOAT to `(14,7)` and DOUBLE to `(30,15)`.
`format` must be foldable
(`DATATYPE_MISMATCH.NON_FOLDABLE_INPUT`). It is upper-cased (`Locale.ROOT`)
and validated at analysis with the
same tokenizer and rules as `to_number`. The result is a STRING. It is NULL
when `expr` is NULL or `format` is a
NULL literal.
Format tokens: `0`/`9` are digits; extra leading positions become `0` if the
group starts with `0`, otherwise
spaces. `.`/`D` is the decimal point. `,`/`G` is the grouping separator; it
is printed only after a digit,
otherwise as a space. `$` is a currency sign. `S` prints `+` or `-`. `MI`
prints `-` for negatives and a space for
non-negatives. `PR` wraps negatives in `<>` and appends two spaces
otherwise. When the value has more digits than
the format allows, every digit position is printed as `#` (no error).
### Examples
```sql
SELECT to_char(454, '999');
-- 454
SELECT to_char(454.00, '000D00');
-- 454.00
SELECT to_char(12454, '99G999');
-- 12,454
SELECT to_char(78.12, '$99.99');
-- $78.12
SELECT to_char(-12454.8, '99G999D9S');
-- 12,454.8-
SELECT to_varchar(12454.8, '99,999.9S');
-- 12,454.8+
SELECT to_varchar(111.11, '99.9');
-- ##.#
-- From StringExpressionsSuite (decimal input, result shown between quotes):
-- (454, '9999') -> ' 454'
-- (454, '00000') -> '00454'
-- (0.4542, '.00000') -> '.45420'
-- (4542, '0000.') -> '4542 '
-- (12, '000,000') -> '000,012'
-- (12454, '999,9999') -> ' 1,2454'
-- (-454.8, '99G999.9PR') -> ' <454.8>'
-- (-454, '999PR') -> '<454>'
-- (4.31, '9999999999999999.999999999999999MI') -> '
4.310000000000000 '
-- (-4.31, 'MI9999999999999999.999999999999999') -> '
-4.310000000000000'
-- (454, '0') -> '#'
-- (4.67, '99.9') -> '##.#'
```
From `postgreSQL/numeric.sql.out` (`val` is `DECIMAL(38,10)`):
`to_char(-24926804.0450474200,
'0999999999999999.999999999999999')` gives
`0000000024926804.045047420000000`. The sign is dropped because the
format has no `S`/`MI`/`PR`. For `val` = 0 with
`'S0999999999999999.999999999999999'` it gives
`+0000000000000000.000000000000000`.
### Edge cases to test
- NULL `expr` gives NULL. A NULL literal `format` gives NULL (SPARK-41452).
A non-foldable `format` is an analysis
error.
- Invalid formats fail at analysis with the same `INVALID_FORMAT.*`
subclasses as `to_number` (`EMPTY`,
`WRONG_NUM_DIGIT`, `CUR_MUST_BEFORE_DIGIT`, `CUR_MUST_BEFORE_DEC`,
`CONT_THOUSANDS_SEPS`,
`THOUSANDS_SEPS_MUST_BEFORE_DEC`, `WRONG_NUM_TOKEN`, `UNEXPECTED_TOKEN`).
`StringExpressionsSuite` checks that
`ToCharacter(Decimal(456), fmt)` reports the same error as `ToNumber` for
each invalid format.
- Overflow prints `#` for every digit position before and after the point,
never an error: `(454,'00')` gives
`##` and `(4.67,'9.9')` gives `#.#`.
- Zero and fractions below one: `(0, '9999999999999999.999999999999999S')`
gives
`' 0.000000000000000+'`. A space directly before `.` becomes
`0`.
- A trailing lone decimal point is replaced by a space (`(4542,'99999.')`
gives `' 4542 '`). If the result would be
empty, `+` or `-`, it returns `0`.
- Sign placement with leading spaces: `S`, `MI` and `PR` characters are
moved past padding spaces
(`(83028485, 'S99999999999.9999999')` gives `' +83028485.0000000'`).
- Trailing zeros of the input's scale are ignored before fitting:
`Decimal(unscaled = 43100000000, precision = 38, scale = 10)` with
`'9999999999999999.999999999999999S'` gives
`' 4.310000000000000+'`.
- Implicit casts: integer, FLOAT and DOUBLE input go through CAST to DECIMAL
first. FLOAT/DOUBLE `NaN` and
`Infinity` cast to NULL, so the result is NULL. A DOUBLE above
`DECIMAL(30,15)` range follows CAST overflow
rules (NULL with ANSI off, error with `spark.sql.ansi.enabled = true`, the
default in 4.0+).
- Lower-case format letters (`'99g999d9s'`) behave like upper case.
- Collations (4.0+): `format` accepts any collated STRING, including trim
collations. The output uses the session
default collation (`DefaultStringProducingExpression`).
- Scalar and array `expr`; `format` is always a scalar.
### Differences across Spark versions
- 3.4: `to_char` only, registered directly as `ToCharacter`. 3.5: adds
`to_varchar` as an alias.
Datetime/binary input is not supported in either.
- 4.0+: both names go through `ToCharacterBuilder` (datetime/binary dispatch
described above). Binary-form errors
are `NON_FOLDABLE_ARGUMENT` (non-foldable format),
`INVALID_PARAMETER_VALUE.NULL` (NULL format) and
`INVALID_PARAMETER_VALUE.BINARY_FORMAT` (`expects one of binary formats
'base64', 'hex', 'utf-8', but got
<invalidFormat>.`). `format` becomes collation-aware.
- Numeric formatting (`ToNumberParser.format`) is unchanged across 3.4 to
4.2.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/numberFormatExpressions.scala`
(`ToCharacterBuilder`, `ToCharacter`). The formatter is
`ToNumberParser.format` in
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/ToNumberParser.scala`.
- DataFusion core's `to_char`
(`datafusion/functions/src/datetime/to_char.rs`, alias `date_format`) accepts
only
date/time/timestamp/duration input and uses chrono `strftime` patterns. It
does no numeric formatting and its
patterns differ from Spark's, so it cannot be reused for this form.
Registering a Spark `to_char` will shadow it.
- Share the tokenizer/validator with the `to_number` / `try_to_number` port
(separate issue). Spark uses one
`ToNumberParser` class for both directions.
- Coerce integral/float input to `Decimal128` with the `DecimalType.forType`
mapping above, so precision and the
NaN handling match Spark.
- Belongs in `crates/spark/src/function/string/` (Spark group
`string_funcs`).
- Tests: `crates/sqllogictest/test_files/string/to_char.slt` and
`to_varchar.slt` already exist with commented-out
queries. Enable them and add the cases above. Document with `#[user_doc]`,
and note that only numeric input is
supported.
---
_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]