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]

Reply via email to