Repository: kayak/pypika
Pull request #530 (2020-12-08 17:38:02)

Fix groupby_alias specified multiple times in kwargs when subqueries are used
Affects Oracle and MSSQL query builders only.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/35543808/badge)](https://coveralls.io/builds/35543808)

Coverage increased (+0.008%) to 98.204% when pulling **e3d52463d8070416c31f2c7a9382082a6c933c17 on mikeengland:fix-groupby-issue-with-subqueries** into **a3f1d025822ed34408ad93c8e8d9f3f794378f27 on kayak:master**.


Patch:
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 84b56d9..991df2b 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -276,7 +276,10 @@ class OracleQueryBuilder(QueryBuilder):
         super().__init__(dialect=Dialects.ORACLE, **kwargs)
 
     def get_sql(self, *args: Any, **kwargs: Any) -> str:
-        return super().get_sql(*args, groupby_alias=False, **kwargs)
+        # Oracle does not support group by a field alias
+        # Note: set directly in kwargs as they are re-used down the tree in the case of subqueries!
+        kwargs['groupby_alias'] = False
+        return super().get_sql(*args, **kwargs)
 
 
 class OracleQuery(Query):
@@ -577,7 +580,10 @@ class MSSQLQueryBuilder(QueryBuilder):
         return querystring
 
     def get_sql(self, *args: Any, **kwargs: Any) -> str:
-        return super().get_sql(*args, groupby_alias=False, **kwargs)
+        # MSSQL does not support group by a field alias.
+        # Note: set directly in kwargs as they are re-used down the tree in the case of subqueries!
+        kwargs['groupby_alias'] = False
+        return super().get_sql(*args, **kwargs)
 
     def _top_sql(self) -> str:
         if self._top:


Test patch:
diff --git a/pypika/tests/dialects/test_mssql.py b/pypika/tests/dialects/test_mssql.py
index 2c8dd4a..1c7f78b 100644
--- a/pypika/tests/dialects/test_mssql.py
+++ b/pypika/tests/dialects/test_mssql.py
@@ -1,5 +1,7 @@
 import unittest
 
+from pypika import Table
+from pypika.analytics import Count
 from pypika.dialects import MSSQLQuery
 from pypika.utils import QueryException
 
@@ -48,3 +50,20 @@ class SelectTests(unittest.TestCase):
         q = MSSQLQuery.from_("abc").select("def").orderby("def").fetch_next(10).offset(10)
 
         self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY', str(q))
+
+    def test_groupby_alias_False_does_not_group_by_alias_with_standard_query(self):
+        t = Table('table1')
+        col = t.abc.as_('a')
+        q = MSSQLQuery.from_(t).select(col, Count('*')).groupby(col)
+
+        self.assertEqual('SELECT "abc" "a",COUNT(\'*\') FROM "table1" GROUP BY "abc"', str(q))
+
+    def test_groupby_alias_False_does_not_group_by_alias_when_subqueries_are_present(self):
+        t = Table('table1')
+        subquery = MSSQLQuery.from_(t).select(t.abc)
+        col = subquery.abc.as_('a')
+        q = MSSQLQuery.from_(subquery).select(col, Count('*')).groupby(col)
+
+        self.assertEqual(
+            'SELECT "sq0"."abc" "a",COUNT(\'*\') FROM (SELECT "abc" FROM "table1") "sq0" GROUP BY "sq0"."abc"', str(q)
+        )
diff --git a/pypika/tests/dialects/test_oracle.py b/pypika/tests/dialects/test_oracle.py
new file mode 100644
index 0000000..c3a757a
--- /dev/null
+++ b/pypika/tests/dialects/test_oracle.py
@@ -0,0 +1,21 @@
+import unittest
+
+from pypika import OracleQuery, Table
+from pypika.analytics import Count
+
+
+class SelectTests(unittest.TestCase):
+    def test_groupby_alias_False_does_not_group_by_alias_with_standard_query(self):
+        t = Table('table1')
+        col = t.abc.as_('a')
+        q = OracleQuery.from_(t).select(col, Count('*')).groupby(col)
+
+        self.assertEqual('SELECT abc a,COUNT(\'*\') FROM table1 GROUP BY abc', str(q))
+
+    def test_groupby_alias_False_does_not_group_by_alias_when_subqueries_are_present(self):
+        t = Table('table1')
+        subquery = OracleQuery.from_(t).select(t.abc)
+        col = subquery.abc.as_('a')
+        q = OracleQuery.from_(subquery).select(col, Count('*')).groupby(col)
+
+        self.assertEqual('SELECT sq0.abc a,COUNT(\'*\') FROM (SELECT abc FROM table1) sq0 GROUP BY sq0.abc', str(q))