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

GROUP_ID returns wrong result

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      We implemented the GROUP_ID() function in CALCITE-512 but we got the specification wrong, and it returns the wrong result.

      GROUP_ID is not in the SQL standard. It is implemented only by Oracle.

      I mistakenly believed that GROUP_ID() is equivalent to GROUPING_ID(g1, ..., gn) (in a query with GROUP BY g1, ..., gn). In fact, GROUP_ID is useful only if you have duplicate grouping sets. If grouping sets are distinct, GROUP_ID() will always return zero.

      Example 1

      SELECT deptno, job, GROUP_ID() AS g
      FROM Emp
      GROUP BY ROLLUP(deptno, job)
      
          DEPTNO JOB		      G
      ---------- --------- ----------
      	10 CLERK	      0
      	10 MANAGER	      0
      	10 PRESIDENT	      0
      	10		      0
      	20 CLERK	      0
      	20 ANALYST	      0
      	20 MANAGER	      0
      	20		      0
      	30 CLERK	      0
      	30 MANAGER	      0
      	30 SALESMAN	      0
      	30		      0
      			      0
      

      produces grouping sets (deptno, job), (deptno), (). These are distinct, so GROUP_ID() is 0 for all rows.

      Example 2

      SELECT deptno, GROUP_ID() AS g
      FROM Emp
      GROUP BY GROUPING SETS (deptno, (), ());
      
          DEPTNO	    G
      ---------- ----------
      	10	    0
      	20	    0
      	30	    0
      		    0
      		    1
      

      As you can see, the grouping set () occurs twice. So there is one row in the result for each occurrence: the first occurrence has g = 0; the second has g = 1.

      In my fix for CALCITE-1069, I will change GROUP_ID() to always return 0. This is wrong, but nevertheless closer to the required behavior.

      Attachments

        Issue Links

        Activity

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

          People

            donnyzone Feng Zhu
            julianhyde Julian Hyde
            Votes:
            0 Vote for this issue
            Watchers:
            7 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 - 5h 20m
                5h 20m

                Slack

                  Issue deployment