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 aa11fd6e54 [CALCITE-6973] Add rule to convert Minus to Filter
aa11fd6e54 is described below
commit aa11fd6e5402de239ebb6ed8f2b82b8b449eedbd
Author: Zhen Chen <[email protected]>
AuthorDate: Fri Apr 25 06:46:11 2025 +0800
[CALCITE-6973] Add rule to convert Minus to Filter
---
.../org/apache/calcite/rel/rules/CoreRules.java | 4 +
.../calcite/rel/rules/MinusToFilterRule.java | 121 +++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 26 +++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 53 +++++++++
4 files changed, 204 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 fb4fdd5ea7..be7d8998b8 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
@@ -383,6 +383,10 @@ private CoreRules() {}
public static final IntersectToSemiJoinRule INTERSECT_TO_SEMI_JOIN =
IntersectToSemiJoinRule.Config.DEFAULT.toRule();
+ /** Rule that translates a {@link Minus} to {@link Filter}. */
+ public static final MinusToFilterRule MINUS_TO_FILTER =
+ MinusToFilterRule.Config.DEFAULT.toRule();
+
/** Rule that translates a distinct
* {@link Minus} into a group of operators
* composed of {@link Union}, {@link Aggregate}, etc. */
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/MinusToFilterRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/MinusToFilterRule.java
new file mode 100644
index 0000000000..2a04991b0b
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rel/rules/MinusToFilterRule.java
@@ -0,0 +1,121 @@
+/*
+ * 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.Filter;
+import org.apache.calcite.rel.core.Minus;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.tools.RelBuilder;
+
+import org.immutables.value.Value;
+
+/**
+ * Rule that replaces {@link Minus} operator with {@link Filter}
+ * when both inputs are from the same source with only filter conditions
differing.
+ * Only inspect a single {@link Filter} layer in the inputs of {@link Minus}.
+ * For inputs with nested {@link Filter}s, apply {@link CoreRules#FILTER_MERGE}
+ * as a preprocessing step.
+ *
+ * <p>Example transformation:
+ * <blockquote><pre>
+ * SELECT mgr, comm FROM emp WHERE mgr = 12
+ * EXCEPT
+ * SELECT mgr, comm FROM emp WHERE comm = 5
+ *
+ * to
+ *
+ * SELECT DISTINCT mgr, comm FROM emp
+ * WHERE mgr = 12 AND NOT(comm = 5)
+ * </pre></blockquote>
+ */
[email protected]
+public class MinusToFilterRule
+ extends RelRule<MinusToFilterRule.Config>
+ implements TransformationRule {
+
+ /** Creates an MinusToFilterRule. */
+ protected MinusToFilterRule(Config config) {
+ super(config);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Minus minus = call.rel(0);
+ if (minus.all || minus.getInputs().size() != 2) {
+ return;
+ }
+ final RelBuilder builder = call.builder();
+ final RelNode leftInput = call.rel(1);
+ final Filter rightInput = call.rel(2);
+
+ if (!RexUtil.isDeterministic(rightInput.getCondition())) {
+ return;
+ }
+
+ RelNode leftBase;
+ RexNode leftCond = null;
+ if (leftInput instanceof Filter) {
+ Filter leftFilter = (Filter) leftInput;
+ leftBase = leftFilter.getInput().stripped();
+ leftCond = leftFilter.getCondition();
+ } else {
+ leftBase = leftInput.stripped();
+ }
+
+ final RelNode rightBase = rightInput.getInput().stripped();
+ if (!leftBase.equals(rightBase)) {
+ return;
+ }
+
+ // Right input is Filter, right cond should be not null
+ final RexNode finalCond = leftCond != null
+ ? builder.and(leftCond, builder.not(rightInput.getCondition()))
+ : builder.not(rightInput.getCondition());
+
+ builder.push(leftBase)
+ .filter(finalCond)
+ .distinct();
+
+ call.transformTo(builder.build());
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = ImmutableMinusToFilterRule.Config.of()
+ .withOperandFor(Minus.class, RelNode.class, Filter.class);
+
+ @Override default MinusToFilterRule toRule() {
+ return new MinusToFilterRule(this);
+ }
+
+ /** Defines an operand tree for the given classes. */
+ default Config withOperandFor(Class<? extends Minus> minusClass,
+ Class<? extends RelNode> relNodeClass, Class<? extends Filter>
filterClass) {
+ return withOperandSupplier(
+ b0 -> b0.operand(minusClass).inputs(
+ b1 -> b1.operand(relNodeClass).anyInputs(),
+ b2 -> b2.operand(filterClass).anyInputs()))
+ .as(Config.class);
+ }
+ }
+}
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 90680ca528..0ab315d701 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -10421,4 +10421,30 @@ private void
checkLoptOptimizeJoinRule(LoptOptimizeJoinRule rule) {
.withRule(CoreRules.JOIN_CONDITION_PUSH)
.check();
}
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6973";>[CALCITE-6973]
+ * Add rule for convert Minus to Filter</a>. */
+ @Test void testMinusToFilterRule() {
+ final String sql = "SELECT mgr, comm FROM emp WHERE mgr = 12\n"
+ + "EXCEPT\n"
+ + "SELECT mgr, comm FROM emp WHERE comm = 5\n";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.MINUS_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6973";>[CALCITE-6973]
+ * Add rule for convert Minus to Filter</a>. */
+ @Test void testMinusToFilterRule2() {
+ final String sql = "SELECT mgr, comm FROM emp\n"
+ + "EXCEPT\n"
+ + "SELECT mgr, comm FROM emp WHERE comm = 5\n";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.MINUS_TO_FILTER)
+ .check();
+ }
}
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 180f98499c..0f2a9704af 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -9012,6 +9012,59 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], $f3=[1])
LogicalFilter(condition=[=($7, 30)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMinusToFilterRule">
+ <Resource name="sql">
+ <![CDATA[SELECT mgr, comm FROM emp WHERE mgr = 12
+EXCEPT
+SELECT mgr, comm FROM emp WHERE comm = 5
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalFilter(condition=[=($0, 12)])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[=($1, 5)])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{0, 1}])
+ LogicalFilter(condition=[AND(=($0, 12), <>($1, 5))])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMinusToFilterRule2">
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[=($1, 5)])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{0, 1}])
+ LogicalFilter(condition=[<>($1, 5)])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="sql">
+ <![CDATA[SELECT mgr, comm FROM emp
+EXCEPT
+SELECT mgr, comm FROM emp WHERE comm = 5
]]>
</Resource>
</TestCase>
