[CALCITE-2514] Add SqlIdentifier conversion to ITEM operator for dynamic tables in ExtendedExpander

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.17.0
• Fix Version/s: 1.18.0
• Assignee: Julian Hyde
• Reporter: Arina Ielchiieva
• Created: 31/Aug/18 10:21
• Updated: 23/Dec/18 00:12
• Resolved: 03/Sep/18 12:06

Description

SqlIdentifier is not converted to ITEM operator for dynamic tables in ExtendedExpander and thus query returns wrong results. Happens when isGroupByAlias is set to true in SQL conformance.
Query example:

select n_regionkey, max(min(n_nationkey)) over(partition by n_regionkey)
from (select * from SALES.NATION)
group by n_regionkey

Incorrect logical plan:

LogicalProject(N_REGIONKEY=[ITEM($0, 'N_REGIONKEY')], EXPR$1=[MAX($1) OVER (PARTITION BY ITEM($0, 'N_REGIONKEY') RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
  LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
    LogicalProject(**=[$0], $f1=[ITEM($0, 'N_NATIONKEY')])
      LogicalProject(**=[$0])
        LogicalTableScan(table=[[CATALOG, SALES, NATION]])

Correct logical plan:

LogicalProject(N_REGIONKEY=[$0], EXPR$1=[MAX($1) OVER (PARTITION BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
  LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
    LogicalProject(N_REGIONKEY=[ITEM($0, 'N_REGIONKEY')], $f1=[ITEM($0, 'N_NATIONKEY')])
      LogicalProject(**=[$0])
        LogicalTableScan(table=[[CATALOG, SALES, NATION]])