You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2025/10/28 09:58:44 UTC
[I] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
alamb opened a new issue, #18319:
URL: https://github.com/apache/datafusion/issues/18319
### Is your feature request related to a problem or challenge?
Filing on behalf of @colinmarc and @drin so we can get more community help
It is much easier for table providers such as the build in parquet one as well as iceberg to push down `<col> op <constant>` expressions. They often don't push the predicates down if they have a scalar function wrapping them
For example using the predicate
```sql
date_trunc(part, column) <= constant_rhs
```
Will not typically be able to be pushed down
However, this equivalent expression can be
```sql
column <= date_trunc(part, date_add(constant_rhs, INTERVAL 1 part)
```
### Describe the solution you'd like
It would be good if DataFusion could do this type of rewrite
### Describe alternatives you've considered
This seems to fit well into the existing simply expressions framework:
https://github.com/apache/datafusion/tree/main/datafusion/optimizer/src/simplify_expressions
And we already do something very similar for casts in unwrap casts: https://github.com/apache/datafusion/blob/e12ef3ae90677fe4b1bc548feea2b3082eecdaa2/datafusion/optimizer/src/simplify_expressions/unwrap_cast.rs#
For example
```sql
CAST(x AS float) = 5
```
is rewritten to
``sql
x = CAST(5 as float)
```
The code for that is here:
Perhaps we can follow a similar model for date_trunc
Note There is already a [`ScalarUDFImpl::simplify`](https://docs.rs/datafusion/latest/datafusion/logical_expr/trait.ScalarUDFImpl.html#method.simplify) for simplifying functions, however that method doesn't get any part of the larger expression (the `=` in the above code) so we might have to extend the API somewhat
### 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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3814146279
I'll try to work on this early next week now that #19722 is merged and #20008 is imminent
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3492292055
There's a PR to fix that which we found while doing the implementation I shared in the diff above:
https://github.com/duckdb/duckdb/pull/19628
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3481136630
Hi @UBarney, I have an implementation that seems to work, but I wrote it as a custom optimizer rule and i'm still new to rust and datafusion. I can share the implementation in a PR and maybe you can help me to refactor the pieces into the correct places? I am not going to be working on this today, so I'll add soon to unblock you (in case you do)
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "2010YOUY01 (via GitHub)" <gi...@apache.org>.
2010YOUY01 commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3794189969
> > [@sdf-jkl](https://github.com/sdf-jkl) that sounds great. IMO, preimage sounds misleading for something that's using interval containment to simplify a predicate, but maybe there will be other types of functions that can be reversed.
>
> The preimage terminology is from ClickHouse and I believe it refers to https://en.wikipedia.org/wiki/Image_(mathematics)#Inverse_image .
>
> If you have a better suggestion on terminology perhaps we can change it before we release 53.0.0
I think this is terminology bloat: we’re introducing a new term to describe an already well-known concept, and this might cause confusions.
Fundamentally, this is just expression simplification. The difference is that traditional expression-simplification routines aim to reduce computation cost, while in the “preimage” case the simplification is done to enable predicate pruning. Here, we can tolerate slightly slower evaluation if it makes pruning possible. (and this issue's simplification case both made compute fast, and pruning possible)
Perhaps we should simply call it `simplify_expr()` or `simplify_expr_for_pruning()`, and mention that this is equivalent to what ClickHouse calls a “preimage.”
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3750231507
@2010YOUY01 ooh thanks for chiming in!
I think the 2 approaches are supplementary, as in there is an overlap in when they're applicable, but there are also scenarios in which I imagine they'll have different performance impacts, with "better performance" going either way.
We don't actually need this PR to land since we have it implemented in our system. Landing this PR reduces our drift from upstream, but I think the value of it is that others can benefit sooner.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "UBarney (via GitHub)" <gi...@apache.org>.
UBarney commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3747638823
> [@UBarney](https://github.com/UBarney) I can take this over if you'd like. I should have some time to push it forward this week and maybe you can help review?
Sure, feel free to take this over!
However, @2010YOUY01's proposal in #19487 seems to provide a more robust solution for this issue. It might be worth waiting until that PR is merged before proceeding with this one. What do you think?
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3765510245
> [@drin](https://github.com/drin) I think once [#19722](https://github.com/apache/datafusion/pull/19722) is closed you could reuse the [preimage api](https://github.com/apache/datafusion/pull/19722/changes#diff-8a327db2db945bcf6ca2b4229885532feae127e94a450600d3fac6ecdc0eeb3fR235-R245) for this simplification.
I agree -- I finally got around to reviewing https://github.com/apache/datafusion/pull/19722 and I think it is looking quite close.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3872136445
I wonder how this project is going?
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3797098532
I don't mind if the API is named preimage, but it feels like a very broad term for a very specific optimization.
This is the entirety of what the clickhouse paper has to say about `preimage`:
> Second, some functions can compute the preimage of a given function result. This is used to replace comparisons of constants with function calls on the key columns by comparing the key column value with the preimage. For example, toYear(k) = 2024 can be replaced by k >= 2024-01-01 && k < 2025-01-01
The optimization of `toYear(k) = 2024` to `k >= 2024-01-01 && k < 2025-01-01` is literally that the year 2024 is an interval that ranges from `2024-01-01` (interval minimum value) to `2025-01-01` (interval maximum value). Which, to me is simply interval containment.
That being said, if the preimage API can be easily extended to other functions then I think preimage is a great name. For example, if a function can register its inverse (its "preimage function"). I tried looking at the API just now and it can probably be expanded beyond interval arithmetic?
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3799533539
> That being said, if the preimage API can be easily extended to other functions then I think preimage is a great name. For example, if a function can register its inverse (its "preimage function" or inverse function, `f^-1`). I tried looking at the API just now and it can probably be expanded beyond interval arithmetic?
The two functions we have identified are date_part and date_trunc.
- [ ] https://github.com/apache/datafusion/issues/18319
- [ ] https://github.com/apache/datafusion/issues/19889
If we can figure out how to extend the API that would be great. A good usecase would probably help a lot.
> So while the terminology is accurate, it does add some cognitive overhead for something that is conceptually quite simple.
I do agree with you that the terminology is not commonly used (and I had to look up what pre-image meant, as it seems a somewhat obscure mathematical concept)
I made a PR to try and simplify the docs here:
- https://github.com/apache/datafusion/pull/20008
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "2010YOUY01 (via GitHub)" <gi...@apache.org>.
2010YOUY01 commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3798031798
How about calling it `simplify_func_op_constant()` for the existing `preimage()` in https://github.com/apache/datafusion/blob/4d63f8c9277705a6062625bc099151d0e4995692/datafusion/expr/src/udf.rs#L239
I agree it’s a bit hand-wavy, and `preimage` or `interval_containment` are more semantically precise. But if we introduce a new term, it might have two effects:
- Some users may skip it and not bother trying to understand it, which can hurt adoption.
- Others may fall into a rabbit hole: reading the Wikipedia page for “preimage” and then the ClickHouse paper just to figure out what it means (I did🤦🏼 I just figured it should be my personal issue).
So while the terminology is accurate, it does add some cognitive overhead for something that is conceptually quite simple.
Though I don't think it's a big deal, `preimage` is fine, given we already have great doc comment and example.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3481402339
I apologize for how messy this is, but I'm out of time until the afternoon. Here's a branch I pushed and a diff: https://github.com/apache/datafusion/compare/main...drin:datafusion:octalene.feat-optimize-datetrunc?expand=1
I don't want to open a PR until I have the time to clean up the description. If you don't want to start with this as context then feel free to ignore it for now.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "2010YOUY01 (via GitHub)" <gi...@apache.org>.
2010YOUY01 commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3748471631
> that PR has a very long description. At a glance it definitely seems relevant but I can't tell how long it would take to accommodate dates and I'm not sure parquet automatically maintains statistics for dates by default.
One issue with #19487 is that it may take longer to land in a release. The major challenge is review latency (any help to move it forward sooner would be greatly appreciated). Making `date_trunc` prunable itself should be fairly straightforward. This rewrite-based approach is also much more likely to get merged quickly, so it makes sense to proceed with it if you need a solution in the near term.
The gist of #19487 is we let the pruning framework handle arbitrarily complex predicates automatically, otherwise we assume the pruner can only handle naive exprs like `col < constant`, and we have to maintain dozens of rewrite rules like this one, and the rewrite rule is not flexible enough to handle slightly more complex patterns. (like it rewrites `date_trunc(part, column) <= constant_rhs`, but might fail to prune if we wrap `column` with one additional date-related function.) So I believe it's a better long-term solution.
> Also, it doesn't seem efficient for this particular optimization because it evaluates per batch (micro-partition?) something that can be evaluated once up front. I think the only scenario it has equivalent performance is in the case of tens of batches and when the plan is not cached in any way.
For the performance part, I think this rewrite do fold one constant value in the example (RHS of <=), but the `<=` expr still have to get evaluated on all containers anyway.
```
column <= date_trunc(part, date_add(constant_rhs, INTERVAL 1 part)
```
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3748296205
that PR has a very long description. At a glance it definitely seems relevant but I can't tell how long it would take to accommodate dates and I'm not sure parquet automatically maintains statistics for dates by default.
Also, it doesn't seem efficient for this particular optimization because it evaluates per batch (micro-partition?) something that can be evaluated once up front. I think the only scenario it has equivalent performance is in the case of tens of batches and when the plan is not cached in any way.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3843271234
> mmm.. I see, [#19733](https://github.com/apache/datafusion/pull/19733) implements `preimage` for `date_part` so I just need to implement `preimage` for `date_trunc` in [#18648](https://github.com/apache/datafusion/pull/18648). Sounds good
Yeah, I am pretty stoked with the API that @sdf-jkl has come up with -- I think adding support for `date_part` will be about 30 lines of code and then a bunch of tests 🙏
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3820311223
> Once a udf has the preimage implemented, the transformation will be applied
An example of how it works is here: https://github.com/apache/datafusion/pull/19733
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3456858821
It appears this PR to duckdb added the same optimization:
- https://github.com/duckdb/duckdb/pull/18457
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3785987867
@sdf-jkl that sounds great. IMO, preimage sounds misleading for something that's using interval containment to simplify a predicate, but maybe there will be other types of functions that can be reversed.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "sdf-jkl (via GitHub)" <gi...@apache.org>.
sdf-jkl commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3916856934
@alamb Is there a reason to use [`DateTruncGranularity`](https://github.com/apache/datafusion/blob/468b690d71350bc19c7e7bafd5dc61800973d91e/datafusion/functions/src/datetime/date_trunc.rs#L57) instead of [`IntervalUnit`](https://github.com/apache/arrow-rs/blob/2f40f78e4feae3aee261d9608cede9535e1429e0/arrow-cast/src/parse.rs#L1055) in `date_trunc.rs`?
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3947006720
updated the PR with something that I think is reasonable
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "sdf-jkl (via GitHub)" <gi...@apache.org>.
sdf-jkl commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-4208726738
@alamb I spoke with @drin, and I’ll be taking this over. Could you reassign this to me?
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3837496584
mmm.. then I think #19733 subsumes #18648
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3915905460
okay, picking this up now, before something else bumps it lol
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "sdf-jkl (via GitHub)" <gi...@apache.org>.
sdf-jkl commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3764827977
@drin I think once #18789 (I split it in two smaller ones #19722, #19733) is closed you could reuse the [preimage api](https://github.com/apache/datafusion/pull/19722/changes#diff-8a327db2db945bcf6ca2b4229885532feae127e94a450600d3fac6ecdc0eeb3fR235-R245) for this simplification.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "UBarney (via GitHub)" <gi...@apache.org>.
UBarney commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3490324927
Rewriting `date_trunc(part, column) <= constant_rhs` as `column <= date_trunc(part, date_add(constant_rhs, INTERVAL 1 part))` may lead to a bug like [this](https://github.com/duckdb/duckdb/issues/19635)
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #18319: Optimize the evaluation of `DATE_TRUNC(<col>) == <constant>)` when pushed down
URL: https://github.com/apache/datafusion/issues/18319
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "UBarney (via GitHub)" <gi...@apache.org>.
UBarney commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3480641547
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
Re: [I] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3498047027
sorry, I should be able to clean things up a bit and share a reference PR today if it'd be useful. I know you independently found the mentioned bug, so you may have already made good progress on a PR
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3916520294
For example:
```rust
// lhs(>) --> column >= next_interval(part, const_rhs)
// lhs(<=) --> column < next_interval(part, const_rhs)
```
If I have `date_trunc('month', col('date')) > '2025-12-03'`, I need to know the `>` from the predicate in order to know whether the resulting preimage range begins with `2025-12-01` or if it begins with `2026-01-01`.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3791463609
> [@sdf-jkl](https://github.com/sdf-jkl) that sounds great. IMO, preimage sounds misleading for something that's using interval containment to simplify a predicate, but maybe there will be other types of functions that can be reversed.
The preimage terminology is from ClickHouse and I believe it refers to https://en.wikipedia.org/wiki/Image_(mathematics)#Inverse_image .
If you have a better suggestion on terminology perhaps we can change it before we release 53.0.0
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3745607457
@UBarney I can take this over if you'd like. I should have some time to push it forward this week and maybe you can help review?
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3797037133
> Fundamentally, this is just expression simplification. The difference is that traditional expression-simplification routines aim to reduce computation cost, while in the “preimage” case the simplification is done to enable predicate pruning. Here, we can tolerate slightly slower evaluation if it makes pruning possible. (and this issue's simplification case both made compute fast, and pruning possible)
I think it is a little more complicated than this -- specifically the API that was added is **not** a general purpose simplification for predicate evaluation -- it is pretty specific for functions that can bound input columns to a known range (the "preimage")
The reason for this is that the simplification looks like
```sql
<constant> <op> <function_call>
```
If kicks in if `function_call` can provide a range (preimage) over which it always evaluates to the `<constant>` and then the simplifier rewrites the expression in terms of new operations and that range
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "sdf-jkl (via GitHub)" <gi...@apache.org>.
sdf-jkl commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3916823216
> [@sdf-jkl](https://github.com/sdf-jkl) I have a couple questions I'm hoping you can help me out with:
>
> 1. for `take_function_args` for `date_trunc` function, do I get the comparison operator? I need to know for example if the predicate is an inequality and which direction (<= vs < and >= vs >).
> 2. in `preimage` for `date_part` you used `part_normalization` and change the `part` argument to a simplified string. I assume I should expect the same argument for `date_trunc`? As in, I should convert the `part` to a string in a similar manner?
>
> Thanks!
@drin,
1. No, you don't need the comparison operator. `preimage` should return an `Interval` and [`rewrite_with_preimage`](https://github.com/apache/datafusion/blob/468b690d71350bc19c7e7bafd5dc61800973d91e/datafusion/optimizer/src/simplify_expressions/udf_preimage.rs#L29) can determine which bound to use (lower or upper). The comparison operator is outside the function call, so it's not one of the function arguments, you can't retrieve it with `take_function_args`.
2. In `date_part`, we simplify the string to make sure we can parse an `IntervalUnit` from it. `date_trunc`, on the other hand, uses its own `DateTruncGranularity` struct instead of `IntervalUnit` and not doing any extra cleanup in `invoke_with_args` other than `lower()`. You could do the same.
```rust
let granularity = DateTruncGranularity::from_str(&granularity_str)?;
```
> For example:
>
> // lhs(>) --> column >= next_interval(part, const_rhs)
> // lhs(<=) --> column < next_interval(part, const_rhs)
> If I have date_trunc('month', col('date')) > '2025-12-03', I need to know the > from the predicate in order to know whether the resulting preimage range begins with 2025-12-01 or if it begins with 2026-01-01.
The operator will be checked during the `preimage_in_comparison` [rule](https://github.com/apache/datafusion/blob/468b690d71350bc19c7e7bafd5dc61800973d91e/datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs#L2004) in `expr_simplifier`, so there is no need to worry about it.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "UBarney (via GitHub)" <gi...@apache.org>.
UBarney commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3502889234
> I know you independently found the mentioned bug, so you may have already made good progress on a PR
I've only just started, and I probably won't have time to continue working on it for the next few days.
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3916501200
@sdf-jkl I have a couple questions I'm hoping you can help me out with:
1. for `take_function_args` for `date_trunc` function, do I get the comparison operator? I need to know for example if the predicate is an inequality and which direction (<= vs < and >= vs >).
2. in `preimage` for `date_part` you used `part_normalization` and change the `part` argument to a simplified string. I assume I should expect the same argument for `date_trunc`? As in, I should convert the `part` to a string in a similar manner?
Thanks!
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "drin (via GitHub)" <gi...@apache.org>.
drin commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3873124931
Sorry, it got bumped a bit towards the end of the week. I should be able to work on this in the next day or so
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3873637069
Thanks @drin !
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "sdf-jkl (via GitHub)" <gi...@apache.org>.
sdf-jkl commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-3814208807
Is is applied in expression simplifier here: https://github.com/apache/datafusion/blob/36c0cda206db6cdb4ace600422835c890294a1f1/datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs#L1997-L1999
Once a udf has the `preimage` implemented, the transformation will be applied
--
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] Optimize the evaluation of `DATE_TRUNC() == )` when pushed down [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18319:
URL: https://github.com/apache/datafusion/issues/18319#issuecomment-4208985928
Done -- thank you @sdf-jkl
--
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