You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "davidhewitt (via GitHub)" <gi...@apache.org> on 2025/03/24 11:48:25 UTC
[I] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
davidhewitt opened a new issue, #15387:
URL: https://github.com/apache/datafusion/issues/15387
### Describe the bug
I am testing against Datafusion main (d68fca91fd4c73f2cd92d61d036bc0411a2ed826).
With the query below I expect there to be no `FilterExec` because the condition is trivial `x = x`. But it is not eliminated.
### To Reproduce
```
> explain with test AS (SELECT unnest(generate_series(1, 10)) as x)
select count(*) from test WHERE x = x;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | Projection: count(Int64(1)) AS count(*) |
| | Aggregate: groupBy=[[]], aggr=[[count(Int64(1))]] |
| | SubqueryAlias: test |
| | Projection: |
| | Filter: __unnest_placeholder(generate_series(Int64(1),Int64(10)),depth=1) = __unnest_placeholder(generate_series(Int64(1),Int64(10)),depth=1) |
| | Unnest: lists[__unnest_placeholder(generate_series(Int64(1),Int64(10)))|depth=1] structs[] |
| | Projection: List([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS __unnest_placeholder(generate_series(Int64(1),Int64(10))) |
| | EmptyRelation |
| physical_plan | ProjectionExec: expr=[count(Int64(1))@0 as count(*)] |
| | AggregateExec: mode=Final, gby=[], aggr=[count(Int64(1))] |
| | CoalescePartitionsExec |
| | AggregateExec: mode=Partial, gby=[], aggr=[count(Int64(1))] |
| | ProjectionExec: expr=[] |
| | CoalesceBatchesExec: target_batch_size=8192 |
| | FilterExec: __unnest_placeholder(generate_series(Int64(1),Int64(10)),depth=1)@0 = __unnest_placeholder(generate_series(Int64(1),Int64(10)),depth=1)@0 |
| | RepartitionExec: partitioning=RoundRobinBatch(8), input_partitions=1 |
| | UnnestExec |
| | ProjectionExec: expr=[[1, 2, 3, 4, 5, 6, 7, 8, 9, 10] as __unnest_placeholder(generate_series(Int64(1),Int64(10)))] |
| | PlaceholderRowExec |
| | |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
### Expected behavior
_No response_
### Additional context
_No response_
--
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] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #15387:
URL: https://github.com/apache/datafusion/issues/15387#issuecomment-2777214161
But is that possible to evaluate via a simplifier? I'd think that in general we don't know that until execution time.
--
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] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15387:
URL: https://github.com/apache/datafusion/issues/15387#issuecomment-2779003076
I will file a ticket
--
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] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15387:
URL: https://github.com/apache/datafusion/issues/15387#issuecomment-2779011846
Here is a simpler reproducer showing the `x = x` filter is still present and can be replaced with `x IS NOT NULL`:
```sql
> create table foo (x int)
as values (1), (2), (null);
0 row(s) fetched.
Elapsed 0.041 seconds.
> select * from foo;
+------+
| x |
+------+
| 1 |
| 2 |
| NULL |
+------+
3 row(s) fetched.
Elapsed 0.002 seconds.
> select * from foo where x = x;
+---+
| x |
+---+
| 1 |
| 2 |
+---+
2 row(s) fetched.
Elapsed 0.009 seconds.
> select * from foo where x IS NOT NULL;
+---+
| x |
+---+
| 1 |
| 2 |
+---+
2 row(s) fetched.
Elapsed 0.002 seconds.
> explain select * from foo where x = x;
+---------------+-------------------------------+
| plan_type | plan |
+---------------+-------------------------------+
| physical_plan | ┌───────────────────────────┐ |
| | │ CoalesceBatchesExec │ |
| | │ -------------------- │ |
| | │ target_batch_size: │ |
| | │ 8192 │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ FilterExec │ |
| | │ -------------------- │ |
| | │ predicate: x = x │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ DataSourceExec │ |
| | │ -------------------- │ |
| | │ bytes: 176 │ |
| | │ format: memory │ |
| | │ rows: 1 │ |
| | └───────────────────────────┘ |
| | |
+---------------+-------------------------------+
1 row(s) fetched.
Elapsed 0.006 seconds.
```
So a sketch for the solution to this issue is:
1. Add a rule in `ExprSimplifier` for `<expr> = <expr>` --> `<expr> IS NOT NULL` in this match statement: https://github.com/apache/datafusion/blob/2cd6ed99dab90ca73497374e860f89e6fe83af1d/datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs#L730-L738
2. Add tests in slt (perhaps like above)
--
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] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15387:
URL: https://github.com/apache/datafusion/issues/15387#issuecomment-2779013119
As this now has a description and suggested implementation I think it is a good first issue for someone who wants to implement an optimization
--
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] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #15387: Trivial WHERE filter not eliminated when combined with CTE
URL: https://github.com/apache/datafusion/issues/15387
--
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] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15387:
URL: https://github.com/apache/datafusion/issues/15387#issuecomment-2749150412
🤔 maybe we should add an expr simplifier rule that converts `x = x` to `true` if x is not null (because null = null would be null)
--
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] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15387:
URL: https://github.com/apache/datafusion/issues/15387#issuecomment-2778998957
> But is that possible to evaluate via a simplifier? I'd think that in general we don't know that until execution time.
we could simplify `x = x` to `x IS NOT NULL` which is likely much faster to evaluatie
--
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] Trivial WHERE filter not eliminated when combined with CTE [datafusion]
Posted by "ding-young (via GitHub)" <gi...@apache.org>.
ding-young commented on issue #15387:
URL: https://github.com/apache/datafusion/issues/15387#issuecomment-2780253832
take
--
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