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 6748fc8dee [CALCITE-7002] Create an optimization rule to eliminate
UNION from the same source with different filters
6748fc8dee is described below
commit 6748fc8deed0e0a9185145f5ac55bbdc598f218d
Author: Zhen Chen <[email protected]>
AuthorDate: Tue May 13 23:57:20 2025 +0800
[CALCITE-7002] Create an optimization rule to eliminate UNION from the same
source with different filters
---
.../org/apache/calcite/rel/rules/CoreRules.java | 14 +
.../calcite/rel/rules/MinusToFilterRule.java | 5 +-
.../calcite/rel/rules/SetOpToFilterRule.java | 287 +++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 158 ++++++++++-
.../org/apache/calcite/test/RelOptRulesTest.xml | 314 ++++++++++++++++++++-
5 files changed, 771 insertions(+), 7 deletions(-)
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 94b69febcc..89753edb1d 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
@@ -388,7 +388,21 @@ private CoreRules() {}
public static final IntersectToSemiJoinRule INTERSECT_TO_SEMI_JOIN =
IntersectToSemiJoinRule.Config.DEFAULT.toRule();
+ /** Rule that translates a {@link Union} to {@link Filter}. */
+ public static final SetOpToFilterRule UNION_FILTER_TO_FILTER =
+ SetOpToFilterRule.Config.UNION.toRule();
+
+ /** Rule that translates a {@link Intersect} to {@link Filter}. */
+ public static final SetOpToFilterRule INTERSECT_FILTER_TO_FILTER =
+ SetOpToFilterRule.Config.INTERSECT.toRule();
+
/** Rule that translates a {@link Minus} to {@link Filter}. */
+ public static final SetOpToFilterRule MINUS_FILTER_TO_FILTER =
+ SetOpToFilterRule.Config.MINUS.toRule();
+
+ /** Rule that sames as {@link CoreRules#MINUS_FILTER_TO_FILTER},
+ * But it is deprecated. */
+ @Deprecated
public static final MinusToFilterRule MINUS_TO_FILTER =
MinusToFilterRule.Config.DEFAULT.toRule();
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
index 2a04991b0b..0753e1ceca 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/MinusToFilterRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/MinusToFilterRule.java
@@ -51,7 +51,10 @@ public class MinusToFilterRule
extends RelRule<MinusToFilterRule.Config>
implements TransformationRule {
- /** Creates an MinusToFilterRule. */
+ /**
+ * This rule is replaced by {@link SetOpToFilterRule}.
+ * Please see {@link CoreRules#MINUS_FILTER_TO_FILTER} */
+ @Deprecated
protected MinusToFilterRule(Config config) {
super(config);
}
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SetOpToFilterRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SetOpToFilterRule.java
new file mode 100644
index 0000000000..127485239c
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SetOpToFilterRule.java
@@ -0,0 +1,287 @@
+/*
+ * 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.Intersect;
+import org.apache.calcite.rel.core.Minus;
+import org.apache.calcite.rel.core.SetOp;
+import org.apache.calcite.rel.core.Union;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.util.Pair;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+import org.immutables.value.Value;
+
+import java.util.ArrayList;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Collectors;
+
+/**
+ * Rule that replaces {@link SetOp} operator with {@link Filter}
+ * when both inputs are from the same source with only filter conditions
differing.
+ * For nested filters, the rule {@link CoreRules#FILTER_MERGE}
+ * should be used prior to invoking this one.
+ *
+ * <p>Example:
+ *
+ * <p>UNION
+ * <blockquote><pre>
+ * SELECT mgr, comm FROM emp WHERE mgr = 12
+ * UNION
+ * SELECT mgr, comm FROM emp WHERE comm = 5
+ *
+ * is rewritten to
+ *
+ * SELECT DISTINCT mgr, comm FROM emp
+ * WHERE mgr = 12 OR comm = 5
+ * </pre></blockquote>
+ *
+ * <p>UNION with multiple inputs
+ * <blockquote><pre>
+ * SELECT deptno FROM emp WHERE deptno = 12
+ * UNION
+ * SELECT deptno FROM dept WHERE deptno = 5
+ * UNION
+ * SELECT deptno FROM emp WHERE deptno = 6
+ * UNION
+ * SELECT deptno FROM dept WHERE deptno = 10
+ *
+ * is rewritten to
+ *
+ * SELECT deptno FROM emp WHERE deptno = 12 OR deptno = 6
+ * UNION
+ * SELECT deptno FROM dept WHERE deptno = 5 OR deptno = 10
+ * </pre></blockquote>
+ *
+ * <p>INTERSECT
+ * <blockquote><pre>
+ * SELECT mgr, comm FROM emp WHERE mgr = 12
+ * INTERSECT
+ * SELECT mgr, comm FROM emp WHERE comm = 5
+ *
+ * is rewritten to
+ *
+ * SELECT DISTINCT mgr, comm FROM emp
+ * WHERE mgr = 12 AND comm = 5
+ * </pre></blockquote>
+ *
+ * <p>EXCEPT
+ * <blockquote><pre>
+ * SELECT mgr, comm FROM emp WHERE mgr = 12
+ * EXCEPT
+ * SELECT mgr, comm FROM emp WHERE comm = 5
+ *
+ * is rewritten to
+ *
+ * SELECT DISTINCT mgr, comm FROM emp
+ * WHERE mgr = 12 AND NOT(comm = 5)
+ * </pre></blockquote>
+ */
[email protected]
+public class SetOpToFilterRule
+ extends RelRule<SetOpToFilterRule.Config>
+ implements TransformationRule {
+
+ /** Creates an SetOpToFilterRule. */
+ protected SetOpToFilterRule(Config config) {
+ super(config);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ config.matchHandler().accept(this, call);
+ }
+
+ private void match(RelOptRuleCall call) {
+ final SetOp setOp = call.rel(0);
+ final List<RelNode> inputs = setOp.getInputs();
+ if (setOp.all || inputs.size() < 2) {
+ return;
+ }
+
+ final RelBuilder builder = call.builder();
+ Pair<RelNode, @Nullable RexNode> first =
extractSourceAndCond(inputs.get(0).stripped());
+
+ // Groups conditions by their source relational node and input position.
+ // - Key: Pair of (sourceRelNode, inputPosition)
+ // - inputPosition is null for mergeable conditions
+ // - inputPosition contains original index for non-mergeable inputs
+ // - Value: List of conditions
+ //
+ // For invalid conditions (non-deterministic expressions or containing
subqueries),
+ // positions are tagged with their input indices to skip unmergeable inputs
+ // during map-based grouping. Other positions are set to null.
+ Map<Pair<RelNode, @Nullable Integer>, List<@Nullable RexNode>>
sourceToConds =
+ new LinkedHashMap<>();
+
+ RelNode firstSource = first.left;
+ sourceToConds.computeIfAbsent(Pair.of(firstSource, null),
+ k -> new ArrayList<>()).add(first.right);
+
+ for (int i = 1; i < inputs.size(); i++) {
+ final RelNode input = inputs.get(i).stripped();
+ final Pair<RelNode, @Nullable RexNode> pair =
extractSourceAndCond(input);
+ sourceToConds.computeIfAbsent(Pair.of(pair.left, pair.right != null ?
null : i),
+ k -> new ArrayList<>()).add(pair.right);
+ }
+
+ if (sourceToConds.size() == inputs.size()) {
+ return;
+ }
+
+ int branchCount = 0;
+ for (Map.Entry<Pair<RelNode, @Nullable Integer>, List<@Nullable RexNode>>
entry
+ : sourceToConds.entrySet()) {
+ Pair<RelNode, @Nullable Integer> left = entry.getKey();
+ List<@Nullable RexNode> conds = entry.getValue();
+ // Single null condition indicates pass-through branch,
+ // directly add its corresponding input to the new inputs list.
+ if (conds.size() == 1 && conds.get(0) == null) {
+ builder.push(left.left);
+ branchCount++;
+ continue;
+ }
+
+ List<RexNode> condsNonNull = conds.stream().map(e -> {
+ assert e != null;
+ return e;
+ }).collect(Collectors.toList());
+
+ RexNode combinedCond =
+ combineConditions(builder, condsNonNull, setOp, left.left ==
firstSource);
+
+ builder.push(left.left)
+ .filter(combinedCond);
+ branchCount++;
+ }
+
+ // RelBuilder will not create 1-input SetOp
+ // and remove the distinct after a SetOp
+ buildSetOp(builder, branchCount, setOp)
+ .distinct();
+ call.transformTo(builder.build());
+ }
+
+ private static RelBuilder buildSetOp(RelBuilder builder, int count, RelNode
setOp) {
+ if (setOp instanceof Union) {
+ return builder.union(false, count);
+ } else if (setOp instanceof Intersect) {
+ return builder.intersect(false, count);
+ } else if (setOp instanceof Minus) {
+ return builder.minus(false, count);
+ }
+ // unreachable
+ throw new IllegalStateException("unreachable code");
+ }
+
+ private static Pair<RelNode, @Nullable RexNode> extractSourceAndCond(RelNode
input) {
+ if (input instanceof Filter) {
+ Filter filter = (Filter) input;
+ if (!RexUtil.isDeterministic(filter.getCondition())
+ || RexUtil.SubQueryFinder.containsSubQuery(filter)) {
+ // Skip non-deterministic conditions or those containing subqueries
+ return Pair.of(input, null);
+ }
+ return Pair.of(filter.getInput().stripped(), filter.getCondition());
+ }
+ // For non-filter inputs, use TRUE literal as default condition.
+ return Pair.of(input.stripped(),
+ input.getCluster().getRexBuilder().makeLiteral(true));
+ }
+
+ /**
+ * Creates a combined condition where the first condition
+ * is kept as-is and all subsequent conditions are negated,
+ * then joined with AND operators.
+ *
+ * <p>For example, given conditions [cond1, cond2, cond3],
+ * this constructs (cond1 AND NOT(cond2) AND NOT(cond3)).
+ */
+ private static RexNode andFirstNotRest(RelBuilder builder, List<RexNode>
conds) {
+ List<RexNode> allConds = new ArrayList<>();
+ allConds.add(conds.get(0));
+ for (int i = 1; i < conds.size(); i++) {
+ allConds.add(builder.not(conds.get(i)));
+ }
+ return builder.and(allConds);
+ }
+
+ /**
+ * Combines conditions according to set operation:
+ * UNION: OR combination
+ * INTERSECT: AND combination
+ * MINUS: Special handling where first source uses AND-NOT combination.
+ */
+ private RexNode combineConditions(RelBuilder builder, List<RexNode> conds,
+ SetOp setOp, boolean isFirstSource) {
+ if (setOp instanceof Union) {
+ return builder.or(conds);
+ } else if (setOp instanceof Intersect) {
+ return builder.and(conds);
+ } else if (setOp instanceof Minus) {
+ return isFirstSource
+ ? andFirstNotRest(builder, conds)
+ : builder.or(conds);
+ }
+ // unreachable
+ throw new IllegalStateException("unreachable code");
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable(singleton = false)
+ public interface Config extends RelRule.Config {
+ Config UNION = ImmutableSetOpToFilterRule.Config.builder()
+ .withMatchHandler(SetOpToFilterRule::match)
+ .build()
+ .withOperandSupplier(
+ b0 -> b0.operand(Union.class).anyInputs())
+ .as(Config.class);
+
+ Config INTERSECT = ImmutableSetOpToFilterRule.Config.builder()
+ .withMatchHandler(SetOpToFilterRule::match)
+ .build()
+ .withOperandSupplier(
+ b0 -> b0.operand(Intersect.class).anyInputs())
+ .as(Config.class);
+
+ Config MINUS = ImmutableSetOpToFilterRule.Config.builder()
+ .withMatchHandler(SetOpToFilterRule::match)
+ .build()
+ .withOperandSupplier(
+ b0 -> b0.operand(Minus.class).anyInputs())
+ .as(Config.class);
+
+ @Override default SetOpToFilterRule toRule() {
+ return new SetOpToFilterRule(this);
+ }
+
+ /** Forwards a call to {@link #onMatch(RelOptRuleCall)}. */
+ MatchHandler<SetOpToFilterRule> matchHandler();
+
+ /** Sets {@link #matchHandler()}. */
+ Config withMatchHandler(MatchHandler<SetOpToFilterRule> matchHandler);
+ }
+}
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 88dad59f12..6347d9f5b8 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -10505,26 +10505,176 @@ private void
checkLoptOptimizeJoinRule(LoptOptimizeJoinRule rule) {
/** 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() {
+ @Test void testMinusToFilterRuleWithTwoFilters() {
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)
+ .withRule(CoreRules.MINUS_FILTER_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() {
+ @Test void testMinusToFilterRuleWithOneFilter() {
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)
+ .withRule(CoreRules.MINUS_FILTER_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testMinusToFilterRuleNWayWithMultiSources() {
+ final String sql = "SELECT deptno FROM emp WHERE deptno > 6\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM dept WHERE deptno > 8\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM emp WHERE deptno > 12\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM dept WHERE deptno > 10\n";
+ sql(sql)
+ .withPreRule(CoreRules.MINUS_MERGE, CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.MINUS_FILTER_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testMinusToFilterRuleFourWayWithForSources() {
+ final String sql = "SELECT deptno FROM emp WHERE deptno = 12\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM dept WHERE deptno = 5\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM empnullables WHERE deptno = 6\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM deptnullables WHERE deptno = 10\n";
+ sql(sql)
+ .withPreRule(CoreRules.MINUS_MERGE, CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.MINUS_FILTER_TO_FILTER)
+ .checkUnchanged();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testMinusToFilterNWayWithSubquery() {
+ final String sql = "SELECT deptno FROM emp WHERE deptno > 12\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM dept WHERE deptno = 5\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM emp e1 WHERE EXISTS (\n"
+ + " SELECT 1 FROM emp e2\n"
+ + " WHERE e2.comm = e1.comm)\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM dept WHERE deptno = 10\n"
+ + "EXCEPT\n"
+ + "SELECT deptno FROM emp WHERE deptno > 20\n";
+ sql(sql)
+ .withPreRule(CoreRules.MINUS_MERGE, CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.MINUS_FILTER_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testUnionToFilterRuleWithTwoFilters() {
+ final String sql = "SELECT mgr, comm FROM emp WHERE mgr = 12\n"
+ + "UNION\n"
+ + "SELECT mgr, comm FROM emp WHERE comm = 5\n";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.UNION_FILTER_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testUnionToFilterRuleWithOneFilter() {
+ final String sql = "SELECT mgr, comm FROM emp\n"
+ + "UNION\n"
+ + "SELECT mgr, comm FROM emp WHERE comm = 5\n";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.UNION_FILTER_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testUnionToFilterRuleNWayWithMultiSources() {
+ final String sql = "SELECT deptno FROM emp WHERE deptno = 12\n"
+ + "UNION\n"
+ + "SELECT deptno FROM dept WHERE deptno = 5\n"
+ + "UNION\n"
+ + "SELECT deptno FROM emp WHERE deptno = 6\n"
+ + "UNION\n"
+ + "SELECT deptno FROM dept WHERE deptno = 10\n";
+ sql(sql)
+ .withPreRule(CoreRules.UNION_MERGE, CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.UNION_FILTER_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testIntersectToFilterRuleWithTwoFilters() {
+ final String sql = "SELECT mgr, comm FROM emp WHERE mgr = 12\n"
+ + "INTERSECT\n"
+ + "SELECT mgr, comm FROM emp WHERE comm = 5\n";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.INTERSECT_FILTER_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testIntersectToFilterRuleWithOneFilter() {
+ final String sql = "SELECT mgr, comm FROM emp\n"
+ + "INTERSECT\n"
+ + "SELECT mgr, comm FROM emp WHERE comm = 5\n";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.INTERSECT_FILTER_TO_FILTER)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7002";>[CALCITE-7002]
+ * Create an optimization rule to eliminate UNION
+ * from the same source with different filters</a>. */
+ @Test void testIntersectToFilterRuleNWayWithMultiSources() {
+ final String sql = "SELECT deptno FROM emp WHERE deptno < 12\n"
+ + "INTERSECT\n"
+ + "SELECT deptno FROM dept WHERE deptno > 5\n"
+ + "INTERSECT\n"
+ + "SELECT deptno FROM emp WHERE deptno > 6\n"
+ + "INTERSECT\n"
+ + "SELECT deptno FROM dept WHERE deptno < 10\n";
+ sql(sql)
+ .withPreRule(CoreRules.INTERSECT_MERGE,
CoreRules.PROJECT_FILTER_TRANSPOSE)
+ .withRule(CoreRules.INTERSECT_FILTER_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 18afbd7e2c..9fa38bb416 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6320,6 +6320,99 @@ LogicalIntersect(all=[true])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(NAME=[CAST($1):VARCHAR(20) NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testIntersectToFilterRuleWithTwoFilters">
+ <Resource name="sql">
+ <![CDATA[SELECT mgr, comm FROM emp WHERE mgr = 12
+INTERSECT
+SELECT mgr, comm FROM emp WHERE comm = 5
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalIntersect(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="testIntersectToFilterRuleWithOneFilter">
+ <Resource name="sql">
+ <![CDATA[SELECT mgr, comm FROM emp
+INTERSECT
+SELECT mgr, comm FROM emp WHERE comm = 5
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalIntersect(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>
+ </TestCase>
+ <TestCase name="testIntersectToFilterRuleNWayWithMultiSources">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno FROM emp WHERE deptno < 12
+INTERSECT
+SELECT deptno FROM dept WHERE deptno > 5
+INTERSECT
+SELECT deptno FROM emp WHERE deptno > 6
+INTERSECT
+SELECT deptno FROM dept WHERE deptno < 10
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalIntersect(all=[false])
+ LogicalFilter(condition=[<($0, 12)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[>($0, 5)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[>($0, 6)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<($0, 10)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalIntersect(all=[false])
+ LogicalFilter(condition=[SEARCH($0, Sarg[(6..12)])])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[SEARCH($0, Sarg[(5..10)])])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -9162,7 +9255,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2])
]]>
</Resource>
</TestCase>
- <TestCase name="testMinusToFilterRule">
+ <TestCase name="testMinusToFilterRuleWithTwoFilters">
<Resource name="sql">
<![CDATA[SELECT mgr, comm FROM emp WHERE mgr = 12
EXCEPT
@@ -9189,7 +9282,7 @@ LogicalAggregate(group=[{0, 1}])
]]>
</Resource>
</TestCase>
- <TestCase name="testMinusToFilterRule2">
+ <TestCase name="testMinusToFilterRuleWithOneFilter">
<Resource name="planBefore">
<![CDATA[
LogicalMinus(all=[false])
@@ -9212,6 +9305,131 @@ LogicalAggregate(group=[{0, 1}])
<![CDATA[SELECT mgr, comm FROM emp
EXCEPT
SELECT mgr, comm FROM emp WHERE comm = 5
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMinusToFilterRuleNWayWithMultiSources">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno FROM emp WHERE deptno > 6
+EXCEPT
+SELECT deptno FROM dept WHERE deptno > 8
+EXCEPT
+SELECT deptno FROM emp WHERE deptno > 12
+EXCEPT
+SELECT deptno FROM dept WHERE deptno > 10
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalFilter(condition=[>($0, 6)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[>($0, 8)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[>($0, 12)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[>($0, 10)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalFilter(condition=[SEARCH($0, Sarg[(6..12]])])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[>($0, 8)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMinusToFilterRuleFourWayWithForSources">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno FROM emp WHERE deptno = 12
+EXCEPT
+SELECT deptno FROM dept WHERE deptno = 5
+EXCEPT
+SELECT deptno FROM empnullables WHERE deptno = 6
+EXCEPT
+SELECT deptno FROM deptnullables WHERE deptno = 10
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalFilter(condition=[=($0, 12)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[=($0, 5)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[=($0, 6)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMinusToFilterNWayWithSubquery">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno FROM emp WHERE deptno > 12
+EXCEPT
+SELECT deptno FROM dept WHERE deptno = 5
+EXCEPT
+SELECT deptno FROM emp e1 WHERE EXISTS (
+ SELECT 1 FROM emp e2
+ WHERE e2.comm = e1.comm)
+EXCEPT
+SELECT deptno FROM dept WHERE deptno = 10
+EXCEPT
+SELECT deptno FROM emp WHERE deptno > 20
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalFilter(condition=[>($0, 12)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[=($0, 5)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[=($6, $cor0.COMM)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[>($0, 20)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalFilter(condition=[SEARCH($0, Sarg[(12..20]])])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[SEARCH($0, Sarg[5, 10])])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[=($6, $cor0.COMM)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -18494,6 +18712,98 @@ LogicalUnion(all=[false])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(DEPTNO=[$0], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToFilterRuleWithTwoFilters">
+ <Resource name="sql">
+ <![CDATA[SELECT mgr, comm FROM emp WHERE mgr = 12
+UNION
+SELECT mgr, comm FROM emp WHERE comm = 5
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(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=[OR(=($0, 12), =($1, 5))])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToFilterRuleWithOneFilter">
+ <Resource name="sql">
+ <![CDATA[SELECT mgr, comm FROM emp
+UNION
+SELECT mgr, comm FROM emp WHERE comm = 5
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(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}])
+ LogicalProject(MGR=[$3], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUnionToFilterRuleNWayWithMultiSources">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno FROM emp WHERE deptno = 12
+UNION
+SELECT deptno FROM dept WHERE deptno = 5
+UNION
+SELECT deptno FROM emp WHERE deptno = 6
+UNION
+SELECT deptno FROM dept WHERE deptno = 10
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[false])
+ LogicalFilter(condition=[=($0, 12)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[=($0, 5)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[=($0, 6)])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalUnion(all=[false])
+ LogicalFilter(condition=[SEARCH($0, Sarg[6, 12])])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[SEARCH($0, Sarg[5, 10])])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
