[CALCITE-5530] RelToSqlConverter[ORDER BY] generates an incorrect field alias when 2 projection fields have the same name

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.33.0
• Fix Version/s: 1.35.0
• Component/s: jdbc-adapter
• Labels: pull-request-available
• Assignee: Jiajun Xie
• Reporter: Abbas Gadhia
• Created: 15/Feb/23 14:57
• Updated: 27/Jul/23 10:22
• Resolved: 03/May/23 02:51

Description

In queries typical of Teradata,

if there exists an expression alias that is also a field in the underlying table, any references to that field in the ORDER BY, refer to the underlying physical column rather than the expression alias.

For ex. in the following query and Rel
 

SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO;

LogicalProject(EMPNO=[$0])
  LogicalSort(sort0=[$1], dir0=[ASC])
    LogicalProject(EMPNO=[UPPER($1)], EMPNO0=[$0])
      LogicalTableScan(table=[[scott, EMP]])

EMPNO actually refers to the underlying physical column. 

 

The output of RelToSqlConverter is the following

SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO0;
SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP ORDER BY 2;

Here EMPNO0 or ordinal 2 does not exist and fails.

 

This issue, i believe was introduced due to the changes done as part of https://issues.apache.org/jira/browse/CALCITE-4901

Issue Links

• Blocked: CALCITE-5656 RelToSqlConverter has an error using ordinal for ORDER BY
• relates to: CALCITE-5724 Generated SQL uses literal values in ORDER BY clauses
• links to: GitHub Pull Request #3085