[
https://issues.apache.org/jira/browse/CALCITE-5171?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-5171.
----------------------------------
Fix Version/s: 1.31.0
Resolution: Fixed
Fixed in
[231008a1|https://github.com/apache/calcite/commit/231008a16d8a3f13d0ef3601dd5a809f62a3803b].
> 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
> Fix For: 1.31.0
>
>
> 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)
