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


##########
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:
   `MutableArrayData` 
[asserts](https://github.com/apache/arrow-rs/blob/b391e5af7dff9f6909af45c01f7dddb68ba08c0f/arrow-data/src/transform/mod.rs#L463-L469)
 that all of its inputs have the same type, so this panics when the item's type 
differs from the element type. `coerce_types` prevents that in SQL, but Comet 
calls `invoke_with_args()` without coercion. Calling `invoke_with_args` with a 
`List(Int32)` array and an `Int64` item panics with `Arrays with inconsistent 
types passed to MutableArrayData`. Comet's version [checks the 
types](https://github.com/apache/datafusion-comet/blob/ba16e1a53127c90ba00fdc7058703945a8bb9f34/native/spark-expr/src/array_funcs/array_insert.rs#L152-L158)
 and returns an internal error. Could we keep that check, with a unit test for 
it?
   
   ```suggestion
       let values = list.values().to_data();
       let item_data = item.to_data();
       if values.data_type() != item_data.data_type() {
           return internal_err!(
               "array_insert expects the item type {} to match the element type 
{}",
               item_data.data_type(),
               values.data_type()
           );
       }
       let mut out = MutableArrayData::with_capacities(
           vec![&values, &item_data],
           true,
           Capacities::Array(total),
       );
   ```



##########
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:
   Spark 4.2.0 ends both of these messages with the SQLSTATE: `... (the first 
element has index 1). SQLSTATE: 22003` and ``... in the function 
`array_insert`. SQLSTATE: 54000``. Other hand-built runtime errors in the crate 
include it, for example 
[`encode`](https://github.com/apache/datafusion-spark/blob/125600164aa825e2fe805887dfdb384d4182f2a8/crates/spark/src/function/string/encode.rs#L118).
 Could we append ` SQLSTATE: 22003` and ` SQLSTATE: 54000`? The `.slt` patterns 
for the size limit don't anchor the end, so they keep passing, and the 
`INVALID_INDEX_OF_ZERO` cases could assert the full text.



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