Improve handling of NULL and empty array for array functions.

[izveigor]

Is your feature request related to a problem or challenge?

One of the most important topic for implementing a good array function interaction ecosystem is NULL and empty array handling. There were a lot of attempts and impromevents, but all of these contain a lot of imperfections.
So, I think this theme deserves more attention from the developers.

List:

The list contains all issues and pull requests dedicated NULL and empty array handling:

Issues:

• array_concat does not work with nested empty arrays #7028
• Сustomize behavior of NULLS in array functions #6808
• Supports NULL in arrays #6556
• All array functions should represent NULL as an element #7142
• Support empty array #6561
• make_array does not properly support nulls  #6887
• make_array null handling with nested version #7136
• make_array should support pure null array #7144

Pull Requests:

• feat: support NULL in array functions #6662
• feat: make_array support empty arguments #6593
• fix: array_concat with arrays with different dimensions, add _list* aliases for _array* functions #7008

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[jayzhan211]

Add more issues

• make array_union/array_except/array_intersect handle empty/null arrays rightly #8269
• empty([])