Support Grouping functions with Group By CUBE/ROLLUP/GROUPING SETS

[mingmwang]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.

PostgreSQL, SparkSQL and Oracle support using GROUPING functions to specify the null is from subtotal or from original
data.
https://www.postgresql.org/docs/15/functions-aggregate.html#FUNCTIONS-HYPOTHETICAL-TABLE

Databricks SparkSQL
https://docs.databricks.com/sql/language-manual/functions/grouping.html

Oracle
https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets#grouping

Describe the solution you'd like

Describe alternatives you've considered

Additional context

[mingmwang]

I'm working on it now.

[l1t1]

where is the document of these features?

[bgjackma]

take

[bgjackma]

I don't think this works as a aggregate function in the physical plan. It depends on the grouping rather than the data, so the current abstraction doesn't have access to the necessary information.

I think this case (along with GROUPING_ID, any others?) will need special handling in GroupHashedAggregateStream.

[alamb]

Note there is a PR with a proposed implementation from @JasonLi-cn in #10208

[bgjackma]

Thanks for the heads up, that's helpful however I think I have a slightly nicer solution that doesn't force changes on existing accumulators.

[eejbyfeldt]

Note that @mingmwang (the author of this ticket) had an alternative approach here: #5749 but it seems like it was never pushed above the finish line. It is based on how it is implemented in Spark.

What is good with that approach is that it also helps address other issues in the current implementation of grouping sets. For example the current implementation will produce incorrect results when there are null values in the actual columns. I also looked into creating PR for only fixing that bug in a similar way to #5749 but need to spend some more time on it.