[
https://issues.apache.org/jira/browse/CALCITE-7505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18079202#comment-18079202
]
Haris Sattar commented on CALCITE-7505:
---------------------------------------
Hi [~krooswu],
I have a working fix in a local fork. Removing the hasImplicitTableAlias()
branch in SqlImplementor.Result.asSelect() so it always goes through asFrom()
resolves the issue in our environment. However, I've only tested it with
PrestoSqlDialect and correlated subqueries, so I'm not sure if there are
broader implications for other dialects or node types. Feel free to pick this
up. Happy to share more details if helpful.
> 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)
