| 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: | |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | Chaithra Gopala Reddy | CPU Architecture: | Any |
| Tags: | Contribution, grouping, rollup | ||
[8 Jul 2009 18:15]
shane adams
[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
