Bug #46053 Adding grouping() function to support rollup identification for subtotals.
Submitted: 8 Jul 2009 18:15 Modified: 9 Mar 2017 17:24
Reporter: shane adams (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Chaithra Gopalareddy CPU Architecture:Any
Tags: Contribution, grouping, rollup
Triage: Needs Triage: D5 (Feature request)

[8 Jul 2009 18:15] shane adams
Description:
I am in need of the grouping() function as implemented in Oracle.  

The following URL describes the feature in detail.

http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10736/aggreg.htm#i1007434

The succinct description:
 
"Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP.  That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0."

How to repeat:
 CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

 insert into t1 values ( 1,2,3), (1,2,3),(1,2,3),(1,2,3),( 1,2,3), (1,2,3),(1,2,3);

mysql> select grouping(a), a, sum(b) from t1 group by a with rollup;
+-------------+------+--------+
| grouping(a) | a    | sum(b) |
+-------------+------+--------+
|           0 |    1 |     14 |
|           1 | NULL |     14 |
+-------------+------+--------+
2 rows in set (0.00 sec)

Suggested fix:
I have gone and implemented this in the code.  It's a fairly small change in sql_select.cc and item_func.*  It will be my first contribution so I'm still figuring everything out.  I discussed the implementation with Sinisa Milivojevi who got me started.
[8 Jul 2009 23:50] shane adams
# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: adamsch1@yahoo.com-20090708234218-4fw8ouhyy7vg81e0
# target_branch: bzr+ssh://bazaar.launchpad.net/%7Emysql/mysql-\
#   server/mysql-5.1/
# testament_sha1: 58dc898b61aa531cd35e4c2f51b2164d12098f0f
# timestamp: 2009-07-08 23:45:12 +0000
# base_revision_id: joro@sun.com-20090708131116-kyz8iotbum8w9yic
# 
# Begin patch
=== modified file 'sql/item_create.cc'
--- sql/item_create.cc	2009-03-24 18:27:33 +0000
+++ sql/item_create.cc	2009-07-08 23:42:18 +0000
@@ -1079,6 +1079,18 @@
   virtual ~Create_func_greatest() {}
 };
 
+class Create_func_grouping : public Create_func_arg1
+{
+public:
+  virtual Item *create(THD *thd, Item *arg1);
+
+  static Create_func_grouping s_singleton;
+
+protected:
+  Create_func_grouping() {}
+  virtual ~Create_func_grouping() {}
+};
+
 
 class Create_func_hex : public Create_func_arg1
 {
@@ -3561,6 +3573,15 @@
 }
 
 
+Create_func_grouping Create_func_grouping::s_singleton;
+
+Item*
+Create_func_grouping::create(THD *thd, Item *arg1 )
+{
+  return new (thd->mem_root) Item_func_grouping(arg1);
+}
+
+
 Create_func_hex Create_func_hex::s_singleton;
 
 Item*
@@ -4810,6 +4831,7 @@
   { { C_STRING_WITH_LEN("GET_LOCK") }, BUILDER(Create_func_get_lock)},
   { { C_STRING_WITH_LEN("GLENGTH") }, GEOM_BUILDER(Create_func_glength)},
   { { C_STRING_WITH_LEN("GREATEST") }, BUILDER(Create_func_greatest)},
+  { { C_STRING_WITH_LEN("GROUPING") }, BUILDER(Create_func_grouping)},
   { { C_STRING_WITH_LEN("HEX") }, BUILDER(Create_func_hex)},
   { { C_STRING_WITH_LEN("IFNULL") }, BUILDER(Create_func_ifnull)},
   { { C_STRING_WITH_LEN("INET_ATON") }, BUILDER(Create_func_inet_aton)},

=== modified file 'sql/item_func.h'
--- sql/item_func.h	2009-07-03 10:36:04 +0000
+++ sql/item_func.h	2009-07-08 23:42:18 +0000
@@ -55,7 +55,7 @@
                   NOW_FUNC, TRIG_COND_FUNC,
                   SUSERVAR_FUNC, GUSERVAR_FUNC, COLLATE_FUNC,
                   EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP, UDF_FUNC,
