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)
