Repository: sqlfluff/sqlfluff
Pull request: Exclude WINDOW clauses from L054
<!--Firstly, thanks for adding this feature! Secondly, please check the key steps against the checklist below to make your contribution easy to merge.-->

<!--Please give the Pull Request a meaningful title (including the dialect this PR is for if it is dialect specific), as this will automatically be added to the release notes, and then the Change Log.-->

### Brief summary of the change made
<!--If there is an open issue for this, then please include `fixes #XXXX` or `closes #XXXX` replacing `XXXX` with the issue number and it will automatically close the issue when the pull request is merged. Alternatively if not fully closed you can say `makes progress on #XXXX` to create a link on that issue without closing it.-->
Fixes #2453

### Are there any other side effects of this change that we should be aware of?
Nope

### Pull Request checklist
- [x] Please confirm you have completed any of the necessary steps below.

- Included test cases to demonstrate any code changes, which may be one or more of the following:
  - `.yml` rule test cases in `test/fixtures/rules/std_rule_cases`.
  - `.sql`/`.yml` parser test cases in `test/fixtures/dialects` (note YML files can be auto generated with `tox -e generate-fixture-yml`).
  - Full autofix test cases in `test/fixtures/linter/autofix`.
  - Other.
- Added appropriate documentation for the change.
- Created GitHub issues for any relevant followup/future enhancements if appropriate.


Problem statement:
Remove WINDOW functions clauses from Rule L054 
SQLFluff is saying I’m violating L054, cause I’ve got implicit ORDER BY clauses, but EXPLICIT ones inside ORDER BY of my WINDOW Functions. I'd suggest that Window functions clauses should be excluded from this rule since, although feasible, it's unnatural to use IMPLICIT clauses within them.

```sql
SELECT 
    field_1
    , field_2
    , SUM(field_3) as field_3_total
    , SUM(field_3) OVER (ORDER BY field_1) AS field_3_window_sum
FROM table
GROUP BY 1, 2
ORDER BY 1, 2
```

Hints from issue discussion:

Commit message: Revert accidently removed test case

Patch to review:
diff --git a/src/sqlfluff/rules/L054.py b/src/sqlfluff/rules/L054.py
index a33ff246b5c..e8b7d7a9388 100644
--- a/src/sqlfluff/rules/L054.py
+++ b/src/sqlfluff/rules/L054.py
@@ -1,5 +1,5 @@
 """Implementation of Rule L054."""
-from typing import Optional
+from typing import Optional, List
 
 from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
 from sqlfluff.core.rules.doc_decorators import (
@@ -11,6 +11,8 @@
 class Rule_L054(BaseRule):
     """Inconsistent column references in ``GROUP BY/ORDER BY`` clauses.
 
+    Note: ORDER BY clauses from WINDOW clauses are ignored by this rule.
+
     | **Anti-pattern**
     | A mix of implicit and explicit column references are used in a ``GROUP BY``
     | clause.
@@ -78,6 +80,7 @@ class Rule_L054(BaseRule):
     """
 
     config_keywords = ["group_by_and_order_by_style"]
+    _ignore_types: List[str] = ["window_specification"]
 
     def _eval(self, context: RuleContext) -> Optional[LintResult]:
         """Inconsistent column references in GROUP BY/ORDER BY clauses."""
@@ -88,6 +91,11 @@ def _eval(self, context: RuleContext) -> Optional[LintResult]:
         if not context.segment.is_type("groupby_clause", "orderby_clause"):
             return None
 
+        # Ignore Windowing clauses
+        for parent in context.parent_stack:
+            if parent.type in self._ignore_types:
+                return LintResult()
+
         # Look at child segments and map column references to either the implict or
         # explicit category.
         # N.B. segment names are used as the numeric literal type is 'raw', so best to
diff --git a/test/fixtures/rules/std_rule_cases/L054.yml b/test/fixtures/rules/std_rule_cases/L054.yml
index 769160b50bd..d3998fac7a2 100644
--- a/test/fixtures/rules/std_rule_cases/L054.yml
+++ b/test/fixtures/rules/std_rule_cases/L054.yml
@@ -467,3 +467,28 @@ test_fail_consistent_exasol:
     rules:
       L054:
         group_by_and_order_by_style: consistent
+
+test_pass_window:
+  pass_str: |
+    SELECT
+        field_1
+        , field_2
+        , SUM(field_3) as field_3_total
+        , SUM(field_3) OVER (ORDER BY field_1) AS field_3_window_sum
+    FROM table1
+    GROUP BY 1, 2
+    ORDER BY 1, 2
+
+test_pass_window_snowflake:
+  pass_str: |
+    SELECT
+        field_1
+        , field_2
+        , SUM(field_3) as field_3_total
+        , SUM(field_3) OVER (ORDER BY field_1) AS field_3_window_sum
+    FROM table1
+    GROUP BY 1, 2
+    ORDER BY 1, 2
+  configs:
+    core:
+      dialect: snowflake


Code review comments:
diff_hunk: @@ -88,6 +91,11 @@ def _eval(self, context: RuleContext) -> Optional[LintResult]:
         if not context.segment.is_type("groupby_clause", "orderby_clause"):
             return None
 
+        # Ignore Windowing clauses
+        for parent in context.parent_stack:
path: src/sqlfluff/rules/L054.py
text: @user1:
I think you could simplify this a bit with the functional API (it's good at eliminating loops):
```
if context.functional.parent_stack.select(sp.is_type(*_self._ignore_types)):
    return LintResult()
```

@author:
Done. Also changed the LintResult to return Memory like the others.