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]

Reply via email to