[CALCITE-4668] RelBuilder.join should convert Correlate to Join if correlation variable is unused

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: James Starr
• Reporter: James Starr
• Created: 25/Jun/21 19:42
• Updated: 20/Oct/21 05:24
• Resolved: 07/Jul/21 08:39

Description

We generate correlated join when no correlate variable is present on the right side. However, this could be simplified to emit a normal join.

Given:

final RelNode rel = builder
    .scan("EMP")
    .variable(v)
    .scan("DEPT")
    .join(JoinRelType.LEFT,
        builder.equals(builder.field(2, 0, "SAL"),
            builder.literal(1000)),
        ImmutableSet.of(v.get().id))
    .build();

Currently the following is emitted:

LogicalFilter(condition=[=($7, $8)])
  LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
    LogicalTableScan(table=[[scott, EMP]])
    LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
      LogicalTableScan(table=[[scott, DEPT]])

After the changes the following will be emitted:

LogicalJoin(condition=[=($7, $8)], joinType=[left])
  LogicalTableScan(table=[[scott, EMP]])
  LogicalTableScan(table=[[scott, DEPT]])

Issue Links

• links to: GitHub Pull Request #2446