[CALCITE-2143] RelToSqlConverter produces incorrect SQL with aggregation

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.15.0
• Fix Version/s: 1.16.0
• Assignee: Julian Hyde
• Reporter: Austin Haas
• Created: 18/Jan/18 23:10
• Updated: 27/Feb/24 22:23
• Resolved: 23/Jan/18 02:43

Description

This plan:

LogicalAggregate(group=[{1}], my-alias=[SUM($0)])
  LogicalAggregate(group=[{0, 1}])
    LogicalProject(b=[$2], a=[$0])
      LogicalJoin(condition=[=($0, $1)], joinType=[inner])
        LogicalTableScan(table=[[rel-1]])
        LogicalTableScan(table=[[rel-2]])

Produces this erroneous SQL:

SELECT `t0`.`my-alias` AS `a`, (SUM(`t0`.`a`)) AS `my-alias`
FROM (SELECT `rel-2`.`b`, `rel-1`.`a`
      FROM `rel-1`
      INNER JOIN `rel-2` ON (`rel-1`.`a` = `rel-2`.`a`)
      GROUP BY `rel-2`.`b`, `rel-1`.`a`) AS `t0`
GROUP BY `t0`.`my-alias`

Note the first field of the outer SELECT, where 'my-alias' is aliased as 'a'.

Any clues how to fix would be appreciated.

Issue Links

• relates to: CALCITE-3440 RelToSqlConverter does not properly alias ambiguous ORDER BY