[
https://issues.apache.org/jira/browse/CALCITE-7665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18099061#comment-18099061
]
Yu Xu edited comment on CALCITE-7665 at 7/25/26 3:01 AM:
---------------------------------------------------------
Thanks for the suggestion [~julianhyde] ! I’ve done some research.
regarding the NULL situation:
I verified that transforming NOT(OR(=(x, a), =(x, b))) into AND(<>(x, a), <>(x,
b)) is valid under SQL's three-valued logic. De Morgan's laws hold for
TRUE/FALSE/UNKNOWN, and I checked the truth table to confirm there are no
subtle differences, including when the condition is inside a CASE expression
where UNKNOWN is preserved. I will address this issue in a subsequent PR.
moving this to RelBuilder:
De Morgan's laws have already been implemented in RexSimplify.simplifyNot():
{code:java}
case OR:
// NOT distributivity for OR
newOperands = new ArrayList<>();
for (RexNode operand : ((RexCall) a).getOperands()) {
newOperands.add(simplify(not(operand), unknownAs));
}
return simplify( rexBuilder.makeCall( call.getParserPosition(),
SqlStdOperatorTable.AND, newOperands), unknownAs);
{code}
While relying on `RexSimplify` ensures successful conversion, handling this
earlier within `SqlToRelConverter` offers distinct advantages:
1. Generating `AND(NOT_EQUALS)` directly within `SqlToRelConverter` ensures the
initial plan is clean from the start, regardless of whether downstream
simplification occurs.
2. Relying solely on `RexSimplify` means the simplification might be missed in
scenarios that bypass the full optimization pipeline—such as running `EXPLAIN`
on the initial plan or using simple query paths in certain adapters. Handling
it in `SqlToRelConverter` covers these cases.
3. Reduce the expression nesting depth by eliminating a layer of `NOT` calls.
The deeper a node is, the higher the overhead for subsequent traversal,
matching, and copying. Removing this redundancy directly during the conversion
phase benefits both the optimizer and the executor.
4. Maintain symmetry with existing optimizations for single-value cases;
`SqlToRelConverter` already handles such scenarios, and the optimization for
multi-value `NOT_IN` simply completes this symmetry.
Therefore, in my view, optimizing multi-value `NOT_IN` expressions within
`SqlToRelConverter` results in an initial SQL-to-`RelNode` conversion that is
more standardized, flattened, and readable, without depending on whether
subsequent optimizer phases are enabled.
This is the information I have; please feel free to point out any errors.
was (Author: JIRAUSER307770):
Thanks for the suggestion [~julianhyde] ! I’ve done some research.
regarding the NULL situation:
I verified that transforming NOT(OR(=(x, a), =(x, b))) into AND(<>(x, a), <>(x,
b)) is valid under SQL's three-valued logic. De Morgan's laws hold for
TRUE/FALSE/UNKNOWN, and I checked the truth table to confirm there are no
subtle differences, including when the condition is inside a CASE expression
where UNKNOWN is preserved. I will address this issue in a subsequent PR.
moving this to RelBuilder:
De Morgan's laws have already been implemented in RexSimplify.simplifyNot():
{code:java}
case OR:
// NOT distributivity for OR
newOperands = new ArrayList<>();
for (RexNode operand : ((RexCall) a).getOperands()) {
newOperands.add(simplify(not(operand), unknownAs));
}
return simplify( rexBuilder.makeCall( call.getParserPosition(),
SqlStdOperatorTable.AND, newOperands), unknownAs);
{code}
While relying on `RexSimplify` ensures successful conversion, handling this
earlier within `SqlToRelConverter` offers distinct advantages:
1. Generating `AND(NOT_EQUALS)` directly within `SqlToRelConverter` ensures the
initial plan is clean from the start, regardless of whether downstream
simplification occurs.
2. Relying solely on `RexSimplify` means the simplification might be missed in
scenarios that bypass the full optimization pipeline—such as running `EXPLAIN`
on the initial plan or using simple query paths in certain adapters. Handling
it in `SqlToRelConverter` covers these cases.
Therefore, in my view, optimizing multi-value `NOT_IN` expressions within
`SqlToRelConverter` results in an initial SQL-to-`RelNode` conversion that is
more standardized, flattened, and readable, without depending on whether
subsequent optimizer phases are enabled.
This is the information I have; please feel free to point out any errors.
> 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
> 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)
