feat(isthmus): support GROUPING / GROUPING_ID via CASE over the grouping-set-index column

[nielspardon]

Background

Isthmus already supports GROUPING SETS / ROLLUP / CUBE and GROUP_ID() (see #527 / #565). The one remaining piece of #71 is the SQL GROUPING(expr) / GROUPING_ID(expr, …) aggregate functions, which have no direct Substrait equivalent (there is no grouping function in substrait/extensions/*.yaml).

Because of this, TpcdsQueryTest runs TPC-DS queries 27, 36, 70, 86 via rewritten alternate forms (27a/36a/70a/86a.sql, added in #513) that strip out GROUPING. The original queries do not convert.

Why not add a Substrait grouping function?

That was proposed upstream in substrait#824 but stalled without consensus and was auto-closed as stale. The maintainer-preferred alternative there does not require any spec change: GROUPING can be derived from the extra i32 grouping-set-index ("disambiguation") column that an Aggregate with more than one grouping set already emits, using a CASE expression. See tokoko's worked examples.

Proposed approach (forward: Calcite → Substrait)

Extend the existing project-wrapper in SubstraitRelVisitor.visit(Aggregate) (which already substitutes LITERAL_AGG literals and remaps GROUP_ID onto the disambiguation column). Add a third case: replace each Calcite GROUPING/GROUPING_ID call position with a CASE over the grouping-set-index column, where each arm is the bit/bitmask value computed from which grouping sets contain the referenced field(s).

Open sub-problems

1. Reverse direction (Substrait → Calcite GROUPING) — reconstructing a GROUPING call from the CASE/index pattern. This is what blocked the earlier attempt.
2. Duplicate grouping sets edge case (flagged in the PR discussion).

Acceptance

• GROUPING/GROUPING_ID convert Calcite → Substrait (forward), with round-trip tests.
• Ideally reverse direction too; if not feasible initially, scope it as a follow-up.
• Remove 27/36/70/86 from alternateForms in TpcdsQueryTest and delete the *a.sql files once the originals pass.

Remaining work split out from #71.

[alexandrefimov]

Impala's Calcite planner emits GROUPING and GROUPING_ID as aggregate calls, and they hit exactly this gap. Against isthmus 0.101.0 and calcite-core 1.42.0, converting the logical plan with SubstraitRelVisitor.convert(RelRoot, ConverterProvider):

select int_col, grouping(int_col) as g, count(*) as c
from t group by rollup(int_col)

UnsupportedOperationException: Unable to find binding for call GROUPING($0)

The query shape does not matter: plain grouping sets ((int_col), ()) and cube(int_col, bigint_col) fail at the same place, and grouping_id(int_col, bigint_col) gives Unable to find binding for call GROUPING_ID($0, $1). Both functions arrive as an AggregateCall on the LogicalAggregate, so it is the aggregate path rather than a scalar one. Same result on 0.100.0.

Everything else around grouping sets converts for us: rollup, cube and multi-set grouping all come through unchanged, and these two functions are the only failures.

So deriving GROUPING from the grouping-set-index column, as proposed here, would close the whole remaining gap on our side.

[alexandrefimov]

The building block this rests on needs a look first. GROUPING derived from the grouping-set-index column inherits whatever that column is: its type comes back as an i64 rather than the i32 the spec gives it (#1162), and until #1161 the grouping columns of an aggregate over several sets changed order on the way through Calcite, so the index column was not where the plan said it was.