Repository: sqlfluff/sqlfluff

Issue:
A dead loop to fix this lint issue in 2.0.0
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

After upgrading sqlfluff to 2.0.0. There is one issue sqlfluff cannot fix, and it is a dead loop.

### Expected Behaviour

No lint issue after fixing.

### Observed Behaviour

A dead loop to fix the lint issue.

### How to reproduce

After upgrading to sqlfluff 2.0.0,

when I run `sqlfluff lint --dialect=postgres my.sql` on

```sql
insert into iot_data (timestamp, temperature, humidity)
select
    ts,
    random() * 50 + 20 as temperature,
    random() * 50 + 50 as humidity
from (
    select generate_series(
            current_timestamp - interval '1 day',
            current_timestamp,
            interval '1 second'
        ) as ts
) as t;
```

It will show
```shell
L:   7 | P:  11 | LT02 | Expected line break and indent of 8 spaces before
                       | 'generate_series'. [layout.indent]
```

After I run `sqlfluff fix --force --dialect=postgres my.sql`, it will change to
```sql
insert into iot_data (timestamp, temperature, humidity)
select
    ts,
    random() * 50 + 20 as temperature,
    random() * 50 + 50 as humidity
from (
    select
        generate_series(
            current_timestamp - interval '1 day',
            current_timestamp,
            interval '1 second'
        ) as ts
) as t;
```

Now when I run `sqlfluff lint --dialect=postgres my.sql` again, it will show
```shell
L:   7 | P:   5 | LT09 | Select targets should be on a new line unless there is
                       | only one select target.
                       | [layout.select_targets]
```

Now if I run `sqlfluff fix --force --dialect=postgres my.sql` again, it will change back to original code again. So it is a dead loop... 🥲

### Dialect

postgres

### Version

2.0.0

### Configuration

[sqlfluff]
exclude_rules = L016

### Are you willing to work on and submit a PR to address the issue?

- [ ] 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: ## Pull Request Test Coverage Report for [Build 4429740052](https://coveralls.io/builds/57898430)

* **54** of **54**   **(100.0%)**  changed or added relevant lines in **1** file are covered.
* No unchanged relevant lines lost coverage.
* Overall coverage remained the same at **100.0%**

---



