[
https://issues.apache.org/jira/browse/CALCITE-7194?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-7194:
-----------------------------------------
Description:
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}
was:
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.
{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}
> 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)
