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]

Reply via email to