[CALCITE-894] Do not generate redundant column alias for the left relation when translating IN subquery

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.4.0-incubating
• Fix Version/s: 1.5.0
• Assignee: Wei Xue
• Reporter: Wei Xue
• Created: 21/Sep/15 19:17
• Updated: 27/Feb/24 22:24
• Resolved: 29/Sep/15 17:53

Description

select * from "hr"."emps" where "deptno" in (
  select "deptno" from "hr"."depts")

would be converted into

LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])
  LogicalJoin(condition=[=($5, $6)], joinType=[inner])
    LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$1])
      EnumerableTableScan(table=[[hr, emps]])
    LogicalAggregate(group=[{0}])
      LogicalProject(deptno=[$0])
        EnumerableTableScan(table=[[hr, depts]])

There is an additional "$f5=[$1]" in the LogicalProject, which might cause us trouble in the later optimization stage. For example if "emps" table had a collation trait on $1, the Project would have multiple collation traits and would be flattened.
Instead, we want the converted rel to be like:

LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])
  LogicalJoin(condition=[=($1, $5)], joinType=[inner])
    EnumerableTableScan(table=[[hr, emps]])
    LogicalAggregate(group=[{0}])
      LogicalProject(deptno=[$0])
        EnumerableTableScan(table=[[hr, depts]])