[
https://issues.apache.org/jira/browse/CALCITE-5171?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17543704#comment-17543704
]
Julian Hyde edited comment on CALCITE-5171 at 5/29/22 7:03 PM:
---------------------------------------------------------------
I plan to fix this at the same time as CALCITE-35 and CALCITE-5153; see the PR
for the former.
was (Author: julianhyde):
I plan to fix this at the same time as CALCITE-35 and CALCITE-3153; see the PR
for the former.
> NATURAL join and USING should fail if join columns are not unique
> -----------------------------------------------------------------
>
> Key: CALCITE-5171
> URL: https://issues.apache.org/jira/browse/CALCITE-5171
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
> Priority: Major
>
> NATURAL join and USING should fail if join columns are not unique. For
> example:
> {code:sql}
> select e.ename, d.dname
> from dept as d
> natural join (select ename, sal as deptno, deptno from emp) as e;
> {code}
> fails in Postgres with error
> {noformat}
> ERROR: common column name "deptno" appears more than once in right table
> {noformat}
> A similar query with {{USING}} fails with the same error:
> {code:sql}
> select e.ename, d.dname
> from dept as d
> join (select ename, sal as deptno, deptno from emp) as e using (deptno);
> {code}
> And reversed:
> {code:sql}
> select e.ename, d.dname
> from (select ename, sal as deptno, deptno from emp) as e
> join dept as d using (deptno);
> {code}
> gives the reverse message:
> {noformat}
> ERROR: common column name "deptno" appears more than once in left table
> {noformat}
> The error only occurs if the duplicate column is referenced. The following
> query has a duplicate {{hiredate}} column but Postgres considers it valid:
> {code:sql}
> select e.ename, d.dname
> from dept as d
> join (select ename, sal as hiredate, deptno from emp) as e using (deptno);
> {code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
