You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (Jira)" <ji...@apache.org> on 2026/03/26 18:34:00 UTC
[jira] [Comment Edited] (CALCITE-7453) Preserve duplicate output names through star expansion and report resulting ambiguity correctly
[ https://issues.apache.org/jira/browse/CALCITE-7453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18068795#comment-18068795 ]
Julian Hyde edited comment on CALCITE-7453 at 3/26/26 6:33 PM:
---------------------------------------------------------------
Yes.
Also, you say that
{code}select * from (select deptno as num, empno as num from emp){code}
should succeed. I disagree. It currently fails and I think that is correct behavior. (Try it on other databases.)
Calcite's rule (maybe you can find the relevant Jira case) is that subquery columns need to be unique if they are referenced. (Postgres, I believe, requires columns to be unique even if not referenced.)
was (Author: julianhyde):
Yes.
Also, you say that
{code}select * from (select deptno as num, empno as num from emp){code}
should succeed. I disagree. It currently fails and I think that is correct behavior. (Try it on other databases.)
> Preserve duplicate output names through star expansion and report resulting ambiguity correctly
> -----------------------------------------------------------------------------------------------
>
> Key: CALCITE-7453
> URL: https://issues.apache.org/jira/browse/CALCITE-7453
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.42.0
> Reporter: Nirmal Govindaraj
> Assignee: Nirmal Govindaraj
> Priority: Major
> Labels: pull-request-available
>
> h2. Issue
> Two queries exposed the same underlying problem from opposite directions.
> {code:java}
> select * from (select deptno as num, empno as num from emp){code}
> should work, but failed with exception
> {code:java}
> At line 1, column 8: Column 'NUM' is ambiguousorg.apache.calcite.runtime.CalciteContextException: At line 1, column 8: Column 'NUM' is ambiguous at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:486) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:511) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:960) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945) at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:6151) at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:531) at org.apache.calcite.sql.validate.SqlValidatorImpl.findTableColumnPair(SqlValidatorImpl.java:4476) at org.apache.calcite.sql.validate.SqlValidatorImpl.isRolledUpColumn(SqlValidatorImpl.java:4523) at org.apache.calcite.sql.validate.SqlValidatorImpl.expandStar(SqlValidatorImpl.java:715) at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:481) {code}
> {code:java}
> select deptno from (select deptno, * from dept){code}
> should fail as ambiguous, but did not
> The first query failed because Calcite lost exact field identity after star expansion when a subquery exposed multiple columns with the same visible name. Later validation paths such as rolled-up-column checks re-resolved the expanded identifier by name, and that name-based lookup could bind to the wrong field. In practice this surfaced via isRolledUp, but the underlying issue was broader: after star expansion, two distinct source fields had collapsed to the same visible identifier and Calcite no longer knew which one it was looking at.
> The second query did not fail because star expansion was uniquifying output aliases. That meant Calcite was not preserving the real visible row type of the subquery. Once the duplicate column name was rewritten to a synthetic unique name, outer resolution no longer saw an ambiguity.
> These are two sides of the same problem: Calcite was not preserving duplicate visible output names through star, and it also did not retain exact field identity when duplicate names survived star expansion.
> h2. Solution
> This change fixes the problem at the root:
> * Star expansion now preserves visible output names exactly, including duplicates. For duplicate-name fields produced by star expansion, Calcite records exact source-field metadata during validation.
> * Later consumers use that exact metadata where name-based lookup is no longer sufficient
> * Ambiguous references are now reported where the preserved output row type is genuinely ambiguous.
> h2. Notes
> # EXCLUDE / EXCEPT duplicate-name semantics are not being defined by this PR and can be discussed separately in follow-up work.
> # Existing pivot.iq coverage includes cases that intentionally generate same-name aliases and currently fail, but after preserving duplicate output names the expected behavior becomes less clear and raises a broader design question about how pivot-generated aliases should behave. That discussion is better handled in a follow-up PR.
> h2. Compatibility
> This will change visible output column names compared to previous Calcite behavior
--
This message was sent by Atlassian Jira
(v8.20.10#820010)