[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17812256#comment-17812256
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 10:35 AM:
------------------------------------------------------------------
I will try to keep the changes as small as possible.
The issue in general is caused by the fact, that the columns are renamed inside
the {{Join}} class inside {{SqlValidatorUtil.deriveJoinRowType}} and this
change is not reflected in the generated SQL. This works in most cases except
when a filter is pushed down into a join. In this case an additional SELECT
wrapper is generated which causes a re-aliasing (in the issue above {{t2}})
Therefore, I tried to reflect the rename of the fields also in the generated
SQL statement.
But it seems that this change will also cause other issues (at least in our
application)
was (Author: kramerul):
I will try to keep the changes as small as possible.
The issue in general is caused by the fact, that the columns are renamed inside
the {{Join}} class inside {{SqlValidatorUtil.deriveJoinRowType}} and this
change is not reflected in the generated SQL. This works in most cases except
when a filter is pushed down into a join. In this case an additional SELECT
wrapper is generated which causes a re-aliasing (in the issue above {{t2}})
Therefore, I tried to reflect the rename of the field also in the generated SQL
statement.
But it seems that this change will also cause other issues (at least in our
application)
> JDBC adapter generates invalid query when the same table is joined multiple
> times
> ---------------------------------------------------------------------------------
>
> Key: CALCITE-6221
> URL: https://issues.apache.org/jira/browse/CALCITE-6221
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.36.0
> Environment: Local development
> Reporter: Ulrich Kramer
> Priority: Major
> Labels: pull-request-available
>
> Adding the following unit test to {{JdbcAdapterTest}}
> {code:java}
> @Test void testUnknownColumn() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("SELECT\n" +
> " \"content-format-owner\",\n" +
> " \"content-owner\"\n" +
> "FROM\n" +
> " (\n" +
> " SELECT\n" +
> " d1.dname AS \"content-format-owner\",\n" +
> " d2.dname || ' ' AS \"content-owner\"\n" +
> " FROM\n" +
> " scott.emp e1\n" +
> " left outer join scott.dept d1 on e1.deptno =
> d1.deptno\n" +
> " left outer join scott.dept d2 on e1.deptno =
> d2.deptno\n" +
> " left outer join scott.emp e2 on e1.deptno =
> e2.deptno\n" +
> " GROUP BY\n" +
> " d1.dname,\n" +
> " d2.dname\n" +
> " )\n" +
> "WHERE\n" +
> " \"content-owner\" IN (?)")
> .runs();
> }
> {code}
> Fails because the following SQL is sent to the underlying database
> {code:SQL}
> SELECT
> "t2"."DNAME" AS "content-format-owner",
> "t2"."DNAME0" || ' ' AS "content-owner"
> FROM
> (
> SELECT
> *
> FROM
> (
> SELECT
> "DEPTNO"
> FROM
> "SCOTT"."EMP"
> ) AS "t"
> LEFT JOIN (
> SELECT
> "DEPTNO",
> "DNAME"
> FROM
> "SCOTT"."DEPT"
> ) AS "t0" ON "t"."DEPTNO" = "t0"."DEPTNO"
> LEFT JOIN (
> SELECT
> "DEPTNO",
> "DNAME"
> FROM
> "SCOTT"."DEPT"
> ) AS "t1" ON "t"."DEPTNO" = "t1"."DEPTNO"
> WHERE
> "t1"."DNAME" || ' ' = ?
> ) AS "t2"
> LEFT JOIN (
> SELECT
> "DEPTNO"
> FROM
> "SCOTT"."EMP"
> ) AS "t3" ON "t2"."DEPTNO" = "t3"."DEPTNO"
> GROUP BY
> "t2"."DNAME",
> "t2"."DNAME0"
> {code}
> The column {{"t2"."DNAME0"}} does not exist.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
