This is an automated email from the ASF dual-hosted git repository.
xuzifu666 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new cc1dcc4892 [CALCITE-7665] Convert multi-value NOT IN to AND of
NOT_EQUALS in SqlToRelConverter
cc1dcc4892 is described below
commit cc1dcc48925699d729e8e77d08526bc3c618f704
Author: Yu Xu <[email protected]>
AuthorDate: Sun Jul 26 11:28:16 2026 +0800
[CALCITE-7665] Convert multi-value NOT IN to AND of NOT_EQUALS in
SqlToRelConverter
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 7 +++++
.../apache/calcite/test/SqlToRelConverterTest.java | 10 ++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 4 +--
.../apache/calcite/test/SqlToRelConverterTest.xml | 30 +++++++++++++++++++---
4 files changed, 46 insertions(+), 5 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index d5cdb29ade..828c3d94ab 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1994,6 +1994,13 @@ public RelNode convertToSingleValueSubq(
case ALL:
return RexUtil.composeConjunction(rexBuilder, comparisons, true);
case NOT_IN:
+ if (comparisons.stream().allMatch(c -> c.getKind() == SqlKind.EQUALS)) {
+ final List<RexNode> notEqualsComparisons =
+ Util.transform(comparisons,
+ c -> rexBuilder.makeCall(SqlStdOperatorTable.NOT_EQUALS,
+ ((RexCall) c).getOperands()));
+ return RexUtil.composeConjunction(rexBuilder, notEqualsComparisons,
false);
+ }
return rexBuilder.makeCall(SqlStdOperatorTable.NOT,
RexUtil.composeDisjunction(rexBuilder, comparisons));
case IN:
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 8b75a495b7..4b2a764e66 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1286,6 +1286,16 @@ private void checkCallTypesConsistent(String sql) {
sql(sql).ok();
}
+ @Test void testNotInSingleValue() {
+ final String sql = "select * from emp where deptno not in (10)";
+ sql(sql).ok();
+ }
+
+ @Test void testNotInMultiValue() {
+ final String sql = "select * from emp where deptno not in (10, 20)";
+ sql(sql).ok();
+ }
+
@Test void testAggFilterWithInSubQuery() {
final String sql = "select\n"
+ " count(*) filter (where empno in (select deptno from
empnullables))\n"
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 33d72da4d3..35dc172427 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6938,7 +6938,7 @@ join sales.emp e on e.deptno = d.deptno and d.deptno not
in (4, 6)]]>
<Resource name="planBefore">
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
- LogicalJoin(condition=[AND(=($16, $7), NOT(OR(=($7, 4), =($7, 6))))],
joinType=[inner])
+ LogicalJoin(condition=[AND(=($16, $7), <>($7, 4), <>($7, 6))],
joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -6947,7 +6947,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
LogicalJoin(condition=[=($16, $7)], joinType=[inner])
- LogicalFilter(condition=[NOT(OR(=($7, 4), =($7, 6)))])
+ LogicalFilter(condition=[AND(<>($7, 4), <>($7, 6))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalFilter(condition=[SEARCH($7, Sarg[(-∞..4), (4..6), (6..+∞)])])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index ee3a20d4cd..f3c0134656 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -6318,7 +6318,7 @@
LogicalProject(EXPR$0=[COMPARE_STRINGS_OR_NUMERIC_VALUES(1, 1)])
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0])
- LogicalFilter(condition=[CASE(true, NOT(OR(=($7, 10), =($7, 20))), false,
NOT(false), NOT(OR(=($7, 30), =($7, 40))))])
+ LogicalFilter(condition=[CASE(true, AND(<>($7, 10), <>($7, 20)), false,
NOT(false), AND(<>($7, 30), <>($7, 40)))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -6330,7 +6330,7 @@ LogicalProject(EMPNO=[$0])
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0])
- LogicalFilter(condition=[CASE(true, NOT(OR(=($7, 10), =($7, 20))),
NOT(true))])
+ LogicalFilter(condition=[CASE(true, AND(<>($7, 10), <>($7, 20)), NOT(true))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -6342,7 +6342,7 @@ LogicalProject(EMPNO=[$0])
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0])
- LogicalFilter(condition=[CASE(true, CAST(NOT(OR(=($7, 10), =($7,
20)))):BOOLEAN, null:BOOLEAN)])
+ LogicalFilter(condition=[CASE(true, CAST(AND(<>($7, 10), <>($7,
20))):BOOLEAN, null:BOOLEAN)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -6362,6 +6362,30 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalProject($f0=[true])
LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInMultiValue">
+ <Resource name="sql">
+ <![CDATA[select * from emp where deptno not in (10, 20)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[AND(<>($7, 10), <>($7, 20))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInSingleValue">
+ <Resource name="sql">
+ <![CDATA[select * from emp where deptno not in (10)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[<>($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
