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 bddcbaf233 [CALCITE-6930] Implementing JoinConditionOrExpansionRule
bddcbaf233 is described below
commit bddcbaf2339c488fea1ece2b39e84d99e81a6ccb
Author: Zhen Chen <[email protected]>
AuthorDate: Fri Apr 11 22:58:01 2025 +0800
[CALCITE-6930] Implementing JoinConditionOrExpansionRule
---
.../org/apache/calcite/rel/rules/CoreRules.java | 4 +
.../apache/calcite/rel/rules/JoinCommuteRule.java | 6 +
.../rel/rules/JoinConditionOrExpansionRule.java | 385 +++++++++++++++++++++
.../java/org/apache/calcite/test/JdbcTest.java | 175 ++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 87 +++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 174 ++++++++++
6 files changed, 831 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 012ae2ce39..c05f18bbaf 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
@@ -552,6 +552,10 @@ private CoreRules() {}
public static final FilterJoinRule.JoinConditionPushRule JOIN_CONDITION_PUSH
=
FilterJoinRule.JoinConditionPushRule.JoinConditionPushRuleConfig.DEFAULT.toRule();
+ /** Rule that transforms a join with OR conditions into a UNION ALL of
multiple joins. */
+ public static final JoinConditionOrExpansionRule
JOIN_CONDITION_OR_EXPANSION_RULE =
+ JoinConditionOrExpansionRule.Config.DEFAULT.toRule();
+
/** Rule to add a semi-join into a {@link Join}. */
public static final JoinAddRedundantSemiJoinRule
JOIN_ADD_REDUNDANT_SEMI_JOIN =
JoinAddRedundantSemiJoinRule.Config.DEFAULT.toRule();
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/JoinCommuteRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/JoinCommuteRule.java
index f95d4b122e..c037e2fa9d 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/JoinCommuteRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/JoinCommuteRule.java
@@ -137,6 +137,12 @@ public JoinCommuteRule(Class<? extends Join> clazz,
.build();
}
+ public static RexNode swapJoinCond(RexNode cond, Join join, RexBuilder
rexBuilder) {
+ final VariableReplacer variableReplacer =
+ new VariableReplacer(rexBuilder, join.getLeft().getRowType(),
join.getRight().getRowType());
+ return variableReplacer.apply(cond);
+ }
+
@Override public boolean matches(RelOptRuleCall call) {
Join join = call.rel(0);
// SEMI and ANTI join cannot be swapped.
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/JoinConditionOrExpansionRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/JoinConditionOrExpansionRule.java
new file mode 100644
index 0000000000..a5e2d670e5
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/rel/rules/JoinConditionOrExpansionRule.java
@@ -0,0 +1,385 @@
+/*
+ * 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.RelOptUtil;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexInputRef;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.tools.RelBuilder;
+
+import org.immutables.value.Value;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.stream.Collectors;
+import java.util.stream.Stream;
+
+/**
+ * Planner rule that matches a
+ * {@link org.apache.calcite.rel.core.Join}
+ * and expands OR clauses in join conditions.
+ *
+ * <p>This rule expands OR conditions in join clauses into
+ * multiple separate join conditions, allowing the optimizer
+ * to handle these conditions more efficiently.
+ *
+ * <p>The following is an example of inner join,
+ * and other examples for other kinds of joins
+ * can be found in the code below.
+ *
+ * <p>Project[*]
+ * └── Join[OR(t1.id=t2.id, t1.age=t2.age), inner]
+ * ├── TableScan[t1]
+ * └── TableScan[t2]
+ *
+ * <p>into
+ *
+ * <p>Project[*]
+ * └── UnionAll
+ * ├── Join[t1.id=t2.id, inner]
+ * │ ├── TableScan[t1]
+ * │ └── TableScan[t2]
+ * └── Join[t1.age=t2.age AND t1.id≠t2.id, inner]
+ * ├─── TableScan[t1]
+ * └─── TableScan[t2]
+ *
+ */
[email protected]
+public class JoinConditionOrExpansionRule
+ extends RelRule<JoinConditionOrExpansionRule.Config>
+ implements TransformationRule {
+
+ /** Creates an JoinConditionExpansionOrRule. */
+ protected JoinConditionOrExpansionRule(Config config) {
+ super(config);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public boolean matches(RelOptRuleCall call) {
+ Join join = call.rel(0);
+ List<RexNode> orConds = RelOptUtil.disjunctions(join.getCondition());
+ return orConds.size() > 1;
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ Join join = call.rel(0);
+ RelBuilder relBuilder = call.builder();
+
+ RelNode expanded;
+ switch (join.getJoinType()) {
+ case INNER:
+ expanded = expandInnerJoin(join, relBuilder);
+ break;
+ case ANTI:
+ expanded = expandAntiJoin(join, relBuilder);
+ break;
+ case LEFT:
+ expanded = expandLeftOrRightJoin(join, true, relBuilder);
+ break;
+ case RIGHT:
+ expanded = expandLeftOrRightJoin(join, false, relBuilder);
+ break;
+ case FULL:
+ expanded = expandFullJoin(join, relBuilder);
+ break;
+ default:
+ return;
+ }
+ call.transformTo(expanded);
+ }
+
+ private List<RexNode> splitCond(Join join) {
+ final RexBuilder builder = join.getCluster().getRexBuilder();
+ final List<RexNode> orConds = RelOptUtil.disjunctions(join.getCondition());
+ final int leftFieldCount = join.getLeft().getRowType().getFieldCount();
+
+ final List<RexNode> result = new ArrayList<>();
+ final List<RexNode> otherBuffer = new ArrayList<>();
+
+ for (RexNode cond : orConds) {
+ if (isValidCond(cond, leftFieldCount)) {
+ if (!otherBuffer.isEmpty()) {
+ result.add(RexUtil.composeDisjunction(builder, otherBuffer));
+ otherBuffer.clear();
+ }
+ result.add(cond);
+ } else {
+ otherBuffer.add(cond);
+ }
+ }
+
+ if (!otherBuffer.isEmpty()) {
+ result.add(RexUtil.composeDisjunction(builder, otherBuffer));
+ }
+
+ return result;
+ }
+
+ private boolean isValidCond(RexNode node, int leftFieldCount) {
+ if (!(node instanceof RexCall)) {
+ return false;
+ }
+
+ RexCall call = (RexCall) node;
+ SqlKind kind = call.getKind();
+ switch (kind) {
+ case EQUALS:
+ case IS_NOT_DISTINCT_FROM:
+ RexNode left = call.getOperands().get(0);
+ RexNode right = call.getOperands().get(1);
+
+ if (left instanceof RexInputRef && right instanceof RexInputRef) {
+ RexInputRef leftRef = (RexInputRef) left;
+ RexInputRef rightRef = (RexInputRef) right;
+ return (leftRef.getIndex() < leftFieldCount && rightRef.getIndex() >=
leftFieldCount)
+ || (leftRef.getIndex() >= leftFieldCount && rightRef.getIndex() <
leftFieldCount);
+ }
+ return false;
+ case AND:
+ return call.getOperands().stream()
+ .allMatch(op -> isValidCond(op, leftFieldCount));
+ default:
+ return false;
+ }
+ }
+
+ /**
+ * This method will make the following conversions.
+ *
+ * <p>Project[*]
+ * └── Join[OR(t1.id=t2.id, t1.age=t2.age), left]
+ * ├── TableScan[t1]
+ * └── TableScan[t2]
+ *
+ * <p>into
+ *
+ * <p>Project[*]
+ * └── UnionAll
+ * ├── Join[t1.id=t2.id, inner]
+ * │ ├── TableScan[t1]
+ * │ └── TableScan[t2]
+ * ├── Join[t1.age=t2.age AND t1.id≠t2.id, inner]
+ * │ ├── TableScan[t1]
+ * │ └── TableScan[t2]
+ * └── Project[t1-side cols + NULLs]
+ * └── Join[t1.id=t2.id, anti]
+ * ├── Join[t1.age=t2.age, anti]
+ * │ ├── TableScan[t1]
+ * │ └── TableScan[t2]
+ * └── TableScan[t2]
+ */
+ private RelNode expandLeftOrRightJoin(Join join, boolean isLeftJoin,
+ RelBuilder relBuilder) {
+ List<RexNode> orConds = splitCond(join);
+ List<RelNode> joins = expandLeftOrRightJoinToRelNodes(join, orConds,
isLeftJoin, relBuilder);
+ return relBuilder.pushAll(joins)
+ .union(true, joins.size())
+ .build();
+ }
+
+ private List<RelNode> expandLeftOrRightJoinToRelNodes(Join join,
List<RexNode> orConds,
+ boolean isLeftJoin, RelBuilder relBuilder) {
+ List<RelNode> joins = new ArrayList<>();
+ joins.addAll(expandInnerJoinToRelNodes(join, orConds, relBuilder));
+ joins.add(expandAntiJoinToRelNode(join, orConds, isLeftJoin, true,
relBuilder));
+ return joins;
+ }
+
+ /**
+ * This method will make the following conversions.
+ *
+ * <p>Project[*]
+ * └── Join[OR(t1.id=t2.id, t1.age=t2.age), full]
+ * ├── TableScan[t1]
+ * └── TableScan[t2]
+ *
+ * <p>into
+ *
+ * <p>Project[*]
+ * └── UnionAll
+ * ├── Join[t1.id=t2.id, inner]
+ * │ ├── TableScan[t1]
+ * │ └── TableScan[t2]
+ * ├── Join[t1.age=t2.age AND t1.id≠t2.id, inner]
+ * │ ├── TableScan[t1]
+ * │ └── TableScan[t2]
+ * ├── Project[t1-side cols + NULLs]
+ * │ └── Join[t1.id=t2.id, anti]
+ * │ ├── Join[t1.age=t2.age, anti]
+ * │ │ ├── TableScan[t1]
+ * │ │ └── TableScan[t2]
+ * │ └── TableScan[t2]
+ * └── Project[NULLs + t2-side cols]
+ * └── Join[t2.id=t1.id, anti]
+ * ├── Join[t2.age=t1.age, anti]
+ * │ ├── TableScan[t2]
+ * │ └── TableScan[t1]
+ * └── TableScan[t1]
+ */
+ private RelNode expandFullJoin(Join join, RelBuilder relBuilder) {
+ List<RexNode> orConds = splitCond(join);
+ List<RelNode> joins = new ArrayList<>();
+ joins.addAll(expandInnerJoinToRelNodes(join, orConds, relBuilder));
+ joins.add(expandAntiJoinToRelNode(join, orConds, false, true, relBuilder));
+ joins.add(expandAntiJoinToRelNode(join, orConds, true, true, relBuilder));
+
+ relBuilder.pushAll(joins)
+ .union(true, joins.size());
+
+ final List<RexNode> projects = join.getRowType().getFieldList().stream()
+ .map(field -> {
+ RexNode rexNode = relBuilder.field(field.getIndex());
+ return field.getType().equals(rexNode.getType())
+ ? rexNode
+ : relBuilder.getRexBuilder().makeCast(field.getType(), rexNode,
true, false);
+ }).collect(Collectors.toList());
+
+ return relBuilder.project(projects)
+ .build();
+ }
+
+ /**
+ * This method will make the following conversions.
+ *
+ * <p>Project[*]
+ * └── Join[OR(t1.id=t2.id, t1.age=t2.age), inner]
+ * ├── TableScan[t1]
+ * └── TableScan[t2]
+ *
+ * <p>into
+ *
+ * <p>Project[*]
+ * └── UnionAll
+ * ├── Join[t1.id=t2.id, inner]
+ * │ ├── TableScan[t1]
+ * │ └── TableScan[t2]
+ * └── Join[t1.age=t2.age AND t1.id≠t2.id, inner]
+ * ├─── TableScan[t1]
+ * └─── TableScan[t2]
+ */
+ private RelNode expandInnerJoin(Join join, RelBuilder relBuilder) {
+ List<RexNode> orConds = splitCond(join);
+ List<RelNode> joins = expandInnerJoinToRelNodes(join, orConds, relBuilder);
+ return relBuilder.pushAll(joins)
+ .union(true, joins.size())
+ .build();
+ }
+
+ private List<RelNode> expandInnerJoinToRelNodes(Join join, List<RexNode>
orConds,
+ RelBuilder relBuilder) {
+ List<RelNode> joins = new ArrayList<>();
+ for (int i = 0; i < orConds.size(); i++) {
+ RexNode orCond = orConds.get(i);
+ for (int j = 0; j < i; j++) {
+ orCond = relBuilder.and(orCond, relBuilder.not(orConds.get(j)));
+ }
+
+ relBuilder.push(join.getLeft())
+ .push(join.getRight())
+ .join(JoinRelType.INNER, orCond);
+
+ joins.add(relBuilder.build());
+ }
+ return joins;
+ }
+
+ /**
+ * This method will make the following conversions.
+ *
+ * <p>Project[*]
+ * └── Join[OR(id=id0, age=age0), anti]
+ * ├── TableScan[tbl]
+ * └── TableScan[tbl]
+ *
+ * <p>into
+ *
+ * <p>HashJoin[id=id0, anti]
+ * ├── HashJoin[age=age0, anti]
+ * │ ├── TableScan[tbl]
+ * │ └── TableScan[tbl]
+ * └── TableScan[tbl]
+ */
+ private RelNode expandAntiJoin(Join join, RelBuilder relBuilder) {
+ List<RexNode> orConds = splitCond(join);
+ return expandAntiJoinToRelNode(join, orConds, true, false, relBuilder);
+ }
+
+ private RelNode expandAntiJoinToRelNode(Join join, List<RexNode> orConds,
+ boolean isLeftAnti, boolean isAppendNulls, RelBuilder relBuilder) {
+ RelNode left = isLeftAnti ? join.getLeft() : join.getRight();
+ RelNode right = isLeftAnti ? join.getRight() : join.getLeft();
+
+ RelNode top = left;
+ for (int i = 0; i < orConds.size(); i++) {
+ RexNode orCond = orConds.get(i);
+ relBuilder.push(top)
+ .push(right)
+ .join(JoinRelType.ANTI,
+ isLeftAnti
+ ? orCond
+ : JoinCommuteRule.swapJoinCond(orCond, join,
relBuilder.getRexBuilder()));
+ top = relBuilder.build();
+ }
+
+ if (!isAppendNulls) {
+ return top;
+ }
+
+ relBuilder.push(top);
+ List<RexNode> fields = new ArrayList<>(relBuilder.fields());
+ List<RexNode> nulls = new ArrayList<>();
+ for (int i = 0; i < right.getRowType().getFieldCount(); i++) {
+ nulls.add(
+ relBuilder.getRexBuilder().makeNullLiteral(
+ right.getRowType().getFieldList().get(i).getType()));
+ }
+
+ List<RexNode> projects = isLeftAnti
+ ? Stream.concat(fields.stream(),
nulls.stream()).collect(Collectors.toList())
+ : Stream.concat(nulls.stream(),
fields.stream()).collect(Collectors.toList());
+
+ return relBuilder.project(projects)
+ .build();
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = ImmutableJoinConditionOrExpansionRule.Config.of()
+ .withOperandFor(Join.class);
+
+ @Override default JoinConditionOrExpansionRule toRule() {
+ return new JoinConditionOrExpansionRule(this);
+ }
+
+ /** Defines an operand tree for the given classes. */
+ default Config withOperandFor(Class<? extends Join> joinClass) {
+ return withOperandSupplier(b -> b.operand(joinClass).anyInputs())
+ .as(Config.class);
+ }
+ }
+}
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 f9d6775a95..95b1266027 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -1975,6 +1975,181 @@ private void checkResultSetMetaData(Connection
connection, String sql)
"c0=Drink; c1=Dairy; c2=USA; c3=WA; c4=Bellingham");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRule() {
+ final String sql = ""
+ + "SELECT \"t1\".\"deptno\", \"t1\".\"empid\", \"t2\".\"deptno\",
\"t2\".\"empid\"\n"
+ + "FROM \"hr\".\"emps\" AS \"t1\"\n"
+ + "INNER JOIN (\n"
+ + " SELECT (\"deptno\" + 10) AS \"deptno\", (\"empid\" + 100) AS
\"empid\" FROM \"hr\".\"emps\"\n"
+ + ") AS \"t2\"\n"
+ + "ON (\"t1\".\"deptno\" = \"t2\".\"deptno\") OR (\"t1\".\"empid\" =
\"t2\".\"empid\")";
+
+ String[] returns = new String[] {
+ "deptno=20; empid=200; deptno=20; empid=200",
+ "deptno=20; empid=200; deptno=20; empid=210",
+ "deptno=20; empid=200; deptno=20; empid=250"};
+
+ CalciteAssert.hr()
+ .query(sql)
+ .returnsUnordered(returns);
+
+ CalciteAssert.hr()
+ .query(sql)
+ .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
+ planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
+ })
+ .returnsUnordered(returns);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleMultiOr() {
+ final String sql = ""
+ + "SELECT \"t1\".\"deptno\", \"t1\".\"empid\", \"t2\".\"deptno\",
\"t2\".\"empid\"\n"
+ + "FROM \"hr\".\"emps\" AS \"t1\"\n"
+ + "INNER JOIN (\n"
+ + " SELECT (\"deptno\" + 10) AS \"deptno\", (\"empid\" + 100) AS
\"empid\" FROM \"hr\".\"emps\"\n"
+ + ") AS \"t2\"\n"
+ + "ON (\"t1\".\"deptno\" = \"t2\".\"deptno\")"
+ + "OR (\"t1\".\"salary\" > 8000)"
+ + "OR (\"t1\".\"empid\" = \"t2\".\"empid\")";
+
+ String[] returns = new String[] {
+ "deptno=10; empid=100; deptno=20; empid=200",
+ "deptno=10; empid=100; deptno=20; empid=210",
+ "deptno=10; empid=100; deptno=20; empid=250",
+ "deptno=10; empid=100; deptno=30; empid=300",
+ "deptno=10; empid=110; deptno=20; empid=200",
+ "deptno=10; empid=110; deptno=20; empid=210",
+ "deptno=10; empid=110; deptno=20; empid=250",
+ "deptno=10; empid=110; deptno=30; empid=300",
+ "deptno=20; empid=200; deptno=20; empid=200",
+ "deptno=20; empid=200; deptno=20; empid=210",
+ "deptno=20; empid=200; deptno=20; empid=250"};
+
+ CalciteAssert.hr()
+ .query(sql)
+ .returnsUnordered(returns);
+
+ CalciteAssert.hr()
+ .query(sql)
+ .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
+ planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
+ })
+ .returnsUnordered(returns);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleLeft() {
+ final String sql = ""
+ + "SELECT \"t1\".\"deptno\", \"t1\".\"empid\", \"t2\".\"deptno\",
\"t2\".\"empid\"\n"
+ + "FROM \"hr\".\"emps\" AS \"t1\"\n"
+ + "LEFT JOIN (\n"
+ + " SELECT (\"deptno\" + 10) AS \"deptno\", (\"empid\" + 100) AS
\"empid\" FROM \"hr\".\"emps\"\n"
+ + ") AS \"t2\"\n"
+ + "ON (\"t1\".\"deptno\" = \"t2\".\"deptno\") OR (\"t1\".\"empid\" =
\"t2\".\"empid\")";
+
+ String[] returns = new String[] {
+ "deptno=10; empid=100; deptno=null; empid=null",
+ "deptno=10; empid=110; deptno=null; empid=null",
+ "deptno=10; empid=150; deptno=null; empid=null",
+ "deptno=20; empid=200; deptno=20; empid=200",
+ "deptno=20; empid=200; deptno=20; empid=210",
+ "deptno=20; empid=200; deptno=20; empid=250"};
+
+ CalciteAssert.hr()
+ .query(sql)
+ .returnsUnordered(returns);
+
+ CalciteAssert.hr()
+ .query(sql)
+ .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
+ planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
+ })
+ .returnsUnordered(returns);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleLeftUnion() {
+ final String sql = ""
+ + "SELECT \"t1\".\"deptno\", \"t1\".\"empid\", \"t2\".\"deptno\",
\"t2\".\"empid\"\n"
+ + "FROM \"hr\".\"emps\" AS \"t1\"\n"
+ + "LEFT JOIN (\n"
+ + " SELECT (\"deptno\" + 10) AS \"deptno\", (\"empid\" + 100) AS
\"empid\" FROM \"hr\".\"emps\"\n"
+ + " UNION ALL\n"
+ + " SELECT (\"deptno\" + 10) AS \"deptno\", (\"empid\" + 100) AS
\"empid\" FROM \"hr\".\"emps\"\n"
+ + ") AS \"t2\"\n"
+ + "ON (\"t1\".\"deptno\" = \"t2\".\"deptno\") OR (\"t1\".\"empid\" =
\"t2\".\"empid\")";
+
+ String[] returns = new String[] {
+ "deptno=10; empid=100; deptno=null; empid=null",
+ "deptno=10; empid=110; deptno=null; empid=null",
+ "deptno=10; empid=150; deptno=null; empid=null",
+ "deptno=20; empid=200; deptno=20; empid=200",
+ "deptno=20; empid=200; deptno=20; empid=200",
+ "deptno=20; empid=200; deptno=20; empid=210",
+ "deptno=20; empid=200; deptno=20; empid=210",
+ "deptno=20; empid=200; deptno=20; empid=250",
+ "deptno=20; empid=200; deptno=20; empid=250"};
+
+ CalciteAssert.hr()
+ .query(sql)
+ .returnsUnordered(returns);
+
+ CalciteAssert.hr()
+ .query(sql)
+ .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
+ planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
+ })
+ .returnsUnordered(returns);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleLeftWithNull() {
+ final String sql = ""
+ + "SELECT \"t1\".\"deptno\", \"t1\".\"commission\", \"t2\".\"deptno\",
\"t2\".\"commission\"\n"
+ + "FROM \"hr\".\"emps\" AS \"t1\"\n"
+ + "LEFT JOIN (\n"
+ + " SELECT (\"deptno\" + 10) AS \"deptno\", \"commission\" FROM
\"hr\".\"emps\"\n"
+ + ") AS \"t2\"\n"
+ + "ON (\"t1\".\"deptno\" = \"t2\".\"deptno\") OR
(\"t1\".\"commission\" = \"t2\".\"commission\")";
+
+ String[] returns = new String[] {
+ "deptno=10; commission=1000; deptno=20; commission=1000",
+ "deptno=10; commission=250; deptno=20; commission=250",
+ "deptno=10; commission=null; deptno=null; commission=null",
+ "deptno=20; commission=500; deptno=20; commission=1000",
+ "deptno=20; commission=500; deptno=20; commission=250",
+ "deptno=20; commission=500; deptno=20; commission=null",
+ "deptno=20; commission=500; deptno=30; commission=500"};
+
+ CalciteAssert.hr()
+ .query(sql)
+ .returnsUnordered(returns);
+
+ CalciteAssert.hr()
+ .query(sql)
+ .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
+ planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
+ })
+ .returnsUnordered(returns);
+ }
+
/** Four-way join. Used to take 80 seconds. */
@Disabled
@Test void testJoinFiveWay() {
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 7d6233a6f6..47db4459fd 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -8973,6 +8973,93 @@ private RelOptFixture spatial(String sql) {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRule() {
+ String sql = "select *\n"
+ + "from EMP as p1\n"
+ + "inner join EMP as p2 on p1.empno = p2.empno or p1.mgr = p2.mgr";
+ sql(sql).withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleMultiOr() {
+ String sql = "select *\n"
+ + "from EMP as p1\n"
+ + "inner join EMP as p2 on p1.mgr < p2.mgr or\n"
+ + "p1.empno = p2.empno or p1.sal < 0 or ln(p1.sal) < 10";
+ sql(sql).withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleRight() {
+ String sql = "select *\n"
+ + "from EMP as p1\n"
+ + "right join DEPT as p2 on p1.empno = p2.deptno or p1.ename <
p2.name";
+ sql(sql).withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleLeft() {
+ String sql = "select *\n"
+ + "from EMP as p1\n"
+ + "left join EMP as p2 on p1.empno = p2.empno or p1.sal = p2.sal";
+ sql(sql).withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleAnti() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("DEPT")
+ .scan("EMP")
+ .antiJoin(
+ b.or(
+ b.equals(
+ b.field(2, 0, "DEPTNO"),
+ b.field(2, 1, "DEPTNO")),
+ b.equals(
+ b.field(2, 0, "DNAME"),
+ b.field(2, 1, "JOB"))))
+ .build();
+ relFn(relFn)
+ .withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6930";>[CALCITE-6930]
+ * Implementing JoinConditionOrExpansionRule</a>. */
+ @Test void testJoinConditionOrExpansionRuleFull() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("DEPT")
+ .scan("EMP")
+ .join(JoinRelType.FULL,
+ b.or(
+ b.equals(
+ b.field(2, 0, "DEPTNO"),
+ b.field(2, 1, "DEPTNO")),
+ b.equals(
+ b.field(2, 0, "DNAME"),
+ b.field(2, 1, "JOB"))))
+ .build();
+ relFn(relFn)
+ .withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ .check();
+ }
+
@Test void testExchangeRemoveConstantKeysRule() {
final Function<RelBuilder, RelNode> relFn = b -> b
.scan("EMP")
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 27cbd80a40..4ff87b444e 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6627,6 +6627,180 @@ 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>
+ <TestCase name="testJoinConditionOrExpansionRule">
+ <Resource name="sql">
+ <![CDATA[select *
+from EMP as p1
+inner join EMP as p2 on p1.empno = p2.empno or p1.mgr = p2.mgr]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalJoin(condition=[OR(=($0, $9), =($3, $12))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalUnion(all=[true])
+ LogicalJoin(condition=[=($0, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[AND(=($3, $12), <>($0, $9))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinConditionOrExpansionRuleAnti">
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalJoin(condition=[OR(=($0, $10), =($1, $5))], joinType=[anti])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalJoin(condition=[=($1, $5)], joinType=[anti])
+ LogicalJoin(condition=[=($0, $10)], joinType=[anti])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinConditionOrExpansionRuleFull">
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalJoin(condition=[OR(=($0, $10), =($1, $5))], joinType=[full])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalJoin(condition=[=($0, $10)], joinType=[inner])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalJoin(condition=[AND(=($1, $5), <>($0, $10))], joinType=[inner])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalProject($f0=[null:TINYINT], $f1=[null:VARCHAR(14)],
$f2=[null:VARCHAR(13)], EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])
+ LogicalJoin(condition=[=($9, $2)], joinType=[anti])
+ LogicalJoin(condition=[=($8, $7)], joinType=[anti])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], $f3=[null:SMALLINT],
$f4=[null:VARCHAR(10)], $f5=[null:VARCHAR(9)], $f6=[null:SMALLINT],
$f7=[null:DATE], $f8=[null:DECIMAL(7, 2)], $f9=[null:DECIMAL(7, 2)],
$f10=[null:TINYINT])
+ LogicalJoin(condition=[=($1, $5)], joinType=[anti])
+ LogicalJoin(condition=[=($0, $10)], joinType=[anti])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinConditionOrExpansionRuleLeft">
+ <Resource name="sql">
+ <![CDATA[select *
+from EMP as p1
+left join EMP as p2 on p1.empno = p2.empno or p1.sal = p2.sal]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalJoin(condition=[OR(=($0, $9), =($5, $14))], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalUnion(all=[true])
+ LogicalJoin(condition=[=($0, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[AND(=($5, $14), <>($0, $9))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[null:INTEGER],
$f10=[null:VARCHAR(20)], $f11=[null:VARCHAR(10)], $f12=[null:INTEGER],
$f13=[null:TIMESTAMP(0)], $f14=[null:INTEGER], $f15=[null:INTEGER],
$f16=[null:INTEGER], $f17=[null:BOOLEAN])
+ LogicalJoin(condition=[=($5, $14)], joinType=[anti])
+ LogicalJoin(condition=[=($0, $9)], joinType=[anti])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinConditionOrExpansionRuleMultiOr">
+ <Resource name="sql">
+ <![CDATA[select *
+from EMP as p1
+inner join EMP as p2 on p1.mgr < p2.mgr or
+p1.empno = p2.empno or p1.sal < 0 or ln(p1.sal) < 10]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalJoin(condition=[OR(<($3, $12), =($0, $9), <($5, 0), <(LN($5),
10.0E0))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalUnion(all=[true])
+ LogicalJoin(condition=[<($3, $12)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[AND(=($0, $9), >=($3, $12))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[AND(OR(<($5, 0), <(LN($5), 10.0E0)), >=($3, $12),
<>($0, $9))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinConditionOrExpansionRuleRight">
+ <Resource name="sql">
+ <![CDATA[select *
+from EMP as p1
+right join DEPT as p2 on p1.empno = p2.deptno or p1.ename < p2.name]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10])
+ LogicalJoin(condition=[OR(=($0, $9), <($1, CAST($10):VARCHAR(20) NOT
NULL))], joinType=[right])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10])
+ LogicalUnion(all=[true])
+ LogicalJoin(condition=[=($0, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalJoin(condition=[AND(<($1, CAST($10):VARCHAR(20) NOT NULL), <>($0,
$9))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject($f0=[null:INTEGER], $f1=[null:VARCHAR(20)],
$f2=[null:VARCHAR(10)], $f3=[null:INTEGER], $f4=[null:TIMESTAMP(0)],
$f5=[null:INTEGER], $f6=[null:INTEGER], $f7=[null:INTEGER], $f8=[null:BOOLEAN],
DEPTNO=[$0], NAME=[$1])
+ LogicalJoin(condition=[<($3, CAST($1):VARCHAR(20) NOT NULL)],
joinType=[anti])
+ LogicalJoin(condition=[=($2, $0)], joinType=[anti])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
