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

In JOIN ... USING and NATURAL JOIN, common columns should be unambiguous

    XMLWordPrintableJSON

    Details

      Description

      In JOIN ... USING and NATURAL JOIN, common columns should be unambiguous. For example, the following 3 queries are valid on Oracle, and the last is invalid. In each case, DEPTNO is the common column.

      # DEPTNO is common to left and right side. It does not need to
      # be qualified with "EMP." or "DEPT."
      select deptno from emp natural join dept;
      select count(deptno) from emp join dept using (deptno);   
      select count(*) from emp natural join dept group by deptno;
      
      # In fact, it is illegal to qualify
      SQL> select dept.deptno from emp natural join dept;
      ORA-25155: column used in NATURAL join cannot have qualifier
      

      Here's a patch for join.iq:

      diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
      index 18a20fe8aa..4c1a5ab4c4 100644
      --- a/core/src/test/resources/sql/join.iq
      +++ b/core/src/test/resources/sql/join.iq
      @@ -36,6 +36,22 @@ on emp.deptno = dept.deptno or emp.ename = dept.dname;
       
       !ok
       
      +# Common column of NATURAL JOIN does not need to be qualified
      +select deptno from emp natural join dept;
      +!ok
      +
      +# Common column of JOIN ... USING does not need to be qualified
      +select count(deptno) from emp join dept using (deptno);
      +!ok
      +
      +# Common column of JOIN ... USING does not need to be qualified
      +select count(*) from emp join dept using (deptno) group by deptno;
      +!ok
      +
      +# Qualifying the common column is an error
      +select dept.deptno from emp natural join dept;
      +!error
      +
       # As an INNER join, it can be executed as an equi-join followed by a filter
      

      Currently, the first 3 queries wrongly give an error Column 'DEPTNO' is ambiguous, and the last query succeeds when it should fail.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Chunwei Lei Chunwei Lei
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 2.5h
                  2.5h