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]