Repository: sqlfluff/sqlfluff
Pull request: L052: Fix case where no preceding segments and mulitline
<!--Firstly, thanks for adding this feature! Secondly, please check the key steps against the checklist below to make your contribution easy to merge.-->

<!--Please give the Pull Request a meaningful title (including the dialect this PR is for if it is dialect specific), as this will automatically be added to the release notes, and then the Change Log.-->

### Brief summary of the change made
<!--If there is an open issue for this, then please include `fixes #XXXX` or `closes #XXXX` replacing `XXXX` with the issue number and it will automatically close the issue when the pull request is merged. Alternatively if not fully closed you can say `makes progress on #XXXX` to create a link on that issue without closing it.-->
Closes #2268. This change accounts for the possibility that we have to move the semicolon onto a newline and there are no segments between the semicolon and the newline (previous tested statements such as select always had a meta dedent segment that we could anchor on so this wasn't noticed).

### Are there any other side effects of this change that we should be aware of?
No

### Pull Request checklist
- [X] Please confirm you have completed any of the necessary steps below.

- Included test cases to demonstrate any code changes, which may be one or more of the following:
  - `.yml` rule test cases in `test/fixtures/rules/std_rule_cases`.
  - `.sql`/`.yml` parser test cases in `test/fixtures/dialects` (note YML files can be auto generated with `tox -e generate-fixture-yml`).
  - Full autofix test cases in `test/fixtures/linter/autofix`.
  - Other.
- Added appropriate documentation for the change.
- Created GitHub issues for any relevant followup/future enhancements if appropriate.


Problem statement:
L052: Auto-fix appended extra `semicolon` in `CREATE TABLE` statement when `multiline_newline` is `True`
## Expected Behaviour

* Auto-fix of L052 should move `;` at the end of `CREATE TABLE` statement to the next line when `multiline_newline = True`.

## Observed Behaviour

* Auto-fix printed the following when `sqlfluff fix` is executed:
```
==== finding fixable violations ====
WARNING    Loop limit on fixes reached [10].
== [sample.sql] FAIL
L:   3 | P:   2 | L052 | Statements must end with a semi-colon.
==== fixing violations ====
1 fixable linting violations found
```
* Auto-fix for L052 did not move `;` to the next line.
* Auto-fix for L052 appended multiple `;` after the `CREATE TABLE` statement.

## Steps to Reproduce

1. Create `sample.sql` with the following content:
```
CREATE TABLE my_table (
    id INTEGER
);
```
2. Override L052 to force semi colon of multi-line statement to be on the new line with the following override: (You can create a `.sqlfluff` with the content below.)
```
[sqlfluff:rules:L052]
multiline_newline = True
```
3. Run `sqlfluff fix sample.sql -f`

## Dialect

Ansi (Default - Not specific to any dialect.)

## Version

```
(.venv) ~/code/sqlfluff (main) $ sqlfluff fix sample.sql
(.venv) ~/code/sqlfluff (main) $ sqlfluff --version
sqlfluff, version 0.9.1
(.venv) ~/code/sqlfluff (main) $ python --version
Python 3.9.9
```

## Configuration
* Override L052 to force multiline statement's semicolon to always be in a new line.
```
[sqlfluff:rules:L052]
multiline_newline = True
```

## Output of `sqlfluff fix`

```
(.venv) ~/code/sqlfluff (main) $ sqlfluff fix sample.sql
==== finding fixable violations ====
WARNING    Loop limit on fixes reached [10].
== [sample.sql] FAIL
L:   3 | P:   2 | L052 | Statements must end with a semi-colon.
==== fixing violations ====
1 fixable linting violations found
Are you sure you wish to attempt to fix these? [Y/n] ...
Attempting fixes...
Persisting Changes...
== [sample.sql] PASS
Done. Please check your files to confirm.
All Finished 📜 🎉!
(.venv) ~/code/sqlfluff (main) $
```

