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

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

Leonid Chistov created CALCITE-5402:
---------------------------------------

             Summary: 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


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\".\"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}
 

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