-                  NEG_FUNC, GSYSVAR_FUNC };
+                  NEG_FUNC, GSYSVAR_FUNC, GROUPING_FUNC };
   enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL,
                        OPTIMIZE_EQUAL };
   enum Type type() const { return FUNC_ITEM; }
@@ -776,6 +776,21 @@
   const char *func_name() const { return "greatest"; }
 };
 
+/*
+  Objects of this class are used as markers in ROLLUP queries
+  they get replaced by a literal int of 1 or 0 depending on 
+  if the row is a subtotal 
+*/
+class Item_func_grouping :public Item_int_func
+{
+  int level;
+public:
+  Item_func_grouping(Item *a) :Item_int_func(a), level(0) {}
+  Item *column() { return args[0]; }
+  const char *func_name() const { return "grouping"; }
+  enum Functype functype() const   { return GROUPING_FUNC; }
+  longlong val_int() { return level; }
+};
 
 /* 
   Objects of this class are used for ROLLUP queries to wrap up 

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc	2009-07-03 10:43:54 +0000
+++ sql/sql_select.cc	2009-07-08 23:42:18 +0000
@@ -15766,6 +15766,36 @@
               return 1;
 	    item->maybe_null= 1;		// Value will be null sometimes
             null_item->result_field= item->get_tmp_table_field();
+
+            /*
+              Look for grouping(col) entries that match the current elemnt
+              that will be set to null.  Replace it with the current level
+              we simply substitute grouping(col) entries for subtotal rows
+            */
+            {
+              List_iterator<Item> group_it(rollup.fields[pos]);
+              Item *group_item;
+              while ((group_item= group_it++))
+              {
+                if (group_item->type() == Item::FUNC_ITEM &&
+                     ((Item_func*)group_item)->functype() ==
+                        Item_func::GROUPING_FUNC )  {
+                  Item_func_grouping *gitem = (Item_func_grouping*)group_item;
+                  Field *b = item->get_tmp_table_field();
+                  Field *a = gitem->column() ?
+                    gitem->column()->get_tmp_table_field() : 0 ;
+                  if( a && b && a->eq(b) )
+                  {
+                    /* Found one - this col is a subtotal */
+                    Item_int *inty = new (thd->mem_root)Item_int((int32)1,
+                      MY_INT32_NUM_DECIMAL_DIGITS);
+                    group_it.replace(inty);
+                    break;
+                  }
+                }
+              }
+            }
+
             item= null_item;
 	    break;
 	  }

# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVflaHgABAR/gEBVdABRf///
/+ffqr////tgCQ77bve95tGgArLbAA0ttABQQlEJoIyaptqnqfqmm9NU/VM9R6pphM1HqHogB6mh
6noI9TaDN6pGeqD0gaNAAAAAGgAAAAAEVPR6Saepo9QA0B6gAAADQAAAAASmkFT01PJpRjCZTTyD
JM0j1PUaZPUGQAA0NAHDQyaaGmRoaZGQZGRoZAYmjJoAyZGIYSSCGp6EwQCYjQ0EynoiHqYEPUY1
NqZDQPKUNkBU1+VXMxrVVVtxFLEeaIzR9vpuP6pLJ3a6+zS6x71aM4HioUPnW7s5+OlVuYaXVlyc
bc8HVgaIJqFG/xbD3bc6aEMCpycKh55OHD5y4QraOjCEw9+fNRC8ZCOjY5IEJEETZUOBRCNzH5qu
jZr2v1PqL7Ms/760VT+6WUcxLkDA24ySSGSOC1d6hrU7Oy48eNQMkI73POM4LemPFTBiQAIkVlQE
lYEH4mZ5QlrRBHPPOT0GShxmbA7REgu2TQpAqEzJQygUDNCOQeKJgJisvCpEAoJT0iRhQY51jiK0
pJUx8mq2yzQzzVFXpF9Gy3qukuv8ZbwtijL9ccrHFGGJZIV0iSFeeAlkvPG7CsmYykVU4QXz+Deu
x2Qu4r81rzDcmSGRGU0wXqxjG1E38sQNpMxlZ2zLF7vJDDwXS8i28XHjt/CNuigvx60RkKVLm4uR
GKd9NNFQmuoydM9qOPufiHD841aDH6TM2l/24+1kK6X0zbxOg2UzPAYqtANa2iHGqodGLZmDeYFy
X8roUgGPL2PflswIpktEUQ5sKVj+StKt/8doTqrX80XBpeZYdskIdBHPtg9VFH1C5OAHicLUPHCg
bHR32D5+3WUpL456DMhZgB0hPuB/+uBAeQP2QMBX97mZ17mZ52Z7AVhFcqx6Pe5ZPjN7yl2KrFlv
VFLUVkR5AimSa9rslyEOmIxVqnqG/cLlNYeKR3qwRYMOSwKy+xxn206IsPuQECopEOERcU0szyDg
HUncMajSfGyCsraqeBUBNFLU+EJn+aKqQt1COorvoLJsbRjSMs2gqHpw44Lh/7WqPLTfpk119r7p
NVnBxHBPJCKSNPmUTGgYxe5ul5YZBIqMxqMhhiRM4n7VkIaq6WZ9p7raOXdjeYoxJ5142vsqKSHX
OwhYmI6BtQqLzq9xrXWdxgdpzMd9wWpzusearnDmLy+FEeRSatsSpFB1lBuDL45yKiUWlgulJbbb
bdu0mda/Hr6C0mbCV0TWPo0o7DUWGHUI1vH6ior05JGp5QXsm2CKB1pARS+qkoMduciSqxKS89Sv
EavoVejC6xK8AstLH3xqeKlqRRqKS4pxIDFFqncUGaLFsMTaUWlD9s4EnBnRTRTQHYGo5c+hxPbS
tdEDMtWTmj3jlbYWVM2dHRUlWjFrLS0hnkrluHwTgGpLfqDn5vi4qrg+HsQVP8jtVaRn1PiMKKCj
On5oKrEap3Zmt7Du1K7RF74GwIXdJGQ6sUDkcTKSJH27ekPzKa/5u0ShhFzt/1Dkb0cDaMUuuntl
KX//S9hWwFLJFcUUa35BNz6flHdunkN/hvdD5CxyCibdeZvG86zZRZj3ed4+bO2Y0jsOkkCEkQYb
utFoA49anqAc4R753meJOWJIBUjFKupU52VNbGBlPufEPvkXFqr/39R2IZZbz7zE+wWhIjWfl8Iv
f9vsI7kR0AqdK/LZiI+7u3m+PBtidIZYCVhX6mopk6VJqOJyPyP6rqOGv3mo3HvLrDrOw8+gpDor
+l4bdq5leJx/eL4MLkFBAYGVhpHXBEcAbD+5+gvgfmSKRr7peCRH34UvuHubqPd2NagII6Yd69Rx
C0ZlQcx6e48A96t7jzO04m44GZFKNw6taBGY+zUkVHAvf2BKBzxYkefUPhQaByq41wx/PzlSudRU
yktgh/mcx1vNtZQYGcqMjDOrQ8jQbL3F45AwQFoySVp5EVRf3ZG8hfL+ygd56w4okuEkgMSw6Jof
UqB6TZ/a88H6syn6rqwtRLTaQZ1IxnIoYOA4RtHk0tf6FRKfI6HObclrPYRQ44HJxzOkwN5IJFBE
OBMNK8Dy/BeC6gkuG16POpCPN/JqRF9KA4B0dJ2CIG02gPapNd9EWutAh7NE3e4xpm4IQWdeELDt
b/EsLOC9DQO7QPucUINzeNLjMa0h5ARwERD5hrvFzfQww/d2aBH8Deoqo+Oz8PWHPE7JCMZFWtls
Fl3hNkNIuTEY0LdXDRKdLJg8LWESw24DHTGptKQ7hbviqVNoNrkwXjAkOKsgOvK3ITbhfbaWzJ5p
yo2BOW43TStp6TWli1h7jJQ1IgRgq1cuRFE0tAwOUW3GELRMHedZWTlx57L82ct11s1MnEAIHZAN
LSmjYj0I/Z2XG3uVdapvWbiXhIzqAKOXtzRoZmc5nDD61MT0jowB4vnYAmwpCtpg1BBJzAnsO3f0
dfzNqzr2EVnlWMkwesZDmSYsdxHqemoFUo5kzVJxDcoDIRCpFIwDvPeRDOkX+Eku8qtJpMta4vTW
jlaqn+Ij1vQKIxsMBEcXgMVh09PSjviGwNlofu5qaA1kVSa8v1ogrbRvm3nnhhAwLy1yw8NArlMy
Ju1HXMZpnUOcwm5tyi/kj+SDu4ERHqscyNehy1snwRXU2cfatK2o2J6jL1yWlHcgUComquscOXzO
4biIzD8rrXw5GofAYs31w456yxIz3qMc0h4QCDFghlJGRLXgega2ZmbkmYyVqqU3V9FonB8hOuMO
JJzZnOTmkOzQgOyGjpToaeNBBijUNQXiloknad9MayYIa+caoSBJkmSZhGxxgKxw7p+g4jxdizUT
4kSt7XXF5HAoiKK1rtOoepMZmRNxJDnvZoHraiJpWk0HIiFgbX24CNVBb5NFQVQoJI8vJxVEZAoz
RnNDuhTGoIZBBfOIkzzxIjhGIxTxO7HOI810vGmZlkSwPtatBWYUboPvZw/t3iIP7Uj0JqZENW49
BFCf8+ZcrBVkzIEtadwxmDEb517eDxOzsCguaP8kyFqUkl/xdyRThQkFflaHgA==
[9 Jul 2009 5:11] shane adams
My patch for this feature

