Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-5171

NATURAL join and USING should fail if join columns are not unique

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.31.0
    • None
    • None

    Description

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

      Attachments

        Activity

          People

            julianhyde Julian Hyde
            julianhyde Julian Hyde
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: