[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17812318#comment-17812318
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:38 PM:
-----------------------------------------------------------------
I also thought about applying {{FilterIntoJoinRule}} only for none JDBC rules.
But this could lead to query plans, which are much more expensive.
In our application, the issue is also fixed by disabling this rule.
was (Author: kramerul):
I also thought about applying {{FilterIntoJoinRule}} only for none JDBC rules.
But this could lead to query plans, which are much more expensive.
> 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)
