[Epic] General ticket for the concept of the practical implementation of ARRAY

[izveigor]

Is your feature request related to a problem or challenge?

It was decided that further development of array functions will be carried out according to the following concept: #6855.
According to the concept, any changes may be subject to rational criticism, so some aspects may change.
It should be noted that this list is incomplete and will be updated in the future.
The ticket contains many tickets. Each ticket is divided into respective categories:

List of features

A complete list of features, the implementation of which is desirable from the point of view of this concept 🎯:

Cleanups

• Lots of code duplication in array_functions (array_replace_all, array_replace_n, array_replace, etc) #7988

Managing array elements:

By index:

• array_element Implement array_element function #6974
• array_slice Implement array_slice function #6975

By borders:

• array_pop_front Implement array_pop_front function #6996
• array_pop_back Implement array_pop_back function #6997

Working with Array Characteristics

Dimension

• flatten Implement flatten function #6995

Set language:

Basic operations on sets:

• Implement array_union function #6981
• Implement array_intersect function #6978
• Implement array_except function #6979

Checking if elements belong:

• array_has_all Implement array_has_all function #6973
• array_has_any Implement array_has_any function #6976
• array_has Implement array_has function #6977

Form an array:

• array_repeat Implement array_repeat function #7197
• array_resize Implement array_resize function #7194

Array aggregate functions:

• Implement array_aggregate function #7213
• Implement array aggregate functions #7214

Array operators:

• Support all array operators to work with columns #7119

Handling NULL and empty array:

• Improve handling of NULL and empty array for array functions. #7143
• All array functions should represent NULL as an element #7142
• make_array should support pure null array #7144
• make_array null handling with nested version #7136
• Implement empty function #7290

Refactoring:

• Refactoring the array ecosystem #7279
• Improve array data types #7288

Other functions:

• Implement array_distinct function #7289
• Support udf range #8028

Bugs

• array_agg with pyarrow errors with ArrowInvalid: Schema at index 0 was different #8032

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[izveigor]

cc @alamb @jayzhan211
I also send to people who have recently been interested in the topic of the array: @parkma99 @maxburke @bubbajoe

[jayzhan211]

@izveigor, since there are array_intersect and array_except, did you find any function similar to array_union, the function that returns a list of all elements that exist in one of the l1 and l2 without duplicates? list_intersect([1,2,3], [3,4,5]) -> [1,2,3,4,5]. It seems to me a helpful set operation, but not sure why Duckdb does not have this one.

[jayzhan211]

It makes sense to allow nested array as the lhs value of three of the array_has function. We just need to check the element one by one.

The difference of array_has and array_has_all is the rhs value. The former is element (non-array), and the latter is array (one-dimension). Should we consider them as two different functions as Duckdb does?

I found Azure has just array_contains and array_overlap, I prefer their version.

array_contains(nested array, element or one-dimension array): Returns true if all element or element in the array exists in left-hand side array.
array_overlap(nested array, one-dimension array) the same as array_has_any but a better naming.

[izveigor]

@izveigor, since there are array_intersect and array_except, did you find any function similar to array_union, the function that returns a list of all elements that exist in one of the l1 and l2 without duplicates? list_intersect([1,2,3], [3,4,5]) -> [1,2,3,4,5]. It seems to me a helpful set operation, but not sure why Duckdb does not have this one.

