Stamatis Zampetakis created CALCITE-7194:
--------------------------------------------
Summary: 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
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}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
