[jira] [Assigned] (CALCITE-5171) NATURAL join and USING should fail if join columns are not unique

["Julian Hyde (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-5171?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde reassigned CALCITE-5171:
------------------------------------

    Assignee: Julian Hyde

> 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)