Repository: sqlfluff/sqlfluff

Issue:
Rule suggestion: `UNION [ALL|DISTINCT]` on new line
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### Description

I would like to suggest a new rule that puts `UNION [ALL|DISTINCT]` statements on their own line, aligned to the surrounding `SELECT` statements.

For example, currently 

```sql
SELECT 1 UNION ALL
SELECT 2
```

passes without errors. This new rule could fix that to

```sql
SELECT 1 
UNION ALL
SELECT 2
```

Or in a more complex example

```sql
SELECT * FROM (
    SELECT 1 UNION ALL
    SELECT 2
)
```

fixed to

```sql
SELECT * FROM (
    SELECT 1 
    UNION ALL
    SELECT 2
)
```

### Use case

I have looked at a few SQL style guides and they don't really seem to mention any policy regarding `UNION` statements. However, in 99% of the SQL I have encountered `UNION` statements always seemed to be on a new line. It would be great to have an option to lint the remaining 1% 😉 

### Dialect

ansi

### Are you willing to work on and submit a PR to address the issue?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)


Discussion:
codecov[bot]: # [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3330?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) Report
> Merging [#3330](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3330?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (8b2bc81) into [main](https://codecov.io/gh/sqlfluff/sqlfluff/commit/f7e80a184e66b047d35cb08670625d533f5020ed?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (f7e80a1) will **not change** coverage.
> The diff coverage is `100.00%`.

```diff
@@            Coverage Diff            @@
##              main     #3330   +/-   ##
=========================================
  Coverage   100.00%   100.00%           
=========================================
  Files          168       169    +1     
  Lines        12703     12734   +31     
=========================================
+ Hits         12703     12734   +31     
```


| [Impacted Files](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3330?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) | Coverage Δ | |
|---|---|---|
| [src/sqlfluff/rules/L065.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3330/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL3J1bGVzL0wwNjUucHk=) | `100.00% <100.00%> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3330?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3330?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Last update [f7e80a1...8b2bc81](https://codecov.io/gh/sqlfluff/sqlfluff/pull/3330?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).


Patch:
diff --git a/src/sqlfluff/rules/L065.py b/src/sqlfluff/rules/L065.py
new file mode 100644
index 000000000..f2e4255b3
--- /dev/null
+++ b/src/sqlfluff/rules/L065.py
@@ -0,0 +1,156 @@
+"""Implementation of Rule L065."""
+from typing import List, Optional, Iterable
+
+import sqlfluff.core.rules.functional.segment_predicates as sp
+from sqlfluff.core.parser import BaseSegment, NewlineSegment
+from sqlfluff.core.rules.base import BaseRule, LintFix, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import document_fix_compatible, document_groups
+
+
+@document_groups
+@document_fix_compatible
+class Rule_L065(BaseRule):
+    """Set operators should be surrounded by newlines.
+
+    **Anti-pattern**
+
+    In this example, `UNION ALL` is not on a line ifself.
+
+    .. code-block:: sql
+
+        SELECT 'a' AS col UNION ALL
+        SELECT 'b' AS col
+
+    **Best practice**
+
+    .. code-block:: sql
+
+        SELECT 'a' AS col
+        UNION ALL
+        SELECT 'b' AS col
+
+    """
+
+    groups = ("all",)
+
+    _target_elems = ("set_operator",)
+
+    def _eval(self, context: RuleContext) -> List[LintResult]:
+        """Set operators should be surrounded by newlines.
+
+        For any set operator we check if there is any NewLineSegment in the non-code
+        segments preceeding or following it.
+
+        In particular, as part of this rule we allow multiple NewLineSegments.
+        """
+        segment = context.functional.segment
+
+        expression = segment.children()
+        set_operator_segments = segment.children(sp.is_type(*self._target_elems))
+        results: List[LintResult] = []
+
+        # If len(set_operator) == 0 this will essentially not run
+        for set_operator in set_operator_segments:
+            preceeding_code = (
+                expression.reversed().select(start_seg=set_operator).first(sp.is_code())
+            )
+            following_code = expression.select(start_seg=set_operator).first(
+                sp.is_code()
+            )
+            res = {
+                "before": expression.select(
+                    start_seg=preceeding_code.get(), stop_seg=set_operator
+                ),
+                "after": expression.select(
+                    start_seg=set_operator, stop_seg=following_code.get()
+                ),
+            }
+
+            newline_before_set_operator = res["before"].first(sp.is_type("newline"))
+            newline_after_set_operator = res["after"].first(sp.is_type("newline"))
+
+            # If there is a whitespace directly preceeding/following the set operator we
+            # are replacing it with a newline later.
+            preceeding_whitespace = res["before"].first(sp.is_type("whitespace")).get()
+            following_whitespace = res["after"].first(sp.is_type("whitespace")).get()
+
+            if newline_before_set_operator and newline_after_set_operator:
+                continue
+            elif not newline_before_set_operator and newline_after_set_operator:
+                results.append(
+                    LintResult(
+                        anchor=set_operator,
+                        description=(
+                            "Set operators should be surrounded by newlines. "
+                            f"Missing newline before set operator {set_operator.raw}."
+                        ),
+                        fixes=_generate_fixes(whitespace_segment=preceeding_whitespace),
+                    )
+                )
+            elif newline_before_set_operator and not newline_after_set_operator:
+                results.append(
+                    LintResult(
+                        anchor=set_operator,
+                        description=(
+                            "Set operators should be surrounded by newlines. "
+                            f"Missing newline after set operator {set_operator.raw}."
+                        ),
+                        fixes=_generate_fixes(whitespace_segment=following_whitespace),
+                    )
+                )
+            else:
+                preceeding_whitespace_fixes = _generate_fixes(
+                    whitespace_segment=preceeding_whitespace
+                )
+                following_whitespace_fixes = _generate_fixes(
+                    whitespace_segment=following_whitespace
+                )
+
+                # make mypy happy
+                assert isinstance(preceeding_whitespace_fixes, Iterable)
+                assert isinstance(following_whitespace_fixes, Iterable)
+
+                fixes = []
+                fixes.extend(preceeding_whitespace_fixes)
+                fixes.extend(following_whitespace_fixes)
+
+                results.append(
+                    LintResult(
+                        anchor=set_operator,
+                        description=(
+                            "Set operators should be surrounded by newlines. "
+                            "Missing newline before and after set operator "
+                            f"{set_operator.raw}."
+                        ),
+                        fixes=fixes,
+                    )
+                )
+
+        return results
+
+
+def _generate_fixes(
+    whitespace_segment: BaseSegment,
+) -> Optional[List[LintFix]]:
+
+    if whitespace_segment:
+        return [
+            LintFix.replace(
+                anchor_segment=whitespace_segment,
+                # NB: Currently we are just inserting a Newline here. This alone will
+                # produce not properly indented SQL. We rely on L003 to deal with
+                # indentation later.
+                # As a future improvement we could maybe add WhitespaceSegment( ... )
+                # here directly.
+                edit_segments=[NewlineSegment()],
+            )
+        ]
+    else:
+        # We should rarely reach here as set operators are always surrounded by either
+        # WhitespaceSegment or NewlineSegment.
+        # However, in exceptional cases the WhitespaceSegment might be enclosed in the
+        # surrounding segment hierachy and not accessible by the rule logic.
+        # At the time of writing this is true for `tsql` as covered in the test
+        # `test_fail_autofix_in_tsql_disabled`. If we encounter such case, we skip
+        # fixing.
+        return []


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L065.yml b/test/fixtures/rules/std_rule_cases/L065.yml
new file mode 100644
index 000000000..a5088d055
--- /dev/null
+++ b/test/fixtures/rules/std_rule_cases/L065.yml
@@ -0,0 +1,122 @@
+rule: L065
+
+test_fail_simple_fix_union_all_before:
+  fail_str: |
+      SELECT 'a' UNION ALL
+      SELECT 'b'
+  fix_str: |
+      SELECT 'a'
+      UNION ALL
+      SELECT 'b'
+
+test_fail_simple_fix_union_all_after:
+  fail_str: |
+      SELECT 'a'
+      UNION ALL SELECT 'b'
+  fix_str: |
+      SELECT 'a'
+      UNION ALL
+      SELECT 'b'
+
+test_fail_simple_fix_union_all_before_and_after:
+  fail_str: |
+      SELECT 'a' UNION ALL SELECT 'b'
+  fix_str: |
+      SELECT 'a'
+      UNION ALL
+      SELECT 'b'
+
+test_pass_multiple_newlines_are_allowed:
+  pass_str: |
+      SELECT 'a'
+
+
+      UNION ALL
+
+
+      SELECT 'b'
+
+# The autofix of L065 doesn't respect indentation of the surrounding query.
+# Hence, the fix result of only L065 looks ugly. But L003 will fix the indentation
+# in a second step.
+# See the test blow.
+test_fail_fix_works_in_subqueries:
+  fail_str: |
+      SELECT * FROM (
+          SELECT 'g' UNION ALL
+          SELECT 'h'
+          UNION ALL SELECT 'j'
+      )
+  fix_str: |
+      SELECT * FROM (
+          SELECT 'g'
+      UNION ALL
+          SELECT 'h'
+          UNION ALL
+      SELECT 'j'
+      )
+
+# Test autofix after L003 passes L065
+test_pass_fix_works_in_subqueries_after_L003_fix:
+  pass_str: |
+      SELECT * FROM (
+          SELECT 'g'
+          UNION ALL
+          SELECT 'h'
+          UNION ALL
+          SELECT 'j'
+      )
+
+test_fail_simple_fix_union_before_and_after:
+  fail_str: |
+      SELECT 'a' UNION SELECT 'b'
+  fix_str: |
+      SELECT 'a'
+      UNION
+      SELECT 'b'
+
+test_fail_simple_fix_intersect_before_and_after:
+  fail_str: |
+      SELECT 'a' INTERSECT SELECT 'b'
+  fix_str: |
+      SELECT 'a'
+      INTERSECT
+      SELECT 'b'
+
+test_fail_simple_fix_except_before_and_after:
+  fail_str: |
+      SELECT 'a' EXCEPT SELECT 'b'
+  fix_str: |
+      SELECT 'a'
+      EXCEPT
+      SELECT 'b'
+
+test_fail_simple_fix_minus_before_and_after:
+  fail_str: |
+      SELECT 'a' EXCEPT SELECT 'b'
+  fix_str: |
+      SELECT 'a'
+      EXCEPT
+      SELECT 'b'
+
+test_fail_simple_fix_bigquery_intersect_distinct_before_and_after:
+  fail_str: |
+      SELECT 'a' INTERSECT DISTINCT SELECT 'b'
+  fix_str: |
+      SELECT 'a'
+      INTERSECT DISTINCT
+      SELECT 'b'
+  configs:
+    core:
+      dialect: bigquery
+
+test_fail_autofix_in_tsql_disabled:
+  fail_str: |
+    SELECT supplyID, supplier
+    FROM dbo.SUPPLY1
+    UNION ALL
+    SELECT supplyID, supplier
+    FROM dbo.SUPPLY2
+  configs:
+    core:
+      dialect: tsql
diff --git a/test/rules/std_L003_L065_combo_test.py b/test/rules/std_L003_L065_combo_test.py
new file mode 100644
index 000000000..f29a27afd
--- /dev/null
+++ b/test/rules/std_L003_L065_combo_test.py
@@ -0,0 +1,46 @@
+"""Tests the combination of L003 and L065.
+
+L003: Indentation not consistent with previous lines
+L065: Set operators should be surrounded by newlines
+
+Auto fix of L065 does not insert correct indentation but just Newlines. It relies on
+L003 to sort out the indentation later. This is what is getting tested here.
+"""
+
+import sqlfluff
+
+
+def test__rules__std_L003_L065_union_all_in_subquery_lint():
+    """Verify a that L065 reports lint errors in subqueries."""
+    sql = (
+        "SELECT * FROM (\n"
+        "    SELECT 'g' UNION ALL\n"
+        "    SELECT 'h'\n"
+        "    UNION ALL SELECT 'j'\n"
+        ")\n"
+    )
+    result = sqlfluff.lint(sql)
+
+    assert "L065" in [r["code"] for r in result]
+
+
+def test__rules__std_L003_L065_union_all_in_subquery_fix():
+    """Verify combination of rules L003 and L065 produces a correct indentation."""
+    sql = (
+        "SELECT * FROM (\n"
+        "    SELECT 'g' UNION ALL\n"
+        "    SELECT 'h'\n"
+        "    UNION ALL SELECT 'j'\n"
+        ")\n"
+    )
+    fixed_sql = (
+        "SELECT * FROM (\n"
+        "    SELECT 'g'\n"
+        "    UNION ALL\n"
+        "    SELECT 'h'\n"
+        "    UNION ALL\n"
+        "    SELECT 'j'\n"
+        ")\n"
+    )
+    result = sqlfluff.fix(sql)
+    assert result == fixed_sql