Repository: kayak/pypika (python)

Issue:
Disable group by alias for Oracle and SQL Server dialects as it is not supported
MySQL, PostgreSQL (and Redshift I believe, as it is based on PostgreSQL) and Vertica support grouping by an alias defined in a select due to the order that certain parts of the query are executed:
https://stackoverflow.com/a/3841804/1224372

We should disable grouping by alias for SQL Server and Oracle dialects.

Pull request:
Disable group by alias functionality for Oracle and SQL Server builders
I just realised functionality to support this was added in https://github.com/kayak/pypika/pull/193, so this issue was a quick fix.

Fixes #89

Patch:
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 2e3ec18..2610501 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -32,12 +32,14 @@ This contains all of the utility classes such as exceptions and decorators.
 """
 # noinspection PyUnresolvedReferences
 from .dialects import (
+    ClickHouseQuery,
     Dialects,
     MSSQLQuery,
     MySQLQuery,
     OracleQuery,
     PostgreSQLQuery,
     RedshiftQuery,
+    SQLLiteQuery,
     VerticaQuery,
 )
 # noinspection PyUnresolvedReferences
diff --git a/pypika/dialects.py b/pypika/dialects.py
index cfbb6be..350ebdf 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -17,8 +17,12 @@ from pypika.utils import (
 
 
 class MySQLQueryBuilder(QueryBuilder):
+    QUOTE_CHAR = '`'
+
     def __init__(self):
-        super(MySQLQueryBuilder, self).__init__(quote_char='`', dialect=Dialects.MYSQL, wrap_union_queries=False)
+        super(MySQLQueryBuilder, self).__init__(quote_char=self.QUOTE_CHAR,
+                                                dialect=Dialects.MYSQL,
+                                                wrap_union_queries=False)
         self._duplicate_updates = []
 
     @builder
@@ -82,6 +86,14 @@ class VerticaQuery(Query):
         return VerticaQueryBuilder()
 
 
+class OracleQueryBuilder(QueryBuilder):
+    def __init__(self):
+        super(OracleQueryBuilder, self).__init__(dialect=Dialects.ORACLE)
+
+    def get_sql(self, *args, **kwargs):
+        return super(OracleQueryBuilder, self).get_sql(*args, groupby_alias=False, **kwargs)
+
+
 class OracleQuery(Query):
     """
     Defines a query class for use with Oracle.
@@ -89,7 +101,7 @@ class OracleQuery(Query):
 
     @classmethod
     def _builder(cls):
-        return QueryBuilder(dialect=Dialects.ORACLE)
+        return OracleQueryBuilder()
 
 
 class PostgreQueryBuilder(QueryBuilder):
@@ -120,9 +132,7 @@ class PostgreQueryBuilder(QueryBuilder):
     def do_update(self, update_field, update_value):
         if self._on_conflict_do_nothing:
             raise QueryException('Can not have two conflict handlers')
-        #assert len(update_fields) == len(update_values), 'number of fields does noth match with number of values'
-        #for i, f in enumerate(update_fields):
-        #field = None
+
         if isinstance(update_field, str):
             field = self._conflict_field_str(update_field)
         elif isinstance(update_field, Field):
@@ -132,7 +142,7 @@ class PostgreQueryBuilder(QueryBuilder):
     def _conflict_field_str(self, term):
         if self._insert_table:
             return Field(term, table=self._insert_table)
-            
+
     def _on_conflict_sql(self, **kwargs):
         if self._on_conflict_field is None:
             return ''
@@ -153,7 +163,6 @@ class PostgreQueryBuilder(QueryBuilder):
 
             return conflict_query
 
-
     @builder
     def returning(self, *terms):
         for term in terms:
@@ -193,7 +202,7 @@ class PostgreQueryBuilder(QueryBuilder):
 
         if isinstance(term, Star):
             self._set_returns_for_star()
-        
+
         self._returns.append(term)
 
     def _return_field_str(self, term):
@@ -249,6 +258,14 @@ class RedshiftQuery(Query):
         return QueryBuilder(dialect=Dialects.REDSHIFT)
 
 
+class MSSQLQueryBuilder(QueryBuilder):
+    def __init__(self):
+        super(MSSQLQueryBuilder, self).__init__(dialect=Dialects.MSSQL)
+
+    def get_sql(self, *args, **kwargs):
+        return super(MSSQLQueryBuilder, self).get_sql(*args, groupby_alias=False, **kwargs)
+
+
 class MSSQLQuery(Query):
     """
     Defines a query class for use with Microsoft SQL Server.
@@ -256,7 +273,7 @@ class MSSQLQuery(Query):
 
     @classmethod
     def _builder(cls):
-        return QueryBuilder(dialect=Dialects.MSSQL)
+        return MSSQLQueryBuilder()
 
 
 class ClickHouseQuery(Query):


Test patch:
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index bdbad97..0c68bd3 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -17,6 +17,8 @@ from pypika import (
     Table,
     Tables,
     VerticaQuery,
+    ClickHouseQuery,
+    SQLLiteQuery,
     functions as fn,
 )
 
@@ -386,6 +388,38 @@ class GroupByTests(unittest.TestCase):
             'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"',
             q.get_sql(groupby_alias=False))
 
+    def test_groupby__no_alias_platforms(self):
+        for query_cls in [MSSQLQuery, OracleQuery]:
+            with self.subTest('for query class {}'.format(query_cls.__class__.__name__)):
+                bar = self.t.bar.as_('bar01')
+                q = query_cls.from_(self.t) \
+                    .select(fn.Sum(self.t.foo), bar) \
+                    .groupby(bar)
+
+                self.assertEqual(
+                    'SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar"',
+                    str(q)
+                )
+
+    def test_groupby__alias_platforms(self):
+        for query_cls in [MySQLQuery, VerticaQuery, PostgreSQLQuery, RedshiftQuery, ClickHouseQuery, SQLLiteQuery]:
+            with self.subTest('for query class {}'.format(query_cls.__class__.__name__)):
+                bar = self.t.bar.as_('bar01')
+                q = query_cls.from_(self.t) \
+                    .select(fn.Sum(self.t.foo), bar) \
+                    .groupby(bar)
+
+                quote_char = query_cls._builder().QUOTE_CHAR \
+                    if isinstance(query_cls._builder().QUOTE_CHAR, str) else '"'
+
+                self.assertEqual(
+                    'SELECT SUM({quote_char}foo{quote_char}),{quote_char}bar{quote_char} '
+                    '{quote_char}bar01{quote_char} '
+                    'FROM {quote_char}abc{quote_char} '
+                    'GROUP BY {quote_char}bar01{quote_char}'.format(quote_char=quote_char),
+                    str(q)
+                )
+
     def test_groupby__alias_with_join(self):
         table1 = Table('table1', alias='t1')
         bar = table1.bar.as_('bar01')
@@ -696,7 +730,7 @@ class AliasTests(unittest.TestCase):
         my_foo = table_abc.foo.as_('my_foo')
         q = Query.from_(table_abc) \
             .select(my_foo, table_abc.bar) \
-            .groupby(my_foo)\
+            .groupby(my_foo) \
             .orderby(my_foo)
 
         self.assertEqual('SELECT "q0"."foo" "my_foo","q0"."bar" '