WL#1979: GROUPING() function

Affects: WorkLog-3.4   —   Status: Un-Assigned   —   Priority: Medium

The function GRIUPING() is used with ROLLUP/CUBE 
GROUPING(column) returns 1 if the column's value in the row was generated by 
rollup/cube: 
 
The SQL:2003 standard specifies: 
 
The grouping operation is of the form GROUPING(<column reference>). The 
result of such an invocation is 1 (one) in the case of a row whose 
values are the results of aggregation over that <column reference> 
during the execution of a grouped query containing CUBE, ROLLUP, or 
GROUPING SET, and 0 (zero) otherwise. 
 
The function exists in MSSQL and Oracle: 
 
http://www.orafusion.com/art_agg.htm 
http://www.winnetmag.com/SQLServer/Articles/ArticleID/5104/pg/2/2.html 
 
OLAP feature. 

Feature requests:
BUG#3156 GROUPING() Feature request
 
Peter suggests:

* The data type of GROUPING is INTEGER.
The standard only requires that it be exact numeric with scale 0.
DB2 says "small integer".
SQL Server says "tinyint".
But it's easier to remember if most functions return integers.

* GROUPING(x) is an error if x isn't a column in GROUP BY.
I suppose the error would be similar to
ERROR 1055 (42000): x isn't in GROUP BY
but I doubt that it should depend on whether
sql_mode=ONLY_FULL_GROUP_BY.

* GROUPING is not a new reserved word.
But "create function grouping ..." will cause the
note-level warning 1585
"This function 'grouping' has the same name as a native function".

* GROUPING may only be in a select list.
Therefore it may not be in a HAVING clause or an ORDER BY clause.
And it may not be in a subquery which is inside the select list.
But it may be in a scalar function which is inside the select list.

* The HLD quotation (which says return 1
"in the case of a row whose values are the results of
aggregation") is too vague, because GROUPING can produce
different values (0 or 1) for different columns in the same row.
[...] Perhaps:
"If the column value is not null, return 0.
If the column value is null because the original is null, return 0.
If the column value is null because ROLLUP produced it, return 1."