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]