[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812425#comment-17812425
]
Julian Hyde edited comment on CALCITE-6221 at 1/30/24 5:39 PM:
---
Thank you for reducing the number of cosmetic changes. It is now easier to
review and see the impact.
I agree that the subselect is necessary for the outer join case,
{code:java}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
However, there seem to be changes for inner join cases with narrower SELECT
clauses, e.g.
{code:java}
SELECT X, X0 FROM (
SELECT * FROM A
INNER JOIN ( SELECT ID AS ID0, X AS X0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
Those cases don't seem necessary. The following would suffice:
{code:java}
SELECT A.X, B.X AS X0
FROM A
INNER JOIN B ON A.ID = B.ID
WHERE ...{code}
was (Author: julianhyde):
I agree that the subselect is necessary for the outer join case,
{code:java}
SELECT * FROM (
SELECT * FROM A
LEFT OUTER JOIN ( SELECT ID AS ID0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
However, there seem to be changes for inner join cases with narrower SELECT
clauses, e.g.
{code:java}
SELECT X, X0 FROM (
SELECT * FROM A
INNER JOIN ( SELECT ID AS ID0, X AS X0 FROM B ) D ON A.ID = D.ID0
WHERE ...
) AS C{code}
Those cases don't seem necessary. The following would suffice:
{code:java}
SELECT A.X, B.X AS X0
FROM A
INNER JOIN B ON A.ID = B.ID
WHERE ...{code}
> 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)
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812318#comment-17812318
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:39 PM:
-
I also thought about applying {{FilterIntoJoinRule}} only for none JDBC
relations. 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.
In our application, the issue is also fixed by disabling this rule.
> 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)
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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
>
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812296#comment-17812296
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:09 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 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:
{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"
>
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812296#comment-17812296
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:09 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:
{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.
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:
{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.
> 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"
>
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812296#comment-17812296
]
Ulrich Kramer edited comment on CALCITE-6221 at 1/30/24 1:06 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:
{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.
was (Author: kramerul):
Now, I restricted the renaming of the fields in SQL to our special case. If
there is a {{Filter}} around a {{Join}} an additional sub-select is inserted.
Before the fix, a statement like the following one was generated:
{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.
> 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"
>
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Comment Edited] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
