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)