This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new cdcaaf62b1 [CALCITE-7663] RelToSqlConverter generates ambiguous column
references when expanding SELECT * over a join with duplicate field names
cdcaaf62b1 is described below
commit cdcaaf62b196e91a530a18344e069f0d17f3c0d0
Author: Alexis Cubilla <[email protected]>
AuthorDate: Tue Jul 21 23:07:11 2026 -0300
[CALCITE-7663] RelToSqlConverter generates ambiguous column references when
expanding SELECT * over a join with duplicate field names
When a dialect's supportGenerateSelectStar() returns false for a join with
duplicate field names, the SELECT * expansion in SqlImplementor did not
alias
the expanded columns to their unique row-type field names. A sub-query
wrapping
such a join then exposed two identically named columns, making outer
references
ambiguous (e.g. PostgreSQL: column reference "id" is ambiguous). Alias each
expanded column to its unique row-type field name, mirroring the validator
path.
---
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 32 +++++++++++++++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 43 +++++++++++++++++++++-
2 files changed, 72 insertions(+), 3 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index c74da741d7..4b42df2eec 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -297,6 +297,18 @@ protected SqlCall as(SqlNode e, String alias, String...
fieldNames) {
return SqlStdOperatorTable.AS.createCall(POS, operandList);
}
+ /** Wraps a column reference in an {@code AS} alias when its intrinsic name
+ * differs from {@code name}, so that the column is emitted with
+ * {@code name}. */
+ private SqlNode renameAs(SqlNode fieldNode, String name) {
+ final String currentName = fieldNode instanceof SqlIdentifier
+ ? Util.last(((SqlIdentifier) fieldNode).names)
+ : null;
+ return name.equals(currentName)
+ ? fieldNode
+ : as(fieldNode, name);
+ }
+
/** Returns whether a list of expressions projects all fields, in order,
* from the input, with the same names. */
public static boolean isStar(List<RexNode> exps, RelDataType inputRowType,
@@ -2082,9 +2094,17 @@ private Builder builder(RelNode rel, Set<Clause>
clauses) {
newContext = aliasContext(aliases, qualified);
}
if (!dialect.supportGenerateSelectStar(rel.getInput(0))) {
+ // Rename each expanded column to its (unique) row-type field name.
+ // Otherwise a sub-query that wraps a join with duplicate field names
+ // (e.g. two columns named DEPTNO) would expose two identically named
+ // columns, which is ambiguous when referenced from an outer query.
+ final List<String> fieldNames = rel.getRowType().getFieldNames();
final List<SqlNode> expandedSelectList = new ArrayList<>();
for (int i = 0; i < newContext.fieldCount; i++) {
- expandedSelectList.add(newContext.field(i));
+ final SqlNode field = newContext.field(i);
+ expandedSelectList.add(i < fieldNames.size()
+ ? renameAs(field, fieldNames.get(i))
+ : field);
}
select.setSelectList(new SqlNodeList(expandedSelectList, POS));
}
@@ -2449,9 +2469,17 @@ SqlSelect maybeExpandStar(SqlSelect select) {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
final Context ctx = aliasContext(aliases, qualified);
+ // Rename each expanded column to its (unique) row-type field name.
+ // Otherwise a sub-query that wraps a join with duplicate field names
+ // (e.g. two columns named DEPTNO) would expose two identically named
+ // columns, which is ambiguous when referenced from an outer query.
+ final List<String> fieldNames =
expectedRel.getRowType().getFieldNames();
final List<SqlNode> expandedList = new ArrayList<>();
for (int i = 0; i < ctx.fieldCount; i++) {
- expandedList.add(ctx.field(i));
+ final SqlNode field = ctx.field(i);
+ expandedList.add(i < fieldNames.size()
+ ? renameAs(field, fieldNames.get(i))
+ : field);
}
return new SqlSelect(select.getParserPosition(),
(SqlNodeList) select.getOperandList().get(0),
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 801612976b..3fe9678233 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -9991,11 +9991,14 @@ private void checkLiteral2(String expression, String
expected) {
b.equals(b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO")))
.build();
+ // The join has two columns named DEPTNO; the second is aliased to its
+ // unique row-type field name (DEPTNO0) so the result never exposes two
+ // identically named columns (CALCITE-7663).
final String expected = "SELECT"
+ " \"EMP\".\"EMPNO\", \"EMP\".\"ENAME\", \"EMP\".\"JOB\","
+ " \"EMP\".\"MGR\", \"EMP\".\"HIREDATE\", \"EMP\".\"SAL\","
+ " \"EMP\".\"COMM\", \"EMP\".\"DEPTNO\","
- + " \"DEPT\".\"DEPTNO\","
+ + " \"DEPT\".\"DEPTNO\" AS \"DEPTNO0\","
+ " \"DEPT\".\"DNAME\", \"DEPT\".\"LOC\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "INNER JOIN \"scott\".\"DEPT\""
@@ -10003,6 +10006,44 @@ private void checkLiteral2(String expression, String
expected) {
relFn(relFn).dialect(NO_STAR_DIALECT).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7663";>[CALCITE-7663]</a>.
+ * A join with duplicate field names (two DEPTNO) wrapped by a FETCH becomes
a
+ * sub-query; when it is joined again, the sub-query must not expose two
+ * columns with the same name, otherwise the outer references to them are
+ * ambiguous (e.g. PostgreSQL: {@code column reference "deptno" is
ambiguous}).
+ * Each expanded column is aliased to its unique row-type field name. */
+ @Test void testNoSelectStarJoinWithDuplicateNamesAndFetchIsNotAmbiguous() {
+ 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();
+ final String expected = "SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\","
+ + " \"t\".\"JOB\", \"t\".\"MGR\", \"t\".\"HIREDATE\", \"t\".\"SAL\","
+ + " \"t\".\"COMM\", \"t\".\"DEPTNO\", \"t\".\"DEPTNO0\","
+ + " \"t\".\"DNAME\", \"t\".\"LOC\","
+ + " \"DEPT0\".\"DEPTNO\" AS \"DEPTNO1\","
+ + " \"DEPT0\".\"DNAME\" AS \"DNAME0\", \"DEPT0\".\"LOC\" AS \"LOC0\"\n"
+ + "FROM (SELECT \"EMP\".\"EMPNO\", \"EMP\".\"ENAME\", \"EMP\".\"JOB\","
+ + " \"EMP\".\"MGR\", \"EMP\".\"HIREDATE\", \"EMP\".\"SAL\","
+ + " \"EMP\".\"COMM\", \"EMP\".\"DEPTNO\","
+ + " \"DEPT\".\"DEPTNO\" AS \"DEPTNO0\", \"DEPT\".\"DNAME\",
\"DEPT\".\"LOC\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "INNER JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" =
\"DEPT\".\"DEPTNO\"\n"
+ + "FETCH NEXT 10 ROWS ONLY) AS \"t\"\n"
+ + "INNER JOIN \"scott\".\"DEPT\" AS \"DEPT0\""
+ + " ON \"t\".\"EMPNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "FETCH NEXT 5 ROWS ONLY";
+ relFn(relFn).withPostgresql().ok(expected);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7483";>[CALCITE-7483]
* RelToSqlConverter generates SELECT * despite
supportGenerateSelectStar</a>.
