[ 
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 12:29 PM:
------------------------------------------------------------------

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

Reply via email to