Alexis Cubilla created CALCITE-7663:
---------------------------------------
Summary: 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
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)
