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

Doc of SqlGroupingFunction contradicts its behavior

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.24.0
    • Component/s: None
    • Labels:
      None

      Description

      Currently doc of SqlGroupingFunctions says:

      /**
       * The {@code GROUPING} function.
       *
       * <p>Accepts 1 or more arguments.
       * Example: {@code GROUPING(deptno, gender)} returns
       * 3 if both deptno and gender are being grouped,
       * 2 if only deptno is being grouped,
       * 1 if only gender is being groped,
       * 0 if neither deptno nor gender are being grouped.

      But its behavior in agg.iq is as below:

      # GROUPING in SELECT clause of CUBE query
      select deptno, job, count(*) as c, grouping(deptno) as d,
        grouping(job) j, grouping(deptno, job) as x
      from "scott".emp
      group by cube(deptno, job);
      +--------+-----------+----+---+---+---+
      | DEPTNO | JOB       | C  | D | J | X |
      +--------+-----------+----+---+---+---+
      |     10 | CLERK     |  1 | 0 | 0 | 0 |
      |     10 | MANAGER   |  1 | 0 | 0 | 0 |
      |     10 | PRESIDENT |  1 | 0 | 0 | 0 |
      |     10 |           |  3 | 0 | 1 | 1 |
      |     20 | ANALYST   |  2 | 0 | 0 | 0 |
      |     20 | CLERK     |  2 | 0 | 0 | 0 |
      |     20 | MANAGER   |  1 | 0 | 0 | 0 |
      |     20 |           |  5 | 0 | 1 | 1 |
      |     30 | CLERK     |  1 | 0 | 0 | 0 |
      |     30 | MANAGER   |  1 | 0 | 0 | 0 |
      |     30 | SALESMAN  |  4 | 0 | 0 | 0 |
      |     30 |           |  6 | 0 | 1 | 1 |
      |        | ANALYST   |  2 | 1 | 0 | 2 |
      |        | CLERK     |  4 | 1 | 0 | 2 |
      |        | MANAGER   |  3 | 1 | 0 | 2 |
      |        | PRESIDENT |  1 | 1 | 0 | 2 |
      |        | SALESMAN  |  4 | 1 | 0 | 2 |
      |        |           | 14 | 1 | 1 | 3 |
      +--------+-----------+----+---+---+---+
      (18 rows)
      

       

      The doc needs to be rectified thus to be consistent with query result and the behavior of Hive[1] and PostgreSQL[2]

       [1] https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup?spm=ata.13261165.0.0.528c6dfcXalQFy#EnhancedAggregation,Cube,GroupingandRollup-Groupingfunction

       [2] https://www.postgresql.org/docs/9.5/functions-aggregate.html 

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jinxing6042@126.com Jin Xing
                Reporter:
                jinxing6042@126.com Jin Xing
              • Votes:
                0 Vote for this issue
                Watchers:
                4 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 - 1h 20m
                  1h 20m