wq352400523 commented on code in PR #3379:
URL: https://github.com/apache/calcite/pull/3379#discussion_r1334922101
##########
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:
The initial plan is :
```
LogicalProject(deptno=[$0], added=[$1], name=[$2], empid=[$3], deptno0=[$4],
name0=[$5], deptno1=[$6], added0=[$7], name1=[$8], empid0=[$9], deptno2=[$10],
name2=[$11])
LogicalJoin(condition=[=($6, $10)], joinType=[inner])
LogicalJoin(condition=[=($0, $6)], joinType=[inner])
LogicalJoin(condition=[=($0, $4)], joinType=[inner])
LogicalTableScan(table=[[depts]])
LogicalTableScan(table=[[emps]])
LogicalTableScan(table=[[depts]])
LogicalTableScan(table=[[emps]])
```
And the whole best plan after this rule is :
```
LogicalProject(deptno=[$3], added=[$4], name=[$5], empid=[$9],
deptno0=[$10], name0=[$11], deptno1=[$0], added0=[$1], name1=[$2], empid0=[$6],
deptno2=[$7], name2=[$8])
LogicalJoin(condition=[=($3, $10)], joinType=[inner])
LogicalJoin(condition=[=($0, $7)], joinType=[inner])
LogicalJoin(condition=[=($3, $0)], joinType=[inner])
LogicalTableScan(table=[[depts]])
LogicalTableScan(table=[[depts]])
LogicalTableScan(table=[[emps]])
LogicalTableScan(table=[[emps]])
```
You can see that 'depts' now become a self-join after rule, and join-key of
this self-join is unique key , then we can remove this join further, like:
```
LogicalProject(deptno=[$3], added=[$4], name=[$5], empid=[$9],
deptno0=[$10], name0=[$11], deptno1=[$0], added0=[$1], name1=[$2], empid0=[$6],
deptno2=[$7], name2=[$8])
LogicalJoin(condition=[=($3, $10)], joinType=[inner])
LogicalJoin(condition=[=($0, $7)], joinType=[inner])
LogicalTableScan(table=[[depts]])
LogicalTableScan(table=[[emps]])
LogicalTableScan(table=[[emps]])
```
It doesn't mean to just simply remove the join , but instead with it child
relNode , because they are equivalent.
--
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]
