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

   Part of #178 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `array_position(array, element)` returns the 1-based index of the first 
element of `array` equal to `element`,
   as a BIGINT, or `0` if there is no match. NULL elements never match. The 
expression is null-intolerant: if
   `array` or `element` is NULL, the result is NULL.
   
   Type handling:
   - `ImplicitCastInputTypes` casts both sides to 
`findTightestCommonType(elementType, typeof(element))`, for
     example INT and DOUBLE become DOUBLE.
   - If there is no tightest common type (for example INT and STRING), analysis 
fails with
     `DATATYPE_MISMATCH.ARRAY_FUNCTION_DIFF_TYPES`.
   - A non-array first argument fails with 
`DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE`.
   - A `NullType` argument fails with `DATATYPE_MISMATCH.NULL_TYPE`. A NULL 
literal is first typed by the common
     type rule and then returns NULL, as shown below.
   - The element type must be orderable (`TypeUtils.checkForOrderingExpr`), so 
map elements are rejected.
   
   The function exists since Spark 2.4.0 and has no aliases. There is no third 
start-position argument.
   
   ### Examples
   
   ```sql
   SELECT array_position(array(312, 773, 708, 708), 708);
   -- 3
   SELECT array_position(array(312, 773, 708, 708), 414);
   -- 0
   SELECT array_position(array(1), 1.23D);
   -- 0
   SELECT array_position(array(1), 1.0D);
   -- 1
   SELECT array_position(array(1.D), 1);
   -- 1
   SELECT array_position(array(array(1)), array(1.0D));
   -- 1
   SELECT array_position(array(1, null), array(1, null)[0]);
   -- 1
   SELECT array_position(array(1), '1');
   -- error DATATYPE_MISMATCH.ARRAY_FUNCTION_DIFF_TYPES
   ```
   
   From `CollectionExpressionsSuite`:
   
   | array          | element      | result |
   |----------------|--------------|--------|
   | `[1,null,2,3]` | `3`          | `4`    |
   | `[1,null,2,3]` | `0`          | `0`    |
   | `[1,null,2,3]` | `NULL`       | NULL   |
   | `[null,'']`    | `''`         | `2`    |
   | `[null,'']`    | `'a'`        | `0`    |
   | `[null]`       | `1L`         | `0`    |
   | NULL array     | `''`         | NULL   |
   | `[[1,2],[3,4]]`| `[1,2]`      | `1`    |
   | `[[5,6],[2,1]]`| `[1,2]`      | `0`    |
   
   ### Edge cases to test
   
   - A NULL array, a NULL element, or both. A NULL element against an array 
that contains NULLs still gives NULL,
     never the NULL element's index.
   - An empty array gives `0`.
   - Several matches return the first index.
   - The result type is BIGINT (`Int64`), not unsigned.
   - Floating point:
     - Spark compares with `ordering.equiv` (interpreted) or `genEqual` 
(codegen). Both treat NaN as equal to
       NaN, and `-0.0` as equal to `0.0`.
     - `array_position(array(-0.0D), 0.0D)` is `1`.
     - `array_position(array(CAST('NaN' AS DOUBLE)), CAST('NaN' AS DOUBLE))` is 
`1`, including NaNs with
       different bit patterns.
     - The same applies to floats nested in struct and array elements, which 
are compared with SQL ordering.
   - Struct and array elements are compared by value. Binary elements are 
compared by content
     (`Arrays.equals`).
   - Strings use binary equality. Collated strings (4.0+) use collation-aware 
equality, so `UTF8_LCASE` matches
     `'A'` with `'a'`.
   - Implicit widening: INT array with a DOUBLE needle, a DECIMAL needle 
against an INT array, and DATE against
     TIMESTAMP.
   - Map elements fail analysis.
   - Each argument as a literal or a column, including a column needle with 
per-row NULLs.
   
   ### Differences across Spark versions
   
   None found. Only internal refactors: in 4.0, `NullIntolerant` becomes a 
field, `paramIndex` in error messages
   becomes ordinal ("first"), and `sameType` moves to `DataTypeUtils`.
   
   ### Implementation notes
   
   - Spark implements this in 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/collectionOperations.scala`
     (`ArrayPosition`).
   - DataFusion core has `array_position` (aliases `list_position`, 
`array_indexof`, `list_indexof`) in
     `datafusion/functions-nested/src/position.rs`. It differs from Spark as 
follows:
     - It returns `UInt64`, and it returns NULL rather than `0` when nothing 
matches.
     - It uses `not_distinct`, so a NULL needle matches the first NULL element, 
where Spark returns NULL.
     - It compares floats bitwise under the total order, so `-0.0 != 0.0`, and 
NaNs match only with the same bit
       pattern.
     - It accepts an optional third `start_from` argument, which Spark does not 
have.
     - Its type coercion is DataFusion's, not `findTightestCommonType`.
   - One approach is to wrap the DataFusion function:
     - normalize floats the way 
`crates/spark/src/function/array/array_contains.rs` does (`normalize_floats` /
       `normalize_array`, see apache/datafusion-spark#34),
     - return NULL when the needle is NULL,
     - map "not found" to `0`,
     - cast the result to `Int64`.
   - Nested float normalization (inside struct and list elements) also matters 
for set-style functions, so a
     shared helper in `crates/spark/src/function/array/` would be worth it.
   - Add `crates/sqllogictest/test_files/array/array_position.slt` and document 
the function 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