You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by hy...@apache.org on 2019/05/15 17:33:07 UTC
[calcite] branch master updated: [CALCITE-3028] Support FULL OUTER
JOIN with AggregateJoinTransposeRule (Vineet Garg)
This is an automated email from the ASF dual-hosted git repository.
hyuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new a5b382a [CALCITE-3028] Support FULL OUTER JOIN with AggregateJoinTransposeRule (Vineet Garg)
a5b382a is described below
commit a5b382abb6585f01995a350ca1e3937e2f3b91a1
Author: Vineet Garg <vg...@apache.org>
AuthorDate: Mon May 13 21:45:06 2019 -0700
[CALCITE-3028] Support FULL OUTER JOIN with AggregateJoinTransposeRule (Vineet Garg)
---
.../rel/rules/AggregateJoinTransposeRule.java | 5 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 4 -
.../org/apache/calcite/test/RelOptRulesTest.xml | 123 +++++++++++++++++++++
3 files changed, 125 insertions(+), 7 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinTransposeRule.java
index d2651ae..dda4e10 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinTransposeRule.java
@@ -150,8 +150,7 @@ public class AggregateJoinTransposeRule extends RelOptRule {
// FULL OUTER JOIN is not supported since it could produce wrong result
// due to bug (CALCITE-3012)
private boolean isJoinSupported(final Join join, final Aggregate aggregate) {
- return join.getJoinType() != JoinRelType.FULL
- && (join.getJoinType() == JoinRelType.INNER || aggregate.getAggCallList().isEmpty());
+ return join.getJoinType() == JoinRelType.INNER || aggregate.getAggCallList().isEmpty();
}
public void onMatch(RelOptRuleCall call) {
@@ -348,7 +347,7 @@ public class AggregateJoinTransposeRule extends RelOptRule {
relBuilder.project(projects);
boolean aggConvertedToProjects = false;
- if (allColumnsInAggregate) {
+ if (allColumnsInAggregate && join.getJoinType() != JoinRelType.FULL) {
// let's see if we can convert aggregate into projects
// This shouldn't be done for FULL OUTER JOIN, aggregate on top is always required
List<RexNode> projects2 = new ArrayList<>();
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 055af52..aa307f0 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3769,7 +3769,6 @@ public class RelOptRulesTest extends RelOptTestBase {
/** Test case for
* full outer join, group by on key same as join key, group by on one side */
- @Ignore("[CALCITE-3012]")
@Test public void testPushAggregateThroughtOuterJoin13() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
@@ -3786,7 +3785,6 @@ public class RelOptRulesTest extends RelOptTestBase {
/** Test case for
* full outer join, group by on key same as join key, group by on both side */
- @Ignore("[CALCITE-3012]")
@Test public void testPushAggregateThroughtOuterJoin14() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
@@ -3803,7 +3801,6 @@ public class RelOptRulesTest extends RelOptTestBase {
/** Test case for
* full outer join, group by on both side on non-join keys */
- @Ignore("[CALCITE-3012]")
@Test public void testPushAggregateThroughtOuterJoin15() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
@@ -3820,7 +3817,6 @@ public class RelOptRulesTest extends RelOptTestBase {
/** Test case for
* full outer join, group by key is susbset of join keys */
- @Ignore("[CALCITE-3012]")
@Test public void testPushAggregateThroughtOuterJoin16() {
final HepProgram preProgram = new HepProgramBuilder()
.addRuleInstance(AggregateProjectMergeRule.INSTANCE)
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 ad9d200..fcd662b 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -5409,6 +5409,129 @@ LogicalAggregate(group=[{0}])
]]>
</Resource>
</TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin13">
+ <Resource name="sql">
+ <![CDATA[select e.job
+from (select * from sales.emp where empno = 10) as e
+full outer join sales.dept as d on e.job = d.name
+group by e.job]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{2}])
+ LogicalJoin(condition=[=($2, $10)], joinType=[full])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{0}])
+ LogicalJoin(condition=[=($0, $1)], joinType=[full])
+ LogicalAggregate(group=[{2}])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{1}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin14">
+ <Resource name="sql">
+ <![CDATA[select e.mgr, d.mgr
+from sales.emp as e
+full outer join sales.emp as d on e.mgr = d.mgr
+group by d.mgr, e.mgr]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(MGR=[$1], MGR0=[$0])
+ LogicalProject(MGR0=[$1], MGR=[$0])
+ LogicalAggregate(group=[{3, 12}])
+ LogicalJoin(condition=[=($3, $12)], joinType=[full])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(MGR=[$1], MGR0=[$0])
+ LogicalProject(MGR0=[$1], MGR=[$0])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalJoin(condition=[=($0, $1)], joinType=[full])
+ LogicalAggregate(group=[{3}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{3}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin15">
+ <Resource name="sql">
+ <![CDATA[select e.ename, d.mgr
+from (select * from sales.emp where empno = 10) as e
+full outer join sales.emp as d on e.job = d.job
+group by e.ename,d.mgr]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{1, 12}])
+ LogicalJoin(condition=[=($2, $11)], joinType=[full])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{0, 3}])
+ LogicalJoin(condition=[=($1, $2)], joinType=[full])
+ LogicalAggregate(group=[{1, 2}])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{2, 3}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin16">
+ <Resource name="sql">
+ <![CDATA[select e.job
+from (select * from sales.emp where empno = 10) as e
+full outer join sales.dept as d on e.job = d.name
+and e.deptno + e.empno = d.deptno + 5
+group by e.job]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{2}])
+ LogicalJoin(condition=[AND(=($2, $11), =($9, $12))], joinType=[full])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, $0)])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{0}])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[full])
+ LogicalAggregate(group=[{2, 9}])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($7, $0)])
+ LogicalFilter(condition=[=($0, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{1, 2}])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[+($0, 5)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testPushAggregateThroughtOuterJoin2">
<Resource name="sql">
<![CDATA[select d.ename