Repository: sqlfluff/sqlfluff

Issue:
Update L033 to enforce explicit UNION ALL or UNION DISTINCT
Was discussing this issue with @nolanbconaway, who raised in #general in slack. [Slack Discussion](https://sqlfluff.slack.com/archives/C01GPHR8J0G/p1613511556030200)

In its current state, L033 will not allow a simple `UNION`. This is problematic as `UNION` eliminates duplicates while `UNION ALL` does not.

Instead of enforcing a `UNION ALL` in the place of `UNION`, enforce an explicit `UNION ALL` or `UNION DISTINCT`.



Discussion:

Patch:
diff --git a/src/sqlfluff/core/rules/base.py b/src/sqlfluff/core/rules/base.py
index 3200e35c5..ac30aabf9 100644
--- a/src/sqlfluff/core/rules/base.py
+++ b/src/sqlfluff/core/rules/base.py
@@ -99,7 +99,7 @@ class LintFix:
             to be moved *after* the edit), for an `edit` it implies the segment
             to be replaced.
         edit (:obj:`BaseSegment`, optional): For `edit` and `create` fixes, this
-            hold the segment, or iterable of segments to create to replace at the
+            hold the segment, or iterable of segments to create or replace at the
             given `anchor` point.
 
     """
diff --git a/src/sqlfluff/core/rules/std/L033.py b/src/sqlfluff/core/rules/std/L033.py
index 8f5668ee2..fbd7ecfa1 100644
--- a/src/sqlfluff/core/rules/std/L033.py
+++ b/src/sqlfluff/core/rules/std/L033.py
@@ -1,30 +1,33 @@
 """Implementation of Rule L033."""
 
-from sqlfluff.core.rules.base import BaseRule, LintResult
+from sqlfluff.core.rules.base import BaseRule, LintFix, LintResult
 
 
 class Rule_L033(BaseRule):
-    """UNION ALL is preferred over UNION.
+    """UNION [DISTINCT|ALL] is preferred over just UNION.
 
     | **Anti-pattern**
-    | In this example, UNION ALL should be preferred over UNION
+    | In this example, UNION DISTINCT should be preferred over UNION, because
+    | explicit is better than implicit.
 
     .. code-block:: sql
 
         SELECT a, b FROM table_1 UNION SELECT a, b FROM table_2
 
     | **Best practice**
-    | Replace UNION with UNION ALL
+    | Specify DISTINCT or ALL after UNION. (Note that DISTINCT is the default
+    | behavior.
 
     .. code-block:: sql
 
-        SELECT a, b FROM table_1 UNION ALL SELECT a, b FROM table_2
+        SELECT a, b FROM table_1 UNION DISTINCT SELECT a, b FROM table_2
 
     """
 
     def _eval(self, segment, raw_stack, **kwargs):
-        """Look for UNION keyword not immediately followed by ALL keyword. Note that UNION DISTINCT is valid, rule only applies to bare UNION.
+        """Look for UNION keyword not immediately followed by DISTINCT or ALL.
 
+        Note that UNION DISTINCT is valid, rule only applies to bare UNION.
         The function does this by looking for a segment of type set_operator
         which has a UNION but no DISTINCT or ALL.
         """
@@ -32,5 +35,19 @@ class Rule_L033(BaseRule):
             if "UNION" in segment.raw.upper() and not (
                 "ALL" in segment.raw.upper() or "DISTINCT" in segment.raw.upper()
             ):
-                return LintResult(anchor=segment)
+                union = self.make_keyword(
+                    raw="UNION",
+                    pos_marker=segment.pos_marker,
+                )
+                ins = self.make_whitespace(raw=" ", pos_marker=segment.pos_marker)
+                distinct = self.make_keyword(
+                    raw="DISTINCT",
+                    pos_marker=segment.pos_marker,
+                )
+                return LintResult(
+                    anchor=segment,
+                    fixes=[
+                        LintFix("edit", segment.segments[0], [union, ins, distinct])
+                    ],
+                )
         return LintResult()


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L033.yml b/test/fixtures/rules/std_rule_cases/L033.yml
index 6dd7c0bed..0a3305a2c 100644
--- a/test/fixtures/rules/std_rule_cases/L033.yml
+++ b/test/fixtures/rules/std_rule_cases/L033.yml
@@ -24,20 +24,18 @@ test_2:
       c,
       d
     FROM tbl1
-
-test_3:
-  fail_str: |
+  fix_str: |
     SELECT
       a,
       b
     FROM tbl
-    UNION
+    UNION DISTINCT
     SELECT
       c,
       d
     FROM tbl1
 
-test_4:
+test_3:
   pass_str: |
     SELECT
       a,
@@ -49,7 +47,7 @@ test_4:
       d
     FROM tbl1
 
-test_5:
+test_4:
   pass_str: |
     SELECT
       a,
@@ -65,7 +63,7 @@ test_5:
       d
     FROM tbl1
 
-test_6:
+test_5:
   fail_str: |
     SELECT
       a,
@@ -81,8 +79,23 @@ test_6:
       e,
       f
     FROM tbl2
+  fix_str: |
+    SELECT
+      a,
+      b
+    FROM tbl
+    UNION DISTINCT
+    SELECT
+      c,
+      d
+    FROM tbl1
+    UNION DISTINCT
+    SELECT
+      e,
+      f
+    FROM tbl2
 
-test_7:
+test_6:
   fail_str: |
     select
       a,
@@ -98,3 +111,18 @@ test_7:
       e,
       f
     from tbl2
+  fix_str: |
+    select
+      a,
+      b
+    from tbl
+    union distinct
+    select
+      c,
+      d
+    from tbl1
+    UNION DISTINCT
+    select
+      e,
+      f
+    from tbl2