Re: GROUPING

On 21 May 2015 at 17:15, David Fetter <david(at)fetter(dot)org> wrote:
> On Thu, May 21, 2015 at 04:19:27PM +0100, Andrew Gierth wrote:
>> >>>>> "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.
>
> 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