andygrove opened a new issue, #214:
URL: https://github.com/apache/datafusion-spark/issues/214
Part of #178 and apache/datafusion-comet#7300.
### What the function does
`array_except(array1, array2)` returns the distinct elements of `array1`
that do not appear in `array2`, in `array1`'s order (first occurrence). A NULL
element of `array1` is kept once, unless `array2` also contains a NULL.
Both arguments are cast to `ARRAY<T>` with `T =
TypeCoercion.findTightestCommonType(e1, e2)`
(`BinaryArrayExpressionWithImplicitCast`). 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 the (coerced) type of `array1`, including its `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_except(array(1, 2, 3), array(1, 3, 5));
-- [2]
SELECT array_except(array(1, 2, 4, 3), array(4, 2));
-- [1,3]
SELECT array_except(array(1, 2, 4, 2), array(4, 2, 4));
-- [1]
SELECT array_except(array(1, 2, null, 4, 5, 1), array(1, 2, 4, 2));
-- [null,5]
SELECT array_except(array(1, 2, null, 4, 5, 1), array(-5, 4, null, 2, -1));
-- [1,5]
SELECT array_except(array(1, 2, null, 4, 5, 1), CAST(array() AS ARRAY<INT>));
-- [1,2,null,4,5]
SELECT array_except(array(null, null), CAST(array() AS ARRAY<INT>));
-- [null]
SELECT array_except(array(null, null), array(null));
-- []
SELECT array_except(array(1d, 2d), array(1d, null));
-- [2.0]
SELECT array_except(array(array(1, 2), array(3, 4), array(1, 2)),
array(array(3, 4), array(2, 1), array(3, 4)));
-- [[1,2]]
-- Spark 4.2.0 (SPARK-54918):
SELECT array_except(array(-0.0D), array(0.0D));
-- []
SELECT array_except(array(0.0D), array(-0.0D));
-- []
-- Spark 3.5.x to 4.1.3: [-0.0] and [0.0] respectively
```
### Edge cases to test
- Duplicates in `array1` are removed, even when nothing is subtracted.
- NULL elements: kept once if `array2` has no NULL, removed if it does.
Empty `array1` gives empty.
- NULL array on either side (typed) gives NULL. Untyped `NULL` is an
analysis error.
- Result type and `containsNull` come from `array1` (after coercion), for
example `ARRAY<INT>` minus `ARRAY<BIGINT>` gives `ARRAY<BIGINT>`.
- Type coercion and the `BINARY_ARRAY_DIFF_TYPES` error (`ARRAY<INT>` with
`ARRAY<STRING>`).
- NaN: all NaN bit patterns are one value, so a NaN in `array2` removes
every NaN from `array1`.
- Signed zero in flat float arrays: one value in 4.2.0 (any surviving zero
is output as `+0.0`). Distinct in 3.5.x to 4.1.3, where signs are kept.
- Nested floats in `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). In 4.2.0
both arguments are normalized by `NormalizeFloatingNumbers` (SPARK-54918).
Comet's docs report that 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`,
`ArrayExcept` (`trait ArrayBinaryLike`, `trait ArraySetLike`). The hash path
loads `array2` into one `SQLOpenHashSet` (with NULL and NaN flags), then walks
`array1`, adding each kept element to the same set to dedupe. Other types use a
nested loop with `ordering.equiv`.
- DataFusion core has `array_except`
(`datafusion/functions-nested/src/except.rs`, `general_except`). It uses the
same algorithm (right side into a set, then probe left in order) and so already
matches on ordering, dedupe, NULL elements and NULL arrays. Its return type is
the first argument's type, which matches Spark. It normalizes `-0.0` to `+0.0`
at any depth, which matches 4.2.0. Differences:
- NaN payloads are kept and rows compare by bytes, so `NaN` in `array2`
does not remove `-NaN` from `array1`.
- Pre-4.2 signed-zero behavior can't be expressed.
- The signature accepts untyped `NULL` and uses DataFusion's element
coercion, not `findTightestCommonType`.
A thin wrapper that canonicalizes NaN in both inputs (plus Spark coercion)
is probably enough.
- 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_except.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]