LakeShen commented on code in PR #3379:
URL: https://github.com/apache/calcite/pull/3379#discussion_r1334914732
##########
core/src/test/java/org/apache/calcite/tools/PlannerTest.java:
##########
@@ -1576,4 +1583,77 @@ private static class VaryingTypeSystem extends
DelegatingTypeSystem {
return true;
}
}
+
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5927";>[CALCITE-5927]
+ * Reorder join condition if it contains self-join</a>.
+ */
+ @Test public void testReorderForSelfJoin() throws Exception {
+ final String sql = "select * from \"depts\" as d0\n"
+ + "join \"emps\" as d1 on d0.\"deptno\" = d1.\"deptno\"\n"
+ + "join \"depts\" as d2 on d0.\"deptno\" = d2.\"deptno\"\n"
+ + "join \"emps\" as d3 on d2.\"deptno\" = d3.\"deptno\"\n";
+
+ SchemaPlus rootSchema = Frameworks.createRootSchema(true);
+ // we add customized table into root schema instead of using HrSchema,
+ // because we need statistics
+ rootSchema.add("depts", new AbstractTable() {
+ @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
+ return typeFactory.builder()
+ .add("deptno", SqlTypeName.INTEGER)
+ .add("added", SqlTypeName.INTEGER)
+ .add("name", SqlTypeName.VARCHAR)
+ .build();
+ }
+ @Override public Statistic getStatistic() {
+ return Statistics.of(245D,
+ ImmutableList.of(ImmutableBitSet.of(0)));
+ }
+ });
+ rootSchema.add("emps", new AbstractTable() {
+ @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
+ return typeFactory.builder()
+ .add("empid", SqlTypeName.INTEGER)
+ .add("deptno", SqlTypeName.INTEGER)
+ .add("name", SqlTypeName.VARCHAR)
+ .build();
+ }
+ @Override public Statistic getStatistic() {
+ return Statistics.of(240D,
+ ImmutableList.of(ImmutableBitSet.of(0)));
+ }
+ });
+
+ final FrameworkConfig config = Frameworks.newConfigBuilder()
+ .parserConfig(SqlParser.Config.DEFAULT.withCaseSensitive(false))
+ .defaultSchema(rootSchema)
+ .ruleSets()
+ .build();
+
+ final Planner planner = Frameworks.getPlanner(config);
+ SqlNode sqlNode = planner.parse(sql);
+ sqlNode = planner.validate(sqlNode);
+ RelNode relNode = planner.rel(sqlNode).rel;
+
+ final HepProgram program =
+ HepProgram.builder()
+ .addMatchOrder(HepMatchOrder.BOTTOM_UP)
+ .addGroupBegin()
+ .addRuleInstance(CoreRules.PROJECT_REMOVE)
+ .addRuleInstance(CoreRules.JOIN_PROJECT_BOTH_TRANSPOSE)
+ .addRuleInstance(CoreRules.PROJECT_MERGE)
+ .addGroupEnd()
+ .addRuleInstance(CoreRules.JOIN_TO_MULTI_JOIN)
+ .addRuleInstance(CoreRules.MULTI_JOIN_OPTIMIZE)
+ .build();
+
+ final HepPlanner hepPlanner = new HepPlanner(program);
+ hepPlanner.setRoot(relNode);
+ RelNode bestNode = hepPlanner.findBestExp();
+ final String expected = ""
Review Comment:
What does the initial plan look like？
##########
core/src/test/java/org/apache/calcite/tools/PlannerTest.java:
##########
@@ -1576,4 +1583,77 @@ private static class VaryingTypeSystem extends
DelegatingTypeSystem {
return true;
}
}
+
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5927";>[CALCITE-5927]
+ * Reorder join condition if it contains self-join</a>.
+ */
+ @Test public void testReorderForSelfJoin() throws Exception {
+ final String sql = "select * from \"depts\" as d0\n"
+ + "join \"emps\" as d1 on d0.\"deptno\" = d1.\"deptno\"\n"
+ + "join \"depts\" as d2 on d0.\"deptno\" = d2.\"deptno\"\n"
+ + "join \"emps\" as d3 on d2.\"deptno\" = d3.\"deptno\"\n";
+
+ SchemaPlus rootSchema = Frameworks.createRootSchema(true);
+ // we add customized table into root schema instead of using HrSchema,
+ // because we need statistics
+ rootSchema.add("depts", new AbstractTable() {
+ @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
+ return typeFactory.builder()
+ .add("deptno", SqlTypeName.INTEGER)
+ .add("added", SqlTypeName.INTEGER)
+ .add("name", SqlTypeName.VARCHAR)
+ .build();
+ }
+ @Override public Statistic getStatistic() {
+ return Statistics.of(245D,
+ ImmutableList.of(ImmutableBitSet.of(0)));
+ }
+ });
+ rootSchema.add("emps", new AbstractTable() {
+ @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
+ return typeFactory.builder()
+ .add("empid", SqlTypeName.INTEGER)
+ .add("deptno", SqlTypeName.INTEGER)
+ .add("name", SqlTypeName.VARCHAR)
+ .build();
+ }
+ @Override public Statistic getStatistic() {
+ return Statistics.of(240D,
+ ImmutableList.of(ImmutableBitSet.of(0)));
+ }
+ });
+
+ final FrameworkConfig config = Frameworks.newConfigBuilder()
+ .parserConfig(SqlParser.Config.DEFAULT.withCaseSensitive(false))
+ .defaultSchema(rootSchema)
+ .ruleSets()
+ .build();
+
+ final Planner planner = Frameworks.getPlanner(config);
+ SqlNode sqlNode = planner.parse(sql);
+ sqlNode = planner.validate(sqlNode);
+ RelNode relNode = planner.rel(sqlNode).rel;
+
+ final HepProgram program =
+ HepProgram.builder()
+ .addMatchOrder(HepMatchOrder.BOTTOM_UP)
+ .addGroupBegin()
+ .addRuleInstance(CoreRules.PROJECT_REMOVE)
+ .addRuleInstance(CoreRules.JOIN_PROJECT_BOTH_TRANSPOSE)
+ .addRuleInstance(CoreRules.PROJECT_MERGE)
+ .addGroupEnd()
+ .addRuleInstance(CoreRules.JOIN_TO_MULTI_JOIN)
+ .addRuleInstance(CoreRules.MULTI_JOIN_OPTIMIZE)
+ .build();
+
+ final HepPlanner hepPlanner = new HepPlanner(program);
+ hepPlanner.setRoot(relNode);
+ RelNode bestNode = hepPlanner.findBestExp();
+ final String expected = ""
Review Comment:
What does the initial plan look like？
--
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.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
