[CALCITE-5171] NATURAL join and USING should fail if join columns are not unique - ASF JIRA

XML

Word

Printable

JSON

NATURAL join and USING should fail if join columns are not unique. For example:

select e.ename, d.dname
from dept as d
natural join (select ename, sal as deptno, deptno from emp) as e;

fails in Postgres with error

ERROR: common column name "deptno" appears more than once in right table

A similar query with USING fails with the same error:

select e.ename, d.dname
from dept as d
join (select ename, sal as deptno, deptno from emp) as e using (deptno);

And reversed:

select e.ename, d.dname
from (select ename, sal as deptno, deptno from emp) as e
join dept as d using (deptno);

gives the reverse message:

ERROR: common column name "deptno" appears more than once in left table

The error only occurs if the duplicate column is referenced. The following query has a duplicate hiredate column but Postgres considers it valid:

select e.ename, d.dname
from dept as d
join (select ename, sal as hiredate, deptno from emp) as e using (deptno);

causes

CALCITE-5253 NATURAL join and USING should fail if join columns are not unique - expression validation partially broken