[
https://issues.apache.org/jira/browse/CALCITE-7194?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18022168#comment-18022168
]
Stamatis Zampetakis commented on CALCITE-7194:
----------------------------------------------
Interestingly after CALCITE-1794, the simplifier is able to transform any kind
of {{EXP OP LITERAL}} to SEARCH if the expression ({{{}EXP{}}}) is [wrapped in
a
CAST|https://github.com/apache/calcite/blame/e1ff8b811c68955dca66b47a322526e85724a7d8/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L3166].
+Example+
{noformat}
OR(
=(CAST(UPPER($0)):CHAR(3) NOT NULL, 'ROB'),
=(CAST(UPPER($0)):CHAR(3) NOT NULL, 'BOB')
)
=>
SEARCH(CAST(UPPER($0)):CHAR(2) NOT NULL, Sarg['BOB', 'ROB']:CHAR(3))
{noformat}
The transformation above is already taking place in the simplifier although the
simpler version (without the CAST) is not.
> Simplify comparisons between function calls and literals to SEARCH
> ------------------------------------------------------------------
>
> Key: CALCITE-7194
> URL: https://issues.apache.org/jira/browse/CALCITE-7194
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
>
> In many real world SQL queries there are expressions that evaluate a function
> call and then compare the result with some literal. Frequently, the same
> function call appears multiple times in the same query.
> +Examples+
> {code:sql}
> SELECT ... WHERE UPPER(fname) = 'BOB' OR UPPER(fname) = 'ROB'
> SELECT ... WHERE (salary + bonus) > 100000 AND (salary + bonus) < 200000
> {code}
>
> Such expressions can be represented using the SEARCH operator (CALCITE-4173),
> and take advantage of all associated simplifications based on ranges. The
> goal is to improve RexSimplify class and transform comparisons between
> function calls and literals to SEARCH.
> {noformat}
> OR(=(UPPER($0), 'ROB'), =(UPPER($0), 'BOB')) => SEARCH(UPPER($0), Sarg['BOB',
> 'ROB']:CHAR(3))
> AND(>(+($0, $1), 100000), <(+($0, $1), 200000)) => SEARCH(+($0, $1),
> Sarg[(100000..200000)])
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
