Repository: sqlfluff/sqlfluff

Issue:
Exception thrown when SELECT DISTINCT not on the same line
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

Check a file containing this request:

```sql
SELECT
    DISTINCT `FIELD`
FROM `TABLE`;
```

It fails this way:

```log
CRITICAL   [RF01] Applying rule RF01 to 'file.sql' threw an Exception:  
Traceback (most recent call last):
  File "/app/.venv/lib/python3.9/site-packages/sqlfluff/core/rules/base.py", line 864, in crawl
    res = self._eval(context=context)
  File "/app/.venv/lib/python3.9/site-packages/sqlfluff/rules/references/RF01.py", line 107, in _eval
    self._analyze_table_references(
  File "/app/.venv/lib/python3.9/site-packages/sqlfluff/rules/references/RF01.py", line 152, in _analyze_table_references
    if not self._should_ignore_reference(r, selectable):
  File "/app/.venv/lib/python3.9/site-packages/sqlfluff/rules/references/RF01.py", line 168, in _should_ignore_reference
    ref_path = selectable.selectable.path_to(reference)
  File "/app/.venv/lib/python3.9/site-packages/sqlfluff/core/parser/segments/base.py", line 1184, in path_to
    elif not self.get_start_loc() <= midpoint.get_start_loc() <= self.get_end_loc():
  File "/app/.venv/lib/python3.9/site-packages/sqlfluff/core/parser/segments/base.py", line 877, in get_start_loc
    assert self.pos_marker
AssertionError
== [file.sql] FAIL
L:   1 | P:   1 | LT09 | Select targets should be on a new line unless there is
                       | only one select target. [layout.select_targets]
L:   1 | P:   1 | LT10 | 'SELECT' modifiers (e.g. 'DISTINCT') must be on the same
                       | line as 'SELECT'. [layout.select_modifiers]
L:   1 | P:   1 | RF01 | Unexpected exception: ;
Could you open an issue at
                       | https://github.com/sqlfluff/sqlfluff/issues ?
You can
                       | ignore this exception for now, by adding '-- noqa: RF01'
                       | at the end
of line 1
 [references.from]
L:   2 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:   3 | P:  13 | LT12 | Files must end with a single trailing newline.
                       | [layout.end_of_file]
All Finished!
```

Checking the following request does not throw an exception (move `DISTINCT` on same line than `SELECT`):

```sql
SELECT DISTINCT `FIELD`
FROM `TABLE`;
```

Additionally, I'd like to add that checking the first request on https://online.sqlfluff.com/fluffed leads to the same exception. But if you check this request:
```sql
SELECT 
DISTINCT
`FIELD`
FROM `TABLE`;
```
Then the website crashes.

### Expected Behaviour

I would expect not to have an exception.

### Observed Behaviour

An exception was thrown whereas, I think, there is no reason to throw it.

### How to reproduce

Check the following SQL:

```sql
SELECT
    DISTINCT `FIELD`
FROM `TABLE`;
```

### Dialect

MySQL

### Version

2.3.2

### Configuration