Hints from issue discussion:
@jpy-git: Interested in looking at this one? It relates to L052, which you added recently.

Commit message: Merge branch 'main' into extra_semicolons_create_table

Patch to review:
diff --git a/src/sqlfluff/rules/L052.py b/src/sqlfluff/rules/L052.py
index 68cda935998..7d4f2d2de37 100644
--- a/src/sqlfluff/rules/L052.py
+++ b/src/sqlfluff/rules/L052.py
@@ -193,21 +193,38 @@ def _eval(self, context: RuleContext) -> Optional[LintResult]:
                     anchor_segment = self._handle_trailing_inline_comments(
                         context, anchor_segment
                     )
-
-                    fixes = [
-                        LintFix.replace(
-                            anchor_segment,
-                            [
+                    fixes = []
+                    if anchor_segment == context.segment:
+                        fixes.append(
+                            LintFix.replace(
                                 anchor_segment,
-                                NewlineSegment(),
-                                SymbolSegment(raw=";", type="symbol", name="semicolon"),
-                            ],
-                        ),
-                        LintFix.delete(
-                            context.segment,
-                        ),
-                    ]
-                    fixes.extend(LintFix.delete(d) for d in whitespace_deletions)
+                                [
+                                    NewlineSegment(),
+                                    SymbolSegment(
+                                        raw=";", type="symbol", name="semicolon"
+                                    ),
+                                ],
+                            )
+                        )
+                    else:
+                        fixes.extend(
+                            [
+                                LintFix.replace(
+                                    anchor_segment,
+                                    [
+                                        anchor_segment,
+                                        NewlineSegment(),
+                                        SymbolSegment(
+                                            raw=";", type="symbol", name="semicolon"
+                                        ),
+                                    ],
+                                ),
+                                LintFix.delete(
+                                    context.segment,
+                                ),
+                            ]
+                        )
+                        fixes.extend(LintFix.delete(d) for d in whitespace_deletions)
                     return LintResult(
                         anchor=anchor_segment,
                         fixes=fixes,
diff --git a/test/fixtures/rules/std_rule_cases/L052.yml b/test/fixtures/rules/std_rule_cases/L052.yml
index a8ee9830a7e..2ccedf9187f 100644
--- a/test/fixtures/rules/std_rule_cases/L052.yml
+++ b/test/fixtures/rules/std_rule_cases/L052.yml
@@ -433,3 +433,34 @@ test_fail_newline_block_comment_semi_colon_after:
     rules:
       L052:
         multiline_newline: true
+
+test_fail_newline_create_table:
+  # https://github.com/sqlfluff/sqlfluff/issues/2268
+  fail_str: |
+    CREATE TABLE my_table (
+      id INTEGER
+    );
+  fix_str: |
+    CREATE TABLE my_table (
+      id INTEGER
+    )
+    ;
+  configs:
+    rules:
+      L052:
+        multiline_newline: true
+
+test_fail_newline_create_table_inline_comment:
+  fail_str: |
+    CREATE TABLE my_table (
+      id INTEGER
+    ); --inline comment
+  fix_str: |
+    CREATE TABLE my_table (
+      id INTEGER
+    ) --inline comment
+    ;
+  configs:
+    rules:
+      L052:
+        multiline_newline: true


Code review comments:
diff_hunk: @@ -193,21 +193,38 @@ def _eval(self, context: RuleContext) -> Optional[LintResult]:
                     anchor_segment = self._handle_trailing_inline_comments(
                         context, anchor_segment
                     )
-
-                    fixes = [
-                        LintFix.replace(
-                            anchor_segment,
-                            [
+                    fixes = []
+                    if anchor_segment == context.segment:
path: src/sqlfluff/rules/L052.py
text: @user1:
Nitpick: I think rules are often loose about this, but would it be more precise (and still correct) to instead say:
```suggestion
                    if anchor_segment is context.segment:
```