[CALCITE-4697] GROUPING_ID may be distinct from GROUPING

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Not A Problem
• Assignee: Unassigned
• Reporter: Will Noble
• Created: 16/Jul/21 18:46
• Updated: 15/Oct/21 01:54
• Resolved: 16/Jul/21 21:10

Description

See CALCITE-1652.

I'm having trouble finding the exact text of the SQL Standard 2014, so perhaps this bug is a dud, but I believe these two agg functions may have different return values. I think we can all agree on the behavior of the GROUPING_ID function, which returns a bitmap where the {{n}}th bit is high iff the {{n}}th argument is grouped, but according to at least this documentation, the GROUPING function returns "1 if the values in this row are the results of aggregating over (possibly) multiple values of that column, and 0 if they are not". Also note that the author goes on to say "Does the above table make sense? I'm not sure I'm understanding the GROUPING operation correctly", so this claim must be verified.

If true, that means these functions are not equivalent because GROUPING would return essentially a boolean value indicating if all the argument columns are grouped, whereas GROUPING_ID would return a more nuanced integer value indicating if each of the arguments are grouped, and therefore GROUPING_ID should not be deprecated and should not be treated as an alias for GROUPING.