Re: GROUPING

On 21 May 2015 at 17:15, David Fetter <david@fetter.org> wrote:
> On Thu, May 21, 2015 at 04:19:27PM +0100, Andrew Gierth wrote:
>> >>>>> "Dean" == Dean Rasheed <dean.a.rasheed@gmail.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.
>
> Seems cheap and reasonable.  Making sure people know that GROUPING can
> be called multiple times seems like another cheap and reasonable
> measure.
>

Yeah, seems reasonable. The lack of any bitwise operations on numeric
makes it pretty-much useless in this context.

Regards,
Dean