Support for GROUP BY CUBE, SET, ROLLUPpr_github_383

https://bitbucket.org/zzzeek/sqlalchemy/issues/3429/support-for-group-by-cube-set-rollup

 - new funcs added
 - fix type into sql.elements

Change-Id: I870ee7dc801d553c5309c291402ec468b671e9a9
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/383

4 files changed, 45 insertions, 2 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 2dec3a5c3..b0f0807d6 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -121,7 +121,10 @@ FUNCTIONS = {
     functions.random: 'random%(expr)s',
     functions.sysdate: 'sysdate',
     functions.session_user: 'SESSION_USER',
-    functions.user: 'USER'
+    functions.user: 'USER',
+    functions.cube: 'CUBE%(expr)s',
+    functions.rollup: 'ROLLUP%(expr)s',
+    functions.grouping_sets: 'GROUPING SETS%(expr)s',
 }
 
 EXTRACT_MAP = {
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 36a6a6557..9213d616c 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -3293,7 +3293,7 @@ class WithinGroup(ColumnElement):
     """Represent a WITHIN GROUP (ORDER BY) clause.
 
     This is a special operator against so-called
-    so-called "ordered set aggregate" and "hypothetical
+    "ordered set aggregate" and "hypothetical
     set aggregate" functions, including ``percentile_cont()``,
     ``rank()``, ``dense_rank()``, etc.
 
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index 08f1d32a5..ef041eac7 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -811,3 +811,33 @@ class cume_dist(GenericFunction):
 
     """
     type = sqltypes.Numeric()
+
+
+class cube(GenericFunction):
+    r"""Implement the ``CUBE`` grouping operation.
+
+    This function must be used as argument of :func:`Query.group_by`:
+
+        stmt = select([func.sum(table.c.value), table.c.col_1, table.c.col_2]).\
+               group_by(func.cube(table.c.col_1, table.c.col_2))
+    """
+
+
+class rollup(GenericFunction):
+    r"""Implement the ``ROLLUP`` grouping operation.
+
+    This function must be used as argument of :func:`Query.group_by`:
+
+        stmt = select([func.sum(table.c.value), table.c.col_1, table.c.col_2]).\
+               group_by(func.rollup(table.c.col_1, table.c.col_2))
+    """
+
+
+class grouping_sets(GenericFunction):
+    r"""Implement the ``GROUPING SETS`` grouping operation.
+
+    This function must be used as argument of :func:`Query.group_by`:
+
+        stmt = select([func.sum(table.c.value), table.c.col_1, table.c.col_2]).\
+               group_by(func.grouping_sets(table.c.col_1, table.c.col_2))
+    """
diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py
index 272bd876e..4d43385f6 100644
--- a/test/sql/test_functions.py
+++ b/test/sql/test_functions.py
@@ -86,6 +86,16 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         ]:
             self.assert_compile(func.random(), ret, dialect=dialect)
 
+    def test_grouping_set(self):
+        for clause, compiled, checkparams in [
+            (func.grouping_sets('col_1'), 'GROUPING SETS(:grouping_sets_1)', {'grouping_sets_1': 'col_1'}),
+            (func.grouping_sets('col_1', 'col_2'), 'GROUPING SETS(:grouping_sets_1, :grouping_sets_2)', {'grouping_sets_1': 'col_1', 'grouping_sets_2': 'col_2'}),
+            (func.grouping_sets('col_1', ()), 'GROUPING SETS(:grouping_sets_1, :grouping_sets_2)', {'grouping_sets_1': 'col_1', 'grouping_sets_2': ()}),
+            (func.grouping_sets('col_1', ('col_2', 'col_3')), 'GROUPING SETS(:grouping_sets_1, :grouping_sets_2)', {'grouping_sets_1': 'col_1', 'grouping_sets_2': ('col_2', 'col_3')}),
+            (func.grouping_sets('col_1', ('col_2', 'col_3'), ()), 'GROUPING SETS(:grouping_sets_1, :grouping_sets_2, :grouping_sets_3)', {'grouping_sets_1': 'col_1', 'grouping_sets_2': ('col_2', 'col_3'), 'grouping_sets_3': ()}),
+        ]:
+            self.assert_compile(clause, compiled, checkparams=checkparams)
+
     def test_generic_annotation(self):
         fn = func.coalesce('x', 'y')._annotate({"foo": "bar"})
         self.assert_compile(