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

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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Chunwei Lei Chunwei Lei
            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

                Slack

                  Issue deployment