andygrove opened a new issue, #202:
URL: https://github.com/apache/datafusion-spark/issues/202

   Part of #177 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `overlay(input, replace, pos[, len])` replaces the part of `input` that 
starts at 1-based position `pos` and is `len` units long with `replace`. 
Available since Spark 3.0.0. It can be called as a function or with the 
SQL-standard syntax `OVERLAY(input PLACING replace FROM pos [FOR len])`.
   
   `input` and `replace` must be STRING or BINARY 
(`TypeCollection(StringTypeWithCollation(supportsTrimCollation = true), 
BinaryType)`), and they must be the same type. Mixing them fails with 
`DATATYPE_MISMATCH.DATA_DIFF_TYPES`. `pos` and `len` are INT. All of these go 
through `ImplicitCastInputTypes`. The return type is `input`'s type. It is 
null-intolerant: NULL in any of the four arguments returns NULL.
   
   If `len` is omitted it defaults to `-1`. Any negative `len` means "use the 
length of `replace`" (`replace.numChars` for strings, `replace.length` for 
binary). Strings count characters (code points) and binary counts bytes. The 
result is `substringSQL(input, 1, pos - 1) || replace || substringSQL(input, 
pos + len, Int.MaxValue)`, so `pos <= 0` and `pos` past the end follow Spark's 
`substring` rules instead of raising an error.
   
   ### Examples
   
   ```sql
   SELECT overlay('Spark SQL' PLACING '_' FROM 6);
   -- Spark_SQL
   SELECT overlay('Spark SQL' PLACING 'CORE' FROM 7);
   -- Spark CORE
   SELECT overlay('Spark SQL' PLACING 'ANSI ' FROM 7 FOR 0);
   -- Spark ANSI SQL
   SELECT overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4);
   -- Structured SQL
   SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('_', 'utf-8') 
FROM 6);
   -- Spark_SQL
   SELECT overlay('Spark的SQL' PLACING '_' FROM 6);
   -- Spark_SQL
   ```
   
   Boundary cases from `StringExpressionsSuite`:
   
   ```text
   overlay('Spark SQL' PLACING '_' FROM 10)          -> Spark SQL_     (pos 
past the end)
   overlay('Spark SQL' PLACING '_' FROM 10 FOR 4)    -> Spark SQL_
   overlay('Spark SQL' PLACING '__' FROM 0)          -> __park SQL     (pos = 0)
   overlay('Spark SQL' PLACING '__' FROM 0 FOR 4)    -> __rk SQL
   overlay('Spark SQL' PLACING '__' FROM -10)        -> __park SQL     
(negative pos)
   overlay('Spark SQL' PLACING '__' FROM -10 FOR 4)  -> __rk SQL
   binary [1..9] PLACING [-1,-1] FROM 7 FOR 0        -> 
[1,2,3,4,5,6,-1,-1,7,8,9]
   binary [1..9] PLACING [-1,-1,-1,-1] FROM 7        -> 
[1,2,3,4,5,6,-1,-1,-1,-1]
   binary [1..9] PLACING [-1,-1] FROM 0 FOR 4        -> [-1,-1,4,5,6,7,8,9]
   ```
   
   ### Edge cases to test
   
   - NULL `input`, `replace`, `pos` or `len` returns NULL, for both STRING and 
BINARY.
   - `len` omitted versus `len = -1` versus `len = -5`: all use the length of 
`replace`. `len = 0` is a pure insert.
   - `pos = 0` and negative `pos`:
     - The prefix is empty.
     - The suffix starts at `pos + len` using `substringSQL` semantics, where a 
negative start counts from the end. For example, `overlay('Spark SQL' PLACING 
'__' FROM -3)` gives `__L`, because the suffix is `substring('Spark SQL', -1)`. 
This was derived from the code.
   - `pos` greater than the length plus 1 appends `replace` with no gap.
   - 32-bit overflow of `pos + len`: Spark computes this as a Java `int` and it 
wraps. For example, `overlay('Spark SQL' PLACING '_' FROM 2147483647)` gives 
`Spark SQL_Spark SQ`: the suffix start wraps to `Int.MinValue`, and 
`substringSQL` then returns the first 8 characters. This was derived from 
reading `UTF8String.substringSQL`/`substring`. Spark has no test for it.
   - Multi-byte input: positions count code points for strings and bytes for 
binary. A surrogate pair is one character.
   - An empty `replace` with `len` omitted removes nothing (`len` becomes 0), 
so the result equals `input`.
   - Type checks:
     - (STRING, BINARY) or (BINARY, STRING) fails with `DATA_DIFF_TYPES`.
     - Numeric `input`/`replace` are cast to STRING by the analyzer.
     - A string `pos` that is not a number fails with `CAST_INVALID_INPUT` 
under ANSI mode (the default since 4.0).
   - Literal and column variants for every argument.
   - Collations (4.0+): any collation is accepted and the operation ignores it. 
Two conflicting explicit collations is an analysis error.
   
   ### Differences across Spark versions
   
   - 3.4 / 3.5: as above.
   - 4.0: collation-aware input types; ANSI is on by default (this affects 
casts of `pos`/`len`). Semantics are unchanged.
   - 4.1 / 4.2: none found.
   
   ### Implementation notes
   
   - Spark source: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala`
 (`Overlay`, `object Overlay.calculate` for UTF8String and `Array[Byte]`). 
Parser: `AstBuilder.visitOverlay` in 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala`.
 Helpers: `UTF8String.substringSQL` and `ByteArray.subStringSQL` 
(`common/unsafe/src/main/java/org/apache/spark/unsafe/types/`).
   - DataFusion core has `overlay` (`datafusion/functions/src/core/overlay.rs`, 
string types only, Int64 `pos`/`len`). It does not match Spark:
     - it errors with "overlay start position must be at least 1" for `pos < 1`;
     - a negative `len` is used as is, not replaced by the length of `replace`;
     - it has no BINARY support;
     - there are no `substringSQL` negative-index semantics and no 32-bit 
wraparound.
   
     This needs a new kernel. The existing Spark `substring` in 
`crates/spark/src/function/string/substring.rs` may provide reusable 
`substringSQL`-style index logic.
   - Module: `crates/spark/src/function/string/overlay.rs`. Check that 
`OVERLAY(... PLACING ... FROM ... FOR ...)` planned through DataFusion's SQL 
planner resolves to the Spark UDF.
   - Tests: there is no `overlay.slt` yet. Add 
`crates/sqllogictest/test_files/string/overlay.slt` following existing files, 
covering STRING and BINARY. 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