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]

Reply via email to