You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by GitBox <gi...@apache.org> on 2019/04/17 23:41:14 UTC
[GitHub] [calcite] jcamachor commented on a change in pull request #1171:
[CALCITE-3011] Support for joins with AggregateJoinTransposeRule
jcamachor commented on a change in pull request #1171: [CALCITE-3011] Support for joins with AggregateJoinTransposeRule
URL: https://github.com/apache/calcite/pull/1171#discussion_r276472812
##########
File path: core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
##########
@@ -3384,6 +3384,174 @@ private void transitiveInference(RelOptRule... extraRules) throws Exception {
checkPlanning(tester, preProgram, new HepPlanner(program), sql);
}
+ // 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);
+ }
+
+ // 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);
+ }
+
+ // 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);
+ }
+
+ // 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);
+ }
+
+ // 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);
+ }
+
+ // 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);
+ }
+
+ // 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"
+ + "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);
+ }
+
+ // 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"
+ + "join sales.dept as d on e.job = d.name\n"
Review comment:
This should be outer.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services