I think we can use Apache Spark SQL and Azure DataBricks function array_union (See: https://sparkbyexamples.com/spark/spark-sql-array-functions/ and https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/array_union). Yes, I agree that the full set of set operations is a very powerful tool.

It makes sense to allow nested array as the lhs value of three of the array_has function. We just need to check the element one by one.

Absolutely agree! I think this is the main mistake with PostgreSQL's array function set. Each element, including the array/list itself, must be processed by any function.

The difference of array_has and array_has_all is the rhs value. The former is element (non-array), and the latter is array (one-dimension). Should we consider them as two different functions as Duckdb does?

I prefer to use ClickHouse/DuckDB version of these functions, because there are a lot of possibilities for making full use of the set language (specifically we should check whether an element belongs to a set and check whether a set is a subset of a certain set).

[jayzhan211]

I will work on three of array_has

[jayzhan211]

I would like to try on array_aggregate. https://duckdb.org/docs/sql/functions/nested#list-aggregates
There are many kinds of rewrite functions, but most of them are not supported yet. I think we can start with the easy one or an existing function, like sum, min, or max.

[jayzhan211]

@izveigor , I dont fully understand about the difference between list_xxx and array_xxx in duckdb. As you said, list is for mutable, and array is for immutable. But like append a mutable function, why do they have array_append? contains a immutable function, but they have list_contains. For aggregate rewrite functions like list_min, list_sum, they don't alias with array, no array_min or array_sum, but we can do the same things with array_aggregate().

How do we differentiate list, and array in datafusion?

[izveigor]

I would like to try on array_aggregate. https://duckdb.org/docs/sql/functions/nested#list-aggregates There are many kinds of rewrite functions, but most of them are not supported yet. I think we can start with the easy one or an existing function, like sum, min, or max.

It sounds great! Thank you, @jayzhan211! I created the tickets about this topic: #7213 and #7214.

@izveigor , I dont fully understand about the difference between list_xxx and array_xxx in duckdb. As you said, list is for mutable, and array is for immutable. But like append a mutable function, why do they have array_append? contains a immutable function, but they have list_contains. For aggregate rewrite functions like list_min, list_sum, they don't alias with array, no array_min or array_sum, but we can do the same things with array_aggregate().

How do we differentiate list, and array in datafusion?

I don't fully understand DuckDB principles myself. In my opinion every function should have list_ and array_ prefix due to the habit of using a single word. About mutability, this is purely my assumption of separation of functions, we may not adhere to it.

[jayzhan211]

I would like to try on array_aggregate. https://duckdb.org/docs/sql/functions/nested#list-aggregates There are many kinds of rewrite functions, but most of them are not supported yet. I think we can start with the easy one or an existing function, like sum, min, or max.

It sounds great! Thank you, @jayzhan211! I created the tickets about this topic: #7213 and #7214.

@izveigor , I dont fully understand about the difference between list_xxx and array_xxx in duckdb. As you said, list is for mutable, and array is for immutable. But like append a mutable function, why do they have array_append? contains a immutable function, but they have list_contains. For aggregate rewrite functions like list_min, list_sum, they don't alias with array, no array_min or array_sum, but we can do the same things with array_aggregate().
How do we differentiate list, and array in datafusion?

I don't fully understand DuckDB principles myself. In my opinion every function should have list_ and array_ prefix due to the habit of using a single word. About mutability, this is purely my assumption of separation of functions, we may not adhere to it.

I asked someone that is familiar with duckdb, he says the reason that they have list_xxx and array_xxx is due to compatibility, postgres for array_xxx, and others for list_xxx. In this case, since we heavily adopted from duckdb, clickhouse and others, we should also use similar naming from them, but I don't think mutability is the thing that differentiates list_xxx and array_xxx, it might be misleading if we said that.

If someone asks what is the difference between array_xxx and list_xxx in datafusion, I think we should answer they are the same thing, we just use the name from duckdb, clickhouse, etc!

[alamb]

I asked someone that is familiar with duckdb, he says the reason that they have list_xxx and array_xxx is due to compatibility, postgres for array_xxx, and others for list_xxx.

This would be great to add to the documentation if you had a chance -- thank you for the research @jayzhan211

[izveigor]

@jayzhan211, thank you for the research. I agree with you and we should create the subpage of the documentation that represents our syntax (we diccussed it in #6855 (comment)). I will deal with it soon.

[edmondop]

Can I pick something @jiangzhx that nobody else is looking to?

[izveigor]

Hello, @edmondop!
Yes, you can pick all tickets if there are no assignments.

[izveigor]

Hello, @alamb and @jayzhan211.
I would like to inform you that, unfortunately, due to life circumstances, I will no longer be able to take part in the project, or at least in the near future.
I hope that my ideas helped with the development of the project. It was a pleasure working with you and this experience really improved my programming skills.
Finally, I’ll leave my thoughts on the last problem, I hope someone can implement it: #7580.
Good luck!

[alamb]

@izveigor -- thank you for letting us know and for all your work so far in this project. I think it has driven DataFusion significantly and we have all benefited from your work and inspiration.

Good luck with your next adventure.

[edmondop]

I propose to merge #6978 and then refactor both that pr and this #6981 to make easy to implement #6979 .

All these functions traverse arrays and use some accumulator, so we can create an accumulator trait and three instances (union, intersection, except). @alamb what do yout hink?

[jayzhan211]

TODO:

• Support stride for array_slice #8784
• ARRAY_AGG of column of type list ORDER BY column of type non-list #8512
• Implement array_resize function #7194 array_resize
• All array functions should represent NULL as an element #7142 / Improve handling of NULL and empty array for array functions. #7143 Null / Empty Handling
• Reuse signture / type coercion for list concat operator Convert list operator to function in sql_expr_to_logical_expr #8506
• Support AND operator as alias of list intersect/array intersect function like Duckdb #8483 list intersect operator
• Implement array_aggregate function #7213 / Implement array aggregate functions #7214 array aggregate
• Implement unnest function #6555 unnest
• Fixed Size List / Large List ?
• Other array functions supported in both DuckDB And ClickHouse list_zip, list_filter (with lambda function)

[Weijun-H]

• support list_reverse in array #9021

[alamb]

This looks pretty good to me now, I think we have done all the major items so closing this epic as good. We can handle small cleanup items as normal tickets. Please let me know if you disagree