Optimize the evaluation of DATE_TRUNC(<col>) == <constant>) when pushed down

@colinmarc

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

date_trunc(part, column) <= constant_rhs

Will not typically be able to be pushed down

However, this equivalent expression can be

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

CAST(x AS float) = 5

is rewritten to

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 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

It appears this PR to duckdb added the same optimization:

Add date_trunc() simplification rules duckdb/duckdb#18457

take

@UBarney

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)

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.

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

There's a PR to fix that which we found while doing the implementation I shared in the diff above:
duckdb/duckdb#19628

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

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.

@UBarney

@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?

@2010YOUY01

@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 would provide a much better foundation for this issue. It might be worth waiting until that PR is merged before proceeding with this one. What do you think?

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.

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

@drin

Thanks @drin !

okay, picking this up now, before something else bumps it lol

@sdf-jkl

@sdf-jkl I have a couple questions I'm hoping you can help me out with:

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 >).
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!

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.

@drin

@sdf-jkl I have a couple questions I'm hoping you can help me out with:

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 >).

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,

No, you don't need the comparison operator. preimage should return an Interval and rewrite_with_preimage 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.
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.

        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 in expr_simplifier, so there is no need to worry about it.

@alamb

@alamb Is there a reason to use DateTruncGranularity instead of IntervalUnit in date_trunc.rs?

updated the PR with something that I think is reasonable

Improve documentation for ScalarUDFImpl::preimage (apache#20008)

@alamb

@alamb I spoke with @drin, and I’ll be taking this over. Could you reassign this to me?

@sdf-jkl

Done -- thank you @sdf-jkl

Support Preimage for `date_trunc` (apache#22114)

Navigation Menu

Repository navigation

Optimize the evaluation of `DATE_TRUNC(<col>) == <constant>)` when pushed down #18319

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

18 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Optimize the evaluation of DATE_TRUNC(<col>) == <constant>) when pushed down #18319

Description

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Activity

alamb commented on Oct 28, 2025

UBarney commented on Nov 3, 2025

drin commented on Nov 3, 2025

drin commented on Nov 3, 2025

UBarney commented on Nov 5, 2025

drin commented on Nov 5, 2025

drin commented on Nov 6, 2025

UBarney commented on Nov 7, 2025

drin commented on Jan 13, 2026

UBarney commented on Jan 14, 2026

drin commented on Jan 14, 2026

18 remaining items

drin commented on Feb 9, 2026

alamb commented on Feb 9, 2026

drin commented on Feb 17, 2026

drin commented on Feb 17, 2026

drin commented on Feb 17, 2026

sdf-jkl commented on Feb 17, 2026

sdf-jkl commented on Feb 17, 2026

drin commented on Feb 23, 2026

sdf-jkl commented on Apr 8, 2026

alamb commented on Apr 8, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Optimize the evaluation of `DATE_TRUNC(<col>) == <constant>)` when pushed down #18319