Attachment: diffy (application/octet-stream, text), 7.54 KiB.

[9 Jul 2009 5:23] Valeriy Kravchuk
Thank you for the contribution.
[13 Jul 2009 8:49] Sergei Golubchik
for the reference: WL#1979
[13 Jul 2009 15:35] liz drachnik
Hello Shane   - 

 In order for us to continue the process of reviewing your contribution
 to MySQL - We need you to review and sign the Sun|MySQL contributor
 agreement (the "SCA")

 The process is explained here:
 http://forge.mysql.com/wiki/Sun_Contributor_Agreement

 Getting a signed/approved SCA on file will help us facilitate your
 contribution-- this one, and others in the future.

 Thank you !

 Liz Drachnik  - Program Manager - MySQL
[13 Jul 2009 22:36] shane adams
Hey Liz,

I fax'd the application late last week.  Let me know if it did or did not go through.
[13 Jul 2009 22:47] liz drachnik
Comment to Shane - a garbled fax came through electronically to the SCA alias last week  - couldn't tell who it was from/what project it was for.  (I don't see hardcopy) Can you possibly email? thank you
[13 Jul 2009 23:02] liz drachnik
Shane - looks as though your fax made it through. 

I need to get the email address you used to login to the MySQL site   - not on the form

can you just email mysql_sca@sun.com and that way i can get it and you don't have to post here and get spammed.....
[13 Jul 2009 23:09] shane adams
Found a bug in my code regarding select field ordering.  Will update the patch but wanted to give you a heads up that it currently has bugs so not to waste your time reviewing formally.
[14 Jul 2009 1:57] shane adams
drop table if exists t1;
create table t1( a int, b int, c int );

insert into t1 values ( 1,2,3 ), (2,3,4), ( 2,3,4), (1,2,3);

select grouping(a), a   from t1 group by a with rollup;
select a, grouping(a)  from t1 group by a with rollup;

drop table if exists t1;

The first select, grouping(a) returns the correct value, the second select statement it does not.  Still scratching my head on this one.
[14 Jul 2009 17:39] shane adams
new patch submitted as file attachment.
[14 Oct 2009 9:04] Valeriy Kravchuk
Bug #3156 was marked as a duplicate of this one.
[11 Oct 2015 18:54] Zhe Dong
Seems that this bug is currently still unfixed. I'm willing to push it into the current developing branch.
[27 Oct 2015 13:39] OCA Admin
Contribution submitted via Github - Bug #46053 GROUPING() function for ROLLUP  
(*) Contribution by Zhe Dong (Github jimmybupt, mysql-server/pull/30#issuecomment-150820440): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it

Contribution: git_patch_48649477.txt (text/plain), 6.42 KiB.

[9 Mar 2017 17:24] Paul Dubois
Posted by developer:
 
Implemented in 8.0.1. See:

https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html