[
https://issues.apache.org/jira/browse/CALCITE-7663?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu updated CALCITE-7663:
---------------------------------
Priority: Minor (was: Major)
> RelToSqlConverter generates ambiguous column references when expanding
> SELECT * over a join with duplicate field names
> -----------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7663
> URL: https://issues.apache.org/jira/browse/CALCITE-7663
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Environment: PostgreSQL dialect (any dialect where
> `supportGenerateSelectStar` returns false for joins with duplicate field
> names)
> Reporter: Alexis Cubilla
> Assignee: Alexis Cubilla
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> When {{RelToSqlConverter}} converts a {{RelNode}} to SQL for a dialect whose
> {{SqlDialect.supportGenerateSelectStar()}} returns {{false}} for a join with
> duplicate field names (e.g. {{PostgresqlSqlDialect}}), the {{SELECT *}} is
> expanded into explicit column references, but the expanded columns are *not*
> aliased to their (unique) row-type field names. When such a projection wraps a
> join that has two columns with the same name (e.g. two {{DEPTNO}} columns, or
> two {{id}} columns from different tables), the resulting sub-query exposes two
> identically named columns. Referencing that sub-query from an outer query is
> then ambiguous and the target database rejects it.
> On PostgreSQL this surfaces as:
> {code}
> ERROR: column reference "id" is ambiguous
> {code}
> This only happens through the programmatic path (RelBuilder / a RelNode that
> is
> converted directly), because the SQL parser + validator path uniquifies the
> output names before conversion. It is reproducible on 1.42.0; the same plan
> produced valid SQL in 1.41.0, so this is a regression (it appears related to
> CALCITE-7483, which broadened where {{SELECT *}} is expanded).
> h3. Reproduction
> Build a plan where a join with duplicate field names is wrapped by a FETCH
> (so it becomes a sub-query) and then joined again:
> {code:java}
> final Function<RelBuilder, RelNode> relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.INNER,
> b.equals(b.field(2, 0, "DEPTNO"), b.field(2, 1, "DEPTNO")))
> .limit(0, 10)
> .scan("DEPT")
> .join(JoinRelType.INNER,
> b.equals(b.field(2, 0, "EMPNO"), b.field(2, 1, "DEPTNO")))
> .limit(0, 5)
> .build();
> // converted with PostgresqlSqlDialect
> {code}
> h4. Actual output (invalid)
> The inner sub-query {{t}} exposes two columns named {{DEPTNO}}, and the outer
> query references {{"t"."DEPTNO"}}:
> {code:sql}
> SELECT "t"."EMPNO", ..., "t"."DEPTNO", "t"."DEPTNO", ... -- two "DEPTNO"
> FROM (SELECT "EMP"."EMPNO", ..., "EMP"."DEPTNO", "DEPT"."DEPTNO", ...
> FROM "scott"."EMP"
> INNER JOIN "scott"."DEPT" ON "EMP"."DEPTNO" = "DEPT"."DEPTNO"
> FETCH NEXT 10 ROWS ONLY) AS "t"
> INNER JOIN "scott"."DEPT" AS "DEPT0" ON "t"."EMPNO" = "DEPT0"."DEPTNO"
> FETCH NEXT 5 ROWS ONLY
> {code}
> h4. Expected output
> Each expanded column is aliased to its unique row-type field name, so no
> sub-query exposes two columns with the same name:
> {code:sql}
> SELECT "t"."EMPNO", ..., "t"."DEPTNO", "t"."DEPTNO0", ...,
> "DEPT0"."DEPTNO" AS "DEPTNO1", ...
> FROM (SELECT "EMP"."EMPNO", ..., "EMP"."DEPTNO",
> "DEPT"."DEPTNO" AS "DEPTNO0", ...
> FROM "scott"."EMP"
> INNER JOIN "scott"."DEPT" ON "EMP"."DEPTNO" = "DEPT"."DEPTNO"
> FETCH NEXT 10 ROWS ONLY) AS "t"
> INNER JOIN "scott"."DEPT" AS "DEPT0" ON "t"."EMPNO" = "DEPT0"."DEPTNO"
> FETCH NEXT 5 ROWS ONLY
> {code}
> h3. Root cause
> In {{SqlImplementor}}, the two {{SELECT *}} expansion blocks
> ({{Result#builder(...)}} and {{Result#maybeExpandStar(...)}}) build the select
> list by calling {{Context.field(i)}}, which returns the *physical* column
> reference (e.g. {{"DEPT"."DEPTNO"}}). The intrinsic name of that reference can
> collide with another column, even though the {{RelNode}} row type already
> holds
> unique field names ({{DEPTNO}}, {{DEPTNO0}}). The expansion never carries
> those
> unique names into the SQL as aliases.
> h3. Proposed fix
> When expanding {{SELECT *}}, alias each column to its (unique) row-type field
> name whenever the column's intrinsic name differs from it. This mirrors what a
> {{Project}} node already does, and matches the SQL produced through the
> validator path. A patch with unit tests is available (PR to follow).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
