[
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 2:54 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.
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 think it makes sense, and I will
refine things accordingly.
I had changed the title of Jira to 'Convert multi-value NOT IN to AND of
NOT_EQUALS in RelBuilder'.
> 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)
