This is an automated email from the ASF dual-hosted git repository.
zhenchen 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 b76fe48726 [CALCITE-7077] Implement a rule to rewrite FULL JOIN as
LEFT JOIN and RIGHT JOIN
b76fe48726 is described below
commit b76fe4872682f014bf024600cdd6217c49ce0e94
Author: Zhen Chen <[email protected]>
AuthorDate: Wed Jul 2 10:54:15 2025 +0800
[CALCITE-7077] Implement a rule to rewrite FULL JOIN as LEFT JOIN and RIGHT
JOIN
---
.../org/apache/calcite/rel/rules/CoreRules.java | 4 +
.../rel/rules/FullToLeftAndRightJoinRule.java | 128 +++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 41 +++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 24 ++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 43 +++++++
5 files changed, 240 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 af1398aa0b..4b0d5127e9 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
@@ -953,4 +953,8 @@ private CoreRules() {}
@RuleConfig(value = "JOIN")
public static final ExpandDisjunctionForJoinInputsRule
EXPAND_JOIN_DISJUNCTION_LOCAL =
ExpandDisjunctionForJoinInputsRule.Config.JOIN.toRule();
+
+ /** Rule that convert FULL JOIN to LEFT JOIN and RIGHT JOIN. */
+ public static final FullToLeftAndRightJoinRule FULL_TO_LEFT_AND_RIGHT_JOIN =
+ FullToLeftAndRightJoinRule.Config.DEFAULT.toRule();
}
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/FullToLeftAndRightJoinRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/FullToLeftAndRightJoinRule.java
new file mode 100644
index 0000000000..74069ddc08
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/rel/rules/FullToLeftAndRightJoinRule.java
@@ -0,0 +1,128 @@
+/*
+ * 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.rel.core.JoinRelType;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexInputRef;
+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.tools.RelBuilder;
+
+import com.google.common.collect.ImmutableList;
+
+import org.immutables.value.Value;
+
+/**
+ * Planner rule that matches a {@link Join}
+ * that join type is FULL, and convert it to
+ * a LEFT JOIN and RIGHT JOIN combination
+ * with a UNION ALL above them.
+ *
+ * <p>The SQL example is as follows:
+ *
+ * <pre>{@code
+ * SELECT *
+ * FROM Employees e
+ * FULL JOIN Departments d ON e.id = d.id
+ * }</pre>
+ *
+ * <p>rewritten into
+ *
+ * <pre>{@code
+ * SELECT *
+ * FROM Employees e
+ * LEFT JOIN Departments d ON e.id = d.id
+ * UNION ALL
+ * SELECT *
+ * FROM Employees e
+ * RIGHT JOIN Departments d ON e.id = d.id
+ * WHERE (e.id = d.id) IS NOT TRUE;
+ * }</pre>
+ */
[email protected]
+public class FullToLeftAndRightJoinRule
+ extends RelRule<FullToLeftAndRightJoinRule.Config>
+ implements TransformationRule {
+
+ /** Creates an FullToLeftAndRightJoinRule. */
+ protected FullToLeftAndRightJoinRule(Config config) {
+ super(config);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Join join = call.rel(0);
+ RelBuilder relBuilder = call.builder();
+
+ if (!RexUtil.isDeterministic(join.getCondition())) {
+ return;
+ }
+
+ RexBuilder rexBuilder = relBuilder.getRexBuilder();
+ RexNode newCondition =
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_TRUE,
join.getCondition());
+
+ final RexShuttle shuttle = new RexShuttle() {
+ @Override public RexNode visitInputRef(RexInputRef inputRef) {
+ return RexInputRef.of(inputRef.getIndex(), join.getRowType());
+ }
+ };
+ newCondition = shuttle.apply(newCondition);
+
+ RelNode newLeft = relBuilder.push(join.getLeft())
+ .push(join.getRight())
+ .join(JoinRelType.LEFT, join.getCondition())
+ .build();
+ RelNode newRight = relBuilder.push(join.getLeft())
+ .push(join.getRight())
+ .join(JoinRelType.RIGHT, join.getCondition())
+ .filter(newCondition)
+ .build();
+
+ relBuilder.pushAll(ImmutableList.of(newLeft, newRight))
+ .union(true);
+
+ call.transformTo(relBuilder.build());
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = ImmutableFullToLeftAndRightJoinRule.Config.of()
+ .withOperandFor(Join.class);
+
+ @Override default FullToLeftAndRightJoinRule toRule() {
+ return new FullToLeftAndRightJoinRule(this);
+ }
+
+ /** Defines an operand tree for the given classes. */
+ default Config withOperandFor(Class<? extends Join> joinClass) {
+ return withOperandSupplier(b -> b.operand(joinClass)
+ .predicate(join -> join.getJoinType() == JoinRelType.FULL)
+ .anyInputs())
+ .as(Config.class);
+ }
+ }
+}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 5539e4436c..eef59a3109 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -37,6 +37,7 @@
import org.apache.calcite.rel.rules.AggregateProjectMergeRule;
import org.apache.calcite.rel.rules.CoreRules;
import org.apache.calcite.rel.rules.FilterJoinRule;
+import org.apache.calcite.rel.rules.FullToLeftAndRightJoinRule;
import org.apache.calcite.rel.rules.ProjectOverSumToSum0Rule;
import org.apache.calcite.rel.rules.ProjectToWindowRule;
import org.apache.calcite.rel.rules.PruneEmptyRules;
@@ -10271,6 +10272,46 @@ private void checkLiteral2(String expression, String
expected) {
sql(sql).ok(expected);
}
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7077";>[CALCITE-7077]
+ * Implement a rule to rewrite FULL JOIN as LEFT JOIN and RIGHT JOIN</a>. */
+ @Test void testFullJoinToLeftAndRightJoin() {
+ final String query = "select * from emp e1\n"
+ + "full join emp e2\n"
+ + "on e1.sal = e2.sal and e1.mgr is null";
+ final String expected = "SELECT *\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "LEFT JOIN \"SCOTT\".\"EMP\" AS \"EMP0\""
+ + " ON \"EMP\".\"SAL\" = \"EMP0\".\"SAL\" AND \"EMP\".\"MGR\" IS
NULL\n"
+ + "UNION ALL\n"
+ + "SELECT \"EMP1\".\"EMPNO\" AS \"EMPNO\", \"EMP1\".\"ENAME\" AS
\"ENAME\","
+ + " \"EMP1\".\"JOB\" AS \"JOB\", \"EMP1\".\"MGR\" AS \"MGR\","
+ + " \"EMP1\".\"HIREDATE\" AS \"HIREDATE\", \"EMP1\".\"SAL\" AS
\"SAL\","
+ + " \"EMP1\".\"COMM\" AS \"COMM\", \"EMP1\".\"DEPTNO\" AS \"DEPTNO\","
+ + " \"EMP2\".\"EMPNO\" AS \"EMPNO0\", \"EMP2\".\"ENAME\" AS
\"ENAME0\","
+ + " \"EMP2\".\"JOB\" AS \"JOB0\", \"EMP2\".\"MGR\" AS \"MGR0\","
+ + " \"EMP2\".\"HIREDATE\" AS \"HIREDATE0\", \"EMP2\".\"SAL\" AS
\"SAL0\","
+ + " \"EMP2\".\"COMM\" AS \"COMM0\", \"EMP2\".\"DEPTNO\" AS
\"DEPTNO0\"\n"
+ + "FROM \"SCOTT\".\"EMP\" AS \"EMP1\"\n"
+ + "RIGHT JOIN \"SCOTT\".\"EMP\" AS \"EMP2\""
+ + " ON \"EMP1\".\"SAL\" = \"EMP2\".\"SAL\" AND \"EMP1\".\"MGR\" IS
NULL\n"
+ + "WHERE (\"EMP1\".\"SAL\" = \"EMP2\".\"SAL\""
+ + " AND \"EMP1\".\"MGR\" IS NULL) IS NOT TRUE) AS \"t0\"";
+
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(FullToLeftAndRightJoinRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ RuleSet rules =
+ RuleSets.ofList(CoreRules.FULL_TO_LEFT_AND_RIGHT_JOIN);
+
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withCalcite()
+ .optimize(rules, hepPlanner)
+ .ok(expected);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final CalciteAssert.SchemaSpec schemaSpec;
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 d7fb23756a..f08d85d4fa 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -11037,4 +11037,28 @@ private void
checkLoptOptimizeJoinRule(LoptOptimizeJoinRule rule) {
})
.check();
}
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7077";>[CALCITE-7077]
+ * Implement a rule to rewrite FULL JOIN as LEFT JOIN and RIGHT JOIN</a>. */
+ @Test void testFullJoinToLeftAndRightJoin() {
+ final String query = "select * from emp e1\n"
+ + "full join emp e2\n"
+ + "on e1.sal = e2.sal and e1.mgr is null";
+ sql(query)
+ .withRule(CoreRules.FULL_TO_LEFT_AND_RIGHT_JOIN)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7077";>[CALCITE-7077]
+ * Implement a rule to rewrite FULL JOIN as LEFT JOIN and RIGHT JOIN</a>. */
+ @Test void testFullJoinToLeftAndRightJoinNonDeterministic() {
+ final String query = "select * from emp e1\n"
+ + "full join emp e2\n"
+ + "on rand() > 0.5";
+ sql(query)
+ .withRule(CoreRules.FULL_TO_LEFT_AND_RIGHT_JOIN)
+ .checkUnchanged();
+ }
}
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 cf7fba849d..69cc261a05 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -5745,6 +5745,49 @@ LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(DEPTNO=[$0], $f0=[true])
LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testFullJoinToLeftAndRightJoin">
+ <Resource name="sql">
+ <![CDATA[select * from emp e1
+full join emp e2
+on e1.sal = e2.sal and e1.mgr is null]]>
+ </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=[AND(=($5, $14), IS NULL($3))], joinType=[full])
+ 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=[AND(=($5, $14), IS NULL($3))], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[OR(<>($5, $14), IS NOT NULL($3))])
+ LogicalJoin(condition=[AND(=($5, $14), IS NULL($3))], joinType=[right])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testFullJoinToLeftAndRightJoinNonDeterministic">
+ <Resource name="sql">
+ <![CDATA[select * from emp e1
+full join emp e2
+on rand() > 0.5]]>
+ </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=[>(RAND(), 0.5E0)], joinType=[full])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
