Repository: sqlfluff/sqlfluff

Issue:
Add fix routine for L025
[This query](http://sqlfluff-online.herokuapp.com/fluffed?sql=H4sIACarKV8C_ytOzUlNLlHQUkgrys9V0CiGcA0VEosVEjUVikuTCktTiyoBBIbljSYAAAA%3D&dialect=postgres) has two problems:

```sh
$ echo 'select * from (select 1 as a) subquery' | sqlfluff lint -
== [stdin] FAIL
L:   1 | P:  31 | L011 | Implicit aliasing of table not allowed. Use explicit
                       | `AS` clause.
L:   1 | P:  31 | L025 | Alias 'subquery' is never used in SELECT statement.
```

It gets fixed as such 

```sh
$ echo 'select * from (select 1 as a) subquery' | sqlfluff fix - --rules L011,L010,L025                                
select * from (select 1 as a) as subquery
```

But if you lint the fixed sql, L025 remains!

```sh
$ echo 'select * from (select 1 as a) subquery' | sqlfluff fix - --rules L011,L010,L025 | sqlfluff lint -
== [stdin] FAIL
L:   1 | P:  34 | L025 | Alias 'subquery' is never used in SELECT statement.
```

The conjunction of these two errors shows that the "real" fix would be [this query](http://sqlfluff-online.herokuapp.com/fluffed?sql=H4sIAEGrKV8C_ytOzUlNLlHQUkgrys9V0CiGcA0VEosVEjV5uQDXINLGHwAAAA%3D%3D&dialect=postgres)

```sh
$ echo 'select * from (select 1 as a)' | sqlfluff lint -
# no violations!
```

Should fluff remove the implicit alias if it is never used?



Discussion:

Patch:
diff --git a/src/sqlfluff/core/dialects/dialect_ansi.py b/src/sqlfluff/core/dialects/dialect_ansi.py
index 154347e10..5de3d80f0 100644
--- a/src/sqlfluff/core/dialects/dialect_ansi.py
+++ b/src/sqlfluff/core/dialects/dialect_ansi.py
@@ -11,6 +11,7 @@ labs full sql grammar. In particular their way for dividing up the expression
 grammar. Check out their docs, they're awesome.
 https://www.cockroachlabs.com/docs/stable/sql-grammar.html#select_stmt
 """
+from typing import List, Tuple, NamedTuple, Optional
 
 from ..parser import (
     Matchable,
@@ -746,6 +747,16 @@ ansi_dialect.add(
 )
 
 
+class AliasInfo(NamedTuple):
+    """Details about a table alias."""
+
+    ref_str: str  # Name given to the alias
+    segment: BaseSegment  # Identifier segment containing the name
+    aliased: bool
+    table_expression: BaseSegment
+    alias_expression: Optional[BaseSegment]
+
+
 @ansi_dialect.segment()
 class TableExpressionSegment(BaseSegment):
     """A table expression."""
@@ -761,7 +772,7 @@ class TableExpressionSegment(BaseSegment):
         Ref("PostTableExpressionGrammar", optional=True),
     )
 
-    def get_eventual_alias(self):
+    def get_eventual_alias(self) -> Optional[AliasInfo]:
         """Return the eventual table name referred to by this table expression.
 
         Returns:
@@ -774,7 +785,7 @@ class TableExpressionSegment(BaseSegment):
         if alias_expression:
             # If it has an alias, return that
             segment = alias_expression.get_child("identifier")
-            return (segment.raw, segment, True)
+            return AliasInfo(segment.raw, segment, True, self, alias_expression)
 
         # If not return the object name (or None if there isn't one)
         # ref = self.get_child("object_reference")
@@ -783,7 +794,7 @@ class TableExpressionSegment(BaseSegment):
             # Return the last element of the reference, which
             # will already be a tuple.
             penultimate_ref = list(ref.iter_raw_references())[-1]
-            return (*penultimate_ref, False)
+            return AliasInfo(penultimate_ref[0], penultimate_ref[1], False, self, None)
         # No references or alias, return None
         return None
 
@@ -972,7 +983,7 @@ class JoinClauseSegment(BaseSegment):
         Dedent,
     )
 
-    def get_eventual_alias(self):
+    def get_eventual_alias(self) -> AliasInfo:
         """Return the eventual table name referred to by this join clause."""
         table_expression = self.get_child("table_expression")
         return table_expression.get_eventual_alias()
@@ -1035,7 +1046,7 @@ class FromClauseSegment(BaseSegment):
         Dedent.when(indented_joins=True),
     )
 
-    def get_eventual_aliases(self):
+    def get_eventual_aliases(self) -> List[Tuple[BaseSegment, AliasInfo]]:
         """List the eventual aliases of this from clause.
 
         Comes as a list of tuples (table expr, tuple (string, segment, bool)).
@@ -1045,7 +1056,7 @@ class FromClauseSegment(BaseSegment):
         join_clauses = self.get_children("join_clause")
         # Iterate through the potential sources of aliases
         for clause in (*direct_table_children, *join_clauses):
-            ref = clause.get_eventual_alias()
+            ref: AliasInfo = clause.get_eventual_alias()
             # Only append if non null. A None reference, may
             # indicate a generator expression or similar.
             table_expr = (
diff --git a/src/sqlfluff/core/rules/std/L015.py b/src/sqlfluff/core/rules/std/L015.py
index 8744dc759..b3ad6be0a 100644
--- a/src/sqlfluff/core/rules/std/L015.py
+++ b/src/sqlfluff/core/rules/std/L015.py
@@ -1,8 +1,10 @@
 """Implementation of Rule L015."""
 
 from ..base import BaseCrawler, LintFix, LintResult
+from ..doc_decorators import document_fix_compatible
 
 
+@document_fix_compatible
 class Rule_L015(BaseCrawler):
     """DISTINCT used with parentheses.
 
