You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "ahirner (via GitHub)" <gi...@apache.org> on 2024/06/16 12:02:40 UTC
[I] Push down filters below `Unnest` in sub queries [datafusion]
ahirner opened a new issue, #10935:
URL: https://github.com/apache/datafusion/issues/10935
### Is your feature request related to a problem or challenge?
Currently where clauses in a sub query are not pushed below `Unnest` expressions. In conjunction with some other limitations, it composing views with `unnest()` not ideal.
In `0.39.0` cli:
```
> CREATE TABLE IF NOT EXISTS v AS VALUES(1,[1,2,3]),(2,[3,4,5]);
0 row(s) fetched.
Elapsed 0.008 seconds.
```
We get equal results with or w/o a subquery:
```
> select unnest(column2) from v where column1=2;
+-------------------+
| unnest(v.column2) |
+-------------------+
| 3 |
| 4 |
| 5 |
+-------------------+
3 row(s) fetched.
Elapsed 0.011 seconds.
```
```
> select "unnest(v.column2)" from (select unnest(column2), column1 from v) where column1=2;
+-------------------+
| unnest(v.column2) |
+-------------------+
| 3 |
| 4 |
| 5 |
+-------------------+
3 row(s) fetched.
Elapsed 0.015 seconds.
```
`Filter` and `FilterExec` are above projections with `unnest`, instead of below.
Ok:
```
> explain select unnest(column2) from v where column1=2;
+---------------+------------------------------------------------------------------------+
| plan_type | plan |
+---------------+------------------------------------------------------------------------+
| logical_plan | Unnest: lists[unnest(v.column2)] structs[] |
| | Projection: v.column2 AS unnest(v.column2) |
| | Filter: v.column1 = Int64(2) |
| | TableScan: v projection=[column1, column2] |
| physical_plan | UnnestExec |
| | RepartitionExec: partitioning=RoundRobinBatch(8), input_partitions=1 |
| | ProjectionExec: expr=[column2@1 as unnest(v.column2)] |
| | CoalesceBatchesExec: target_batch_size=8192 |
| | FilterExec: column1@0 = 2 |
| | MemoryExec: partitions=1, partition_sizes=[1] |
| | |
+---------------+------------------------------------------------------------------------+
```
Problematic:
```
> explain select "unnest(v.column2)" from (select unnest(column2), column1 from v) where column1=2;
+---------------+---------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+---------------------------------------------------------------------------------------+
| logical_plan | Projection: unnest(v.column2) |
| | Filter: v.column1 = Int64(2) |
| | Unnest: lists[unnest(v.column2)] structs[] |
| | Projection: v.column2 AS unnest(v.column2), v.column1 |
| | TableScan: v projection=[column1, column2] |
| physical_plan | ProjectionExec: expr=[unnest(v.column2)@0 as unnest(v.column2)] |
| | CoalesceBatchesExec: target_batch_size=8192 |
| | FilterExec: column1@1 = 2 |
| | RepartitionExec: partitioning=RoundRobinBatch(8), input_partitions=1 |
| | UnnestExec |
| | ProjectionExec: expr=[column2@1 as unnest(v.column2), column1@0 as column1] |
| | MemoryExec: partitions=1, partition_sizes=[1] |
| | |
+---------------+---------------------------------------------------------------------------------------+
```
### Describe the solution you'd like
`FilterExec` is pushed down to tables that support them.
### Describe alternatives you've considered
A workaround is trying to not use subqueries. However, then one cannot unest structs with multiple lists, or unnest a column more than once. Those are also unsupported.
```
> select unnest(unnest([[1],[1,2],[1,2,3]]));
type_coercion
caused by
This feature is not implemented: Unnest should be rewritten to LogicalPlan::Unnest before type coercion
```
```
> select unnest(column2) as x, unnest(column2) as y from v;
Error during planning: Projections require unique expression names but the expression "v.column2 AS unnest(v.column2)" at position 0 and "v.column2 AS unnest(v.column2)" at position 1 have the same name. Consider aliasing ("AS") one of them.
```
(note: a real case would be something like `select unnest(named_struct)['foo'] as foo, unnest(named_struct)['bar'] as bar`).
### Additional context
Maybe related: #5364
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Push down filters below `Unnest` in sub queries [datafusion]
Posted by "ahirner (via GitHub)" <gi...@apache.org>.
ahirner commented on issue #10935:
URL: https://github.com/apache/datafusion/issues/10935#issuecomment-2174216261
> alternative query
Yes. The problem **is** that one can't move the filter to the inner query (views) or one shouldn't have to with other common sql constructs (sub queries, ctes).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Push down filters below `Unnest` in sub queries [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10935:
URL: https://github.com/apache/datafusion/issues/10935#issuecomment-2174883698
I think we can rewrite Unnest plan in push down filter
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Push down filters below `Unnest` in sub queries [datafusion]
Posted by "duongcongtoai (via GitHub)" <gi...@apache.org>.
duongcongtoai commented on issue #10935:
URL: https://github.com/apache/datafusion/issues/10935#issuecomment-2171768257
I think this is related https://github.com/apache/datafusion/issues/10660
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Push down filters below `Unnest` in sub queries [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10935:
URL: https://github.com/apache/datafusion/issues/10935#issuecomment-2171495707
For this kind of query `select unnest(unnest([[1],[1,2],[1,2,3]]));`, I think we can follow what Duckdb is.
Unnest with recursive, and max_depth
`select unnest([[1],[1,2],[1,2,3]], recursive:= true);`
or `select unnest([[1],[1,2],[1,2,3]], max_depth := 2);`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Push down filters below `Unnest` in sub queries [datafusion]
Posted by "ahirner (via GitHub)" <gi...@apache.org>.
ahirner commented on issue #10935:
URL: https://github.com/apache/datafusion/issues/10935#issuecomment-2171783792
Thanks for the pointers @duongcongtoai
I think the recursive and max_depth options can be a special workaround, but introduce a good amount of complexity. Fixing the push down makes single `unnest` composable in general.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Push down filters below `Unnest` in sub queries [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #10935:
URL: https://github.com/apache/datafusion/issues/10935#issuecomment-2176289591
PR to do so: https://github.com/apache/datafusion/pull/10974
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Push down filters below `Unnest` in sub queries [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10935:
URL: https://github.com/apache/datafusion/issues/10935#issuecomment-2172572734
@ahirner
I found an alternative query is to move filter into the query
`select "unnest(v.column2)" from (select unnest(column2), column1 from v where column1=2);`
```
query TT
explain select "unnest(v.column2)" from (select unnest(column2), column1 from v where column1=2);
----
logical_plan
01)Projection: unnest(v.column2)
02)--Unnest: lists[unnest(v.column2)] structs[]
03)----Projection: v.column2 AS unnest(v.column2), v.column1
04)------Filter: v.column1 = Int64(2)
05)--------TableScan: v projection=[column1, column2]
physical_plan
01)ProjectionExec: expr=[unnest(v.column2)@0 as unnest(v.column2)]
02)--UnnestExec
03)----RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
04)------ProjectionExec: expr=[column2@1 as unnest(v.column2), column1@0 as column1]
05)--------CoalesceBatchesExec: target_batch_size=8192
06)----------FilterExec: column1@0 = 2
07)------------MemoryExec: partitions=1, partition_sizes=[1]
```
Given that, it might not be neccessary to support pushing down **outer** filter
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org