Re: GROUPING

>>>>> "Dean" == Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:

>> Consider that in both MSSQL 2014 and Oracle 12 the limit on the number
>> of arguments in a GROUPING() expression is ... 1.

Dean> Actually Oracle haven't quite followed the standard. They have 2
Dean> separate functions: GROUPING() which only allows 1 parameter, and
Dean> GROUPING_ID() which allows multiple parameters, and returns a
Dean> bitmask like our GROUPING() function. However, their
Dean> GROUPING_ID() function seems to return an arbitrary precision
Dean> number and allows an arbitrary number of parameters (well, I
Dean> tested it up 70 to prove it wasn't a 64-bit number).

True. It can handle more than 128 bits, even - I gave up trying after that.

So. Options:

1) change GROUPING() to return bigint and otherwise leave it as is.

2) change GROUPING() to return numeric.

3) change GROUPING() so that the result type varies with the number of
args. I don't see anything in the spec that actually forbids this - it
just says the return type is implementation-defined exact numeric.

A) in addition to any of the above, implement GROUPING_ID() as a simple
alias for GROUPING().

4) leave GROUPING() alone and add a separate GROUPING_ID() with a
different return type.

B) We don't currently have GROUP_ID() - does anyone want it?

*) any other ideas?

--
Andrew (irc:RhodiumToad)