Add GROUPING support to Presto

[raghavsethi]

No description provided.

[cpcloud]

FWIW, the standard specifies a GROUPING(<column reference>) that looks like it's supposed to do this. It's a bit vague.

Postgres implements this as GROUPING(<col1>, <col2>, ..., <colN>) (0 if the <colN> is contained and 1 if it is not contained in the aggregation level, which is the inverse of what Hive does)

http://www.postgresql.org/docs/9.5/static/functions-aggregate.html

[cpcloud]

The standard section is 4.16 in Part 2 of SQL:2011

[raghavsethi]

@cpcloud We will almost certainly implement this as per the spec.

[martint]

It's specified in more detail in section 4.9:

10. If a <grouping operation> is specified, then:
    a) Let T be the aggregation query of <set function specification> that contains <grouping operation>. Each <column reference> shall reference a grouping column of T.
    b) The declared type of the result is exact numeric with an implementation-defined precision and a scale of 0 (zero).
    c) If more than one <column reference> is specified, then let N be the number of <column reference>s and let CR_i, 1 (one) ≤ i ≤ N, be the i-th <column reference>.
    GROUPING ( CR_1, ..., CR_N-1, CR_N )
    is equivalent to:
    CAST ( ( 2 * GROUPING ( CR_1, ..., CR_N-1 ) + GROUPING ( CR_N ) ) AS IDT )
    where IDT is the implementation-defined declared type of the result.

[martint]

For completeness, from section 4.16.2:

The grouping operation is of the form GROUPING(<column reference>). The result of such an invocation is 1 (one) in the case of a row whose values are the results of aggregation over that <column reference> during the execution of a grouped query containing CUBE, ROLLUP, or GROUPING SETS, and 0 (zero) otherwise.

[cpcloud]

Nice, thanks for posting that.

[petroav]

I'd like to start work on this if no one else is looking into it.

[petroav]

I spent some time trying to understand the 4.16.2 section of the spec quoted by Martin above and I'd like to share my findings for the benefit of others who might want to verify that my implementation of GROUPING() conforms to the spec.

The phrase "aggregation over that <column reference>" means that that column is NOT included in the grouping columns and is instead part of the "grouping window", i.e. it is "aggregated over". On first reading I though that "aggregation over" meant that the <column reference> had to be part of the grouping columns.

Both Teradata and Postgres implement the GROUPING() function in this way so I think this is the right interpretation.

[parekhparth]

@petroav, any updates here?

[petroav]

@parekhparth yes I'm going to put up a Teradata internal review tomorrow. The patch was re-written to use a synthetic type so that the arguments are not serialized/de-serialized at every invocation.

[BradRuderman]

@petroav How did the review go?

[martint]

This is close, but it's still being worked on: #7712

[petroav]

Closing this since the feature got merged #8043.