andygrove opened a new issue, #217:
URL: https://github.com/apache/datafusion-spark/issues/217
Part of #178 and apache/datafusion-comet#7300.
### What the function does
`arrays_overlap(a1, a2)` returns `true` if `a1` and `a2` share at least one
non-NULL element. If they share none, both are non-empty, and either contains a
NULL element, it returns NULL. Otherwise it returns `false`. So if either array
is empty, the result is `false`, even when the other side holds NULLs.
Both arguments are cast to `ARRAY<T>` with `T =
TypeCoercion.findTightestCommonType(e1, e2)`
(`BinaryArrayExpressionWithImplicitCast`). With no common type, an untyped
`NULL` (`VOID`) argument, or a non-array argument such as a `MAP`, analysis
fails with `DATATYPE_MISMATCH.BINARY_ARRAY_DIFF_TYPES`. Non-orderable element
types fail with `DATATYPE_MISMATCH.INVALID_ORDERING_TYPE`.
The return type is `BOOLEAN`. It is nullable if either input is nullable or
either input has `containsNull = true`. The function is null-intolerant: a NULL
array gives NULL. Available since 2.4.0, no aliases.
### Examples
```sql
SELECT arrays_overlap(array(1, 2, 3), array(3, 4, 5));
-- true
SELECT arrays_overlap(array(1, 2, 3), array(null, 5, 6));
-- NULL
SELECT arrays_overlap(array(4, 5, 3), array(null, 5, 6));
-- true
SELECT arrays_overlap(array(4, 5, 3), array(7, 8));
-- false
SELECT arrays_overlap(array(null, 5, 6), CAST(array() AS ARRAY<INT>));
-- false
SELECT arrays_overlap(CAST(array() AS ARRAY<INT>), CAST(array(null) AS
ARRAY<INT>));
-- false
SELECT arrays_overlap(CAST(array(null) AS ARRAY<INT>), CAST(array(null) AS
ARRAY<INT>));
-- NULL
SELECT arrays_overlap(array(null, ''), array('def', 'ghi'));
-- NULL
SELECT arrays_overlap(array(1, 2, 3), array(2.0, 2.5));
-- true
SELECT arrays_overlap(array(array('a', 'b'), array('c', 'd')),
array(array('b', 'a'), array('f', 'g')));
-- false
SELECT arrays_overlap(array(1, 2, 3), array('a', 'b', 'c'));
-- error: DATATYPE_MISMATCH.BINARY_ARRAY_DIFF_TYPES
-- Spark 4.2.0 (SPARK-54918):
SELECT arrays_overlap(array(-0.0D), array(0.0D));
-- true
-- Spark 3.4 to 4.1.3: false
```
### Edge cases to test
- Three-valued result: `true` on any non-NULL match (even if NULLs are
present). NULL when there is no match, both arrays are non-empty, and either
has a NULL element. `false` otherwise.
- An empty array on either side gives `false` regardless of NULLs on the
other side.
- Two all-NULL arrays give NULL, not `true`. NULL elements never match each
other.
- NULL array argument (typed) gives NULL. `arrays_overlap(null, null)` and
`MAP` arguments are analysis errors (`BINARY_ARRAY_DIFF_TYPES`).
- Type coercion: `ARRAY<INT>` with `ARRAY<DOUBLE>` compares as `DOUBLE`.
- NaN: Spark uses `java.util.HashSet` with boxed `Double.equals`, so all NaN
bit patterns match each other on every version.
- Signed zero in flat float arrays: `0.0` and `-0.0` do not match in 3.4 to
4.1.3 (`Double.equals`). They match in 4.2.0.
- Nested floats in `STRUCT`/`ARRAY` elements use `ordering.equiv`, so `-0.0`
matches `0.0` and NaN matches NaN on every version.
- `BINARY` elements compare by content (`array(X'0102', X'0304')` with
`array(X'0506', X'0102')` gives `true`). `ARRAY`/`STRUCT` elements compare by
value. Collated strings (4.0+) compare under their collation, for example
`'aaa'` and `'AAA'` under `UTF8_LCASE`.
- Literal and column for each argument. Arrays of very different lengths
(Spark hashes the smaller one, which doesn't change the result).
### Differences across Spark versions
- Signed zero in flat float arrays: distinct up to 4.1.3. Equal in 4.2.0,
where `NormalizeFloatingNumbers` normalizes both arguments (SPARK-54918).
Comet's docs say SPARK-59602 brings this to 4.0.5, 4.1.4 and 4.2.1 (not in the
source trees checked).
- 4.0: `NullIntolerant` became the `nullIntolerant` flag, and
collation-aware comparison was added. No other behavior change found.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/collectionOperations.scala`,
`ArraysOverlap`. `fastEval` is used for atomic types with proper `equals`: it
puts the smaller array into a `java.util.HashSet` and probes the bigger one.
`bruteForceEval` is a nested loop with `ordering.equiv`, used for `BINARY`,
`STRUCT`, `ARRAY` and collated strings. Both track `hasNull`, and both skip the
check entirely when either array is empty.
- DataFusion core registers `arrays_overlap` as an alias of `array_has_any`
(`datafusion/functions-nested/src/array_has.rs`,
`general_array_has_for_all_and_any`, `array_has_any_with_scalar_*`). It is not
Spark-compatible:
- NULL elements compare equal to each other (arrow-row bytes, or
`Option<&str>` equality in the string path), so `[NULL]` with `[NULL]` returns
`true`.
- It never returns NULL for a no-match row that contains NULL elements. It
returns `false`.
- It does not normalize floats: `0.0` and `-0.0` differ (pre-4.2
behavior), and NaN payloads differ.
- It uses DataFusion's coercion and accepts untyped `NULL`.
- datafusion-spark's `array_contains`
(`crates/spark/src/function/array/array_contains.rs`) already layers Spark NULL
semantics (`apply_spark_null_semantics`, `compute_row_has_nulls`) and float
normalization (`normalize_floats`, apache/datafusion-spark#34) over
DataFusion's `array_has`. The same approach fits here: call `array_has_any` on
normalized inputs, then set the result to NULL where it is `false`, both rows
are non-empty, and either row has a NULL element. Ideally, move those helpers
into a shared module. Note that the signed-zero rule is version dependent, so
consider making the zero normalization configurable.
- Put the function in `crates/spark/src/function/array/`. Add
`crates/sqllogictest/test_files/array/arrays_overlap.slt` and document it 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]