[jira] [Updated] (CALCITE-7505) RelToSqlConverter produces duplicate FROM aliases for correlated subqueries when hasImplicitTableAlias() is true

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-7505?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

ASF GitHub Bot updated CALCITE-7505:
------------------------------------
    Labels: pull-request-available  (was: )

> 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
>              Labels: pull-request-available
>
> 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)