[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17812296#comment-17812296
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:14 PM:
-----------------------------------------------------------------
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated for a filter
around a join:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
I would be really grateful if someone has a better idea to detect or handle
this special case.
was (Author: kramerul):
Now, I restricted the renaming of the fields in SQL to our special case. [Only
if there is a {{Filter}} around a
{{Join}}|https://github.com/sap-contributions/calcite/blob/e8f266dceb27841b07ed00aa697bfe8c9c6aa270/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L268-L274]
an additional sub-select is inserted.
Before the fix, a statement like the following one was generated for a filter
around a join:
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
WHERE ...
) AS C
{code}
As {{A}} and {{B}} are no longer visible outside this sub-select, it's not
possible to use them in a {{GROUP BY}}. Before the the fix a {{GROUP BY B.ID}}
was translated to {{GROUP BY C.ID0}}, which is not correct since the column
{{ID0}} does not exist.
The fix modifies the generated statement like this
{code:SQL}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C
{code}
Now {{C.ID0}} exists and can be used for grouping and other stuff.
I would be really grateful if someone has a better idea to detect this special
case.
> 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)
