[jira] [Assigned] (CALCITE-5402) RelToSql generates invalid code if left and right side field names clash

["Leonid Chistov (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-5402?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Leonid Chistov reassigned CALCITE-5402:
---------------------------------------

    Assignee: Leonid Chistov

> RelToSql generates invalid code if left and right side field names clash
> ------------------------------------------------------------------------
>
>                 Key: CALCITE-5402
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5402
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.32.0
>            Reporter: Leonid Chistov
>            Assignee: Leonid Chistov
>            Priority: Minor
>
> Wrong SQL code is generated when left and right side of a semi-join have fields with same name.
> Generated condition looks like `FIELD` = `rhs table`.`FIELD`, where left `FIELD` is resolved to be the same as `rhs table`.`FIELD` during query execution, but not a reference to an outer table of correlated subquery, as was intended.
> Examples of tests that would fail if added to RelToSqlConverterTest.java: 
> {code:java}
> @Test void testSemiJoinNameClash() {
>   final RelBuilder builder = relBuilder();
>   final RelNode root = builder
>       .scan("DEPT")
>       .project(builder.field("DEPTNO"), builder.field("DNAME"))
>       .scan("EMP")
>       .filter(
>           builder.call(SqlStdOperatorTable.GREATER_THAN,
>               builder.field("JOB"),
>               builder.literal((short) 10)))
>       .project(builder.field("DEPTNO"))
>       .join(
>           JoinRelType.SEMI, builder.equals(
>               builder.field(2, 0, "DEPTNO"),
>               builder.field(2, 1, "DEPTNO")))
>       .project(builder.field("DEPTNO"))
>       .build();
>   final String expectedSql = "SELECT \"DEPTNO\"\n"
>       + "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
>       + "FROM \"scott\".\"DEPT\"\n"
>       + "WHERE EXISTS (SELECT 1\n"
>       + "FROM (SELECT \"EMPNO\"\n"
>       + "FROM \"scott\".\"EMP\"\n"
>       + "WHERE \"JOB\" > 10) AS \"t1\"\n"
>       + "WHERE \"DEPT\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
>   assertThat(toSql(root), isLinux(expectedSql));
> } {code}
>  
> {code:java}
> @Test void testSemiJoinWithSameTable() {
>   final RelBuilder builder = relBuilder();
>   final RelNode root = builder
>       .scan("EMP")
>       .project(builder.field("DEPTNO"), builder.field("EMPNO"))
>       .scan("EMP")
>       .filter(
>           builder.call(SqlStdOperatorTable.GREATER_THAN,
>               builder.field("JOB"),
>               builder.literal((short) 10)))
>       .project(builder.field("EMPNO"))
>       .join(
>           JoinRelType.SEMI, builder.equals(
>               builder.field(2, 0, "EMPNO"),
>               builder.field(2, 1, "EMPNO")))
>       .project(builder.field("DEPTNO"))
>       .build();
>   final String expectedSql = "SELECT \"DEPTNO\"\n"
>       + "FROM (SELECT \"DEPTNO\", \"EMPNO\"\n"
>       + "FROM \"scott\".\"EMP\"\n"
>       + "WHERE EXISTS (SELECT 1\n"
>       + "FROM (SELECT \"EMPNO\"\n"
>       + "FROM \"scott\".\"EMP\"\n"
>       + "WHERE \"JOB\" > 10) AS \"t1\"\n"
>       + "WHERE \"EMP\".\"EMPNO\" = \"t1\".\"EMPNO\")) AS \"t\"";
>   assertThat(toSql(root), isLinux(expectedSql));
> } {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)