[
https://issues.apache.org/jira/browse/CALCITE-7665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18099327#comment-18099327
]
Julian Hyde commented on CALCITE-7665:
--------------------------------------
{{SqlToRelConverter}} is 7,000 lines long and does too much. Ideally, it would
do only things that can ONLY be done when the AST is still present. It goes far
beyond that and we should be trying to reduce it, not add new responsibilities.
If you make {{RelBuilder}} do this optimization, it can still happen at
Sql-to-Rel conversion time, and therefore meet your goals 1 and 3, but the code
won't add to the length of {{SqlToRelConverter}}, and the optimization will
happen at other times (e.g when a planner rule creates new {{RelNode}}s).
> 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
> Priority: Major
> Labels: pull-request-available
> 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)
