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

   Part of #178 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `array_intersect(array1, array2)` returns the distinct elements of `array1` 
that also appear in `array2`, in `array1`'s order (first occurrence). A NULL 
element is included once if both arrays contain a NULL. If either array is 
empty, the result is empty.
   
   Argument coercion is the same as for the other binary array set functions 
(`BinaryArrayExpressionWithImplicitCast`). Both arguments are cast to 
`ARRAY<T>`, where `T = TypeCoercion.findTightestCommonType(e1, e2)`. With no 
common type, or an untyped `NULL` (`VOID`) argument, analysis fails with 
`DATATYPE_MISMATCH.BINARY_ARRAY_DIFF_TYPES`. Non-orderable element types 
(`MAP`, `VARIANT`) fail with `DATATYPE_MISMATCH.INVALID_ORDERING_TYPE`.
   
   The result type is `ARRAY<T>` with `containsNull = left.containsNull && 
right.containsNull`. The function is null-intolerant: a NULL array on either 
side gives NULL. Available since 2.4.0, no aliases.
   
   ### Examples
   
   ```sql
   SELECT array_intersect(array(1, 2, 3), array(1, 3, 5));
   -- [1,3]
   SELECT array_intersect(array(4, 2), array(1, 2, 4));
   -- [4,2]
   SELECT array_intersect(array(1, 2, 1, 4), array(4, 2, 4));
   -- [2,4]
   SELECT array_intersect(array(4, 2, 4), array(1, 2, 1, 4));
   -- [4,2]
   SELECT array_intersect(array(1, 2, null, 4, 5), array(-5, 4, null, 2, -1));
   -- [2,null,4]
   SELECT array_intersect(array('c', null, 'a', 'f'), array('b', 'a', null, 
'g'));
   -- [null,"a"]
   SELECT array_intersect(array(null), array(null, null));
   -- [null]
   SELECT array_intersect(CAST(array() AS ARRAY<INT>), array(null));
   -- []
   SELECT array_intersect(array(1d, 2d, null), array(1d));
   -- [1.0]
   SELECT array_intersect(array(array(1, 2), array(3, 4), array(1, 2)), 
array(array(3, 4), array(2, 1)));
   -- [[3,4]]
   -- Spark 4.2.0 (SPARK-54918):
   SELECT array_intersect(array(-0.0D), array(0.0D));
   -- [0.0]
   -- Spark 3.5.x to 4.1.3: []
   ```
   
   ### Edge cases to test
   
   - Result order always follows `array1`, even when `array2` is longer. 
Swapping the arguments changes the order (`[4,2]` compared with `[2,4]` above).
   - NULL handling: NULL appears in the result (at its first position in 
`array1`) only if both sides contain NULL. A NULL only in `array1` is dropped.
   - Empty array on either side gives empty, even if the other side holds only 
NULLs.
   - NULL array argument (typed) gives NULL. Untyped `NULL` is an analysis 
error.
   - Result `containsNull` is the AND of the inputs' `containsNull`. For 
example, `ARRAY<INT>` (no nulls) with `ARRAY<INT>` (nullable) gives 
`containsNull = false`.
   - Type coercion (`INT` with `BIGINT`, `INT` with `DOUBLE`) and the 
`BINARY_ARRAY_DIFF_TYPES` error for `INT` with `STRING`.
   - NaN: all NaN bit patterns are one value. NaN is in the result if both 
sides contain a NaN.
   - Signed zero in flat float arrays: one value in 4.2.0 (output `+0.0`). 
Distinct in 3.5.x to 4.1.3. With `a = array(-0.0D, 0.0D, double('NaN'))` and `b 
= array(0.0D, double('NaN'))`, 4.2.0 returns two elements (`0.0` and `NaN`) per 
its test suite. Reading the 4.1.3 code, it returns `[0.0,NaN]`.
   - Nested floats (`STRUCT`/`ARRAY` elements) compare with `SQLOrderingUtil` 
(`-0.0 = 0.0`, NaN = NaN) on every version.
   - `BINARY`, `ARRAY` and `STRUCT` elements compare by value. Collated strings 
(4.0+) compare under their collation.
   - Literal and column for each argument. Sliced list arrays.
   
   ### Differences across Spark versions
   
   - Signed zero in flat float arrays: in 3.4, merging depends on hash 
collisions. From 3.5.x to 4.1.3 the zeros are distinct (SPARK-45599 made 
`OpenHashSet` use `equals`). In 4.2.0 `NormalizeFloatingNumbers` normalizes 
both arguments (SPARK-54918). Comet's docs say SPARK-59602 adds evaluation-time 
normalization in 4.0.5, 4.1.4 and 4.2.1 (not in the source trees checked).
   - 4.0: `nullIntolerant` refactor, and collation-aware comparison. No other 
behavior change found.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/collectionOperations.scala`,
 `ArrayIntersect` (`trait ArrayBinaryLike`, `trait ArraySetLike`). The hash 
path builds an `SQLOpenHashSet` from `array2`, then walks `array1` with a 
second set for dedupe. Non-hashable types use a nested loop with 
`ordering.equiv`.
   - DataFusion core has `array_intersect` 
(`datafusion/functions-nested/src/set_ops.rs`, `generic_set_loop` with 
`IS_UNION = false`). It is not order-compatible: it builds the lookup set from 
the shorter side and probes the longer side, so when `array1` is shorter than 
`array2`, the output follows `array2`'s order. Reading the code, 
`array_intersect([4, 2], [1, 2, 4])` returns `[2,4]` in DataFusion, while Spark 
returns `[4,2]`. Comet already reports this ordering incompatibility. Other 
differences:
     - The return type is the first argument's type, so `containsNull` comes 
from `array1`. Spark uses `left && right`.
     - NaN payloads are kept and rows compare by bytes, so `NaN` and `-NaN` 
don't match. `-0.0` is normalized to `+0.0` (4.2.0 behavior only).
     - The signature accepts untyped `NULL` and uses DataFusion's coercion, not 
Spark's.
     - If one side's element type is `Null`, it short-circuits to 
`array_distinct` of the other side. That is union-like behavior, so check it is 
not reachable through the Spark coercion.
     The Spark version should always probe `array1` in order. It can still 
build the hash set from `array2`.
   - Reuse the float normalization in 
`crates/spark/src/function/array/array_contains.rs` (`normalize_floats`, see 
apache/datafusion-spark#34) as a shared helper.
   - Put the function in `crates/spark/src/function/array/`. Add 
`crates/sqllogictest/test_files/array/array_intersect.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