This is an automated email from the ASF dual-hosted git repository.
jcamacho 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 ecb469b [CALCITE-3011] Support left and right outer joins with
AggregateJoinTransposeRule (Vineet Garg)
ecb469b is described below
commit ecb469b9fd6122e7597cca93741ec34085b35ab4
Author: Vineet Garg <[email protected]>
AuthorDate: Tue Apr 16 18:37:06 2019 -0700
[CALCITE-3011] Support left and right outer joins with
AggregateJoinTransposeRule (Vineet Garg)
Close apache/calcite#1171
---
.../rel/rules/AggregateJoinTransposeRule.java | 15 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 264 ++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 345 +++++++++++++++++++++
3 files changed, 623 insertions(+), 1 deletion(-)
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 596cef5..d2651ae 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
@@ -79,7 +79,7 @@ public class AggregateJoinTransposeRule extends RelOptRule {
boolean allowFunctions) {
super(
operandJ(aggregateClass, null, agg -> isAggregateSupported(agg,
allowFunctions),
- operandJ(joinClass, null, join -> join.getJoinType() ==
JoinRelType.INNER, any())),
+ operand(joinClass, null, any())),
relBuilderFactory, null);
this.allowFunctions = allowFunctions;
}
@@ -146,12 +146,24 @@ public class AggregateJoinTransposeRule extends
RelOptRule {
return true;
}
+ // OUTER joins are supported for group by without aggregate functions
+ // 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());
+ }
+
public void onMatch(RelOptRuleCall call) {
final Aggregate aggregate = call.rel(0);
final Join join = call.rel(1);
final RexBuilder rexBuilder = aggregate.getCluster().getRexBuilder();
final RelBuilder relBuilder = call.builder();
+ if (!isJoinSupported(join, aggregate)) {
+ return;
+ }
+
// Do the columns used by the join appear in the output of the aggregate?
final ImmutableBitSet aggregateColumns = aggregate.getGroupSet();
final RelMetadataQuery mq = call.getMetadataQuery();
@@ -338,6 +350,7 @@ public class AggregateJoinTransposeRule extends RelOptRule {
boolean aggConvertedToProjects = false;
if (allColumnsInAggregate) {
// 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<>();
for (int key : Mappings.apply(mapping, aggregate.getGroupSet())) {
projects2.add(relBuilder.field(key));
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 8c2c8ed..664a2e5 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3551,6 +3551,270 @@ public class RelOptRulesTest extends RelOptTestBase {
checkPlanning(tester, preProgram, new HepPlanner(program), sql);
}
+ /** Test case for
+ * outer join, group by on non-join keys, group by on non-null generating
side only */
+ @Test public void testPushAggregateThroughtOuterJoin1() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.ename\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.dept as d on e.job = d.name\n"
+ + "group by e.ename";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, group by on non-join keys, on null generating side only */
+ @Test public void testPushAggregateThroughtOuterJoin2() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select d.ename\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.emp as d on e.job = d.job\n"
+ + "group by d.ename";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, group by on both side on non-join keys */
+ @Test public void testPushAggregateThroughtOuterJoin3() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.ename, d.mgr\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.emp as d on e.job = d.job\n"
+ + "group by e.ename,d.mgr";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, group by on key same as join key, group by on non-null
generating side */
+ @Test public void testPushAggregateThroughtOuterJoin4() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.job\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.dept as d on e.job = d.name\n"
+ + "group by e.job";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, group by on key same as join key, group by on null generating
side */
+ @Test public void testPushAggregateThroughtOuterJoin5() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select d.name\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.dept as d on e.job = d.name\n"
+ + "group by d.name";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, group by on key same as join key, group by on both side */
+ @Test public void testPushAggregateThroughtOuterJoin6() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.job,d.name\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.dept as d on e.job = d.name\n"
+ + "group by e.job,d.name";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, group by key is susbset of join keys, group by on non-null
generating side */
+ @Test public void testPushAggregateThroughtOuterJoin7() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.job\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.dept as d on e.job = d.name\n"
+ + "and e.deptno + e.empno = d.deptno + 5\n"
+ + "group by e.job";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, group by key is susbset of join keys, group by on null
generating side */
+ @Test public void testPushAggregateThroughtOuterJoin8() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select d.name\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.dept as d on e.job = d.name\n"
+ + "and e.deptno + e.empno = d.deptno + 5\n"
+ + "group by d.name";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, group by key is susbset of join keys, group by on both sides
*/
+ @Test public void testPushAggregateThroughtOuterJoin9() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.job, d.name\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.dept as d on e.job = d.name\n"
+ + "and e.deptno + e.empno = d.deptno + 5\n"
+ + "group by e.job, d.name";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** Test case for
+ * outer join, with aggregate functions */
+ @Test public void testPushAggregateThroughtOuterJoin10() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select count(e.ename) \n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.emp as d on e.job = d.job\n"
+ + "group by e.ename,d.mgr";
+ sql(sql).withPre(preProgram).with(program).checkUnchanged();
+ }
+
+ /** Test case for
+ * non-equi outer join */
+ @Test public void testPushAggregateThroughtOuterJoin11() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.empno,d.deptno\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "left outer join sales.dept as d on e.empno < d.deptno\n"
+ + "group by e.empno,d.deptno";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql, true);
+ }
+
+ /** Test case for
+ * right outer join, group by on key same as join key, group by on
(left)null generating side */
+ @Test public void testPushAggregateThroughtOuterJoin12() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.job\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "right outer join sales.dept as d on e.job = d.name\n"
+ + "group by e.job";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** 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)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.job\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "full outer join sales.dept as d on e.job = d.name\n"
+ + "group by e.job";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** 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)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.mgr, d.mgr\n"
+ + "from sales.emp as e\n"
+ + "full outer join sales.emp as d on e.mgr = d.mgr\n"
+ + "group by d.mgr, e.mgr";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** 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)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.ename, d.mgr\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "full outer join sales.emp as d on e.job = d.job\n"
+ + "group by e.ename,d.mgr";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /** 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)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateJoinTransposeRule.EXTENDED)
+ .build();
+ final String sql = "select e.job\n"
+ + "from (select * from sales.emp where empno = 10) as e\n"
+ + "full outer join sales.dept as d on e.job = d.name\n"
+ + "and e.deptno + e.empno = d.deptno + 5\n"
+ + "group by e.job";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
@Test public void testPushAggregateThroughJoin2() {
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 6c849c1..53f19e2 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -5312,6 +5312,351 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
]]>
</Resource>
</TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin1">
+ <Resource name="sql">
+ <![CDATA[select e.ename
+from (select * from sales.emp where empno = 10) as e
+left outer join sales.dept as d on e.job = d.name
+group by e.ename]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{1}])
+ LogicalJoin(condition=[=($2, $10)], joinType=[left])
+ 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=[=($1, $2)], joinType=[left])
+ 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=[{1}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin10">
+ <Resource name="sql">
+ <![CDATA[select count(e.ename)
+from (select * from sales.emp where empno = 10) as e
+left outer join sales.emp as d on e.job = d.job
+group by e.ename,d.mgr]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EXPR$0=[$2])
+ LogicalAggregate(group=[{1, 12}], EXPR$0=[COUNT()])
+ LogicalJoin(condition=[=($2, $11)], joinType=[left])
+ 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>
+ </TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin11">
+ <Resource name="sql">
+ <![CDATA[select e.empno,d.deptno
+from (select * from sales.emp where empno = 10) as e
+left outer join sales.dept as d on e.empno < d.deptno
+group by e.empno,d.deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0, 9}])
+ LogicalJoin(condition=[<($0, $9)], joinType=[left])
+ 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>
+ </TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin12">
+ <Resource name="sql">
+ <![CDATA[select e.job
+from (select * from sales.emp where empno = 10) as e
+right 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=[right])
+ 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=[right])
+ 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="testPushAggregateThroughtOuterJoin2">
+ <Resource name="sql">
+ <![CDATA[select d.ename
+from (select * from sales.emp where empno = 10) as e
+left outer join sales.emp as d on e.job = d.job
+group by d.ename]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{10}])
+ LogicalJoin(condition=[=($2, $11)], joinType=[left])
+ 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=[{1}])
+ LogicalJoin(condition=[=($0, $2)], joinType=[left])
+ 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, 2}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPushAggregateThroughtOuterJoin3">
+ <Resource name="sql">
+ <![CDATA[select e.ename, d.mgr
+from (select * from sales.emp where empno = 10) as e
+left 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=[left])
+ 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=[left])
+ 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="testPushAggregateThroughtOuterJoin4">
+ <Resource name="sql">
+ <![CDATA[select e.job
+from (select * from sales.emp where empno = 10) as e
+left 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=[left])
+ 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[
+LogicalProject(JOB=[$0])
+ LogicalJoin(condition=[=($0, $1)], joinType=[left])
+ 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="testPushAggregateThroughtOuterJoin5">
+ <Resource name="sql">
+ <![CDATA[select d.name
+from (select * from sales.emp where empno = 10) as e
+left outer join sales.dept as d on e.job = d.name
+group by d.name]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{10}])
+ LogicalJoin(condition=[=($2, $10)], joinType=[left])
+ 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=[{1}])
+ LogicalJoin(condition=[=($0, $1)], joinType=[left])
+ 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="testPushAggregateThroughtOuterJoin6">
+ <Resource name="sql">
+ <![CDATA[select e.job,d.name
+from (select * from sales.emp where empno = 10) as e
+left outer join sales.dept as d on e.job = d.name
+group by e.job,d.name]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{2, 10}])
+ LogicalJoin(condition=[=($2, $10)], joinType=[left])
+ 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[
+LogicalJoin(condition=[=($0, $1)], joinType=[left])
+ 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="testPushAggregateThroughtOuterJoin7">
+ <Resource name="sql">
+ <![CDATA[select e.job
+from (select * from sales.emp where empno = 10) as e
+left 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=[left])
+ 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=[left])
+ 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="testPushAggregateThroughtOuterJoin8">
+ <Resource name="sql">
+ <![CDATA[select d.name
+from (select * from sales.emp where empno = 10) as e
+left outer join sales.dept as d on e.job = d.name
+and e.deptno + e.empno = d.deptno + 5
+group by d.name]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{11}])
+ LogicalJoin(condition=[AND(=($2, $11), =($9, $12))], joinType=[left])
+ 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=[{2}])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
+ 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="testPushAggregateThroughtOuterJoin9">
+ <Resource name="sql">
+ <![CDATA[select e.job, d.name
+from (select * from sales.emp where empno = 10) as e
+left outer join sales.dept as d on e.job = d.name
+and e.deptno + e.empno = d.deptno + 5
+group by e.job, d.name]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{2, 11}])
+ LogicalJoin(condition=[AND(=($2, $11), =($9, $12))], joinType=[left])
+ 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, 2}])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
+ 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="testSimplifyFilter">
<Resource name="sql">
<