Yu Xu created CALCITE-7665:
------------------------------
Summary: Convert multi-value NOT IN to AND of NOT_EQUALS in
SqlToRelConverter
Key: CALCITE-7665
URL: https://issues.apache.org/jira/browse/CALCITE-7665
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.42.0
Reporter: Yu Xu
Assignee: Yu Xu
Fix For: 1.43.0
Currently "deptno NOT IN (10, 20)" would convert to:
{code:java}
NOT(OR(=(deptno, 10), =(deptno, 20))) {code}
1.Asymmetry with IN the handling of single-valued and multi-valued IN has
already become very straightforward:
{code:java}
deptno IN (10) -> =(deptno, 10)
deptno IN (10, 20) -> OR(=(deptno, 10), =(deptno, 20)) {code}
However, while you optimized `NOT IN` with a single value into `NOT_EQUALS`,
the multi-value case remains as `NOT(OR(EQUALS))`, resulting in a lack of
consistency between the two at the execution plan level.
2. Subsequent optimization rules may fail to match:
Many rules in Calcite operate directly based on `SqlKind` or the operator type.
For instance:
Certain index pushdown and partition pruning optimizations specifically look
for `NOT_EQUALS`.
Constant propagation and range inference handle `<> 10` more naturally than
`NOT(= 10)`.
If expressions consistently appear in the form of `NOT(EQUALS)`, these rules
might overlook them, thereby missing opportunities for further simplification.
3. An extra negation in the generated executable code
When ultimately compiling the `RelNode` into Java code for execution:
{code:java}
`<>(a, b)` directly generates `a != b`.
`NOT(=(a, b))` generates `!(a == b)`. {code}
Although the overhead is minimal, for filter conditions evaluated frequently,
it is best to eliminate any unnecessary operations.
This issue can also be identified from existing tests, such as:
{code:java}
select empno from emp where not case when true then deptno in (10,20) when
false then false else deptno in (30,40) end {code}
current plan is：
{code:java}
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[CASE(true, NOT(OR(=($7, 10), =($7, 20))),
NOT(true))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
it is better covert to：
{code:java}
LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[CASE(true, AND(<>($7, 10), <>($7, 20)), NOT(true))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
