[ https://issues.apache.org/jira/browse/CALCITE-5530?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Abbas Gadhia updated CALCITE-5530: ---------------------------------- 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 {code:java} SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO;{code} {noformat} LogicalProject(EMPNO=[$0]) LogicalSort(sort0=[$1], dir0=[ASC]) LogicalProject(EMPNO=[UPPER($1)], EMPNO0=[$0]) LogicalTableScan(table=[[scott, EMP]]){noformat} EMPNO actually refers to the underlying physical column. The output of RelToSqlConverter is the following {code:java} SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO0;{code} Here EMPNO0 does not exist and fails. was: 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 {code:java} SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO;{code} {noformat} LogicalProject(EMPNO=[$0]) LogicalSort(sort0=[$1], dir0=[ASC]) LogicalProject(EMPNO=[UPPER($1)], EMPNO0=[$0]) LogicalTableScan(table=[[scott, EMP]]){noformat} The output of RelToSqlConverter is the following {code:java} SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO0;{code} Here EMPNO0 does not exist and fails. > RelToSqlConverter[ORDER BY] generates an incorrect field alias when 2 > projection fields have the same name > ---------------------------------------------------------------------------------------------------------- > > Key: CALCITE-5530 > URL: https://issues.apache.org/jira/browse/CALCITE-5530 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.33.0 > Reporter: Abbas Gadhia > Priority: Minor > > 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 > > {code:java} > SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO;{code} > {noformat} > LogicalProject(EMPNO=[$0]) > LogicalSort(sort0=[$1], dir0=[ASC]) > LogicalProject(EMPNO=[UPPER($1)], EMPNO0=[$0]) > LogicalTableScan(table=[[scott, EMP]]){noformat} > EMPNO actually refers to the underlying physical column. > > The output of RelToSqlConverter is the following > {code:java} > SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO0;{code} > Here EMPNO0 does not exist and fails. -- This message was sent by Atlassian Jira (v8.20.10#820010)