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