[
https://issues.apache.org/jira/browse/CALCITE-7737?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18116506#comment-18116506
]
Julian Hyde commented on CALCITE-7737:
--------------------------------------
Thanks for raising this. It would be a very useful family of optimizations.
Do you have any ideas how to represent this in code? (Something better than 50
custom lines added to {{{}RexSimplify{}}}.) I would be interested to see a
template – say on the {{TRUNC}} function – that people could repeat for other
functions. {{interface SqlSingletonAggFunction}} and {{class
SqlMonotonicUnaryFunction}} are examples of "markers" that indicate that
functions have a particular algebraic identity.
It would also be useful if you could produce a list of expressions that would
benefit from this approach. Can the approach extend beyond monotonic functions?
With the help of AI, maybe the list could be close to exhaustive.
Would you agree that null propagation through strong functions is a special
case of this? For example, the {{ABS}} function is strong, which means that
{{ABS(x)}} is null if and only if {{x}} is null. We use this fact to rewrite
{{ABS(x) IS NULL}} to {{x IS NULL}}.
> Predicate rewriting using function preimages
> --------------------------------------------
>
> Key: CALCITE-7737
> URL: https://issues.apache.org/jira/browse/CALCITE-7737
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Darpan Lunagariya (e6data)
> Priority: Minor
>
> h2. Description
> Add support for rewriting predicates on function results into equivalent
> predicates on their input columns using function preimages.
> For a function {{f}} and result domain {{D}}, the preimage is:
> {code:none}
> preimage(f, D) = {x | f(x) belongs to D}
> {code}
> Therefore:
> {code:none}
> SEARCH(f(column), D) <=> SEARCH(column, preimage(f, D))
> {code}
> The rewrite is applied only when exact equivalence can be established.
> h2. Example
> The {{YEAR}} function is monotonic over a date-time input: as the input
> increases, the extracted year never decreases.
> {code:sql}
> YEAR(event_time) = 2020
> {code}
> The exact preimage of {{2020}} is the corresponding timestamp interval:
> {code:sql}
> event_time >= TIMESTAMP '2020-01-01 00:00:00'
> AND event_time < TIMESTAMP '2021-01-01 00:00:00'
> {code}
> The function predicate can therefore be replaced with the base-column range.
> h2. Initial scope
> The initial generic implementation focuses on monotonic functions. For a
> monotonic function, the preimage of an ordered result range can be
> represented using straightforward input boundaries. These boundaries can be
> derived using a symbolic inverse or bisection.
> Non-monotonic functions may produce multiple disjoint input ranges and
> usually require function-specific reasoning. They are not impossible to
> support, but are outside the initial generic scope.
> h2. Benefits
> * It benefits frequently occurring monotonic functions like Floor, Ceil,
> Round, some lossy cast(subject to runtime behaviour) etc.
> * Enables file, row-group and page pruning using min/max statistics.
> * Avoids evaluating the function for every row when the original predicate is
> replaced with its exact preimage.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
