Repository: sqlfluff/sqlfluff

Issue:
Deduplicate violations in the same position
### Search before asking

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


### Description

When linting jinja files with loops we get multiple output violations for each time around the loop. e.g.

```sql
select
    a,
    {% for val in [1, 2, 3, 4, 5, 6] %}
        d+ {{ val }},
    {% endfor %}
    b
```

we get

```
== [test.sql] FAIL
L:   4 | P:  10 | L006 | Missing whitespace before +
L:   4 | P:  10 | L006 | Missing whitespace before +
L:   4 | P:  10 | L006 | Missing whitespace before +
L:   4 | P:  10 | L006 | Missing whitespace before +
L:   4 | P:  10 | L006 | Missing whitespace before +
L:   4 | P:  10 | L006 | Missing whitespace before +
L:   7 | P:   1 | L001 | Unnecessary trailing whitespace.
```

The duplicated `Missing whitespace` isn't helpful for the user. Regardless of whether we keep them in the background (perhaps we should), they shouldn't be shown to the user here because we're showing the same issue multiple times.

### Use case

CLI linting

### Dialect

all

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

- [X] 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/4041?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) Report
Base: **61.24**% // Head: **61.29**% // Increases project coverage by **`+0.04%`** :tada:
> Coverage data is based on head [(`eeab4b1`)](https://codecov.io/gh/sqlfluff/sqlfluff/pull/4041?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) compared to base [(`8161a9a`)](https://codecov.io/gh/sqlfluff/sqlfluff/commit/8161a9a16b579273bba6d723ef446654902891fb?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).
> Patch coverage: 92.30% of modified lines in pull request are covered.

> :exclamation: Current head eeab4b1 differs from pull request most recent head cccdcf5. Consider uploading reports for the commit cccdcf5 to get more accurate results

<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##             main    #4041      +/-   ##
==========================================
+ Coverage   61.24%   61.29%   +0.04%     
==========================================
  Files         190      190              
  Lines       14518    14530      +12     
==========================================
+ Hits         8892     8906      +14     
+ Misses       5626     5624       -2     
```


| [Impacted Files](https://codecov.io/gh/sqlfluff/sqlfluff/pull/4041?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) | Coverage Δ | |
|---|---|---|
| [src/sqlfluff/core/linter/linter.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/4041/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NvcmUvbGludGVyL2xpbnRlci5weQ==) | `75.65% <ø> (+0.71%)` | :arrow_up: |
| [src/sqlfluff/core/linter/linted\_file.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/4041/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NvcmUvbGludGVyL2xpbnRlZF9maWxlLnB5) | `75.68% <92.30%> (+1.05%)` | :arrow_up: |
| [src/sqlfluff/core/templaters/slicers/tracer.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/4041/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NvcmUvdGVtcGxhdGVycy9zbGljZXJzL3RyYWNlci5weQ==) | `87.00% <0.00%> (-0.06%)` | :arrow_down: |

Help us with your feedback. Take ten seconds to tell us [how you rate us](https://about.codecov.io/nps?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Have a feature suggestion? [Share it here.](https://app.codecov.io/gh/feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)

</details>

[:umbrella: View full report at Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/4041?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).   
:loudspeaker: Do you have feedback about the report comment? [Let us know in this issue](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).


Patch:
diff --git a/src/sqlfluff/core/errors.py b/src/sqlfluff/core/errors.py
index 76e8b626c..d686566a4 100644
--- a/src/sqlfluff/core/errors.py
+++ b/src/sqlfluff/core/errors.py
@@ -1,5 +1,5 @@
 """Errors - these are closely linked to what used to be called violations."""
-from typing import Optional, Tuple, List
+from typing import Optional, Tuple, Any, List
 
 CheckTuple = Tuple[str, int, int]
 
@@ -85,6 +85,18 @@ class SQLBaseError(ValueError):
             "description": self.desc(),
         }
 
+    def check_tuple(self) -> CheckTuple:
+        """Get a tuple representing this error. Mostly for testing."""
+        return (
+            self.rule_code(),
+            self.line_no,
+            self.line_pos,
+        )
+
+    def source_signature(self) -> Tuple[Any, ...]:
+        """Return hashable source signature for deduplication."""
+        return (self.check_tuple(), self.desc())
+
     def ignore_if_in(self, ignore_iterable: List[str]):
         """Ignore this violation if it matches the iterable."""
         if self._identifier in ignore_iterable:
@@ -189,13 +201,21 @@ class SQLLintError(SQLBaseError):
             return True
         return False
 
-    def check_tuple(self) -> CheckTuple:
-        """Get a tuple representing this error. Mostly for testing."""
-        return (
-            self.rule.code,
-            self.line_no,
-            self.line_pos,
+    def source_signature(self) -> Tuple[Any, ...]:
+        """Return hashable source signature for deduplication.
+
+        For linting errors we need to dedupe on more than just location and
+        description, we also need to check the edits potentially made, both
+        in the templated file but also in the source.
+        """
+        fix_raws = tuple(
+            tuple(e.raw for e in f.edit) if f.edit else None for f in self.fixes
+        )
+        source_fixes = tuple(
+            tuple(tuple(e.source_fixes) for e in f.edit) if f.edit else None
+            for f in self.fixes
         )
+        return (self.check_tuple(), self.description, fix_raws, source_fixes)
 
     def __repr__(self):
         return "<SQLLintError: rule {} pos:{!r}, #fixes: {}, description: {}>".format(
diff --git a/src/sqlfluff/core/linter/linted_file.py b/src/sqlfluff/core/linter/linted_file.py
index 6ccbe14eb..f86e3ab7d 100644
--- a/src/sqlfluff/core/linter/linted_file.py
+++ b/src/sqlfluff/core/linter/linted_file.py
@@ -54,18 +54,43 @@ class LintedFile(NamedTuple):
         """Make a list of check_tuples.
 
         This assumes that all the violations found are
-        linting violations (and therefore implement `check_tuple()`).
-        If they don't then this function raises that error.
+        linting violations. If they don't then this function
+        raises that error.
         """
         vs: List[CheckTuple] = []
         v: SQLLintError
         for v in self.get_violations():
-            if hasattr(v, "check_tuple"):
+            if isinstance(v, SQLLintError):
                 vs.append(v.check_tuple())
             elif raise_on_non_linting_violations:
                 raise v
         return vs
 
+    @staticmethod
+    def deduplicate_in_source_space(
+        violations: List[SQLBaseError],
+    ) -> List[SQLBaseError]:
+        """Removes duplicates in the source space.
+
+        This is useful for templated files with loops, where we'll
+        get a violation for each pass around the loop, but the user
+        only cares about it once and we're only going to fix it once.
+
+        By filtering them early we get a more a more helpful CLI
+        output *and* and more efficient fixing routine (by handling
+        fewer fixes).
+        """
+        new_violations = []
+        dedupe_buffer = set()
+        for v in violations:
+            signature = v.source_signature()
+            if signature not in dedupe_buffer:
+                new_violations.append(v)
+                dedupe_buffer.add(signature)
+            else:
+                linter_logger.debug("Removing duplicate source violation: %s", v)
+        return new_violations
+
     def get_violations(
         self,
         rules: Optional[Union[str, Tuple[str, ...]]] = None,
diff --git a/src/sqlfluff/core/linter/linter.py b/src/sqlfluff/core/linter/linter.py
index bc1fd69d7..45853b792 100644
--- a/src/sqlfluff/core/linter/linter.py
+++ b/src/sqlfluff/core/linter/linter.py
@@ -736,7 +736,8 @@ class Linter:
 
         linted_file = LintedFile(
             parsed.fname,
-            violations,
+            # Deduplicate violations
+            LintedFile.deduplicate_in_source_space(violations),
             time_dict,
             tree,
             ignore_mask=ignore_buff,


Test patch:
diff --git a/test/core/linter_test.py b/test/core/linter_test.py
index 878e78d1d..d9ca20e00 100644
--- a/test/core/linter_test.py
+++ b/test/core/linter_test.py
@@ -407,8 +407,9 @@ def test__linter__empty_file():
         (
             False,
             [
-                ("L006", 3, 16),
-                ("L006", 3, 16),
+                # there are still two of each because L006 checks
+                # for both *before* and *after* the operator.
+                # The deduplication filter makes sure there aren't 4.
                 ("L006", 3, 16),
                 ("L006", 3, 16),
                 ("L006", 3, 39),
@@ -418,7 +419,11 @@ def test__linter__empty_file():
     ],
 )
 def test__linter__mask_templated_violations(ignore_templated_areas, check_tuples):
-    """Test linter masks files properly around templated content."""
+    """Test linter masks files properly around templated content.
+
+    NOTE: this also tests deduplication of fixes which have the same
+    source position. i.e. `LintedFile.deduplicate_in_source_space()`.
+    """
     lntr = Linter(
         config=FluffConfig(
             overrides={