[
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;
SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP ORDER BY 2;{code}
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
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}
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;
SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP ORDER BY 2;{code}
Here EMPNO0 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
> 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;
> SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP ORDER BY 2;{code}
> 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
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