diff --git a/src/sqlfluff/core/rules/std/L020.py b/src/sqlfluff/core/rules/std/L020.py
index 3de4fe4c7..5c27c49df 100644
--- a/src/sqlfluff/core/rules/std/L020.py
+++ b/src/sqlfluff/core/rules/std/L020.py
@@ -25,17 +25,17 @@ class Rule_L020(BaseCrawler):
         # Are any of the aliases the same?
         for a1, a2 in itertools.combinations(table_aliases, 2):
             # Compare the strings
-            if a1[0] == a2[0] and a1[0]:
+            if a1.ref_str == a2.ref_str and a1.ref_str:
                 # If there are any, then the rest of the code
                 # won't make sense so just return here.
                 return [
                     LintResult(
                         # Reference the element, not the string.
-                        anchor=a2[1],
+                        anchor=a2.segment,
                         description=(
                             "Duplicate table alias {0!r}. Table "
                             "aliases should be unique."
-                        ).format(a2[0]),
+                        ).format(a2.ref_str),
                     )
                 ]
         return None
diff --git a/src/sqlfluff/core/rules/std/L025.py b/src/sqlfluff/core/rules/std/L025.py
index 30b8b93cf..cb8c87574 100644
--- a/src/sqlfluff/core/rules/std/L025.py
+++ b/src/sqlfluff/core/rules/std/L025.py
@@ -1,9 +1,12 @@
 """Implementation of Rule L025."""
 
-from ..base import LintResult
+from ..base import LintFix, LintResult
+from ..doc_decorators import document_fix_compatible
 from sqlfluff.core.rules.std.L020 import Rule_L020
+from sqlfluff.core.dialects.dialect_ansi import AliasInfo
 
 
+@document_fix_compatible
 class Rule_L025(Rule_L020):
     """Tables should not be aliased if that alias is not used.
 
@@ -52,14 +55,33 @@ class Rule_L025(Rule_L020):
             if tbl_ref:
                 tbl_refs.add(tbl_ref[0])
 
-        for ref_str, seg, aliased in table_aliases:
-            if aliased and ref_str not in tbl_refs:
+        alias: AliasInfo
+        for alias in table_aliases:
+            if alias.aliased and alias.ref_str not in tbl_refs:
+                fixes = [LintFix("delete", alias.alias_expression)]
+                found_alias_segment = False
+                # Walk back to remove indents/whitespaces
+                for segment in reversed(alias.table_expression.segments):
+                    if not found_alias_segment:
+                        if segment is alias.alias_expression:
+                            found_alias_segment = True
+                    else:
+                        if (
+                            segment.name == "whitespace"
+                            or segment.name == "newline"
+                            or segment.is_meta
+                        ):
+                            fixes.append(LintFix("delete", segment))
+                        else:
+                            # Stop once we reach an other, "regular" segment.
+                            break
                 violation_buff.append(
                     LintResult(
-                        anchor=seg,
+                        anchor=alias.segment,
                         description="Alias {0!r} is never used in SELECT statement.".format(
-                            ref_str
+                            alias.ref_str
                         ),
+                        fixes=fixes,
                     )
                 )
         return violation_buff or None
diff --git a/src/sqlfluff/core/rules/std/L026.py b/src/sqlfluff/core/rules/std/L026.py
index 90abe7268..1139e01d7 100644
--- a/src/sqlfluff/core/rules/std/L026.py
+++ b/src/sqlfluff/core/rules/std/L026.py
@@ -43,7 +43,7 @@ class Rule_L026(Rule_L025):
         for r in references:
             tbl_ref = r.extract_reference(level=2)
             # Check whether the string in the list of strings
-            if tbl_ref and tbl_ref[0] not in [a[0] for a in table_aliases]:
+            if tbl_ref and tbl_ref[0] not in [a.ref_str for a in table_aliases]:
                 # Last check, this *might* be a correlated subquery reference.
                 if parent_select:
                     parent_aliases, _ = self._get_aliases_from_select(parent_select)


Test patch:
diff --git a/test/core/rules/test_cases/L025.yml b/test/core/rules/test_cases/L025.yml
index 0e3d835b6..e8bb4750a 100644
--- a/test/core/rules/test_cases/L025.yml
+++ b/test/core/rules/test_cases/L025.yml
@@ -3,6 +3,7 @@ rule: L025
 test_1:
   # Aliases not referenced.
   fail_str: SELECT * FROM my_tbl AS foo
+  fix_str: SELECT * FROM my_tbl
 
 test_2:
   pass_str: SELECT * FROM my_tbl AS foo JOIN other_tbl on other_tbl.x = foo.x
@@ -20,3 +21,12 @@ test_ignore_value_table_functions:
   configs:
     core:
       dialect: bigquery
+
+test_4:
+  # Similar to test_1, but with implicit alias.
+  fail_str: SELECT * FROM my_tbl foo
+  fix_str: SELECT * FROM my_tbl
+
+test_5:
+  fail_str: select * from (select 1 as a) subquery
+  fix_str: select * from (select 1 as a)