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

["Abbas Gadhia (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5530 ]


    Abbas Gadhia deleted comment on CALCITE-5530:
    ---------------------------------------

was (Author: nerrve):
I'm using a custom Sql to Rel converter to build a Rel similar to the one mentioned above. 



I have a PR for this that i'm working on, the test cases for which looks similar to below
{code:java}
@Test void testSortWithAnFieldNotInTheProjectionWithASameAliasAsThatInTheProjection() {
  final RelBuilder builder = relBuilder();
  final RelNode base = builder
      .scan("EMP")
      .project(
          builder.alias(
              builder.call(SqlStdOperatorTable.UPPER, builder.field("ENAME")), "EMPNO"
          ),
          builder.field("EMPNO")
      )
      .sort(1)
      .project(builder.field(0))
      .build();

  // The expected string should deliberately have a subquery to handle a scenario in which
  // the projection field has an alias with the same name as that of the field used in the
  // ORDER BY
  String expectedString = ""
      + "SELECT \"EMPNO\"\n"
      + "FROM (SELECT UPPER(\"ENAME\") AS \"EMPNO\", \"EMPNO\" AS \"EMPNO0\"\n"
      + "FROM \"scott\".\"EMP\"\n"
      + "ORDER BY \"EMPNO0\") AS \"t0\"";

  assertThat(toSql(base), isLinux(expectedString));
}

@Test void testSortWithAnExpressionNotInTheProjectionThatRefersToAnUnderlyingFieldWithSameAliasAsThatInTheProjection1() {
  final RelBuilder builder = relBuilder();
  final RelNode base = builder
      .scan("EMP")
      .project(
          builder.alias(
              builder.call(SqlStdOperatorTable.UPPER, builder.field("ENAME")), "EMPNO"
          ),
          builder.call(
              SqlStdOperatorTable.PLUS, builder.field("EMPNO"),
              builder.literal(1)
          )
      )
      .sort(1)
      .project(builder.field(0))
      .build();

  // An output such as
  // "SELECT UPPER(\"ENAME\") AS \"EMPNO\"\nFROM \"scott\".\"EMP\"\nORDER BY LOWER(\"EMPNO\")"
  // would be incorrect since the rel is sorting by the column LOWER(EMPNO) in which EMPNO
  // refers to the physical column EMPNO and not the alias
  String expectedString = ""
      + "SELECT \"EMPNO\"\n"
      + "FROM (SELECT UPPER(\"ENAME\") AS \"EMPNO\", \"EMPNO\" + 1 AS \"$f1\"\n"
      + "FROM \"scott\".\"EMP\"\n"
      + "ORDER BY 2) AS \"t0\"";

  assertThat(toSql(base), isLinux(expectedString));
}


{code}

> 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)