All array functions should represent `NULL` as an element

[izveigor]

Is your feature request related to a problem or challenge?

Follow on to #6662
We have important problem related to using array functions with NULL statements. As @alamb said in PR (see below for full details) DataFusion casting system should be adapted for it, otherwise it will always throw errors.

The current implementation:

❯ select array_append([1, 2, 3, 4, 5], NULL);
Optimizer rule 'simplify_expressions' failed
caused by
This feature is not implemented: Array_append is not implemented for types 'Int64' and 'Null'.

Should be:

❯ select array_append([1, 2, 3, 4, 5], NULL);
----
[1, 2, 3, 4, 5, NULL]

Describe the solution you'd like

@alamb statement about the issue:

I am not sure about this approach of taking either a ListArray or a NullArray

In the other functions, the way NULL is treated is that the input types are always the same (in this case ListArray) and the values would be null (aka array.is_valid(i) would return false for rows that are null.

Complicating matters is if you type a literal null in sql like:

select array_concat([1,2], null)

That comes to DataFusion as a null literal (with DataType::Null). The coercion / casting logic normally will coerce this to the appropriate type.

For example, here is how I think arithmetic works with null:

select 1 + NULL

Arrives like

ScalarValue::Int32(Some(1)) + ScalarValue::Null

And then the coercion logic will add a cast to Int32:

ScalarValue::Int32(Some(1)) + CAST(ScalarValue::Null, DataType::Int32)

And then the constant folder will collapse this into:

ScalarValue::Int32(Some(1)) + ScalarValue::Int32(None)

So by the time the arithmetic kernel sees it, it only has to deal with arguments of Int32

Describe alternatives you've considered

No response

Additional context

No response

[alamb]

To be clear, to be consistent with the rest of the datafusion type system, I think we should stop using DataType::Null to represent null constants in array functions and instead move to using DataType::XXX where XXX is the type of the other list elements.

So after type coercion I would expect make_list(1, 2, NULL) to be

make_list(
  ScalarValue::Int64(Some(1)),
  ScalarValue::Int64(Some(2)),
  ScalarValue::Int64(None),  // NOTE this is not ScalarValue::Null
) -> ListArray w/ Int64 value type

[izveigor]

@alamb, it looks reasonable for me. But what we would do with pure null arrays (see the ticket: #7144). Should it be ScalarValue::Null or something else (like ScalarValue::Int64(None))?
I also agree that we should check empty arrays by length (len == 0 -> empty array) and don't use for these cases NULL checking (like the current version DataFusion array[NULL] -> empty array).

[alamb]

Good question @izveigor -- I left a response here #7144 (comment)

[jayzhan211]

It seems only these two functions have issues with it?

• array_append
• array_prepend

[crepererum]

It seems only these two functions have issues with it?

Apparently array_empty as well.

[jayzhan211]

What is the expected behavior for array concat with nulls and empty arrays?
I try to think about the result by converting null to a compatible non-null type.

// convert [null] to [i64], so got []
select array_concat(make_array(make_array(1, 2), make_array(3, 4)), make_array(null));
----
[[1, 2], [3, 4], []]

// NOTE: Duckdb [[1, 2], [3, 4], null]
// convert [[]] to [[i64]], so got []
select array_concat(make_array(make_array(1, 2), make_array(3, 4)), make_array(make_array()));
----
[[1, 2], [3, 4], []]

// convert null to i64, since is should be at least [i64], so ignore it (follows on duckdb behavior).
query ?
select array_concat(make_array(make_array(1, 2), make_array(3, 4)), null);
----
[[1, 2], [3, 4]]