Merge "Support GROUP BY ROLLUP on MySql/MariaDB" into main

3 files changed, 20 insertions, 0 deletions

diff --git a/doc/build/changelog/unreleased_20/8503.rst b/doc/build/changelog/unreleased_20/8503.rst
new file mode 100644
index 000000000..d4b6307d4
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/8503.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: usecase, mysql, mariadb
+    :tickets: 8503
+
+    The ``ROLLUP`` function will now correctly render ``WITH ROLLUP`` on
+    MySql and MariaDB, allowing the use of group by rollup with these
+    backend.
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 4b0a5e6c5..c0521f61e 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1210,6 +1210,12 @@ class MySQLCompiler(compiler.SQLCompiler):
     def visit_random_func(self, fn, **kw):
         return "rand%s" % self.function_argspec(fn)
 
+    def visit_rollup_func(self, fn, **kw):
+        clause = ", ".join(
+            elem._compiler_dispatch(self, **kw) for elem in fn.clauses
+        )
+        return f"{clause} WITH ROLLUP"
+
     def visit_sequence(self, seq, **kw):
         return "nextval(%s)" % self.preparer.format_sequence(seq)
 
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 9d2c43bfe..a4a0b24e4 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -560,6 +560,13 @@ class CompileTest(ReservedWordFixture, fixtures.TestBase, AssertsCompiledSQL):
             "ALWAYS AS (x + 2)%s)" % text,
         )
 
+    def test_groupby_rollup(self):
+        t = table("tt", column("foo"), column("bar"))
+        q = sql.select(t.c.foo).group_by(sql.func.rollup(t.c.foo, t.c.bar))
+        self.assert_compile(
+            q, "SELECT tt.foo FROM tt GROUP BY tt.foo, tt.bar WITH ROLLUP"
+        )
+
 
 class SQLTest(fixtures.TestBase, AssertsCompiledSQL):