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]