Repository: sqlfluff/sqlfluff

Issue:
Fix causes duplicate table aliases by simply removing them when table appears twice in join
> L020 | Duplicate table alias

Since after applying `sqlfluff fix` on for example:
```sql
select *
from a a1
join b on a1.i = b.j
join a a2 on a2.i = b.k
```
becomes:
```sql
select *
from a
join b on a.i = b.j
join a on a.i = b.k
```

Workaround I think is to move part of the join to a `with` CTE, e.g. in this case:
```sql
with a2 as (select i from a)
select *
from a
join b on a.i = b.j
join a2 on a2.b.k
```

Discussion:

Patch:
diff --git a/src/sqlfluff/core/rules/std/L031.py b/src/sqlfluff/core/rules/std/L031.py
index b4a71b071..3014e7db9 100644
--- a/src/sqlfluff/core/rules/std/L031.py
+++ b/src/sqlfluff/core/rules/std/L031.py
@@ -1,5 +1,9 @@
 """Implementation of Rule L031."""
 
+from collections import Counter, defaultdict
+from typing import Generator, NamedTuple
+
+from sqlfluff.core.parser import BaseSegment
 from sqlfluff.core.rules.base import BaseRule, LintFix, LintResult
 from sqlfluff.core.rules.doc_decorators import document_fix_compatible
 
@@ -59,6 +63,7 @@ class Rule_L031(BaseRule):
                 return None
 
             from_expression = from_clause_segment.get_child("from_expression")
+            from_expression_element = None
             if from_expression:
                 from_expression_element = from_expression.get_child(
                     "from_expression_element"
@@ -97,13 +102,18 @@ class Rule_L031(BaseRule):
             )
         return None
 
-    def _lint_aliases_in_join(
-        self, base_table, table_expression_segments, column_reference_segments, segment
-    ):
-        """Lint and fix all aliases in joins - except for self-joins."""
-        # A buffer to keep any violations.
-        violation_buff = []
+    class TableAliasInfo(NamedTuple):
+        """Structure yielded by_filter_table_expressions()."""
+
+        table_ref: BaseSegment
+        whitespace_ref: BaseSegment
+        alias_exp_ref: BaseSegment
+        alias_identifier_ref: BaseSegment
 
+    @classmethod
+    def _filter_table_expressions(
+        cls, base_table, table_expression_segments
+    ) -> Generator[TableAliasInfo, None, None]:
         for table_exp in table_expression_segments:
             table_ref = table_exp.get_child("table_expression").get_child(
                 "object_reference"
@@ -131,38 +141,75 @@ class Rule_L031(BaseRule):
                 continue
 
             alias_identifier_ref = alias_exp_ref.get_child("identifier")
+            yield cls.TableAliasInfo(
+                table_ref, whitespace_ref, alias_exp_ref, alias_identifier_ref
+            )
+
+    def _lint_aliases_in_join(
+        self, base_table, table_expression_segments, column_reference_segments, segment
+    ):
+        """Lint and fix all aliases in joins - except for self-joins."""
+        # A buffer to keep any violations.
+        violation_buff = []
+
+        to_check = list(
+            self._filter_table_expressions(base_table, table_expression_segments)
+        )
+
+        # How many times does each table appear in the FROM clause?
+        table_counts = Counter(ai.table_ref.raw for ai in to_check)
+
+        # What is the set of aliases used for each table? (We are mainly
+        # interested in the NUMBER of different aliases used.)
+        table_aliases = defaultdict(set)
+        for ai in to_check:
+            table_aliases[ai.table_ref.raw].add(ai.alias_identifier_ref.raw)
+
+        # For each aliased table, check whether to keep or remove it.
+        for alias_info in to_check:
+            # If the same table appears more than once in the FROM clause with
+            # different alias names, do not consider removing its aliases.
+            # The aliases may have been introduced simply to make each
+            # occurrence of the table independent within the query.
+            if (
+                table_counts[alias_info.table_ref.raw] > 1
+                and len(table_aliases[alias_info.table_ref.raw]) > 1
+            ):
+                continue
+
             select_clause = segment.get_child("select_clause")
 
             ids_refs = []
 
             # Find all references to alias in select clause
+            alias_name = alias_info.alias_identifier_ref.raw
             for alias_with_column in select_clause.recursive_crawl("object_reference"):
                 used_alias_ref = alias_with_column.get_child("identifier")
-                if used_alias_ref and used_alias_ref.raw == alias_identifier_ref.raw:
+                if used_alias_ref and used_alias_ref.raw == alias_name:
                     ids_refs.append(used_alias_ref)
 
             # Find all references to alias in column references
             for exp_ref in column_reference_segments:
                 used_alias_ref = exp_ref.get_child("identifier")
                 # exp_ref.get_child('dot') ensures that the column reference includes a table reference
-                if (
-                    used_alias_ref.raw == alias_identifier_ref.raw
-                    and exp_ref.get_child("dot")
-                ):
+                if used_alias_ref.raw == alias_name and exp_ref.get_child("dot"):
                     ids_refs.append(used_alias_ref)
 
             # Fixes for deleting ` as sth` and for editing references to aliased tables
             fixes = [
-                *[LintFix("delete", d) for d in [alias_exp_ref, whitespace_ref]],
                 *[
-                    LintFix("edit", alias, alias.edit(table_ref.raw))
-                    for alias in [alias_identifier_ref, *ids_refs]
+                    LintFix("delete", d)
+                    for d in [alias_info.alias_exp_ref, alias_info.whitespace_ref]
+                ],
+                *[
+                    LintFix("edit", alias, alias.edit(alias_info.table_ref.raw))
+                    for alias in [alias_info.alias_identifier_ref, *ids_refs]
                 ],
             ]
 
             violation_buff.append(
                 LintResult(
-                    anchor=alias_identifier_ref,
+                    anchor=alias_info.alias_identifier_ref,
                     description="Avoid using aliases in join condition",
                     fixes=fixes,
                 )


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L031.yml b/test/fixtures/rules/std_rule_cases/L031.yml
index 284a865ec..eb3ddeb76 100644
--- a/test/fixtures/rules/std_rule_cases/L031.yml
+++ b/test/fixtures/rules/std_rule_cases/L031.yml
@@ -147,3 +147,13 @@ issue_239:
   configs:
     core:
       dialect: bigquery
+
+# The rule was removing the aliases from this query, causing incorrect behavior.
+# (Aliases may not only be used in select targets; they also influence whether
+# multiple joins to a table are independent or not).
+issue_610:
+  pass_str: |
+    SELECT aaaaaa.c
+    FROM aaaaaa
+    JOIN bbbbbb AS b ON b.a = aaaaaa.id
+    JOIN bbbbbb AS b2 ON b2.other = b.id