Improve handling of `NULL` and empty array for array functions.

### 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:
- https://github.com/apache/arrow-datafusion/issues/7028
- https://github.com/apache/arrow-datafusion/issues/6808
- https://github.com/apache/arrow-datafusion/issues/6556
- https://github.com/apache/arrow-datafusion/issues/7142
- https://github.com/apache/arrow-datafusion/issues/6561
- https://github.com/apache/arrow-datafusion/issues/6887
- https://github.com/apache/arrow-datafusion/issues/7136
- https://github.com/apache/arrow-datafusion/issues/7144

### Pull Requests:
- https://github.com/apache/arrow-datafusion/pull/6662
- https://github.com/apache/arrow-datafusion/pull/6593
- https://github.com/apache/arrow-datafusion/pull/7008

### Describe the solution you'd like

_No response_

### Describe alternatives you've considered

_No response_

### Additional context

_No response_

Start by reviewing the related issues (#7028, #6808, #6556, #7142, #6561, #6887, #7136, and #7144) and the referenced pull requests (#6662, #6593, and #7008). The issue does not name implementation files, tests, or a concrete solution, so the desired behavior and completion criteria need to be established before work begins.

Written by the indexing model from the issue text.