[jira] [Commented] (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&focusedCommentId=17812646#comment-17812646
]
Ulrich Kramer commented on CALCITE-6221:
I will try to modify the PR in this direction
> 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] [Commented] (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&focusedCommentId=17812425#comment-17812425
]
Julian Hyde commented on CALCITE-6221:
--
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] [Commented] (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&focusedCommentId=17812318#comment-17812318
]
Ulrich Kramer commented on CALCITE-6221:
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] [Commented] (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&focusedCommentId=17812296#comment-17812296
]
Ulrich Kramer commented on CALCITE-6221:
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"
> {code}
> The column {{"t2"."DNAME0"}} does not exist.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (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&focusedCommentId=17812256#comment-17812256
]
Ulrich Kramer commented on CALCITE-6221:
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)
[jira] [Commented] (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&focusedCommentId=17812036#comment-17812036
]
Julian Hyde commented on CALCITE-6221:
--
There are too many changes in RelToSqlConverterTest (too many to review, and
possibly too many to approve this PR). Some of them are cosmetic (e.g. a string
that used to be broken into two lines, that is now one line). You must revert
those cosmetic changes, so we can see the 'real' changes.
I have not reviewed your {{maybeFixRenamedFields}} method in detail, but it
seems to be a sledge hammer, renaming things that are actually OK. It would
benefit from javadoc, explaining exactly what it does.
Can you push a commit reverting the cosmetic changes, so we can re-review?
> 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] [Commented] (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&focusedCommentId=17810736#comment-17810736
]
Ulrich Kramer commented on CALCITE-6221:
Yes, it's minimal. Everything works if
- second column is not calculated
- one of the joins is missing (even the latest which is not used)
- the filter condition is missing
> 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
>
> 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] [Commented] (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&focusedCommentId=17810582#comment-17810582
]
Julian Hyde commented on CALCITE-6221:
--
Nice bug! Is it minimal? (E.g. does it reproduce with only one use of EMP, or
one use of DEPT, or without {{{}LEFT JOIN{}}}, or with simpler column aliases?)
> 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
>
> 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)
