[ISTHMUS] [TPCDS] Support Grouping

[hbutani]

For example:

• in q27 has the expression: group by rollup (i_item_id, s_state)
• in q36 additionally has rank() over (partition by grouping(i_category)+grouping(i_class)..)

Queries in tpcds having this issue: [27, 36, 86]

How to reproduce: Add a test to TpcdsQueryNoValidation like this:

@ParameterizedTest
  @ValueSource(ints = {27})
  public void tpcdsQ27(int query) throws Exception {
    testQuery(query);
  }

On fixing, please move the fixed queries from the tpcdsFailure list to the tpcdsSuccess list

[jacques-n]

In https://github.com/substrait-io/substrait/pull/240, @guykhazma suggested that this should be considered a function. I'm not convinced that is the case. @hbutani , thoughts?

[hbutani]

• Both Oracle(Grouping, Grouping_id) and Microsoft(Grouping_id) specify these as functions.
• Calcite treats them as a special class of Aggregate functions(GroupingId, Grouping)
• But Spark treats them as special Expressions classes
• Did a cursory search in SQL 2003 specification. I couldn't find anything in there to resolve this.
  • Will try to find an expert to resolve what the spec says about this, if anything... If anybody has an idea please respond.

My two cents: nice to have separate expression types for these like Spark; but I cannot find a reason for them not to be functions.

[julianhyde]

The standard defines GROUPING and GROUPING_ID, which are pretty much synonyms. Oracle also defines GROUP_ID.

To a first approximation, you can treat them as aggregate functions (in that you can only use them in a GROUP BY query); their value is constant for the group (i.e. what will become a record emitted from the GROUP BY) but independent of the actual rows that go into the group.

I think it's overkill to treat them as a special expression type. Aggregate functions will suffice.

[julianhyde]

ROLLUP (along with CUBE, and GROUP BY DISTINCT) is not really a function but a macro that gives you a shorthand for writing grouping sets. I recommend just supporting GROUPING SETS.

[mbasmanova]

ROLLUP (along with CUBE, and GROUP BY DISTINCT) is not really a function but a macro that gives you a shorthand for writing grouping sets. I recommend just supporting GROUPING SETS.

+1. There is a nice explanation of how ROLLUP and CUBE are just shortcuts for GROUPING SETS in the Presto documentation: https://prestodb.io/docs/current/sql/select.html#group-by-clause