andygrove opened a new issue, #267:
URL: https://github.com/apache/datafusion-spark/issues/267
Part of #174 and apache/datafusion-comet#7300.
### What the function does
`conv(num, from_base, to_base)` converts the number written in `num` from
base `from_base` to base `to_base` and returns it as a string. The logic is
`NumberConverter.convert`, ported from Hive's `UDFConv`: the input is parsed
into an unsigned 64-bit value; a positive `to_base` prints that value unsigned,
a negative `to_base` prints it signed. Digits above 9 are printed in upper
case. Available since 1.5.0.
`num` is implicitly cast to STRING (so `conv(-10, 16, -10)` works) and both
bases to INT. The result type is the type of `num` (STRING; collation-aware in
4.0+). The result is NULL if any argument is NULL, if a base is out of range
(`from_base` not in 2..36 or `|to_base|` not in 2..36), or if `num` is empty
after trimming. Overflow while parsing gives `-1` (all bits set) with ANSI off
and an error with ANSI on.
### Examples
```sql
SELECT conv('100', 2, 10);
-- 4
SELECT conv(-10, 16, -10);
-- -16
SELECT conv('3', 10, 2);
-- 11
SELECT conv('-15', 10, -16);
-- -F
SELECT conv('-15', 10, 16);
-- FFFFFFFFFFFFFFF1
SELECT conv('big', 36, 16);
-- 3A48
SELECT conv('11abc', 10, 16);
-- B
SELECT conv('9223372036854775808', 10, 16);
-- 8000000000000000
SELECT conv('92233720368547758070', 10, 16);
-- FFFFFFFFFFFFFFFF (ANSI off)
-- error ARITHMETIC_OVERFLOW (ANSI on)
SELECT conv('-9223372036854775807', 36, 10);
-- 18446744073709551615 (ANSI off)
```
### Edge cases to test
- NULL in each argument returns NULL. `from_base` 1 or 37, `to_base` 37 /
-37 / 0 / 1 / -1 return NULL (`conv('1234', 10, 37)` is NULL). `conv('', 10,
16)` is NULL.
- Trimming: only ASCII spaces (0x20) are removed (`UTF8String.trim`).
`conv(' 10 ', 10, 2)` is `1010`; a leading tab is an invalid digit, so the
result is `0`.
- Invalid digits: parsing stops at the first character that is not a digit
in `from_base`, and the valid prefix is converted (`conv('11abc', 10, 16)` is
`B`). No valid digits gives `0` (`conv('zz', 10, 10)`; `conv('-', 10, 10)`).
- Case: lower- and upper-case letters are accepted as digits; output letters
are upper case.
- Negative input with positive `to_base` is printed as an unsigned
two's-complement value (`FFFFFFFFFFFFFFF1`); with negative `to_base` as signed
(`-F`).
- Overflow with ANSI off saturates to `-1` (all bits) and then follows the
sign rules: `conv('9223372036854775807', 36, 10)` and
`conv('-9223372036854775807', 36, 10)` are `18446744073709551615`;
`conv('100000000000000000000000000000000000000000000000000000000000000000', 2,
8)` is `1777777777777777777777`.
- Overflow with ANSI on (`spark.sql.ansi.enabled=true`, the 4.0+ default):
`ARITHMETIC_OVERFLOW` (SQLSTATE 22003) `Overflow in function conv(). If
necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.`, also
for `-92233720368547758070`.
- `Long.MinValue` with `to_base = -2`: the longest output (64 digits plus
`-`).
- Non-ASCII characters in `num` are invalid digits (the UTF-8 bytes are
checked one at a time).
- Integer and DECIMAL `num` arguments are cast to string first (`conv(1.5,
10, 2)` parses `1.5`, stops at `.`, and returns `1`).
- Column bases (they are not required to be literals).
### Differences across Spark versions
- 4.0: ANSI on by default, so overflow fails by default instead of returning
`FFFFFFFFFFFFFFFF`-style results; collation-aware `num` (result type follows
`num`).
- 3.4 to 4.2 share the same `NumberConverter` logic; ANSI overflow checking
exists in all of them.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala`,
`Conv`; logic in
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/NumberConverter.scala`;
error from `QueryExecutionErrors.overflowInConvError`.
- DataFusion core has no `conv`. `datafusion-spark` has `hex`, `unhex` and
`bin` in `crates/spark/src/function/math/`; none does arbitrary-base conversion.
- Port `NumberConverter` directly (unsigned arithmetic with `u64`,
`Long.divideUnsigned` equivalents), including the stop-at-invalid-digit and
overflow-to-`-1` behaviour. Use `datafusion.execution.enable_ansi_mode` for the
overflow error.
- Tests: the placeholder `crates/sqllogictest/test_files/math/conv.slt`
exists with commented-out queries (note the second one drops the third
argument); enable and extend it. Document 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]