```
[sqlfluff]
# Supported dialects https://docs.sqlfluff.com/en/stable/dialects.html
dialect = mysql
encoding = utf-8
# Exclude rule LT01/layout.spacing: it expects a space even after type of fields (i.e. "INT (11)")
# Exclude rule ST05/structure.subquery: MySQL badly supports CTEs.
exclude_rules = LT01, ST05
ignore = parsing
max_line_length = 120
# Below controls SQLFluff output, see max_line_length for SQL output
output_line_length = 80
templater = raw
verbose = 0

[sqlfluff:layout:type:binary_operator]
line_position = leading

[sqlfluff:layout:type:comma]
line_position = trailing
spacing_before = touch

[sqlfluff:indentation]
# See https://docs.sqlfluff.com/en/stable/indentation.html
indent_unit = space
indented_joins = True
indented_using_on = True
tab_space_size = 4

# Some rules can be configured directly from the config common to other rules
[sqlfluff:rules]
allow_scalar = True
quoted_identifiers_policy = none
single_table_references = consistent
unquoted_identifiers_policy = all

[sqlfluff:rules:aliasing.column]
aliasing = explicit

[sqlfluff:rules:aliasing.table]
aliasing = explicit

[sqlfluff:rules:ambiguous.column_references]
group_by_and_order_by_style = consistent

[sqlfluff:rules:capitalisation.functions]
capitalisation_policy = upper
ignore_words = None

[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = upper
ignore_words = None

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper
ignore_words = None

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper
ignore_words = None

[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = upper

[sqlfluff:rules:convention.count_rows]
prefer_count_0 = False
prefer_count_1 = True

[sqlfluff:rules:convention.select_trailing_comma]
select_clause_trailing_comma = forbid

[sqlfluff:rules:convention.terminator]
multiline_newline = False
require_final_semicolon = True

[sqlfluff:rules:layout.long_lines]
ignore_comment_lines = True

[sqlfluff:rules:references.keywords]
ignore_words = None
quoted_identifiers_policy = none
unquoted_identifiers_policy = all

[sqlfluff:rules:convention.quoted_literals]
preferred_quoted_literal_style = single_quotes

[sqlfluff:rules:references.quoting]
prefer_quoted_identifiers = True

[sqlfluff:rules:references.special_chars]
additional_allowed_characters = ""
allow_space_in_identifier = False
quoted_identifiers_policy = all
# Special characters in identifiers
unquoted_identifiers_policy = 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:
coveralls: 
[![Coverage Status](https://coveralls.io/builds/62671128/badge)](https://coveralls.io/builds/62671128)

coverage: 99.995% (-0.005%) from 100.0% when pulling **ed1eec149f7ce5d2191d16473118c58a0f7523df on ac/issues** into **895e668a0047bd31fb87865fcab50d846bfb88ce on main**.

github-actions[bot]: # Coverage Results ⚠️
```
Name                                        Stmts   Miss  Cover   Missing
-------------------------------------------------------------------------
src/sqlfluff/core/parser/segments/base.py     599      1    99%   832
-------------------------------------------------------------------------
TOTAL                                       16729      1    99%

219 files skipped due to complete coverage.
Coverage failure: total of 99 is less than fail-under=100

