Support recursive unnest

[duongcongtoai]

Is your feature request related to a problem or challenge?

These sql are not supported

##recursive unnest(list->list)
query ?
select * from unnest(unnest(
    [[1,2,3],[4,5,6]]
));
----
1
2
3
4
5
6

##recursive unnest(list->struct_field_access->list)
query ?
select * from unnest(unnest(
    [struct([1,2,3]),struct([4,5,6])]
)['c0']);
----
1
2
3
4
5
6

##recursive unnest(list->struct)
query ?
select * from unnest(unnest(
    [struct([1,2,3],[4,5,6]),struct([7,8,9],[10,11,12])]
));
----
[1,2,3] [4,5,6]
[7,8,9] [10,11,12]

If an unnest expr is a descendant of another unnest expr, this error will be thrown:

This feature is not implemented: Unnest should be rewritten to LogicalPlan::Unnest before type coercion

Describe the solution you'd like

1. Support nested unnest expr by solving the above error.
2. Can we support a syntax similar duckdb: within unnest expr, there is an option to specify recursion level

D select unnest([[1,2,3],[4,5,6]], max_depth:=2);
┌─────────────────────────────────────────────────────────────────────────────────────────────┐
│ unnest(main.list_value(main.list_value(1, 2, 3), main.list_value(4, 5, 6)), max_depth := 2) │
│                                            int32                                            │
├─────────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                           1 │
│                                                                                           2 │
│                                                                                           3 │
│                                                                                           4 │
│                                                                                           5 │
│                                                                                           6 │
└─────────────────────────────────────────────────────────────────────────────────────────────┘

Describe alternatives you've considered

No response

Additional context

No response

[duongcongtoai]

take

[jayzhan211]

@duongcongtoai Should we just support nested unnest syntax like DuckDB? Like unnest(expr, recurisve:=true) and unnest(expr, max_depth:=2)? I noticed they do not support explicit nested unnest.

D select unnest(unnest([[1,2,3]]));
Binder Error: Nested UNNEST calls are not supported - use UNNEST(x, recursive := true) to unnest multiple levels
LINE 1: select unnest(unnest([[1,2,3]]));

[duongcongtoai]

I'm thinking of supporting both, LogicalPlan for unnest can be aware of the recursion, and the recursive query can be rewritten into this LogicalPlan with a recursion_depth

[jayzhan211]

I'm thinking of supporting both, LogicalPlan for unnest can be aware of the recursion, and the recursive query can be rewritten into this LogicalPlan with a recursion_depth

Then, we would also need to deal with somewhat complex syntax like unnest(unnest(e, recursive := true), max_depth := 2). I personally prefer having the simplest syntax for the user.

[toaiduongdh]

But for this query usecase

##recursive unnest(list->struct_field_access->list)
query ?
select * from unnest(unnest(
    [struct([1,2,3]),struct([4,5,6])]
)['c0']);

Where an operator like field access is needed between 2 unnest,it cant be supported by duckdb's syntax

[jayzhan211]

But for this query usecase

##recursive unnest(list->struct_field_access->list)
query ?
select * from unnest(unnest(
    [struct([1,2,3]),struct([4,5,6])]
)['c0']);

Where an operator like field access is needed between 2 unnest,it cant be supported by duckdb's syntax

I see. It looks like a good reason.