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 99a08c4915 [CALCITE-7008] Extend MinusToAntiJoinRule to support n-way
inputs
99a08c4915 is described below
commit 99a08c491558e4e8a5a3c565450be343cfcbdf6f
Author: Zhen Chen <[email protected]>
AuthorDate: Thu May 15 16:48:30 2025 +0800
[CALCITE-7008] Extend MinusToAntiJoinRule to support n-way inputs
---
.../org/apache/calcite/rel/rules/CoreRules.java | 2 +-
.../calcite/rel/rules/MinusToAntiJoinRule.java | 105 ++++++++++++++++-----
.../java/org/apache/calcite/test/JdbcTest.java | 2 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 13 ++-
.../org/apache/calcite/test/RelOptRulesTest.xml | 41 +++++++-
core/src/test/resources/sql/planner.iq | 58 ++++++++++++
6 files changed, 191 insertions(+), 30 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 6cd62c2240..af1398aa0b 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
@@ -418,7 +418,7 @@ private CoreRules() {}
MinusToDistinctRule.Config.DEFAULT.toRule();
/** Rule to translates a {@link Minus} to {@link Join} anti-join}. */
- public static final MinusToAntiJoinRule MINUS_TO_ANTI_JOIN_RULE =
+ public static final MinusToAntiJoinRule MINUS_TO_ANTI_JOIN =
MinusToAntiJoinRule.Config.DEFAULT.toRule();
/** Rule that converts a {@link LogicalMatch} to the result of calling
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/MinusToAntiJoinRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/MinusToAntiJoinRule.java
index 03cbcc33da..b9a2ae4229 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/MinusToAntiJoinRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/MinusToAntiJoinRule.java
@@ -36,11 +36,18 @@
/**
* Planner rule that translates a {@link Minus}
* to a series of {@link org.apache.calcite.rel.core.Join} that type is
- * {@link JoinRelType#ANTI}. This rule supports 2-way Minus conversion,
+ * {@link JoinRelType#ANTI}. This rule supports n-way Minus conversion,
* as this rule can be repeatedly applied during query optimization to
* refine the plan.
*
- * <h2>Example</h2>
+ * <p>Example for 2-way
+ *
+ * <p>Original sql:
+ * <pre>{@code
+ * select ename from emp where deptno = 10
+ * except
+ * select ename from emp where deptno = 20
+ * }</pre>
*
* <p>Original plan:
* <pre>{@code
@@ -50,7 +57,6 @@
* LogicalTableScan(table=[[CATALOG, SALES, EMP]])
* LogicalProject(ENAME=[$1])
* LogicalFilter(condition=[=($7, 20)])
- * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
* }</pre>
*
* <p>Plan after conversion:
@@ -64,6 +70,46 @@
* LogicalFilter(condition=[=($7, 20)])
* LogicalTableScan(table=[[CATALOG, SALES, EMP]])
* }</pre>
+ *
+ * <p>Example for n-way
+ *
+ * <p>Original sql:
+ * <pre>{@code
+ * select ename from emp where deptno = 10
+ * except
+ * select deptno from emp where ename in ('a', 'b')
+ * except
+ * select ename from empnullables
+ * }</pre>
+ *
+ * <p>Original plan:
+ * <pre>{@code
+ * LogicalMinus(all=[false])
+ * LogicalProject(ENAME=[$1])
+ * LogicalFilter(condition=[=($7, 10)])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * LogicalProject(DEPTNO=[CAST($7):VARCHAR NOT NULL])
+ * LogicalFilter(condition=[OR(=($1, 'a'), =($1, 'b'))])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * LogicalProject(ENAME=[$1])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ * }</pre>
+ *
+ * <p>Plan after conversion:
+ * <pre>{@code
+ * LogicalProject(ENAME=[CAST($0):VARCHAR])
+ * LogicalAggregate(group=[{0}])
+ * LogicalJoin(condition=[<=>(CAST($0):VARCHAR, CAST($1):VARCHAR)],
joinType=[anti])
+ * LogicalJoin(condition=[=(CAST($0):VARCHAR, $1)], joinType=[anti])
+ * LogicalProject(ENAME=[$1])
+ * LogicalFilter(condition=[=($7, 10)])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * LogicalProject(DEPTNO=[CAST($7):VARCHAR NOT NULL])
+ * LogicalFilter(condition=[OR(=($1, 'a'), =($1, 'b'))])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * LogicalProject(ENAME=[$1])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ * }</pre>
*/
@Value.Enclosing
public class MinusToAntiJoinRule
@@ -84,37 +130,44 @@ protected MinusToAntiJoinRule(Config config) {
}
List<RelNode> inputs = minus.getInputs();
- if (inputs.size() != 2) {
+ if (inputs.size() < 2) {
return;
}
final RelBuilder relBuilder = call.builder();
final RexBuilder rexBuilder = relBuilder.getRexBuilder();
- RelNode left = inputs.get(0);
- RelNode right = inputs.get(1);
-
- List<RexNode> conditions = new ArrayList<>();
- int fieldCount = left.getRowType().getFieldCount();
-
- for (int i = 0; i < fieldCount; i++) {
- RelDataType leftFieldType =
left.getRowType().getFieldList().get(i).getType();
- RelDataType rightFieldType =
right.getRowType().getFieldList().get(i).getType();
-
- // No further optimization will be performed based on field nullability,
- // as this can be uniformly optimized by other rules.
- conditions.add(
- relBuilder.isNotDistinctFrom(
- rexBuilder.makeInputRef(leftFieldType, i),
- rexBuilder.makeInputRef(rightFieldType, i + fieldCount)));
+ final RelDataType leastRowType = minus.getRowType();
+ RelNode current = inputs.get(0);
+ relBuilder.push(current);
+
+ for (int i = 1; i < inputs.size(); i++) {
+ RelNode next = inputs.get(i);
+ int fieldCount = current.getRowType().getFieldCount();
+
+ List<RexNode> conditions = new ArrayList<>();
+ for (int j = 0; j < fieldCount; j++) {
+ RelDataType leftFieldType =
current.getRowType().getFieldList().get(j).getType();
+ RelDataType rightFieldType =
next.getRowType().getFieldList().get(j).getType();
+ RelDataType leastFieldType =
leastRowType.getFieldList().get(j).getType();
+
+ conditions.add(
+ relBuilder.isNotDistinctFrom(
+ rexBuilder.makeCast(leastFieldType,
+ rexBuilder.makeInputRef(leftFieldType, j)),
+ rexBuilder.makeCast(leastFieldType,
+ rexBuilder.makeInputRef(rightFieldType, j + fieldCount))));
+ }
+ RexNode condition = RexUtil.composeConjunction(rexBuilder, conditions);
+
+ relBuilder.push(next)
+ .join(JoinRelType.ANTI, condition);
+
+ current = relBuilder.peek();
}
- RexNode condition = RexUtil.composeConjunction(rexBuilder, conditions);
-
- relBuilder.push(left)
- .push(right)
- .join(JoinRelType.ANTI, condition)
- .distinct();
+ relBuilder.distinct()
+ .convert(leastRowType, true);
call.transformTo(relBuilder.build());
}
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 ddad5eead2..14b5a3eb04 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -4230,7 +4230,7 @@ public void checkOrderBy(final boolean desc,
p -> {
p.removeRule(CoreRules.MINUS_TO_DISTINCT);
p.removeRule(ENUMERABLE_MINUS_RULE);
- p.addRule(CoreRules.MINUS_TO_ANTI_JOIN_RULE);
+ p.addRule(CoreRules.MINUS_TO_ANTI_JOIN);
})
.explainContains("joinType=[anti]")
.returnsUnordered(returns);
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 6347d9f5b8..0eee78f82b 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3684,7 +3684,18 @@ private void
checkPushJoinThroughUnionOnRightDoesNotMatchSemiOrAntiJoin(JoinRelT
final String sql = "select ename from emp where deptno = 10\n"
+ "except\n"
+ "select ename from emp where deptno = 20\n";
- sql(sql).withRule(CoreRules.MINUS_TO_ANTI_JOIN_RULE)
+ sql(sql).withRule(CoreRules.MINUS_TO_ANTI_JOIN)
+ .check();
+ }
+
+ @Test void testMinusToAntiJoinRuleMultiInputs() {
+ final String sql = "select ename from emp where deptno = 10\n"
+ + "except\n"
+ + "select deptno from emp where ename in ('a', 'b')\n"
+ + "except\n"
+ + "select ename from empnullables\n";
+ sql(sql).withPreRule(CoreRules.MINUS_MERGE)
+ .withRule(CoreRules.MINUS_TO_ANTI_JOIN)
.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 9fa38bb416..9cf941ef4c 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -9113,13 +9113,52 @@ LogicalMinus(all=[false])
<Resource name="planAfter">
<![CDATA[
LogicalAggregate(group=[{0}])
- LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[anti])
+ LogicalJoin(condition=[=($0, $1)], joinType=[anti])
LogicalProject(ENAME=[$1])
LogicalFilter(condition=[=($7, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$1])
LogicalFilter(condition=[=($7, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMinusToAntiJoinRuleMultiInputs">
+ <Resource name="sql">
+ <![CDATA[select ename from emp where deptno = 10
+except
+select deptno from emp where ename in ('a', 'b')
+except
+select ename from empnullables
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalMinus(all=[false])
+ LogicalProject(ENAME=[$1])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[CAST($7):VARCHAR NOT NULL])
+ LogicalFilter(condition=[OR(=($1, 'a'), =($1, 'b'))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(ENAME=[CAST($0):VARCHAR])
+ LogicalAggregate(group=[{0}])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM(CAST($0):VARCHAR,
CAST($1):VARCHAR)], joinType=[anti])
+ LogicalJoin(condition=[=(CAST($0):VARCHAR, $1)], joinType=[anti])
+ LogicalProject(ENAME=[$1])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[CAST($7):VARCHAR NOT NULL])
+ LogicalFilter(condition=[OR(=($1, 'a'), =($1, 'b'))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/planner.iq
b/core/src/test/resources/sql/planner.iq
index 78633557c0..47231cae12 100644
--- a/core/src/test/resources/sql/planner.iq
+++ b/core/src/test/resources/sql/planner.iq
@@ -203,6 +203,64 @@ EnumerableIntersect(all=[false])
EnumerableValues(tuples=[[{ 1.0 }, { 4.0 }, { null }]])
!plan
+# [CALCITE-7008] Extend MinusToAntiJoinRule to support n-way inputs
+!set planner-rules "
+-EnumerableRules.ENUMERABLE_MINUS_RULE,
+-CoreRules.MINUS_TO_DISTINCT,
++CoreRules.MINUS_TO_ANTI_JOIN"
+select a from (values (1.0), (2.0), (3.0), (4.0), (5.0)) as t1 (a)
+except
+select a from (values (1), (2)) as t2 (a)
+except
+select a from (values (1.0), (4.0), (null)) as t3 (a);
++-----+
+| A |
++-----+
+| 3.0 |
+| 5.0 |
++-----+
+(2 rows)
+
+!ok
+
+EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)], A=[$t1])
+ EnumerableNestedLoopJoin(condition=[OR(AND(IS NULL(CAST($0):DECIMAL(11, 1)),
IS NULL(CAST($1):DECIMAL(11, 1))), =(CAST($0):DECIMAL(11, 1),
CAST($1):DECIMAL(11, 1)))], joinType=[anti])
+ EnumerableAggregate(group=[{0}])
+ EnumerableNestedLoopJoin(condition=[=(CAST($0):DECIMAL(11, 1) NOT NULL,
CAST($1):DECIMAL(11, 1) NOT NULL)], joinType=[anti])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1) NOT
NULL], A=[$t1])
+ EnumerableValues(tuples=[[{ 1.0 }, { 2.0 }, { 3.0 }, { 4.0 }, { 5.0
}]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1) NOT
NULL], A=[$t1])
+ EnumerableValues(tuples=[[{ 1 }, { 2 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)],
A=[$t1])
+ EnumerableValues(tuples=[[{ 1.0 }, { 4.0 }, { null }]])
+!plan
+!set planner-rules original
+
+# [CALCITE-7008] Extend MinusToAntiJoinRule to support n-way inputs
+select a from (values (1.0), (2.0), (3.0), (4.0), (5.0)) as t1 (a)
+except
+select a from (values (1), (2)) as t2 (a)
+except
+select a from (values (1.0), (4.0), (null)) as t3 (a);
++-----+
+| A |
++-----+
+| 3.0 |
+| 5.0 |
++-----+
+(2 rows)
+
+!ok
+
+EnumerableMinus(all=[false])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1) NOT
NULL], A=[$t1])
+ EnumerableValues(tuples=[[{ 1.0 }, { 2.0 }, { 3.0 }, { 4.0 }, { 5.0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1) NOT
NULL], A=[$t1])
+ EnumerableValues(tuples=[[{ 1 }, { 2 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):DECIMAL(11, 1)], A=[$t1])
+ EnumerableValues(tuples=[[{ 1.0 }, { 4.0 }, { null }]])
+!plan
+
# Test predicate push down with/without expand disjunction.
with t1 (id1, col11, col12) as (values (1, 11, 111), (2, 12, 122), (3, 13,
133), (4, 14, 144), (5, 15, 155)),
t2 (id2, col21, col22) as (values (1, 21, 211), (2, 22, 222), (3, 23, 233),
(4, 24, 244), (5, 25, 255)),
