[CALCITE-5553] RelStructuredTypeFlattener produces bad plan for single field struct

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.23.0, 1.28.0, 1.33.0
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Andrew Pilloud
• Created: 02/Mar/23 21:57
• Updated: 27/Jul/23 10:22
• Resolved: 14/Mar/23 14:35

Description

A Beam user brought us a query that produces an invalid plan after
upgrading to a newer version of Beam (moving them from Calcite 1.20 to
1.28). I was able to write a test case which demonstrates this issue
with a trivial filter on a table containing a nested struct with a
single field. The issue appears to be coming out of
RelStructuredTypeFlattener.rewrite and I bisected it to a single
commit, e44beba28.

Removing the noFlatteningForInput function added in this commit fixes
the issue. That method doesn't appear to be looking at types at all,
only mismatches in field count, I expect so any single field struct
would hit this. Any suggestions on how we can work around this in
Beam?

The test query and plans are as follows, the test case is in PR 3092.

select dn.skill from sales.dept_single dn WHERE dn.skill.type = ''

Expected plan:

LogicalProject(SKILL=[ROW($0)])
  LogicalFilter(condition=[=($0, '')])
    LogicalProject(TYPE=[$0.TYPE])
      LogicalTableScan(table=[[CATALOG, SALES, DEPT_SINGLE]])

Actual Plan:

LogicalProject(SKILL=[ROW($0)])
  LogicalFilter(condition=[=($0.TYPE, '')])
    LogicalProject(TYPE=[$0.TYPE])
      LogicalTableScan(table=[[CATALOG, SALES, DEPT_SINGLE]])

Plan before flatten:

LogicalProject(SKILL=[$0])
  LogicalFilter(condition=[=($0.TYPE, '')])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT_SINGLE]])

Issue Links

• relates to: CALCITE-5574 Break MockCatalogReaderSimple#init into smaller methods
• links to: GitHub Pull Request #3092