You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2025/09/23 13:36:00 UTC
[jira] [Created] (CALCITE-7194) Simplify comparisons between function calls and literals to SEARCH
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)