Julian Hyde created CALCITE-5171: ------------------------------------ Summary: 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
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)