Haris Sattar created CALCITE-7509:
-------------------------------------
Summary: 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
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)
