Repository: sqlfluff/sqlfluff
Issue category: Bug Report

Issue:
LT01 & ST02 interaction adds a space between coalesce keyword and opening parenthesis
### Search before asking

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


### What Happened

I have a classic unnecessary case statement:
```
case when ended_at is null or date(ended_at) > current_date() then true else false end as is_active,
```

Which is correctly being flagged by ST02:
```
=== [dbt templater] Sorting Nodes...
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
== [my_redacted_model.sql] FAIL
L:  31 | P:  23 | ST02 | Unnecessary CASE statement. Use COALESCE function
                       | instead. [structure.simple_case]
```

Applying `sqlfluff fix` "fixes" the offending case statement with malformed SQL:
```
coalesce (ended_at is null or date(ended_at) > current_date(), false) as is_active,
        
        ^- shouldn't be there
```

which fails a subsequent lint due to the space. Running `sqlfluff` fix twice avoides the error because the offending space is picked up by LT01.


### Expected Behaviour

I would expect ST02 to not add a space between the `coalesce` keyword and the opening parenthesis.

### Observed Behaviour

ST02 adds a space between the `coalesce` keyword and the opening parenthesis.

### How to reproduce

This query reproduces the error for me:

```
with
    subscriptions as (
        select *
        from {{ ref('stg__subscriptions') }}
    ),

    club_subscriptions as (
        select
            case
                when ended_at is null or date(ended_at) > current_date()
                    then true
                else false
            end as is_active
        from subscriptions
    )

select *
from club_subscriptions
```

### Dialect

bigquery, with the dbt templater

### Version

Python: Python 3.10.15

- **sqlfluff**
	```
	"sqlfluff==3.2.3",
    "sqlfluff-templater-dbt==3.2.3",
	```

- **dbt**
	```
    "dbt-adapters==1.7.0",
    "dbt-bigquery==1.8.2",
    "dbt-core==1.8.7",
	```

### Configuration

```
[sqlfluff]
templater = dbt
dialect = bigquery
max_line_length = 120
exclude_rules =
    structure.column_order,
    structure.using,
    references.qualification,
    ambiguous.column_count
warnings = RF04

[sqlfluff:templater:dbt]
project_dir = ./dbt
profiles_dir = ./dbt

[sqlfluff:templater:jinja]
load_macros_from_path = ./dbt/macros

[sqlfluff:indentation]
indented_ctes = true
allow_implicit_indents = true

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.identifiers]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower

```

### 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)



Hints:
Awkwardly this isn't an issue with `ST02`. Let's use the following query as an example:

```sql
select
    case
        when ended_at is null or date(ended_at) > current_date()
        then true else false
    end as is_active
from foo
```

If I fix that with _only_ ST02, then I get this (`sqlfluff fix test.sql -r ST02`):

```sql
select
    coalesce(ended_at is null or date(ended_at) > current_date(), false) as is_active
from foo
```

No problem ✅

HOWEVER, if I run it without restricting to just `ST02` (`sqlfluff fix test.sql`), I get the issue you describe:

```sql
select
    coalesce (ended_at is null or date(ended_at) > current_date(),
    false) as is_active
from foo
```

It appears to be an interaction between LT01 and ST02 for reasons I don't quite understand as if I run (`sqlfluff fix test.sql -r LT01,ST02`) I get:

```sql
select
    coalesce (ended_at is null or date(ended_at) > current_date(), false) as is_active
from foo
```

I suspect this is because the typing of the fixes returned by `ST02` is slightly off.









Patch:
diff --git a/src/sqlfluff/rules/structure/ST02.py b/src/sqlfluff/rules/structure/ST02.py
index de4c168028d..aef10af3648 100644
--- a/src/sqlfluff/rules/structure/ST02.py
+++ b/src/sqlfluff/rules/structure/ST02.py
@@ -14,6 +14,24 @@
 from sqlfluff.utils.functional import FunctionalContext, Segments, sp
 
 
+class FunctionNameSegment(BaseSegment):
+    """Function name, including any prefix bits, e.g. project or schema.
+
+    NOTE: This is a minimal segment definition to allow appropriate fixing.
+    """
+
+    type = "function_name"
+
+
+class FunctionContentsSegment(BaseSegment):
+    """Function Contents.
+
+    NOTE: This is a minimal segment definition to allow appropriate fixing.
+    """
+
+    type = "function_contents"
+
+
 class Rule_ST02(BaseRule):
     """Unnecessary ``CASE`` statement.
 
@@ -92,13 +110,19 @@ def _coalesce_fix_list(
         """Generate list of fixes to convert CASE statement to COALESCE function."""
         # Add coalesce and opening parenthesis.
         edits = [
-            WordSegment("coalesce", type="function_name_identifier"),
-            SymbolSegment("(", type="start_bracket"),
-            coalesce_arg_1,
-            SymbolSegment(",", type="comma"),
-            WhitespaceSegment(),
-            coalesce_arg_2,
-            SymbolSegment(")", type="end_bracket"),
+            FunctionNameSegment(
+                segments=(WordSegment("coalesce", type="function_name_identifier"),)
+            ),
+            FunctionContentsSegment(
+                segments=(
+                    SymbolSegment("(", type="start_bracket"),
+                    coalesce_arg_1,
+                    SymbolSegment(",", type="comma"),
+                    WhitespaceSegment(),
+                    coalesce_arg_2,
+                    SymbolSegment(")", type="end_bracket"),
+                )
+            ),
         ]
 
         if preceding_not:


Test patch:
diff --git a/test/rules/std_LT01_ST02_test.py b/test/rules/std_LT01_ST02_test.py
new file mode 100644
index 00000000000..f57efada099
--- /dev/null
+++ b/test/rules/std_LT01_ST02_test.py
@@ -0,0 +1,49 @@
+"""Tests the python routines within LT04 and ST06."""
+
+import pytest
+
+from sqlfluff.core import FluffConfig, Linter
+
+
+@pytest.mark.parametrize(
+    ["in_sql", "out_sql"],
+    [
+        (
+            """
+select
+    case
+        when ended_at is null or date(ended_at) > current_date()
+        then true else false
+    end as is_active
+from foo
+""",
+            """
+select
+    coalesce(ended_at is null or date(ended_at) > current_date(), false) as is_active
+from foo
+""",
+        ),
+    ],
+)
+def test_rules_std_LT01_and_ST02_interaction(in_sql, out_sql) -> None:
+    """Test interaction between LT04 and ST06.
+
+    Test sql with two newlines with leading commas expecting trailing.
+    """
+    # Lint expected rules.
+    cfg = FluffConfig.from_string(
+        """[sqlfluff]
+dialect = ansi
+rules = LT01,ST02
+"""
+    )
+    linter = Linter(config=cfg)
+
+    # Return linted/fixed file.
+    linted_file = linter.lint_string(in_sql, fix=True)
+
+    # Check expected lint errors are raised.
+    assert set([v.rule.code for v in linted_file.violations]) == {"ST02"}
+
+    # Check file is fixed.
+    assert linted_file.fix_string()[0] == out_sql