(calcite) branch main updated: [CALCITE-7019] Simplify 'NULL IN (20, 10)' to 'NULL'

[xiong]

This is an automated email from the ASF dual-hosted git repository.

xiong 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 d96709c4cc [CALCITE-7019] Simplify 'NULL IN (20, 10)' to 'NULL'
d96709c4cc is described below

commit d96709c4cc7ca962601317d0a70914ad95e306e1
Author: Xiong Duan <nobigo...@gmail.com>
AuthorDate: Thu May 15 18:59:32 2025 +0800

    [CALCITE-7019] Simplify 'NULL IN (20, 10)' to 'NULL'
---
 .../main/java/org/apache/calcite/plan/Strong.java  | 13 +++++
 .../org/apache/calcite/rex/RexProgramTest.java     | 65 ++++++++++++++++++++++
 2 files changed, 78 insertions(+)

diff --git a/core/src/main/java/org/apache/calcite/plan/Strong.java 
b/core/src/main/java/org/apache/calcite/plan/Strong.java
index 759ad69e9f..fa8fc6db61 100644
--- a/core/src/main/java/org/apache/calcite/plan/Strong.java
+++ b/core/src/main/java/org/apache/calcite/plan/Strong.java
@@ -21,11 +21,13 @@
 import org.apache.calcite.rex.RexInputRef;
 import org.apache.calcite.rex.RexLiteral;
 import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUnknownAs;
 import org.apache.calcite.rex.RexUtil;
 import org.apache.calcite.rex.RexVisitorImpl;
 import org.apache.calcite.sql.SqlKind;
 import org.apache.calcite.sql.SqlOperator;
 import org.apache.calcite.util.ImmutableBitSet;
+import org.apache.calcite.util.Sarg;
 
 import com.google.common.collect.ImmutableList;
 import com.google.common.collect.ImmutableSet;
@@ -36,6 +38,8 @@
 import java.util.List;
 import java.util.Map;
 
+import static java.util.Objects.requireNonNull;
+
 /** Utilities for strong predicates.
  *
  * <p>A predicate is strong (or null-rejecting) with regard to selected subset 
of inputs
@@ -237,6 +241,15 @@ public boolean isNull(RexNode node) {
         }
       }
       return allNull(caseValues);
+    case SEARCH:
+      final RexCall searchCall = (RexCall) node;
+      boolean isNull = isNull(searchCall.getOperands().get(0));
+      if (isNull) {
+        final Sarg<?> sarg =
+            requireNonNull(((RexLiteral) 
searchCall.getOperands().get(1)).getValueAs(Sarg.class));
+        return sarg.nullAs == RexUnknownAs.UNKNOWN;
+      }
+      return false;
     default:
       return false;
     }
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java 
b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index ce8a0abb49..1224c63298 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -2217,6 +2217,71 @@ private void checkExponentialCnf(int n) {
     checkSimplify(e, "=(?0.int0, 10)");
   }
 
+  /** Unit test for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-7019";>[CALCITE-7019]
+   * Simplify 'NULL IN (20, 10)' to 'NULL'</a>. */
+  @Test void testSimplifyIn() {
+    // NULL in (20, 10) ==> SEARCH(null:Integer, Sarg[10, 20])
+    //   ==>
+    // NULL
+    checkSimplify3_(in(nullInt, literal(20), literal(10)),
+        "null:BOOLEAN", "false", "true");
+
+    // NULL in (NULL, 10) ==> NULL = NULL or NULL = 10
+    //   ==>
+    // NULL
+    checkSimplify3_(in(nullInt, nullInt, literal(10)),
+        "null:BOOLEAN", "false", "true");
+
+    // 10 in (NULL, 10) ==> 10 = NULL or 10 = 10
+    //   ==>
+    // TRUE
+    checkSimplify(in(literal(10), nullInt, literal(10)), "true");
+
+    // 20 in (NULL, 10) ==> 20 = NULL or 20 = 10
+    //   ==>
+    // NULL
+    checkSimplify3_(in(literal(20), nullInt, literal(10)),
+        "null:BOOLEAN", "false", "true");
+
+    // 10 in (NULL, NULL) ==> 10 = null
+    //   ==>
+    // NULL
+    checkSimplify3_(in(literal(10), nullInt, nullInt),
+        "null:BOOLEAN", "false", "true");
+  }
+
+  @Test void testSimplifyNotIn() {
+    // NULL not in (20, 10) ==> not(SEARCH(null:Integer, Sarg[10, 20]))
+    //   ==>
+    // NULL
+    checkSimplify3_(not(in(nullInt, literal(20), literal(10))),
+        "null:BOOLEAN", "false", "true");
+
+    // NULL not in (NULL, 10) ==> not(null = null or null = 10)
+    //   ==>
+    // NULL
+    checkSimplify3_(not(in(nullInt, nullInt, literal(10))),
+        "null:BOOLEAN", "false", "true");
+
+    // 10 not in (NULL, 10) ==> not(10 = null or 10 = 10)
+    //   ==>
+    // FALSE
+    checkSimplify(not(in(literal(10), nullInt, literal(10))), "false");
+
+    // 20 not in (NULL, 10) ==> not(20 = null or 20 = 10)
+    //   ==>
+    // NULL
+    checkSimplify3_(not(in(literal(20), nullInt, literal(10))),
+        "null:BOOLEAN", "false", "true");
+
+    // 10 not in (NULL, NULL) ==> not(10 = null)
+    //   ==>
+    // NULL
+    checkSimplify3_(not(in(literal(10), nullInt, nullInt)),
+        "null:BOOLEAN", "false", "true");
+  }
+
   @Test void testSimplifyInAnd() {
     // deptno in (20, 10) and deptno = 10
     //   ==>