[CALCITE-1154] Wrong Plan/Result if wrapping a query with SELECT * FROM

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Sean Hsuan-Yi Chu
• Created: 14/Mar/16 21:28
• Updated: 27/May/17 10:22

Description

For example, a query such as

select * from (
    select t1.deptno, t2.deptno
    from emp t1 inner join dept t2
    on t1.empno = t2.deptno
)

gives a plan:

LogicalProject(DEPTNO=[$0], DEPTNO0=[$0])
  LogicalProject(DEPTNO=[$7], DEPTNO0=[$9])
    LogicalJoin(condition=[=($0, $9)], joinType=[inner])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

As the top LogicalProject indicates, although DEPTNO and DEPTNO0 coming from different tables, Calcite thinks they are the same so just pick $0.

Notice if we remove the outermost select * where (), Calcite would not be confused with those two columns.