```

greg-finley: @alanmcruickshank are we able to add a rules test showing that the linked issue is resolved?
alanmcruickshank: > @alanmcruickshank are we able to add a rules test showing that the linked issue is resolved?

Yeah, good idea 👍. Will do...

Patch:
diff --git a/src/sqlfluff/core/parser/segments/base.py b/src/sqlfluff/core/parser/segments/base.py
index 3bdbc0cd8..6f5f34c2f 100644
--- a/src/sqlfluff/core/parser/segments/base.py
+++ b/src/sqlfluff/core/parser/segments/base.py
@@ -13,7 +13,6 @@ from __future__ import annotations
 import logging
 import weakref
 from collections import defaultdict
-from copy import copy
 from dataclasses import dataclass
 from io import StringIO
 from itertools import chain
@@ -965,25 +964,55 @@ class BaseSegment(metaclass=SegmentMetaclass):
             )
 
     def copy(
-        self, segments: Optional[Tuple["BaseSegment", ...]] = None
+        self,
+        segments: Optional[Tuple["BaseSegment", ...]] = None,
+        parent: Optional["BaseSegment"] = None,
     ) -> "BaseSegment":
         """Copy the segment recursively, with appropriate copying of references.
 
         Optionally provide child segments which have already been dealt
         with to avoid another copy operation.
+
+        NOTE: In the copy operation it's really important that we get
+        a clean segregation so that we can't go backward and mutate the
+        source object, but at the same time we should be mindful of what
+        _needs_ to be copied to avoid a deep copy where one isn't required.
         """
-        new_seg = copy(self)
+        cls = self.__class__
+        new_segment = cls.__new__(cls)
         # Position markers are immutable, and it's important that we keep
         # a reference to the same TemplatedFile, so keep the same position
-        # marker.
-        new_seg.pos_marker = self.pos_marker
+        # marker. By updating from the source dict, we achieve that.
+        # By using the __dict__ object we also transfer the _cache_ too
+        # which is stored there by @cached_property.
+        new_segment.__dict__.update(self.__dict__)
+
+        # Reset the parent if provided.
+        if parent:
+            new_segment.set_parent(parent)
+
+        # If the segment doesn't have a segments property, we're done.
+        # NOTE: This is a proxy way of understanding whether it's a RawSegment
+        # of not. Typically will _have_ a `segments` attribute, but it's an
+        # empty tuple.
+        if not self.__dict__.get("segments", None):
+            assert (
+                not segments
+            ), f"Cannot provide `segments` argument to {cls.__name__} `.copy()`\n"
         # If segments were provided, use them.
-        if segments:
-            new_seg.segments = segments
-        # Otherwise copy them.
-        elif self.segments:
-            new_seg.segments = tuple(seg.copy() for seg in self.segments)
-        return new_seg
+        elif segments:
+            new_segment.segments = segments
+        # Otherwise we should handle recursive segment coping.
+        # We use the native .copy() method (this method!) appropriately
+        # so that the same logic is applied in recursion.
+        # We set the parent for children directly on the copy method
+        # to ensure those line up properly.
+        else:
+            new_segment.segments = tuple(
+                seg.copy(parent=new_segment) for seg in self.segments
+            )
+
+        return new_segment
 
     def as_record(self, **kwargs: bool) -> Optional[RecordSerialisedSegment]:
         """Return the segment as a structurally simplified record.
@@ -1400,7 +1429,6 @@ class BaseSegment(metaclass=SegmentMetaclass):
                                 # of a create_before/create_after pair, also add
                                 # this segment before the edit.
                                 seg_buffer.append(seg)
-                                seg.set_parent(self)
 
                             # We're doing a replacement (it could be a single
                             # segment or an iterable)
@@ -1408,7 +1436,6 @@ class BaseSegment(metaclass=SegmentMetaclass):
                             consumed_pos = False
                             for s in f.edit:
                                 seg_buffer.append(s)
-                                s.set_parent(self)
                                 # If one of them has the same raw representation
                                 # then the first that matches gets to take the
                                 # original position marker.
@@ -1424,7 +1451,6 @@ class BaseSegment(metaclass=SegmentMetaclass):
                                 # in the case of a creation before, also add this
                                 # segment on the end
                                 seg_buffer.append(seg)
-                                seg.set_parent(self)
 
                         else:  # pragma: no cover
                             raise ValueError(
@@ -1434,7 +1460,7 @@ class BaseSegment(metaclass=SegmentMetaclass):
                             )
                 else:
                     seg_buffer.append(seg)
-                    seg.set_parent(self)
+
             # Invalidate any caches
             self.invalidate_caches()
 
@@ -1495,6 +1521,7 @@ class BaseSegment(metaclass=SegmentMetaclass):
             # Pass through any additional kwargs
             **{k: getattr(self, k) for k in self.additional_kwargs},
         )
+        new_seg.set_as_parent(recurse=False)
         # Only validate if there's a match_grammar. Otherwise we may get
         # strange results (for example with the BracketedSegment).
         if requires_validate and (
diff --git a/src/sqlfluff/core/rules/base.py b/src/sqlfluff/core/rules/base.py
index 03f0e00b6..bf9357020 100644
--- a/src/sqlfluff/core/rules/base.py
+++ b/src/sqlfluff/core/rules/base.py
@@ -1143,7 +1143,7 @@ class BaseRule(metaclass=RuleMetaclass):
             if root_segment
             else None
         )
-        assert path
+        assert path, f"No path found from {root_segment} to {segment}!"
         for seg in path[::-1]:
             # If the segment allows non code ends, then no problem.
             # We're done. This is usually the outer file segment.


Test patch:
diff --git a/test/core/parser/segments/segments_base_test.py b/test/core/parser/segments/segments_base_test.py
index 426b2fe6c..80fc2a276 100644
--- a/test/core/parser/segments/segments_base_test.py
+++ b/test/core/parser/segments/segments_base_test.py
@@ -302,3 +302,58 @@ def test__parser__base_segments_pickle_safe(raw_seg_list):
     assert test_seg == result_seg
     # Check specifically the treatment of the parent position.
     assert result_seg.segments[0].get_parent() is result_seg
+
+
+def test__parser__base_segments_copy_isolation(DummySegment, raw_seg_list):
+    """Test copy isolation in BaseSegment.
+
+    First on one of the raws and then on the dummy segment.
+    """
+    # On a raw
+    a_seg = raw_seg_list[0]
+    a_copy = a_seg.copy()
+    assert a_seg is not a_copy
+    assert a_seg == a_copy
+    assert a_seg.pos_marker is a_copy.pos_marker
+    a_copy.pos_marker = None
+    assert a_copy.pos_marker is None
+    assert a_seg.pos_marker is not None
+
+    # On a base
+    b_seg = DummySegment(segments=raw_seg_list)
+    b_copy = b_seg.copy()
+    assert b_seg is not b_copy
+    assert b_seg == b_copy
+    assert b_seg.pos_marker is b_copy.pos_marker
+    b_copy.pos_marker = None
+    assert b_copy.pos_marker is None
+    assert b_seg.pos_marker is not None
+
+    # On addition to a lint Fix
+    fix = LintFix("replace", a_seg, [b_seg])
+    for s in fix.edit:
+        assert not s.pos_marker
+    assert b_seg.pos_marker
+
+
+def test__parser__base_segments_parent_ref(DummySegment, raw_seg_list):
+    """Test getting and setting parents on BaseSegment."""
+    # Check initially no parent (because not set)
+    assert not raw_seg_list[0].get_parent()
+    # Add it to a segment (still not set)
+    seg = DummySegment(segments=raw_seg_list)
+    assert not seg.segments[0].get_parent()
+    # Set one parent on one of them (but not another)
+    seg.segments[0].set_parent(seg)
+    assert seg.segments[0].get_parent() is seg
+    assert not seg.segments[1].get_parent()
+    # Set parent on all of them
+    seg.set_as_parent()
+    assert seg.segments[0].get_parent() is seg
+    assert seg.segments[1].get_parent() is seg
+    # Remove segment from parent, but don't unset.
+    # Should still check an return None.
+    seg_0 = seg.segments[0]
+    seg.segments = seg.segments[1:]
+    assert seg_0 not in seg.segments
+    assert not seg_0.get_parent()
diff --git a/test/rules/std_RF01_LT09_test.py b/test/rules/std_RF01_LT09_test.py
new file mode 100644
index 000000000..8c193ad51
--- /dev/null
+++ b/test/rules/std_RF01_LT09_test.py
@@ -0,0 +1,27 @@
+"""Tests observed conflict between RF01 & LT09.
+
+Root cause was BaseSegment.copy().
+"""
+from sqlfluff.core import FluffConfig, Linter
+
+
+def test__rules__std_RF01_LT09_copy() -> None:
+    """Tests observed conflict between RF01 & LT09.
+
+    https://github.com/sqlfluff/sqlfluff/issues/5203
+    """
+    sql = """
+SELECT
+    DISTINCT `FIELD`
+FROM `TABLE`;
+"""
+    cfg = FluffConfig.from_kwargs(
+        dialect="mysql",
+        rules=["RF01", "LT09"],
+    )
+    result = Linter(config=cfg).lint_string(sql)
+    for violation in result.violations:
+        assert "Unexpected exception" not in violation.description
+    assert len(result.violations) == 1
+    only_violation = result.violations[0]
+    assert only_violation.rule_code() == "LT09"