Add support for Spark-compatible `explode_outer` function

[andygrove]

Is your feature request related to a problem or challenge?

Spark supports explode and explode_outer, which are very similar to DataFusion's existing unnest function.

explode_outer treats nulls and empty arrays identically and produces an output row containing null. This differs from unnest with the preserve_nulls option set, which only does this for null inputs and does not generate output for empty arrays.

Describe the solution you'd like

I propose adding a new flag to unnest to treat empty arrays consistently with null arrays.

Describe alternatives you've considered

No response

Additional context

No response

[bhanreddy1973]

Hi @andygrove,

I'd like to work on this issue.

I understand the problem: explode_outer should treat empty arrays [] the same as NULL arrays - both should produce a row with NULL value, unlike the current unnest behavior which skips empty arrays.

Before I start implementing, I have a question about the approach:

1. Option A: Add a new flag like preserve_empty_as_null to the existing unnest function
2. Option B: Create a separate explode_outer function for Spark compatibility

Which approach would you prefer?

I'll start exploring the codebase and familiarizing myself with the unnest implementation in the meantime.

Thanks!