[
https://issues.apache.org/jira/browse/CALCITE-7505?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-7505.
----------------------------------
Fix Version/s: 1.43.0
Resolution: Fixed
Fixed in
[https://github.com/apache/calcite/commit/8447eda2f2fe34af9571f84ccb5de3d74c9e5ecd]
Thank you for the fix [~krooswu]
> RelToSqlConverter fails to alias outer relation for correlated sub-queries in
> Filter
> ------------------------------------------------------------------------------------
>
> Key: CALCITE-7505
> URL: https://issues.apache.org/jira/browse/CALCITE-7505
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.39.0
> Reporter: Haris Sattar
> Assignee: krooswu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> When using a dialect where hasImplicitTableAlias() returns true (e.g.,
> PrestoSqlDialect), SqlImplementor.Result.asSelect() bypasses alias
> uniquification and hardcodes AS "t" for every FROM clause. For correlated
> subqueries, this produces ambiguous SQL where both the outer and inner tables
> share the same alias, causing the correlation reference to resolve to the
> wrong table.
> *Example:*
> Input RelNode tree: a correlated EXISTS subquery with two table scans
> (table_a outer, table_b inner), correlated on column "id".
> Expected SQL output:
> SELECT "id", SUM("amount") AS "total"
> FROM "table_a" AS "t"
> WHERE EXISTS (
> SELECT * FROM "table_b" AS "t0"
> WHERE "id" = "t"."id"
> )
> GROUP BY "id"
> *Actual SQL output:*
> SELECT "id", SUM("amount") AS "total"
> FROM "table_a" AS "t"
> WHERE EXISTS (
> SELECT * FROM "table_b" AS "t"
> WHERE "id" = "t"."id"
> )
> GROUP BY "id"
> Both tables get AS "t". The correlation reference "t"."id" is ambiguous — SQL
> scoping resolves it to the inner table, making the condition WHERE "id" =
> "id" (always true). The subquery never filters anything. Wrong results, no
> error.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
