Fix/Test `Group-By`

2 files changed, 66 insertions, 2 deletions

diff --git a/sqlparse/filters.py b/sqlparse/filters.py
index dad754e..0d0be5c 100644
--- a/sqlparse/filters.py
+++ b/sqlparse/filters.py
@@ -365,6 +365,10 @@ class AlignedIndentFilter:
         return self._process(sql.TokenList(tlist.tokens), base_indent=base_indent)
 
     def _process_parenthesis(self, tlist, base_indent=0):
+        if not tlist.token_next_match(0, T.DML, 'SELECT'):
+            # if this isn't a subquery, don't re-indent
+            return tlist
+
         sub_indent = base_indent + self._max_kwd_len + 2  # add two for the space and parens
         tlist.insert_after(tlist.tokens[0], self.whitespace(sub_indent, newline_before=True))
         # de-indent the last parenthesis
@@ -381,7 +385,7 @@ class AlignedIndentFilter:
     def _process_identifierlist(self, tlist, base_indent=0):
         # columns being selected
         new_tokens = []
-        identifiers = filter(lambda t: isinstance(t, sql.Identifier), tlist.tokens)
+        identifiers = filter(lambda t: t.ttype not in (T.Punctuation, T.Whitespace, T.Newline), tlist.tokens)
         for i, token in enumerate(identifiers):
             if i > 0:
                 new_tokens.append(self.newline())
@@ -441,7 +445,13 @@ class AlignedIndentFilter:
 
         # process any sub-sub statements
         for sgroup in tlist.get_sublists():
-            self._process(sgroup, base_indent=base_indent)
+            prev_token = tlist.token_prev(tlist.token_index(sgroup))
+            indent_offset = 0
+            if prev_token and prev_token.match(T.Keyword, 'BY'):
+                # HACK: make "group by" and "order by" indents work. these are longer than _max_kwd_len.
+                # TODO: generalize this
+                indent_offset = 3
+            self._process(sgroup, base_indent=base_indent + indent_offset)
         return tlist
 
     def _process(self, tlist, base_indent=0, verbose=False):
diff --git a/tests/test_format.py b/tests/test_format.py
index 247c249..18cc117 100644
--- a/tests/test_format.py
+++ b/tests/test_format.py
@@ -165,6 +165,60 @@ class TestFormatReindentAligned(TestCaseBase):
                 '   and b between 3 and 4'
             ]))
 
+    def test_group_by(self):
+        sql = """
+            select a, b, c, sum(x) as sum_x, count(y) as cnt_y
+            from table
+            group by a,b,c
+            having sum(x) > 1
+            and count(y) > 5
+            order by 3,2,1
+            """
+        self.ndiffAssertEqual(
+            self.formatter(sql),
+            '\n'.join([
+                'select a,',
+                '       b,',
+                '       c,',
+                '       sum(x) as sum_x,',
+                '       count(y) as cnt_y',
+                '  from table',
+                ' group by a,',
+                '          b,',
+                '          c',
+                'having sum(x) > 1',
+                '   and count(y) > 5',
+                ' order by 3,',
+                '          2,',
+                '          1',
+            ]))
+
+    def test_group_by_subquery(self):
+        # TODO: add subquery alias in again when test_grouping.TestGrouping.test_identifier_list_subquery fixed
+        sql = """
+            select *, sum_b + 2 as mod_sum
+            from (
+              select a, sum(b) as sum_b
+              from table
+              group by a,z)
+            order by 1,2
+            """
+        self.ndiffAssertEqual(
+            self.formatter(sql),
+            '\n'.join([
+                'select *,',
+                '       sum_b + 2 as mod_sum',
+                '  from (',
+                '        select a,',
+                '               sum(b) as sum_b',
+                '          from table',
+                '         group by a,',
+                '                  z',
+                '       )',
+                ' order by 1,',
+                '          2',
+                ]))
+
 
 class TestFormatReindent(TestCaseBase):