[
https://issues.apache.org/jira/browse/CALCITE-7509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18103043#comment-18103043
]
krooswu commented on CALCITE-7509:
----------------------------------
Hi [~satthar]
Thanks for the report. I tried a test case matching your scenario (correlated
scalar subquery in SELECT list, outer GROUP BY, correlated on the same column),
but the correlation variable registers correctly in my run — no reproduction so
far.
Could you share a failing test, stack trace, or the actual RelNode plan
(`.explain()` output) for the query that fails? That would let me match the
exact Rel shape and reproduce it directly.
Test case I tried (passes):
```java
@Test void testScalarSubQueryOverAggregateWithOuterGroupBy() {
final String sql =
"SELECT deptno, SUM(sal) AS total,\n"
+ " (SELECT COUNT(*)\n"
+ " FROM dept d\n"
+ " WHERE d.deptno = emp.deptno) AS inner_total\n"
+ "FROM emp\n"
+ "GROUP BY deptno";
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.ok(
"SELECT \"DEPTNO\", SUM(\"SAL\") AS \"TOTAL\", (SELECT COUNT(*)\n"
+ "FROM \"SCOTT\".\"DEPT\"\n"
+ "WHERE \"DEPTNO\" = \"t0\".\"DEPTNO\") AS \"INNER_TOTAL\"\n"
+ "FROM \"SCOTT\".\"EMP\" AS \"t0\"\n"
+ "GROUP BY \"DEPTNO\"");
}
```
> RelToSqlConverter fails to register correlation variable for scalar subquery
> above Aggregate
> --------------------------------------------------------------------------------------------
>
> Key: CALCITE-7509
> URL: https://issues.apache.org/jira/browse/CALCITE-7509
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.39.0
> Reporter: Haris Sattar
> Assignee: krooswu
> Priority: Major
>
> When a correlated scalar subquery appears in a Project node above an
> Aggregate node, RelToSqlConverter fails to register the correlation variable
> ($cor0) because variablesSet is empty on the outer Project. The variablesSet
> is defined on the inner Project below the Aggregate but is not propagated
> upward. As a result, the correlation reference is either dropped or emits an
> invalid alias.
> *Example:*
> Input: a correlated scalar subquery in the SELECT list with GROUP BY on the
> outer query. The outer table is table_a, the inner table is table_b,
> correlated on column "id".
> Expected SQL output:
> SELECT "id", SUM("amount") AS "total",
> (SELECT SUM("value") FROM "table_b" AS "t1"
> WHERE "id" = "t"."id") AS "inner_total"
> FROM "table_a" AS "t"
> GROUP BY "id"
> Actual behavior:
> The converter does not register $cor0 against the outer table's alias. The
> correlation reference "t"."id" is either missing or replaced with an invalid
> alias. The SQL is invalid and will be rejected by the execution engine.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