|  Totals | [![Coverage Status](https://coveralls.io/builds/57898430/badge)](https://coveralls.io/builds/57898430) |
| :-- | --: |
| Change from base [Build 4428622009](https://coveralls.io/builds/57892922): |  0.0% |
| Covered Lines: | 17449 |
| Relevant Lines: | 17449 |

---
##### 💛  - [Coveralls](https://coveralls.io)

dmohns: Not sure if it is scope of this PR, but which of the two is the "correct" indentation?

```sql
SELECT SUM(
    1 + SUM(
        2 + 3
    )
) AS col
FROM test_table;
```

vs

```sql
SELECT 
    SUM(
        1 + SUM(
            2 + 3
        )
    ) AS col
FROM test_table;
```

Patch:
diff --git a/src/sqlfluff/rules/layout/LT09.py b/src/sqlfluff/rules/layout/LT09.py
index ae4c2ea12..e110e38b0 100644
--- a/src/sqlfluff/rules/layout/LT09.py
+++ b/src/sqlfluff/rules/layout/LT09.py
@@ -37,16 +37,15 @@ class Rule_LT09(BaseRule):
     Multiple select targets on the same line.
 
     .. code-block:: sql
-        :force:
 
         select a, b
-        from foo
+        from foo;
 
         -- Single select target on its own line.
 
         SELECT
             a
-        FROM foo
+        FROM foo;
 
 
     **Best practice**
@@ -54,18 +53,28 @@ class Rule_LT09(BaseRule):
     Multiple select targets each on their own line.
 
     .. code-block:: sql
-        :force:
 
         select
             a,
             b
-        from foo
+        from foo;
 
         -- Single select target on the same line as the ``SELECT``
         -- keyword.
 
         SELECT a
-        FROM foo
+        FROM foo;
+
+        -- When select targets span multiple lines, however they
+        -- can still be on a new line.
+
+        SELECT
+            SUM(
+                1 + SUM(
+                    2 + 3
+                )
+            ) AS col
+        FROM test_table;
 
     """
 
@@ -207,217 +216,232 @@ class Rule_LT09(BaseRule):
         select_clause = FunctionalContext(context).segment
         parent_stack = context.parent_stack
 
-        if (
+        # If it's all on one line, then there's no issue.
+        if not (
             select_targets_info.select_idx
             < select_targets_info.first_new_line_idx
             < select_targets_info.first_select_target_idx
         ):
-            # Do we have a modifier?
-            select_children = select_clause.children()
-            modifier: Optional[Segments]
-            modifier = select_children.first(sp.is_type("select_clause_modifier"))
-
-            # Prepare the select clause which will be inserted
-            insert_buff = [
-                WhitespaceSegment(),
-                select_children[select_targets_info.first_select_target_idx],
-            ]
+            self.logger.info(
+                "Target at index %s is already on a single line.",
+                select_targets_info.first_select_target_idx,
+            )
+            return None
 
-            # Check if the modifier is one we care about
-            if modifier:
-                # If it's already on the first line, ignore it.
-                if (
-                    select_children.index(modifier.get())
-                    < select_targets_info.first_new_line_idx
-                ):
-                    modifier = None
-            fixes = [
-                # Delete the first select target from its original location.
-                # We'll add it to the right section at the end, once we know
-                # what to add.
-                LintFix.delete(
-                    select_children[select_targets_info.first_select_target_idx],
-                ),
-            ]
+        # Do we have a modifier?
+        select_children = select_clause.children()
+        modifier: Optional[Segments]
+        modifier = select_children.first(sp.is_type("select_clause_modifier"))
 
-            # If we have a modifier to move:
-            if modifier:
-                # Add it to the insert
-                insert_buff = [WhitespaceSegment(), modifier[0]] + insert_buff
+        # Does the target contain a newline?
+        # i.e. even if it's a single element, does it already span more than
+        # one line?
+        if (
+            "newline"
+            in select_children[
+                select_targets_info.first_select_target_idx
+            ].descendant_type_set
+        ):
+            self.logger.info(
+                "Target at index %s spans multiple lines so ignoring.",
+                select_targets_info.first_select_target_idx,
+            )
+            return None
 
-                modifier_idx = select_children.index(modifier.get())
-                # Delete the whitespace after it (which is two after, thanks to indent)
-                if (
-                    len(select_children) > modifier_idx + 1
-                    and select_children[modifier_idx + 2].is_whitespace
-                ):
-                    fixes += [
-                        LintFix.delete(
-                            select_children[modifier_idx + 2],
-                        ),
-                    ]
+        # Prepare the select clause which will be inserted
+        insert_buff = [
+            WhitespaceSegment(),
+            select_children[select_targets_info.first_select_target_idx],
+        ]
+
+        # Check if the modifier is one we care about
+        if modifier:
+            # If it's already on the first line, ignore it.
+            if (
+                select_children.index(modifier.get())
+                < select_targets_info.first_new_line_idx
+            ):
+                modifier = None
+        fixes = [
+            # Delete the first select target from its original location.
+            # We'll add it to the right section at the end, once we know
+            # what to add.
+            LintFix.delete(
+                select_children[select_targets_info.first_select_target_idx],
+            ),
+        ]
 
-                # Delete the modifier itself
+        # If we have a modifier to move:
+        if modifier:
+            # Add it to the insert
+            insert_buff = [WhitespaceSegment(), modifier[0]] + insert_buff
+
+            modifier_idx = select_children.index(modifier.get())
+            # Delete the whitespace after it (which is two after, thanks to indent)
+            if (
+                len(select_children) > modifier_idx + 1
+                and select_children[modifier_idx + 2].is_whitespace
+            ):
                 fixes += [
                     LintFix.delete(
-                        modifier[0],
+                        select_children[modifier_idx + 2],
                     ),
                 ]
 
-                # Set the position marker for removing the preceding
-                # whitespace and newline, which we'll use below.
-                start_idx = modifier_idx
-            else:
-                # Set the position marker for removing the preceding
-                # whitespace and newline, which we'll use below.
-                start_idx = select_targets_info.first_select_target_idx
-
-            if parent_stack and parent_stack[-1].is_type("select_statement"):
-                select_stmt = parent_stack[-1]
-                select_clause_idx = select_stmt.segments.index(select_clause.get())
-                after_select_clause_idx = select_clause_idx + 1
-                if len(select_stmt.segments) > after_select_clause_idx:
-
-                    def _fixes_for_move_after_select_clause(
-                        stop_seg: BaseSegment,
-                        delete_segments: Optional[Segments] = None,
-                        add_newline: bool = True,
-                    ) -> List[LintFix]:
-                        """Cleans up by moving leftover select_clause segments.
-
-                        Context: Some of the other fixes we make in
-                        _eval_single_select_target_element() leave leftover
-                        child segments that need to be moved to become
-                        *siblings* of the select_clause.
-                        """
-                        start_seg = (
-                            modifier[0]
-                            if modifier
-                            else select_children[select_targets_info.first_new_line_idx]
-                        )
-                        move_after_select_clause = select_children.select(
-                            start_seg=start_seg,
-                            stop_seg=stop_seg,
-                        )
-                        # :TRICKY: Below, we have a couple places where we
-                        # filter to guard against deleting the same segment
-                        # multiple times -- this is illegal.
-                        # :TRICKY: Use IdentitySet rather than set() since
-                        # different segments may compare as equal.
-                        all_deletes = IdentitySet(
-                            fix.anchor for fix in fixes if fix.edit_type == "delete"
-                        )
-                        fixes_ = []
-                        for seg in delete_segments or []:
-                            if seg not in all_deletes:
-                                fixes.append(LintFix.delete(seg))
-                                all_deletes.add(seg)
-                        fixes_ += [
-                            LintFix.delete(seg)
-                            for seg in move_after_select_clause
-                            if seg not in all_deletes
-                        ]
-                        fixes_.append(
-                            LintFix.create_after(
-                                select_clause[0],
-                                ([NewlineSegment()] if add_newline else [])
-                                + list(move_after_select_clause),
-                            )
-                        )
-                        return fixes_
-
-                    if select_stmt.segments[after_select_clause_idx].is_type("newline"):
-                        # Since we're deleting the newline, we should also delete all
-                        # whitespace before it or it will add random whitespace to
-                        # following statements. So walk back through the segment
-                        # deleting whitespace until you get the previous newline, or
-                        # something else.
-                        to_delete = select_children.reversed().select(
-                            loop_while=sp.is_type("whitespace"),
-                            start_seg=select_children[start_idx],
+            # Delete the modifier itself
+            fixes += [
+                LintFix.delete(
+                    modifier[0],
+                ),
+            ]
+
+            # Set the position marker for removing the preceding
+            # whitespace and newline, which we'll use below.
+            start_idx = modifier_idx
+        else:
+            # Set the position marker for removing the preceding
+            # whitespace and newline, which we'll use below.
+            start_idx = select_targets_info.first_select_target_idx
+
+        if parent_stack and parent_stack[-1].is_type("select_statement"):
+            select_stmt = parent_stack[-1]
+            select_clause_idx = select_stmt.segments.index(select_clause.get())
+            after_select_clause_idx = select_clause_idx + 1
+            if len(select_stmt.segments) > after_select_clause_idx:
+
+                def _fixes_for_move_after_select_clause(
+                    stop_seg: BaseSegment,
+                    delete_segments: Optional[Segments] = None,
+                    add_newline: bool = True,
+                ) -> List[LintFix]:
+                    """Cleans up by moving leftover select_clause segments.
+
+                    Context: Some of the other fixes we make in
+                    _eval_single_select_target_element() leave leftover
+                    child segments that need to be moved to become
+                    *siblings* of the select_clause.
+                    """
+                    start_seg = (
+                        modifier[0]
+                        if modifier
+                        else select_children[select_targets_info.first_new_line_idx]
+                    )
+                    move_after_select_clause = select_children.select(
+                        start_seg=start_seg,
+                        stop_seg=stop_seg,
+                    )
+                    # :TRICKY: Below, we have a couple places where we
+                    # filter to guard against deleting the same segment
+                    # multiple times -- this is illegal.
+                    # :TRICKY: Use IdentitySet rather than set() since
+                    # different segments may compare as equal.
+                    all_deletes = IdentitySet(
+                        fix.anchor for fix in fixes if fix.edit_type == "delete"
+                    )
+                    fixes_ = []
+                    for seg in delete_segments or []:
+                        if seg not in all_deletes:
+                            fixes.append(LintFix.delete(seg))
+                            all_deletes.add(seg)
+                    fixes_ += [
+                        LintFix.delete(seg)
+                        for seg in move_after_select_clause
+                        if seg not in all_deletes
+                    ]
+                    fixes_.append(
+                        LintFix.create_after(
+                            select_clause[0],
+                            ([NewlineSegment()] if add_newline else [])
+                            + list(move_after_select_clause),
                         )
-                        if to_delete:
-                            # The select_clause is immediately followed by a
-                            # newline. Delete the newline in order to avoid leaving
-                            # behind an empty line after fix, *unless* we stopped
-                            # due to something other than a newline.
-                            delete_last_newline = select_children[
-                                start_idx - len(to_delete) - 1
-                            ].is_type("newline")
-
-                            # Delete the newline if we decided to.
-                            if delete_last_newline:
-                                fixes.append(
-                                    LintFix.delete(
-                                        select_stmt.segments[after_select_clause_idx],
-                                    )
+                    )
+                    return fixes_
+
+                if select_stmt.segments[after_select_clause_idx].is_type("newline"):
+                    # Since we're deleting the newline, we should also delete all
+                    # whitespace before it or it will add random whitespace to
+                    # following statements. So walk back through the segment
+                    # deleting whitespace until you get the previous newline, or
+                    # something else.
+                    to_delete = select_children.reversed().select(
+                        loop_while=sp.is_type("whitespace"),
+                        start_seg=select_children[start_idx],
+                    )
+                    if to_delete:
+                        # The select_clause is immediately followed by a
+                        # newline. Delete the newline in order to avoid leaving
+                        # behind an empty line after fix, *unless* we stopped
+                        # due to something other than a newline.
+                        delete_last_newline = select_children[
+                            start_idx - len(to_delete) - 1
+                        ].is_type("newline")
+
+                        # Delete the newline if we decided to.
+                        if delete_last_newline:
+                            fixes.append(
+                                LintFix.delete(
+                                    select_stmt.segments[after_select_clause_idx],
                                 )
-
-                            fixes += _fixes_for_move_after_select_clause(
-                                to_delete[-1], to_delete
                             )
-                    elif select_stmt.segments[after_select_clause_idx].is_type(
-                        "whitespace"
-                    ):
-                        # The select_clause has stuff after (most likely a comment)
-                        # Delete the whitespace immediately after the select clause
-                        # so the other stuff aligns nicely based on where the select
-                        # clause started.
-                        fixes += [
-                            LintFix.delete(
-                                select_stmt.segments[after_select_clause_idx],
-                            ),
-                        ]
+
                         fixes += _fixes_for_move_after_select_clause(
-                            select_children[
-                                select_targets_info.first_select_target_idx
-                            ],
+                            to_delete[-1], to_delete
                         )
-                    elif select_stmt.segments[after_select_clause_idx].is_type(
-                        "dedent"
-                    ):
-                        # Again let's strip back the whitespace, but simpler
-                        # as don't need to worry about new line so just break
-                        # if see non-whitespace
-                        to_delete = select_children.reversed().select(
-                            loop_while=sp.is_type("whitespace"),
-                            start_seg=select_children[select_clause_idx - 1],
-                        )
-                        if to_delete:
-                            fixes += _fixes_for_move_after_select_clause(
-                                to_delete[-1],
-                                to_delete,
-                                # If we deleted a newline, create a newline.
-                                any(seg for seg in to_delete if seg.is_type("newline")),
-                            )
-                    else:
+                elif select_stmt.segments[after_select_clause_idx].is_type(
+                    "whitespace"
+                ):
+                    # The select_clause has stuff after (most likely a comment)
+                    # Delete the whitespace immediately after the select clause
+                    # so the other stuff aligns nicely based on where the select
+                    # clause started.
+                    fixes += [
+                        LintFix.delete(
+                            select_stmt.segments[after_select_clause_idx],
+                        ),
+                    ]
+                    fixes += _fixes_for_move_after_select_clause(
+                        select_children[select_targets_info.first_select_target_idx],
+                    )
+                elif select_stmt.segments[after_select_clause_idx].is_type("dedent"):
+                    # Again let's strip back the whitespace, but simpler
+                    # as don't need to worry about new line so just break
+                    # if see non-whitespace
+                    to_delete = select_children.reversed().select(
+                        loop_while=sp.is_type("whitespace"),
+                        start_seg=select_children[select_clause_idx - 1],
+                    )
+                    if to_delete:
                         fixes += _fixes_for_move_after_select_clause(
-                            select_children[
-                                select_targets_info.first_select_target_idx
-                            ],
+                            to_delete[-1],
+                            to_delete,
+                            # If we deleted a newline, create a newline.
+                            any(seg for seg in to_delete if seg.is_type("newline")),
                         )
+                else:
+                    fixes += _fixes_for_move_after_select_clause(
+                        select_children[select_targets_info.first_select_target_idx],
+                    )
 
-            if select_targets_info.comment_after_select_idx == -1:
-                fixes += [
-                    # Insert the select_clause in place of the first newline in the
-                    # Select statement
-                    LintFix.replace(
-                        select_children[select_targets_info.first_new_line_idx],
-                        insert_buff,
-                    ),
-                ]
-            else:
-                # The SELECT is followed by a comment on the same line. In order
-                # to autofix this, we'd need to move the select target between
-                # SELECT and the comment and potentially delete the entire line
-                # where the select target was (if it is now empty). This is
-                # *fairly tricky and complex*, in part because the newline on
-                # the select target's line is several levels higher in the
-                # parser tree. Hence, we currently don't autofix this. Could be
-                # autofixed in the future if/when we have the time.
-                fixes = []
-            return LintResult(
-                anchor=select_clause.get(),
-                fixes=fixes,
-            )
-        return None
+        if select_targets_info.comment_after_select_idx == -1:
+            fixes += [
+                # Insert the select_clause in place of the first newline in the
+                # Select statement
+                LintFix.replace(
+                    select_children[select_targets_info.first_new_line_idx],
+                    insert_buff,
+                ),
+            ]
+        else:
+            # The SELECT is followed by a comment on the same line. In order
+            # to autofix this, we'd need to move the select target between
+            # SELECT and the comment and potentially delete the entire line
+            # where the select target was (if it is now empty). This is
+            # *fairly tricky and complex*, in part because the newline on
+            # the select target's line is several levels higher in the
+            # parser tree. Hence, we currently don't autofix this. Could be
+            # autofixed in the future if/when we have the time.
+            fixes = []
+        return LintResult(
+            anchor=select_clause.get(),
+            fixes=fixes,
+        )


Test patch:
diff --git a/test/cli/commands_test.py b/test/cli/commands_test.py
index 374d0634f..8e981d33d 100644
--- a/test/cli/commands_test.py
+++ b/test/cli/commands_test.py
@@ -1381,19 +1381,6 @@ def test__cli__command_lint_serialize_github_annotation():
     )
     result = json.loads(result.output)
     assert result == [
-        {
-            "annotation_level": "warning",
-            # Normalise paths to control for OS variance
-            "file": os.path.normpath(
-                "test/fixtures/linter/identifier_capitalisation.sql"
-            ),
-            "line": 1,
-            "message": "LT09: Select targets should be on a new line unless there is "
-            "only one select target.",
-            "start_column": 1,
-            "end_column": 1,
-            "title": "SQLFluff",
-        },
         {
             "annotation_level": "warning",
             # Normalise paths to control for OS variance
@@ -1493,9 +1480,6 @@ def test__cli__command_lint_serialize_github_annotation_native():
 
     assert result.output == "\n".join(
         [
-            f"::error title=SQLFluff,file={fpath_normalised},line=1,col=1::"
-            "LT09: Select targets should be on a new line unless there is only one "
-            "select target. [layout.select_targets]",
             f"::error title=SQLFluff,file={fpath_normalised},line=2,col=5::"
             "RF02: Unqualified reference 'foo' found in select with more than one "
             "referenced table/view. [references.qualification]",
diff --git a/test/fixtures/rules/std_rule_cases/LT09.yml b/test/fixtures/rules/std_rule_cases/LT09.yml
index 04ab2d131..1fb076362 100644
--- a/test/fixtures/rules/std_rule_cases/LT09.yml
+++ b/test/fixtures/rules/std_rule_cases/LT09.yml
@@ -330,3 +330,14 @@ test_create_view:
     SELECT c
     FROM table1
     INNER JOIN table2 ON (table1.id = table2.id);
+
+test_multiline_single:
+  # https://github.com/sqlfluff/sqlfluff/issues/4516
+  pass_str: |
+    SELECT
+        SUM(
+            1 + SUM(
+                2 + 3
+            )
+        ) AS col
+    FROM test_table