Ulrich Kramer created CALCITE-6221:
--------------------------------------
Summary: Invalid query generated 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
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)
