This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 7cea035f05 [CALCITE-6927] Add rule for join condition remove IS NOT
DISTINCT FROM
7cea035f05 is described below
commit 7cea035f0533e5d420b9cec7008f26d835cb6b84
Author: Zhen Chen <[email protected]>
AuthorDate: Fri Apr 11 22:55:11 2025 +0800
[CALCITE-6927] Add rule for join condition remove IS NOT DISTINCT FROM
---
.../org/apache/calcite/rel/rules/CoreRules.java | 6 +
.../JoinConditionExpandIsNotDistinctFromRule.java | 141 +++++++++++++++++++++
.../java/org/apache/calcite/test/JdbcTest.java | 18 +++
.../org/apache/calcite/test/RelOptRulesTest.java | 28 ++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 48 +++++++
5 files changed, 241 insertions(+)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index 717e919b2d..012ae2ce39 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -324,6 +324,12 @@ private CoreRules() {}
FILTER_EXPAND_IS_NOT_DISTINCT_FROM =
FilterRemoveIsNotDistinctFromRule.Config.DEFAULT.toRule();
+ /** Rule that replaces {@code IS NOT DISTINCT FROM}
+ * in a {@link Join} condition with logically equivalent operations. */
+ public static final JoinConditionExpandIsNotDistinctFromRule
+ JOIN_CONDITION_EXPAND_IS_NOT_DISTINCT_FROM =
+ JoinConditionExpandIsNotDistinctFromRule.Config.DEFAULT.toRule();
+
/** Rule that pushes a {@link Filter} past a {@link SetOp}. */
public static final FilterSetOpTransposeRule FILTER_SET_OP_TRANSPOSE =
FilterSetOpTransposeRule.Config.DEFAULT.toRule();
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/JoinConditionExpandIsNotDistinctFromRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/JoinConditionExpandIsNotDistinctFromRule.java
new file mode 100644
index 0000000000..dfcd66f311
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/rel/rules/JoinConditionExpandIsNotDistinctFromRule.java
@@ -0,0 +1,141 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.rel.rules;
+
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexShuttle;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.tools.RelBuilder;
+
+import org.immutables.value.Value;
+
+/**
+ * Planner rule that replaces {@code IS NOT DISTINCT FROM}
+ * in a {@link Join} condition with logically equivalent operations.
+ *
+ * @see SqlStdOperatorTable#IS_NOT_DISTINCT_FROM
+ * @see RelBuilder#isNotDistinctFrom
+ */
[email protected]
+public final class JoinConditionExpandIsNotDistinctFromRule
+ extends RelRule<JoinConditionExpandIsNotDistinctFromRule.Config>
+ implements TransformationRule {
+
+ /** Creates a JoinConditionExpandIsNotDistinctFromRule. */
+ JoinConditionExpandIsNotDistinctFromRule(Config config) {
+ super(config);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ Join join = call.rel(0);
+ RexNode oldJoinCond = join.getCondition();
+
+ RexCall found =
+ RexUtil.findOperatorCall(SqlStdOperatorTable.IS_NOT_DISTINCT_FROM,
+ oldJoinCond);
+
+ if (found == null) {
+ // no longer contains isNotDistinctFromOperator
+ return;
+ }
+
+ for (RexNode op : found.getOperands()) {
+ if (op.getType().getSqlTypeName() == SqlTypeName.MAP) {
+ // map type cannot be compared
+ return;
+ }
+ }
+
+ RemoveIsNotDistinctFromRexShuttle rewriteShuttle =
+ new RemoveIsNotDistinctFromRexShuttle(
+ join.getCluster().getRexBuilder());
+
+ RelNode newJoin = join.accept(rewriteShuttle);
+ assert newJoin instanceof Join;
+ if (((Join) newJoin).getCondition().equals(join.getCondition())) {
+ return;
+ }
+ call.transformTo(newJoin);
+ }
+
+ //~ Inner Classes ----------------------------------------------------------
+
+ /** The RexShuttle use for converting 'x IS NOT DISTINCT FROM y' to
+ * '(COALESCE(x, {zero}) = COALESCE(y, {zero}))
+ * AND ((x IS NULL) = (y IS NULL))'. */
+ private static class RemoveIsNotDistinctFromRexShuttle extends RexShuttle {
+ final RexBuilder rexBuilder;
+
+ RemoveIsNotDistinctFromRexShuttle(
+ RexBuilder rexBuilder) {
+ this.rexBuilder = rexBuilder;
+ }
+
+ @Override public RexNode visitCall(RexCall call) {
+ RexNode newCall = super.visitCall(call);
+
+ if (call.getOperator()
+ == SqlStdOperatorTable.IS_NOT_DISTINCT_FROM) {
+ RexCall tmpCall = (RexCall) newCall;
+
+ RexNode operand0 = tmpCall.operands.get(0);
+ RexNode operand1 = tmpCall.operands.get(1);
+
+ RexNode operand0Zero = rexBuilder.makeZeroRexNode(operand0.getType());
+ RexNode operand1Zero = rexBuilder.makeZeroRexNode(operand1.getType());
+
+ RexNode coalesceCondition =
+ rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
+ rexBuilder.makeCall(SqlStdOperatorTable.COALESCE,
+ operand0, operand0Zero),
+ rexBuilder.makeCall(SqlStdOperatorTable.COALESCE,
+ operand1, operand1Zero));
+
+ RexNode isNullCondition =
+ rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, operand0),
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, operand1));
+
+ newCall =
+ rexBuilder.makeCall(SqlStdOperatorTable.AND,
+ coalesceCondition, isNullCondition);
+ }
+ return newCall;
+ }
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT =
ImmutableJoinConditionExpandIsNotDistinctFromRule.Config.of()
+ .withOperandSupplier(b -> b.operand(Join.class).anyInputs());
+
+ @Override default JoinConditionExpandIsNotDistinctFromRule toRule() {
+ return new JoinConditionExpandIsNotDistinctFromRule(this);
+ }
+ }
+}
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index f17a8f57fc..f9d6775a95 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2862,6 +2862,24 @@ private void checkNullableTimestamp(CalciteAssert.Config
config) {
+ "T=heodore\n");
}
+ @Test void testJoinConditionExpandIsNotDistinctFrom() {
+ final String sql = ""
+ + "select \"t1\".\"commission\" from \"hr\".\"emps\" as \"t1\"\n"
+ + "join\n"
+ + "\"hr\".\"emps\" as \"t2\"\n"
+ + "on \"t1\".\"commission\" is not distinct from
\"t2\".\"commission\"";
+ CalciteAssert.hr()
+ .query(sql)
+ .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
+ planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
+
planner.addRule(CoreRules.JOIN_CONDITION_EXPAND_IS_NOT_DISTINCT_FROM);
+ })
+ .explainContains("HashJoin")
+ .returnsUnordered("commission=1000",
+ "commission=250",
+ "commission=500",
+ "commission=null");
+ }
@Test void testReuseExpressionWhenNullChecking5() {
final String sql = "select substring(trim(\n"
+ "substring(\"name\",\n"
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 4d56ea2b33..7d6233a6f6 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -8845,6 +8845,34 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule
rule) {
relFn(relFn).withRule(CoreRules.FILTER_EXPAND_IS_NOT_DISTINCT_FROM).check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6927";>[CALCITE-6927]
+ * Add rule for join condition remove IS NOT DISTINCT FROM</a>. */
+ @Test void testJoinConditinExpandIsNotDistinctFromRule() {
+ final String sql = ""
+ + "select t1.ENAME from empnullables as t1\n"
+ + "join\n"
+ + "empnullables as t2\n"
+ + "on t1.ENAME is not distinct from t2.ENAME";
+ sql(sql)
+ .withRule(CoreRules.JOIN_CONDITION_EXPAND_IS_NOT_DISTINCT_FROM)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6927";>[CALCITE-6927]
+ * Add rule for join condition remove IS NOT DISTINCT FROM</a>. */
+ @Test void testJoinConditinExpandIsNotDistinctFromRuleArray() {
+ final String sql = ""
+ + "select t1.ADMINS from DEPT_NESTED_EXPANDED as t1\n"
+ + "join\n"
+ + "DEPT_NESTED_EXPANDED as t2\n"
+ + "on t1.ADMINS is not distinct from t2.ADMINS";
+ sql(sql)
+ .withRule(CoreRules.JOIN_CONDITION_EXPAND_IS_NOT_DISTINCT_FROM)
+ .check();
+ }
+
/** Creates an environment for testing spatial queries. */
private RelOptFixture spatial(String sql) {
final HepProgram program = new HepProgramBuilder()
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 d6ebdfd700..27cbd80a40 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6579,6 +6579,54 @@ LogicalProject(EMPNO=[$3], ENAME=[$4], JOB=[$5],
MGR=[$6], HIREDATE=[$7], SAL=[$
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[scott, EMP]])
LogicalTableScan(table=[[scott, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinConditinExpandIsNotDistinctFromRule">
+ <Resource name="sql">
+ <![CDATA[select t1.ENAME from empnullables as t1
+join
+empnullables as t2
+on t1.ENAME is not distinct from t2.ENAME]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ENAME=[$1])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($1, $10)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(ENAME=[$1])
+ LogicalJoin(condition=[AND(=(COALESCE($1, ''), COALESCE($10, '')), =(IS
NULL($1), IS NULL($10)))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinConditinExpandIsNotDistinctFromRuleArray">
+ <Resource name="sql">
+ <![CDATA[select t1.ADMINS from DEPT_NESTED_EXPANDED as t1
+join
+DEPT_NESTED_EXPANDED as t2
+on t1.ADMINS is not distinct from t2.ADMINS]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(ADMINS=[$3])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($3, $8)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED_EXPANDED]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED_EXPANDED]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(ADMINS=[$3])
+ LogicalJoin(condition=[AND(=(COALESCE($3, CAST(ARRAY()):VARCHAR(5) NOT NULL
ARRAY NOT NULL), COALESCE($8, CAST(ARRAY()):VARCHAR(5) NOT NULL ARRAY NOT
NULL)), =(IS NULL($3), IS NULL($8)))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED_EXPANDED]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED_EXPANDED]])
]]>
</Resource>
</TestCase>
