andygrove commented on code in PR #70:
URL: https://github.com/apache/datafusion-spark/pull/70#discussion_r4603902611


##########
crates/spark/src/function/array/array_insert.rs:
##########
@@ -0,0 +1,457 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for 
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) 
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use crate::function::error_utils::unsupported_data_type_exec_err;
+use arrow::array::{
+    Array, ArrayRef, AsArray, Capacities, GenericListArray, Int32Array, 
MutableArrayData,
+    OffsetSizeTrait, make_array,
+};
+use arrow::buffer::{NullBuffer, OffsetBuffer};
+use arrow::datatypes::{DataType, Field, FieldRef};
+use datafusion_common::utils::take_function_args;
+use datafusion_common::{Result, exec_err, internal_err};
+use datafusion_expr::type_coercion::binary::binary_numeric_coercion;
+use datafusion_expr::{
+    ColumnarValue, ReturnFieldArgs, ScalarFunctionArgs, ScalarUDFImpl, 
Signature,
+    Volatility,
+};
+
+/// `ByteArrayUtils.MAX_ROUNDED_ARRAY_LENGTH` (`Integer.MAX_VALUE - 15`) in 
Spark.
+const MAX_ROUNDED_ARRAY_LENGTH: i64 = 2_147_483_632;
+
+/// Spark-compatible `array_insert(array, pos, item)`.
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#array_insert>
+///
+/// - Indexes are 1-based. A negative index counts from the end, where `-1`
+///   inserts after the last element.
+/// - An index past the end pads with NULLs. A negative index before the start
+///   places the item first, then NULL padding, then the original elements.
+/// - The result is NULL when the array or the index is NULL. A NULL item is
+///   inserted as a NULL element.
+/// - Index `0` is an error.
+/// - The index may be any integer type and is cast to INT, because DataFusion
+///   types bare integer literals as BIGINT.
+///
+/// Spark's `spark.sql.legacy.negativeIndexInArrayInsert` (default `false`) 
makes
+/// `-1` insert before the last element. Use 
[`Self::with_legacy_negative_index`].
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkArrayInsert {
+    signature: Signature,
+    legacy_negative_index: bool,
+}
+
+impl Default for SparkArrayInsert {
+    fn default() -> Self {
+        Self::new()
+    }
+}
+
+impl SparkArrayInsert {
+    pub fn new() -> Self {
+        Self::with_legacy_negative_index(false)
+    }
+
+    pub fn with_legacy_negative_index(legacy_negative_index: bool) -> Self {
+        Self {
+            signature: Signature::user_defined(Volatility::Immutable),
+            legacy_negative_index,
+        }
+    }
+}
+
+/// The element field of a list type.
+fn list_element_field(data_type: &DataType) -> Option<&FieldRef> {
+    match data_type {
+        DataType::List(f) | DataType::LargeList(f) | 
DataType::FixedSizeList(f, _) => {
+            Some(f)
+        }
+        _ => None,
+    }
+}
+
+impl ScalarUDFImpl for SparkArrayInsert {
+    fn name(&self) -> &str {
+        "array_insert"
+    }
+
+    fn signature(&self) -> &Signature {
+        &self.signature
+    }
+
+    fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+        internal_err!("return_field_from_args should be used instead")
+    }
+
+    fn return_field_from_args(&self, args: ReturnFieldArgs) -> 
Result<FieldRef> {
+        let [array, pos, _item] = take_function_args(self.name(), 
args.arg_fields)?;
+        let Some(elem) = list_element_field(array.data_type()) else {
+            return internal_err!(
+                "array_insert expects a list, got {}",
+                array.data_type()
+            );
+        };
+        // Padding can introduce NULLs even when neither input has any, so the
+        // element field is always nullable.
+        let elem = Arc::new(Field::new(elem.name(), elem.data_type().clone(), 
true));
+        let data_type = match array.data_type() {
+            DataType::LargeList(_) => DataType::LargeList(elem),
+            _ => DataType::List(elem),
+        };
+        Ok(Arc::new(Field::new(
+            self.name(),
+            data_type,
+            array.is_nullable() || pos.is_nullable(),
+        )))
+    }
+
+    fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+        let [array, pos, item] = take_function_args(self.name(), arg_types)?;
+
+        let (elem, wrap): (&FieldRef, fn(FieldRef) -> DataType) = match array {
+            DataType::List(f) | DataType::FixedSizeList(f, _) => (f, 
DataType::List),
+            DataType::LargeList(f) => (f, DataType::LargeList),
+            _ => {
+                return Err(unsupported_data_type_exec_err(
+                    self.name(),
+                    "an array as first argument",
+                    array,
+                ));
+            }
+        };
+
+        // Spark takes an INT index. DataFusion types bare integer literals as
+        // BIGINT, so accept every integer type and cast to INT; a value 
outside
+        // the INT range then fails the cast instead of being silently 
truncated.
+        if !pos.is_integer() {
+            return Err(unsupported_data_type_exec_err(
+                self.name(),
+                "an INT index as second argument",
+                pos,
+            ));
+        }
+
+        // Spark uses the tightest common type of the element and the item.
+        let elem_type = elem.data_type();
+        let common = if elem_type == item || *item == DataType::Null {
+            elem_type.clone()
+        } else if *elem_type == DataType::Null {
+            item.clone()
+        } else if let Some(common) = binary_numeric_coercion(elem_type, item) {
+            common
+        } else {
+            return exec_err!(

Review Comment:
   Done in 918dc42. `spark_rejects_pair`, the DataFusion fallback and the local 
type-name helpers are gone. `coerce_types` calls `tightest_common_type(elem, 
item, false)` from #345 (which moved it, with the map key and value widening 
fix, into `function/type_coercion.rs`), together with its non-ANSI 
`struct_field_types_match`, as `array_position` and `array_remove` do. New 
`.slt` cases cover reordered struct fields and an INT vs BIGINT struct field, 
both `ARRAY_FUNCTION_DIFF_TYPES`. The existing map widening case passes. I 
confirmed all three on PySpark 4.2.0. This PR is now stacked on #345.



##########
crates/spark/src/function/array/array_insert.rs:
##########
@@ -0,0 +1,792 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for 
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) 
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use arrow::array::{
+    Array, ArrayRef, AsArray, Capacities, GenericListArray, Int32Array, 
MutableArrayData,
+    OffsetSizeTrait, make_array,
+};
+use arrow::buffer::{NullBuffer, OffsetBuffer};
+use arrow::datatypes::{DataType, Field, FieldRef, Int32Type};
+use datafusion_common::utils::take_function_args;
+use datafusion_common::{
+    DataFusionError, Result, ScalarValue, exec_err, internal_err, 
plan_datafusion_err,
+    plan_err,
+};
+use datafusion_expr::type_coercion::binary::{
+    binary_numeric_coercion, type_union_resolution,
+};
+use datafusion_expr::{
+    ColumnarValue, Documentation, ReturnFieldArgs, ScalarFunctionArgs, 
ScalarUDFImpl,
+    Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+/// `ByteArrayUtils.MAX_ROUNDED_ARRAY_LENGTH` (`Integer.MAX_VALUE - 15`) in 
Spark.
+const MAX_ROUNDED_ARRAY_LENGTH: i64 = 2_147_483_632;
+
+/// Spark-compatible `array_insert` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#array_insert>
+///
+/// Use [`Self::with_legacy_negative_index`] for Spark's
+/// `spark.sql.legacy.negativeIndexInArrayInsert`.
+#[user_doc(
+    doc_section(label = "Array Functions"),
+    description = "Returns `array` with `item` inserted at index `pos`. 
DataFusion has no built-in `array_insert`. \
+        Indexes are 1-based, and a negative index counts from the end, so -1 
inserts after the last element. \
+        An index past the end pads with NULLs before the item. A negative 
index before the start places the item first, \
+        then NULL padding, then the original elements. Returns NULL if `array` 
or `pos` is NULL. \
+        An index of 0 is an error (`INVALID_INDEX_OF_ZERO`), as is a result 
longer than Spark's maximum array length \
+        (`COLLECTION_SIZE_LIMIT_EXCEEDED`). With Spark's 
`spark.sql.legacy.negativeIndexInArrayInsert`, \
+        -1 inserts before the last element instead, and a negative index 
before the start pads one more NULL, \
+        so -(n+1) on an array of n elements gives the item, one NULL, then the 
elements.",
+    syntax_example = "array_insert(array, pos, item)",
+    sql_example = r#"```sql
+> SELECT array_insert(make_array(1, 2, 3), 2, 10);
+[1, 10, 2, 3]
+> SELECT array_insert(make_array(5, 4, 3, 2), -1, 1);
+[5, 4, 3, 2, 1]
+> SELECT array_insert(make_array(1, 2, 3), 5, 100);
+[1, 2, 3, NULL, 100]
+> SELECT array_insert(make_array(1), -3, 100);
+[100, NULL, 1]
+> SELECT array_insert(make_array(1, 2), NULL::INT, 5);
+NULL
+```"#,
+    argument(
+        name = "array",
+        description = "Array expression to insert into. NULL returns NULL."
+    ),
+    argument(
+        name = "pos",
+        description = "Integer index, 1-based, of any integer type (cast to 
INT). A negative value counts from the end. 0 is an error. NULL returns NULL."
+    ),
+    argument(
+        name = "item",
+        description = "Value to insert. Must share a type family with the 
array's elements. A NULL item is inserted as a NULL element."
+    )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkArrayInsert {
+    signature: Signature,
+    legacy_negative_index: bool,
+}
+
+impl Default for SparkArrayInsert {
+    fn default() -> Self {
+        Self::new()
+    }
+}
+
+impl SparkArrayInsert {
+    pub fn new() -> Self {
+        Self {
+            signature: Signature::user_defined(Volatility::Immutable),
+            legacy_negative_index: false,
+        }
+    }
+
+    /// Enables Spark's legacy negative index behavior
+    /// (`spark.sql.legacy.negativeIndexInArrayInsert`): -1 inserts before the
+    /// last element, and a negative index before the start pads one more NULL.
+    ///
+    /// For example `SparkArrayInsert::new().with_legacy_negative_index(true)`.
+    /// Once `SparkOptions` lands (#92), a DataFusion session would read this
+    /// from `spark.sql.legacy.negativeIndexInArrayInsert` instead.
+    pub fn with_legacy_negative_index(mut self, legacy_negative_index: bool) 
-> Self {
+        self.legacy_negative_index = legacy_negative_index;
+        self
+    }
+}
+
+/// The element field of a list type.
+fn list_element_field(data_type: &DataType) -> Option<&FieldRef> {
+    match data_type {
+        DataType::List(f) | DataType::LargeList(f) | 
DataType::FixedSizeList(f, _) => {
+            Some(f)
+        }
+        _ => None,
+    }
+}
+
+/// The precision and scale of a decimal type.
+fn decimal_precision_scale(t: &DataType) -> Option<(u8, i8)> {
+    match t {
+        DataType::Decimal32(p, s)
+        | DataType::Decimal64(p, s)
+        | DataType::Decimal128(p, s)
+        | DataType::Decimal256(p, s) => Some((*p, *s)),
+        _ => None,
+    }
+}
+
+/// The precision Spark's `DecimalType.forType` gives an integer type.
+fn integer_digits(t: &DataType) -> Option<i16> {
+    match t {
+        DataType::Int8 | DataType::UInt8 => Some(3),
+        DataType::Int16 | DataType::UInt16 => Some(5),
+        DataType::Int32 | DataType::UInt32 => Some(10),
+        DataType::Int64 | DataType::UInt64 => Some(20),
+        _ => None,
+    }
+}
+
+/// Whether Spark's `findTightestCommonType` has no common type for `a` and
+/// `b` where DataFusion's coercion finds one, at the top level or inside a
+/// list, struct or map.
+///
+/// DataFusion coerces these pairs (for example INT and STRING to STRING, or
+/// two decimals to a wider decimal), but Spark rejects them:
+/// - a string or binary type with a type of another kind,
+/// - a decimal with a floating point type,
+/// - two decimals of different precision or scale,
+/// - an integer with a decimal that cannot hold the whole integer range
+///   (Spark's `DecimalType.isWiderThan`).
+fn spark_rejects_pair(a: &DataType, b: &DataType) -> bool {
+    #[derive(PartialEq)]
+    enum Kind {
+        String,
+        Binary,
+        Other,
+    }
+    fn kind(t: &DataType) -> Kind {
+        match t {
+            DataType::Utf8 | DataType::LargeUtf8 | DataType::Utf8View => 
Kind::String,
+            DataType::Binary
+            | DataType::LargeBinary
+            | DataType::BinaryView
+            | DataType::FixedSizeBinary(_) => Kind::Binary,
+            _ => Kind::Other,
+        }
+    }
+    match (a, b) {
+        (DataType::Null, _) | (_, DataType::Null) => false,
+        (DataType::Dictionary(_, v), other) | (other, DataType::Dictionary(_, 
v)) => {
+            spark_rejects_pair(v, other)
+        }
+        (DataType::RunEndEncoded(_, v), other)
+        | (other, DataType::RunEndEncoded(_, v)) => {
+            spark_rejects_pair(v.data_type(), other)
+        }
+        (DataType::Struct(fa), DataType::Struct(fb)) => fa.iter().any(|fa| {
+            fb.iter()
+                .find(|fb| fb.name() == fa.name())
+                .is_some_and(|fb| spark_rejects_pair(fa.data_type(), 
fb.data_type()))
+        }),
+        // A map's entries are a struct of the key and the value.
+        (DataType::Map(ea, _), DataType::Map(eb, _)) => {
+            match (ea.data_type(), eb.data_type()) {
+                (DataType::Struct(ka), DataType::Struct(kb)) => ka
+                    .iter()
+                    .zip(kb.iter())
+                    .any(|(fa, fb)| spark_rejects_pair(fa.data_type(), 
fb.data_type())),
+                _ => false,
+            }
+        }
+        _ => {
+            if let (Some(fa), Some(fb)) = (list_element_field(a), 
list_element_field(b)) {
+                return spark_rejects_pair(fa.data_type(), fb.data_type());
+            }
+            if kind(a) != kind(b) {
+                return true;
+            }
+            let (decimal, other) =
+                match (decimal_precision_scale(a), decimal_precision_scale(b)) 
{
+                    (Some(da), Some(db)) => return da != db,
+                    (Some(d), None) => (d, b),
+                    (None, Some(d)) => (d, a),
+                    (None, None) => return false,
+                };
+            let (precision, scale) = decimal;
+            if other.is_floating() {
+                true
+            } else if let Some(digits) = integer_digits(other) {
+                scale < 0 || i16::from(precision) - i16::from(scale) < digits
+            } else {
+                false
+            }
+        }
+    }
+}
+
+/// The Spark SQL name of a type, as Spark prints it in error messages.
+fn spark_type_name(t: &DataType) -> String {
+    match t {
+        DataType::Null => "VOID".to_string(),
+        DataType::Boolean => "BOOLEAN".to_string(),
+        DataType::Int8 => "TINYINT".to_string(),
+        DataType::Int16 => "SMALLINT".to_string(),
+        DataType::Int32 => "INT".to_string(),
+        DataType::Int64 => "BIGINT".to_string(),
+        DataType::Float32 => "FLOAT".to_string(),
+        DataType::Float64 => "DOUBLE".to_string(),
+        DataType::Utf8 | DataType::LargeUtf8 | DataType::Utf8View => 
"STRING".to_string(),
+        DataType::Binary
+        | DataType::LargeBinary
+        | DataType::BinaryView
+        | DataType::FixedSizeBinary(_) => "BINARY".to_string(),
+        DataType::Date32 | DataType::Date64 => "DATE".to_string(),
+        DataType::Timestamp(_, None) => "TIMESTAMP_NTZ".to_string(),
+        DataType::Timestamp(_, Some(_)) => "TIMESTAMP".to_string(),
+        DataType::Dictionary(_, v) => spark_type_name(v),
+        DataType::RunEndEncoded(_, v) => spark_type_name(v.data_type()),
+        DataType::List(f) | DataType::LargeList(f) | 
DataType::FixedSizeList(f, _) => {
+            format!("ARRAY<{}>", spark_type_name(f.data_type()))
+        }
+        DataType::Struct(fields) => {
+            let fields: Vec<String> = fields
+                .iter()
+                .map(|f| format!("{}: {}", f.name(), 
spark_type_name(f.data_type())))
+                .collect();
+            format!("STRUCT<{}>", fields.join(", "))
+        }
+        DataType::Map(entries, _) => match entries.data_type() {
+            DataType::Struct(kv) if kv.len() == 2 => format!(
+                "MAP<{}, {}>",
+                spark_type_name(kv[0].data_type()),
+                spark_type_name(kv[1].data_type())
+            ),
+            _ => t.to_string(),
+        },
+        other => match decimal_precision_scale(other) {
+            Some((p, s)) => format!("DECIMAL({p},{s})"),
+            None => other.to_string(),
+        },
+    }
+}
+
+/// Spark's error when the first argument is not an array, or the item does
+/// not share a type with the array's elements.
+fn diff_types_err(array: &DataType, item: &DataType) -> DataFusionError {
+    plan_datafusion_err!(
+        "[DATATYPE_MISMATCH.ARRAY_FUNCTION_DIFF_TYPES] Input to `array_insert` 
should have been \"ARRAY\" followed by a value with same element type, but it's 
[\"{}\", \"{}\"].",
+        spark_type_name(array),
+        spark_type_name(item)
+    )
+}
+
+impl ScalarUDFImpl for SparkArrayInsert {
+    fn name(&self) -> &str {
+        "array_insert"
+    }
+
+    fn signature(&self) -> &Signature {
+        &self.signature
+    }
+
+    fn documentation(&self) -> Option<&Documentation> {
+        self.doc()
+    }
+
+    fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+        internal_err!("return_field_from_args should be used instead")
+    }
+
+    fn return_field_from_args(&self, args: ReturnFieldArgs) -> 
Result<FieldRef> {
+        let [array, pos, _item] = take_function_args(self.name(), 
args.arg_fields)?;
+        let Some(elem) = list_element_field(array.data_type()) else {
+            return internal_err!(
+                "array_insert expects a list, got {}",
+                array.data_type()
+            );
+        };
+        // Padding can introduce NULLs even when neither input has any, so the
+        // element field is always nullable.
+        let elem = Arc::new(Field::new(elem.name(), elem.data_type().clone(), 
true));
+        let data_type = match array.data_type() {
+            DataType::LargeList(_) => DataType::LargeList(elem),
+            _ => DataType::List(elem),
+        };
+        Ok(Arc::new(Field::new(
+            self.name(),
+            data_type,
+            array.is_nullable() || pos.is_nullable(),
+        )))
+    }
+
+    fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+        let [array, pos, item] = take_function_args(self.name(), arg_types)?;
+
+        let (elem, wrap): (&FieldRef, fn(FieldRef) -> DataType) = match array {
+            DataType::List(f) | DataType::FixedSizeList(f, _) => (f, 
DataType::List),
+            DataType::LargeList(f) => (f, DataType::LargeList),
+            _ => return Err(diff_types_err(array, item)),
+        };
+
+        // Spark takes an INT index. DataFusion types bare integer literals as
+        // BIGINT, so accept every integer type and cast to INT; a value 
outside
+        // the INT range then fails the cast instead of being silently 
truncated.
+        if !pos.is_integer() {
+            return plan_err!(
+                "[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] The second 
parameter of `array_insert` requires the \"INT\" type, however it has the type 
\"{}\".",
+                spark_type_name(pos)
+            );
+        }
+
+        // Spark uses the tightest common type of the element and the item.
+        // Besides numeric widening, that unifies types of the same family, 
such
+        // as Utf8 and Utf8View (parquet's default for strings), timestamps of
+        // different units, and nested types that differ only in nullability.
+        let elem_type = elem.data_type();
+        let common = if elem_type == item || *item == DataType::Null {
+            Some(elem_type.clone())
+        } else if *elem_type == DataType::Null {
+            Some(item.clone())
+        } else if spark_rejects_pair(elem_type, item) {
+            None
+        } else if let Some(common) = binary_numeric_coercion(elem_type, item) {
+            Some(common)
+        } else {
+            type_union_resolution(&[elem_type.clone(), item.clone()])
+        };
+        let Some(common) = common else {
+            return Err(diff_types_err(array, item));
+        };
+
+        let new_elem =
+            Arc::new(Field::new(elem.name(), common.clone(), 
elem.is_nullable()));
+        Ok(vec![wrap(new_elem), DataType::Int32, common])
+    }
+
+    fn invoke_with_args(&self, args: ScalarFunctionArgs) -> 
Result<ColumnarValue> {
+        let all_scalar = args
+            .args
+            .iter()
+            .all(|arg| matches!(arg, ColumnarValue::Scalar(_)));
+        let arrays = ColumnarValue::values_to_arrays(&args.args)?;
+        let [list, pos, item] = take_function_args(self.name(), arrays)?;
+        let Some(pos) = pos.as_primitive_opt::<Int32Type>() else {
+            return internal_err!(
+                "array_insert expects an Int32 index, got {}",
+                pos.data_type()
+            );
+        };
+        let legacy = self.legacy_negative_index;
+        let Some(elem_field) = 
list_element_field(args.return_field.data_type()) else {
+            return internal_err!(
+                "unexpected array_insert return type {}",
+                args.return_field.data_type()
+            );
+        };
+        let elem_field = Arc::clone(elem_field);
+        let result: ArrayRef = match list.data_type() {
+            DataType::List(_) => Arc::new(array_insert(
+                list.as_list::<i32>(),
+                &item,
+                pos,
+                legacy,
+                elem_field,
+            )?),
+            DataType::LargeList(_) => Arc::new(array_insert(
+                list.as_list::<i64>(),
+                &item,
+                pos,
+                legacy,
+                elem_field,
+            )?),
+            other => return internal_err!("array_insert expects a list, got 
{other}"),
+        };
+        if all_scalar {
+            Ok(ColumnarValue::Scalar(ScalarValue::try_from_array(
+                &result, 0,
+            )?))
+        } else {
+            Ok(ColumnarValue::Array(result))
+        }
+    }
+}
+
+/// Where the item goes in a row, and how long the row becomes.
+struct Placement {
+    /// Original elements copied before the item.
+    before: usize,
+    /// NULLs between the item and the original elements (negative index
+    /// before the start of the array).
+    leading_nulls: usize,
+    /// NULLs between the original elements and the item (positive index past
+    /// the end of the array).
+    trailing_nulls: usize,
+    /// Length of the resulting row.
+    new_len: usize,
+}
+
+fn place(pos: i32, len: usize, legacy: bool) -> Result<Placement> {
+    if pos == 0 {
+        return exec_err!(
+            "[INVALID_INDEX_OF_ZERO] The index 0 is invalid. An index shall be 
either < 0 or > 0 (the first element has index 1)."
+        );
+    }
+    let n = len as i64;
+    let pos = i64::from(pos);
+    // (original elements before the item, leading NULLs, trailing NULLs, new 
length)
+    let (before, leading, trailing, new_len) = if pos > 0 {
+        let idx = pos - 1;
+        if idx <= n {
+            (idx, 0, 0, n + 1)
+        } else {
+            (n, 0, idx - n, pos)
+        }
+    } else if -pos > n {
+        let new_len = -pos + i64::from(legacy);
+        (0, new_len - n - 1, 0, new_len)
+    } else {
+        (n + pos + i64::from(!legacy), 0, 0, n + 1)
+    };
+    if new_len > MAX_ROUNDED_ARRAY_LENGTH {
+        return exec_err!(
+            "[COLLECTION_SIZE_LIMIT_EXCEEDED.FUNCTION] Can't create array with 
{new_len} elements which exceeding the array size limit 
{MAX_ROUNDED_ARRAY_LENGTH}, unsuccessful try to create arrays in the function 
`array_insert`."
+        );
+    }

Review Comment:
   Done in 918dc42, with `22003` and `54000`, and the `INVALID_INDEX_OF_ZERO` 
and size-limit cases now assert the full text. The two planning errors also end 
with `SQLSTATE: 42K09` now, as in Spark.



##########
crates/spark/src/function/array/array_insert.rs:
##########
@@ -0,0 +1,792 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for 
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) 
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use arrow::array::{
+    Array, ArrayRef, AsArray, Capacities, GenericListArray, Int32Array, 
MutableArrayData,
+    OffsetSizeTrait, make_array,
+};
+use arrow::buffer::{NullBuffer, OffsetBuffer};
+use arrow::datatypes::{DataType, Field, FieldRef, Int32Type};
+use datafusion_common::utils::take_function_args;
+use datafusion_common::{
+    DataFusionError, Result, ScalarValue, exec_err, internal_err, 
plan_datafusion_err,
+    plan_err,
+};
+use datafusion_expr::type_coercion::binary::{
+    binary_numeric_coercion, type_union_resolution,
+};
+use datafusion_expr::{
+    ColumnarValue, Documentation, ReturnFieldArgs, ScalarFunctionArgs, 
ScalarUDFImpl,
+    Signature, Volatility,
+};
+use datafusion_macros::user_doc;
+
+/// `ByteArrayUtils.MAX_ROUNDED_ARRAY_LENGTH` (`Integer.MAX_VALUE - 15`) in 
Spark.
+const MAX_ROUNDED_ARRAY_LENGTH: i64 = 2_147_483_632;
+
+/// Spark-compatible `array_insert` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#array_insert>
+///
+/// Use [`Self::with_legacy_negative_index`] for Spark's
+/// `spark.sql.legacy.negativeIndexInArrayInsert`.
+#[user_doc(
+    doc_section(label = "Array Functions"),
+    description = "Returns `array` with `item` inserted at index `pos`. 
DataFusion has no built-in `array_insert`. \
+        Indexes are 1-based, and a negative index counts from the end, so -1 
inserts after the last element. \
+        An index past the end pads with NULLs before the item. A negative 
index before the start places the item first, \
+        then NULL padding, then the original elements. Returns NULL if `array` 
or `pos` is NULL. \
+        An index of 0 is an error (`INVALID_INDEX_OF_ZERO`), as is a result 
longer than Spark's maximum array length \
+        (`COLLECTION_SIZE_LIMIT_EXCEEDED`). With Spark's 
`spark.sql.legacy.negativeIndexInArrayInsert`, \
+        -1 inserts before the last element instead, and a negative index 
before the start pads one more NULL, \
+        so -(n+1) on an array of n elements gives the item, one NULL, then the 
elements.",
+    syntax_example = "array_insert(array, pos, item)",
+    sql_example = r#"```sql
+> SELECT array_insert(make_array(1, 2, 3), 2, 10);
+[1, 10, 2, 3]
+> SELECT array_insert(make_array(5, 4, 3, 2), -1, 1);
+[5, 4, 3, 2, 1]
+> SELECT array_insert(make_array(1, 2, 3), 5, 100);
+[1, 2, 3, NULL, 100]
+> SELECT array_insert(make_array(1), -3, 100);
+[100, NULL, 1]
+> SELECT array_insert(make_array(1, 2), NULL::INT, 5);
+NULL
+```"#,
+    argument(
+        name = "array",
+        description = "Array expression to insert into. NULL returns NULL."
+    ),
+    argument(
+        name = "pos",
+        description = "Integer index, 1-based, of any integer type (cast to 
INT). A negative value counts from the end. 0 is an error. NULL returns NULL."
+    ),
+    argument(
+        name = "item",
+        description = "Value to insert. Must share a type family with the 
array's elements. A NULL item is inserted as a NULL element."
+    )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkArrayInsert {
+    signature: Signature,
+    legacy_negative_index: bool,
+}
+
+impl Default for SparkArrayInsert {
+    fn default() -> Self {
+        Self::new()
+    }
+}
+
+impl SparkArrayInsert {
+    pub fn new() -> Self {
+        Self {
+            signature: Signature::user_defined(Volatility::Immutable),
+            legacy_negative_index: false,
+        }
+    }
+
+    /// Enables Spark's legacy negative index behavior
+    /// (`spark.sql.legacy.negativeIndexInArrayInsert`): -1 inserts before the
+    /// last element, and a negative index before the start pads one more NULL.
+    ///
+    /// For example `SparkArrayInsert::new().with_legacy_negative_index(true)`.
+    /// Once `SparkOptions` lands (#92), a DataFusion session would read this
+    /// from `spark.sql.legacy.negativeIndexInArrayInsert` instead.
+    pub fn with_legacy_negative_index(mut self, legacy_negative_index: bool) 
-> Self {
+        self.legacy_negative_index = legacy_negative_index;
+        self
+    }
+}
+
+/// The element field of a list type.
+fn list_element_field(data_type: &DataType) -> Option<&FieldRef> {
+    match data_type {
+        DataType::List(f) | DataType::LargeList(f) | 
DataType::FixedSizeList(f, _) => {
+            Some(f)
+        }
+        _ => None,
+    }
+}
+
+/// The precision and scale of a decimal type.
+fn decimal_precision_scale(t: &DataType) -> Option<(u8, i8)> {
+    match t {
+        DataType::Decimal32(p, s)
+        | DataType::Decimal64(p, s)
+        | DataType::Decimal128(p, s)
+        | DataType::Decimal256(p, s) => Some((*p, *s)),
+        _ => None,
+    }
+}
+
+/// The precision Spark's `DecimalType.forType` gives an integer type.
+fn integer_digits(t: &DataType) -> Option<i16> {
+    match t {
+        DataType::Int8 | DataType::UInt8 => Some(3),
+        DataType::Int16 | DataType::UInt16 => Some(5),
+        DataType::Int32 | DataType::UInt32 => Some(10),
+        DataType::Int64 | DataType::UInt64 => Some(20),
+        _ => None,
+    }
+}
+
+/// Whether Spark's `findTightestCommonType` has no common type for `a` and
+/// `b` where DataFusion's coercion finds one, at the top level or inside a
+/// list, struct or map.
+///
+/// DataFusion coerces these pairs (for example INT and STRING to STRING, or
+/// two decimals to a wider decimal), but Spark rejects them:
+/// - a string or binary type with a type of another kind,
+/// - a decimal with a floating point type,
+/// - two decimals of different precision or scale,
+/// - an integer with a decimal that cannot hold the whole integer range
+///   (Spark's `DecimalType.isWiderThan`).
+fn spark_rejects_pair(a: &DataType, b: &DataType) -> bool {
+    #[derive(PartialEq)]
+    enum Kind {
+        String,
+        Binary,
+        Other,
+    }
+    fn kind(t: &DataType) -> Kind {
+        match t {
+            DataType::Utf8 | DataType::LargeUtf8 | DataType::Utf8View => 
Kind::String,
+            DataType::Binary
+            | DataType::LargeBinary
+            | DataType::BinaryView
+            | DataType::FixedSizeBinary(_) => Kind::Binary,
+            _ => Kind::Other,
+        }
+    }
+    match (a, b) {
+        (DataType::Null, _) | (_, DataType::Null) => false,
+        (DataType::Dictionary(_, v), other) | (other, DataType::Dictionary(_, 
v)) => {
+            spark_rejects_pair(v, other)
+        }
+        (DataType::RunEndEncoded(_, v), other)
+        | (other, DataType::RunEndEncoded(_, v)) => {
+            spark_rejects_pair(v.data_type(), other)
+        }
+        (DataType::Struct(fa), DataType::Struct(fb)) => fa.iter().any(|fa| {
+            fb.iter()
+                .find(|fb| fb.name() == fa.name())
+                .is_some_and(|fb| spark_rejects_pair(fa.data_type(), 
fb.data_type()))
+        }),
+        // A map's entries are a struct of the key and the value.
+        (DataType::Map(ea, _), DataType::Map(eb, _)) => {
+            match (ea.data_type(), eb.data_type()) {
+                (DataType::Struct(ka), DataType::Struct(kb)) => ka
+                    .iter()
+                    .zip(kb.iter())
+                    .any(|(fa, fb)| spark_rejects_pair(fa.data_type(), 
fb.data_type())),
+                _ => false,
+            }
+        }
+        _ => {
+            if let (Some(fa), Some(fb)) = (list_element_field(a), 
list_element_field(b)) {
+                return spark_rejects_pair(fa.data_type(), fb.data_type());
+            }
+            if kind(a) != kind(b) {
+                return true;
+            }
+            let (decimal, other) =
+                match (decimal_precision_scale(a), decimal_precision_scale(b)) 
{
+                    (Some(da), Some(db)) => return da != db,
+                    (Some(d), None) => (d, b),
+                    (None, Some(d)) => (d, a),
+                    (None, None) => return false,
+                };
+            let (precision, scale) = decimal;
+            if other.is_floating() {
+                true
+            } else if let Some(digits) = integer_digits(other) {
+                scale < 0 || i16::from(precision) - i16::from(scale) < digits
+            } else {
+                false
+            }
+        }
+    }
+}
+
+/// The Spark SQL name of a type, as Spark prints it in error messages.
+fn spark_type_name(t: &DataType) -> String {
+    match t {
+        DataType::Null => "VOID".to_string(),
+        DataType::Boolean => "BOOLEAN".to_string(),
+        DataType::Int8 => "TINYINT".to_string(),
+        DataType::Int16 => "SMALLINT".to_string(),
+        DataType::Int32 => "INT".to_string(),
+        DataType::Int64 => "BIGINT".to_string(),
+        DataType::Float32 => "FLOAT".to_string(),
+        DataType::Float64 => "DOUBLE".to_string(),
+        DataType::Utf8 | DataType::LargeUtf8 | DataType::Utf8View => 
"STRING".to_string(),
+        DataType::Binary
+        | DataType::LargeBinary
+        | DataType::BinaryView
+        | DataType::FixedSizeBinary(_) => "BINARY".to_string(),
+        DataType::Date32 | DataType::Date64 => "DATE".to_string(),
+        DataType::Timestamp(_, None) => "TIMESTAMP_NTZ".to_string(),
+        DataType::Timestamp(_, Some(_)) => "TIMESTAMP".to_string(),
+        DataType::Dictionary(_, v) => spark_type_name(v),
+        DataType::RunEndEncoded(_, v) => spark_type_name(v.data_type()),
+        DataType::List(f) | DataType::LargeList(f) | 
DataType::FixedSizeList(f, _) => {
+            format!("ARRAY<{}>", spark_type_name(f.data_type()))
+        }
+        DataType::Struct(fields) => {
+            let fields: Vec<String> = fields
+                .iter()
+                .map(|f| format!("{}: {}", f.name(), 
spark_type_name(f.data_type())))
+                .collect();
+            format!("STRUCT<{}>", fields.join(", "))
+        }
+        DataType::Map(entries, _) => match entries.data_type() {
+            DataType::Struct(kv) if kv.len() == 2 => format!(
+                "MAP<{}, {}>",
+                spark_type_name(kv[0].data_type()),
+                spark_type_name(kv[1].data_type())
+            ),
+            _ => t.to_string(),
+        },
+        other => match decimal_precision_scale(other) {
+            Some((p, s)) => format!("DECIMAL({p},{s})"),
+            None => other.to_string(),
+        },
+    }
+}
+
+/// Spark's error when the first argument is not an array, or the item does
+/// not share a type with the array's elements.
+fn diff_types_err(array: &DataType, item: &DataType) -> DataFusionError {
+    plan_datafusion_err!(
+        "[DATATYPE_MISMATCH.ARRAY_FUNCTION_DIFF_TYPES] Input to `array_insert` 
should have been \"ARRAY\" followed by a value with same element type, but it's 
[\"{}\", \"{}\"].",
+        spark_type_name(array),
+        spark_type_name(item)
+    )
+}
+
+impl ScalarUDFImpl for SparkArrayInsert {
+    fn name(&self) -> &str {
+        "array_insert"
+    }
+
+    fn signature(&self) -> &Signature {
+        &self.signature
+    }
+
+    fn documentation(&self) -> Option<&Documentation> {
+        self.doc()
+    }
+
+    fn return_type(&self, _arg_types: &[DataType]) -> Result<DataType> {
+        internal_err!("return_field_from_args should be used instead")
+    }
+
+    fn return_field_from_args(&self, args: ReturnFieldArgs) -> 
Result<FieldRef> {
+        let [array, pos, _item] = take_function_args(self.name(), 
args.arg_fields)?;
+        let Some(elem) = list_element_field(array.data_type()) else {
+            return internal_err!(
+                "array_insert expects a list, got {}",
+                array.data_type()
+            );
+        };
+        // Padding can introduce NULLs even when neither input has any, so the
+        // element field is always nullable.
+        let elem = Arc::new(Field::new(elem.name(), elem.data_type().clone(), 
true));
+        let data_type = match array.data_type() {
+            DataType::LargeList(_) => DataType::LargeList(elem),
+            _ => DataType::List(elem),
+        };
+        Ok(Arc::new(Field::new(
+            self.name(),
+            data_type,
+            array.is_nullable() || pos.is_nullable(),
+        )))
+    }
+
+    fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+        let [array, pos, item] = take_function_args(self.name(), arg_types)?;
+
+        let (elem, wrap): (&FieldRef, fn(FieldRef) -> DataType) = match array {
+            DataType::List(f) | DataType::FixedSizeList(f, _) => (f, 
DataType::List),
+            DataType::LargeList(f) => (f, DataType::LargeList),
+            _ => return Err(diff_types_err(array, item)),
+        };
+
+        // Spark takes an INT index. DataFusion types bare integer literals as
+        // BIGINT, so accept every integer type and cast to INT; a value 
outside
+        // the INT range then fails the cast instead of being silently 
truncated.
+        if !pos.is_integer() {
+            return plan_err!(
+                "[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] The second 
parameter of `array_insert` requires the \"INT\" type, however it has the type 
\"{}\".",
+                spark_type_name(pos)
+            );
+        }
+
+        // Spark uses the tightest common type of the element and the item.
+        // Besides numeric widening, that unifies types of the same family, 
such
+        // as Utf8 and Utf8View (parquet's default for strings), timestamps of
+        // different units, and nested types that differ only in nullability.
+        let elem_type = elem.data_type();
+        let common = if elem_type == item || *item == DataType::Null {
+            Some(elem_type.clone())
+        } else if *elem_type == DataType::Null {
+            Some(item.clone())
+        } else if spark_rejects_pair(elem_type, item) {
+            None
+        } else if let Some(common) = binary_numeric_coercion(elem_type, item) {
+            Some(common)
+        } else {
+            type_union_resolution(&[elem_type.clone(), item.clone()])
+        };
+        let Some(common) = common else {
+            return Err(diff_types_err(array, item));
+        };
+
+        let new_elem =
+            Arc::new(Field::new(elem.name(), common.clone(), 
elem.is_nullable()));
+        Ok(vec![wrap(new_elem), DataType::Int32, common])
+    }
+
+    fn invoke_with_args(&self, args: ScalarFunctionArgs) -> 
Result<ColumnarValue> {
+        let all_scalar = args
+            .args
+            .iter()
+            .all(|arg| matches!(arg, ColumnarValue::Scalar(_)));
+        let arrays = ColumnarValue::values_to_arrays(&args.args)?;
+        let [list, pos, item] = take_function_args(self.name(), arrays)?;
+        let Some(pos) = pos.as_primitive_opt::<Int32Type>() else {
+            return internal_err!(
+                "array_insert expects an Int32 index, got {}",
+                pos.data_type()
+            );
+        };
+        let legacy = self.legacy_negative_index;
+        let Some(elem_field) = 
list_element_field(args.return_field.data_type()) else {
+            return internal_err!(
+                "unexpected array_insert return type {}",
+                args.return_field.data_type()
+            );
+        };
+        let elem_field = Arc::clone(elem_field);
+        let result: ArrayRef = match list.data_type() {
+            DataType::List(_) => Arc::new(array_insert(
+                list.as_list::<i32>(),
+                &item,
+                pos,
+                legacy,
+                elem_field,
+            )?),
+            DataType::LargeList(_) => Arc::new(array_insert(
+                list.as_list::<i64>(),
+                &item,
+                pos,
+                legacy,
+                elem_field,
+            )?),
+            other => return internal_err!("array_insert expects a list, got 
{other}"),
+        };
+        if all_scalar {
+            Ok(ColumnarValue::Scalar(ScalarValue::try_from_array(
+                &result, 0,
+            )?))
+        } else {
+            Ok(ColumnarValue::Array(result))
+        }
+    }
+}
+
+/// Where the item goes in a row, and how long the row becomes.
+struct Placement {
+    /// Original elements copied before the item.
+    before: usize,
+    /// NULLs between the item and the original elements (negative index
+    /// before the start of the array).
+    leading_nulls: usize,
+    /// NULLs between the original elements and the item (positive index past
+    /// the end of the array).
+    trailing_nulls: usize,
+    /// Length of the resulting row.
+    new_len: usize,
+}
+
+fn place(pos: i32, len: usize, legacy: bool) -> Result<Placement> {
+    if pos == 0 {
+        return exec_err!(
+            "[INVALID_INDEX_OF_ZERO] The index 0 is invalid. An index shall be 
either < 0 or > 0 (the first element has index 1)."
+        );
+    }
+    let n = len as i64;
+    let pos = i64::from(pos);
+    // (original elements before the item, leading NULLs, trailing NULLs, new 
length)
+    let (before, leading, trailing, new_len) = if pos > 0 {
+        let idx = pos - 1;
+        if idx <= n {
+            (idx, 0, 0, n + 1)
+        } else {
+            (n, 0, idx - n, pos)
+        }
+    } else if -pos > n {
+        let new_len = -pos + i64::from(legacy);
+        (0, new_len - n - 1, 0, new_len)
+    } else {
+        (n + pos + i64::from(!legacy), 0, 0, n + 1)
+    };
+    if new_len > MAX_ROUNDED_ARRAY_LENGTH {
+        return exec_err!(
+            "[COLLECTION_SIZE_LIMIT_EXCEEDED.FUNCTION] Can't create array with 
{new_len} elements which exceeding the array size limit 
{MAX_ROUNDED_ARRAY_LENGTH}, unsuccessful try to create arrays in the function 
`array_insert`."
+        );
+    }
+    Ok(Placement {
+        before: before as usize,
+        leading_nulls: leading as usize,
+        trailing_nulls: trailing as usize,
+        new_len: new_len as usize,
+    })
+}
+
+fn array_insert<O: OffsetSizeTrait>(
+    list: &GenericListArray<O>,
+    item: &ArrayRef,
+    pos: &Int32Array,
+    legacy: bool,
+    elem_field: FieldRef,
+) -> Result<GenericListArray<O>> {
+    // First pass: validate every row and compute the output offsets, so the
+    // child data can be allocated once at its final size.
+    let mut placements = Vec::with_capacity(list.len());
+    let mut offsets = Vec::with_capacity(list.len() + 1);
+    offsets.push(O::usize_as(0));
+    let mut total = 0usize;
+    for row in 0..list.len() {
+        // A NULL array or NULL index gives a NULL row with no child values.
+        let placement = if list.is_null(row) || pos.is_null(row) {
+            None
+        } else {
+            let p = place(pos.value(row), list.value_length(row).as_usize(), 
legacy)?;
+            total += p.new_len;
+            Some(p)
+        };
+        placements.push(placement);
+        // A `List` (i32 offsets) can overflow across rows even though each 
row is
+        // within Spark's per-array limit.
+        let Some(offset) = O::from_usize(total) else {
+            return exec_err!(
+                "array_insert result is too large for the list's offset type"
+            );
+        };
+        offsets.push(offset);
+    }
+
+    let values = list.values().to_data();
+    let item_data = item.to_data();
+    let mut out = MutableArrayData::with_capacities(
+        vec![&values, &item_data],
+        true,
+        Capacities::Array(total),
+    );

Review Comment:
   Applied in 918dc42, with `mismatched_item_type_is_an_error` calling 
`invoke_with_args` with a `List(Int32)` array and an `Int64` item.



-- 
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