[
https://issues.apache.org/jira/browse/CALCITE-7505?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
krooswu reassigned CALCITE-7505:
--------------------------------
Assignee: krooswu
> RelToSqlConverter produces duplicate FROM aliases for correlated subqueries
> when hasImplicitTableAlias() is true
> ----------------------------------------------------------------------------------------------------------------
>
> 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
>
> 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)
