Repository navigation
Support recursive unnest #10660
Description
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
- Support nested unnest expr by solving the above error.
- 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
Activity
duongcongtoai commentedon Jun 1, 2024
take
jayzhan211 commentedon Jun 22, 2024
@duongcongtoai Should we just support nested unnest syntax like DuckDB? Like
unnest(expr, recurisve:=true)andunnest(expr, max_depth:=2)? I noticed they do not support explicit nested unnest.duongcongtoai commentedon Jun 22, 2024
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 commentedon Jun 22, 2024
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 commentedon Jun 22, 2024
But for this query usecase
Where an operator like field access is needed between 2 unnest,it cant be supported by duckdb's syntax
jayzhan211 commentedon Jun 22, 2024
I see. It looks like a good reason.