Nested UDF array signatures are unwieldy

### Is your feature request related to a problem or challenge?

https://github.com/apache/datafusion/blob/70c26a06716eb552f0fc1d2115957a4089504f11/datafusion/expr-common/src/signature.rs#L526-L541

https://github.com/apache/datafusion/blob/70c26a06716eb552f0fc1d2115957a4089504f11/datafusion/expr-common/src/signature.rs#L565-L577

There are two current use cases which aren't well supported by our current array signature code:

- Can't coerce to a specific element type
- Can't handle arrays of different element type

For the first point, see how for `cosine_distance` it needs to define signature via user defined in order to ensure input lists come as `list`:

https://github.com/apache/datafusion/blob/70c26a06716eb552f0fc1d2115957a4089504f11/datafusion/functions-nested/src/cosine_distance.rs#L100-L139

For the second point, an example is `arrays_zip` which can accept lists of different type (though technically since it can accept an arbitrary number of arguments this is also another limitation)

### Describe the solution you'd like

See if we can refine the array signature code so more of our nested UDFs can use it, instead of resorting to user defined

### Describe alternatives you've considered

_No response_

### Additional context

Potentially related:

- https://github.com/apache/datafusion/issues/6559

Start with the array signature code in datafusion/expr-common/src/signature.rs at the referenced sections, then compare the user-defined signature in datafusion/functions-nested/src/cosine_distance.rs and the arrays_zip implementation. Determine what the existing signature model cannot express. Done means nested UDFs can express fixed element coercion and differing element types without resorting to user-defined signatures.

Written by the indexing model from the issue text.