Repository: sqlfluff/sqlfluff

Issue:
L043 - sqlfluff fix returns broken coalesce statement. 
## Expected Behaviour
When `sqlfluff fix` applies a fix based on [L043](https://docs.sqlfluff.com/en/latest/rules.html?highlight=ignore#sqlfluff.core.rules.Rule_L043), the end result should be something like:
![image](https://user-images.githubusercontent.com/28986302/115073403-24de2080-9ead-11eb-8413-76e456a62669.png)

## Observed Behaviour
Instead, for some of the `case` statements that are fixed by `L043` sqlfluff returns, the returned fix is missing the characters `coalesce(` needed for the coalesce to work. 
![image](https://user-images.githubusercontent.com/28986302/115073366-155ed780-9ead-11eb-832c-796721912aaf.png)

Other times, the case statement (in another part of the model or in another model entirely) is fixed as expected _including_ the `coalesce(` at the start. Not sure why this would be the case? 🤔 

If I run `sqlfluff fix` on the same model where I observe this strange behaviour, I continue to get the same missing `coalesce(` in the returned fix.

## Steps to Reproduce

I have called out where the L043 fix is producing an unexpected fix with the comments `-- BEFORE L043 FIX` and `--AFTER L043 FIX` in the example SQL files below.

<details>
<summary>Example dbt model BEFORE running "sqlfluff fix"</summary>
<p>

```sql
-- filename: sl_stripe_subscriptions_with_perks_history.sql

{{
  config(
    materialized='table',
    sort=['user_id', 'created']
  )
}}

with subscriptions as (

    select * from {{ ref('stg_sl_stripe_subscriptions') }}

),

customers as (

    select * from {{ ref('stg_sl_stripe_customers') }}

),

perks as (

    select * from {{ ref('base_sl_web_issued_perk_code') }}

),

subscriptions_xf as (

    select
        subscriptions.customer_id,
        customers.user_id,
        subscriptions.id as subscription_id,
        subscriptions.created,  -- start of subscription (including trial if one existed)
        subscriptions.trial_start, -- start of trial (if existed)
        subscriptions.trial_end,  -- end of trial (if existed)
        subscriptions.current_period_start,  -- start of the current period (window for which charge applies)
        subscriptions.current_period_end,  -- end of the current period (window for which charge applies)
        subscriptions.canceled_at,  -- when the user canceled the subscription
        subscriptions.ended_at,  -- when the subscription actually ended 
        subscriptions.status,
        subscriptions.brand,
        subscriptions.interval,
        subscriptions.discount_id,
        subscriptions.is_deleted,
        subscriptions.promotion_id,
        subscriptions.metadata_migrated

    from subscriptions

    left join customers
        on subscriptions.customer_id = customers.customer_id

),

subscriptions_xf_2 as (

    select
        subscriptions_xf.user_id,
        subscriptions_xf.customer_id,
        subscriptions_xf.subscription_id,
        subscriptions_xf.created,  -- start of subscription (including trial if one existed)
        subscriptions_xf.trial_start, -- start of trial (if existed)
        subscriptions_xf.trial_end,  -- end of trial (if existed)
        subscriptions_xf.current_period_start,  -- start of the current period (window for which charge applies)
        subscriptions_xf.current_period_end,  -- end of the current period (window for which charge applies)
        subscriptions_xf.canceled_at,  -- when the user canceled the subscription
        subscriptions_xf.ended_at,  -- when the subscription actually ended 
        subscriptions_xf.status,
        subscriptions_xf.brand,
        subscriptions_xf.interval,
        subscriptions_xf.discount_id,
        subscriptions_xf.is_deleted,
        subscriptions_xf.promotion_id,
        subscriptions_xf.metadata_migrated,

        case  -- BEFORE L043 FIX
            when perks.perk is null then false
            else true
        end as perk_redeemed,

        perks.received_at as perk_received_at,
        perks.perk

    from subscriptions_xf

    left join perks
        on subscriptions_xf.user_id = perks.user_id
        and subscriptions_xf.created < perks.received_at  -- perk can only occur after trial/subscription starts
        and coalesce(subscriptions_xf.ended_at, current_date + 1) > perks.received_at  -- perk must be redeemed before trial/subscription ends, but can't be in future (i.e. tomorrow)

),

final as (

    select
        user_id,
        customer_id,
        subscription_id,
        created,  -- start of subscription (including trial if one existed)
        trial_start, -- start of trial (if existed)
        trial_end,  -- end of trial (if existed)

        case
            when trial_end is null then created
            when metadata_migrated is not null and "interval" = 'annual' then dateadd('year', -1, trial_end) -- when annual subscription migrated to Stripe 
            when metadata_migrated is not null and "interval" = 'month' then dateadd('month', -1, trial_end) -- when monthly subscription migrated to Stripe 
            else trial_end
        end as subscription_start,  -- start of PAID subscription (does not include trial period)

        current_period_start,  -- start of the current period (window for which charge applies)
        current_period_end,  -- end of the current period (window for which charge applies)
        canceled_at,  -- when the user canceled the subscription
        ended_at,  -- when the subscription actually ended 
        "status",
        brand,
        "interval",
        discount_id,
        is_deleted,
        promotion_id,
        metadata_migrated,
        perk_redeemed,
        perk_received_at,
        perk

    from subscriptions_xf_2

)

select * from final

```
</p>
</details>


<details>
<summary>Example dbt model AFTER running "sqlfluff fix"</summary>
<p>

```sql
-- filename: sl_stripe_subscriptions_with_perks_history.sql

{{
    config(
        materialized='table',
        sort=['user_id', 'created']
    )
}}

with subscriptions as (

    select * from {{ ref('stg_sl_stripe_subscriptions') }}

),

customers as (

    select * from {{ ref('stg_sl_stripe_customers') }}

),

perks as (

    select * from {{ ref('base_sl_web_issued_perk_code') }}

),

subscriptions_xf as (

    select
        subscriptions.customer_id,
        customers.user_id,
        subscriptions.id as subscription_id,
        subscriptions.created,  -- start of subscription (including trial if one existed)
        subscriptions.trial_start, -- start of trial (if existed)
        subscriptions.trial_end,  -- end of trial (if existed)
        subscriptions.current_period_start,  -- start of the current period (window for which charge applies)
        subscriptions.current_period_end,  -- end of the current period (window for which charge applies)
        subscriptions.canceled_at,  -- when the user canceled the subscription
        subscriptions.ended_at,  -- when the subscription actually ended
        subscriptions.status,
        subscriptions.brand,
        subscriptions.interval,
        subscriptions.discount_id,
        subscriptions.is_deleted,
        subscriptions.promotion_id,
        subscriptions.metadata_migrated

    from subscriptions

    left join customers
        on subscriptions.customer_id = customers.customer_id

),

subscriptions_xf_2 as (

    select
        subscriptions_xf.user_id,
        subscriptions_xf.customer_id,
        subscriptions_xf.subscription_id,
        subscriptions_xf.created,  -- start of subscription (including trial if one existed)
        subscriptions_xf.trial_start, -- start of trial (if existed)
        subscriptions_xf.trial_end,  -- end of trial (if existed)
        subscriptions_xf.current_period_start,  -- start of the current period (window for which charge applies)
        subscriptions_xf.current_period_end,  -- end of the current period (window for which charge applies)
        subscriptions_xf.canceled_at,  -- when the user canceled the subscription
        subscriptions_xf.ended_at,  -- when the subscription actually ended
        subscriptions_xf.status,
        subscriptions_xf.brand,
        subscriptions_xf.interval,
        subscriptions_xf.discount_id,
        subscriptions_xf.is_deleted,
        subscriptions_xf.promotion_id,
        subscriptions_xf.metadata_migrated,

        perks.perk is null, false) as perk_redeemed,  -- AFTER L043 FIX

        perks.received_at as perk_received_at,
        perks.perk

    from subscriptions_xf

    left join perks
        on subscriptions_xf.user_id = perks.user_id
            and subscriptions_xf.created < perks.received_at  -- perk can only occur after trial/subscription starts
            -- perk must be redeemed before trial/subscription ends, but can't be in future (i.e. tomorrow)
            and coalesce(subscriptions_xf.ended_at, current_date + 1) > perks.received_at

),

final as (

    select
        user_id,
        customer_id,
        subscription_id,
        created,  -- start of subscription (including trial if one existed)
        trial_start, -- start of trial (if existed)
        trial_end,  -- end of trial (if existed)

        case
            when trial_end is null then created
            -- when annual subscription migrated to Stripe
            when metadata_migrated is not null and "interval" = 'annual' then dateadd('year', -1, trial_end)
            -- when monthly subscription migrated to Stripe
            when metadata_migrated is not null and "interval" = 'month' then dateadd('month', -1, trial_end)
            else trial_end
        end as subscription_start,  -- start of PAID subscription (does not include trial period)

        current_period_start,  -- start of the current period (window for which charge applies)
        current_period_end,  -- end of the current period (window for which charge applies)
        canceled_at,  -- when the user canceled the subscription
        ended_at,  -- when the subscription actually ended
        "status",
        brand,
        "interval",
        discount_id,
        is_deleted,
        promotion_id,
        metadata_migrated,
        perk_redeemed,
        perk_received_at,
        perk

    from subscriptions_xf_2

)

select * from final

```
</p>
</details>

As shown in the second screenshot with git-diffs, this is producing an unexpected fix.

## Version
I have installed sqlfluff from source using `pip install git+https://github.com/sqlfluff/sqlfluff.git@master` (currently on commit: 9970bf2) so that I can take advantage of recent PRs (#868 and #964) instead of having to wait for the next release.

## Configuration

<details>
<summary>SQLFluff configuration</summary>
<p>
# For SQLFluff Rules reference, see:
# https://docs.sqlfluff.com/en/stable/rules.html#rules-reference
[sqlfluff]
verbose = 0
nocolor = False
dialect = postgres
templater = dbt
rules = None
exclude_rules = L032,L033,L034,L037,L044
recurse = 0
output_line_length = 120
runaway_limit = 10
ignore = parsing
ignore_templated_areas = True

[sqlfluff:indentation]
indented_joins = False
template_blocks_indent = True

# Some rules can be configured directly from the config common to other rules.
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 120
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
only_aliases = True

# Some rules have their own specific config.
# All SQLFluff rules can be found at: https://docs.sqlfluff.com/en/stable/rules.html#rules-reference
# When a rule in not listed below, we inherit the default behavior from above.
[sqlfluff:rules:L003]
lint_templated_tokens = True

[sqlfluff:rules:L010]  # Keywords
capitalisation_policy = lower

[sqlfluff:rules:L014]  # Unquoted identifiers
extended_capitalisation_policy = lower
unquoted_identifiers_policy = all

[sqlfluff:rules:L016]
# Setting to True allows us to copy/paste long URLs as comments
ignore_comment_lines = True

[sqlfluff:rules:L030]  # Function names
capitalisation_policy = lower

[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid

[sqlfluff:rules:L040]  # Null & Boolean Literals
capitalisation_policy = lower

[sqlfluff:rules:L042]
# By default, allow subqueries in from clauses, but not join clauses.
forbid_subquery_in = join

```
</p>
</details>



Discussion:

Patch:
diff --git a/src/sqlfluff/core/rules/std/L043.py b/src/sqlfluff/core/rules/std/L043.py
index 1bfeb8786..36ace0be9 100644
--- a/src/sqlfluff/core/rules/std/L043.py
+++ b/src/sqlfluff/core/rules/std/L043.py
@@ -81,11 +81,12 @@ class Rule_L043(BaseRule):
                 and else_bool_type is not None
                 and then_bool_type != else_bool_type
             ):
-                # Delete everything but the first expression
-                fixes = []
+                # Generate list of segments to delete -- everything but the
+                # first expression.
+                delete_segments = []
                 for s in segment.segments:
                     if s != segment.segments[expression_idx]:
-                        fixes.append(LintFix("delete", s))
+                        delete_segments.append(s)
                 # If then-false, add "not" and space
                 edits = []
                 if then_bool_type == "FALSE":
@@ -113,10 +114,12 @@ class Rule_L043(BaseRule):
                     ),
                 ]
                 edits.extend(coalesce_parenthesis)
+                edit_coalesce_target = segment.segments[0]
+                fixes = []
                 fixes.append(
                     LintFix(
                         "edit",
-                        segment.segments[0],
+                        edit_coalesce_target,
                         edits,
                     )
                 )
@@ -149,6 +152,15 @@ class Rule_L043(BaseRule):
                         closing_parenthesis,
                     )
                 )
+                # Generate a "delete" action for each segment in
+                # delete_segments EXCEPT the one being edited to become a call
+                # to "coalesce(". Deleting and editing the same segment has
+                # unpredictable behavior.
+                fixes += [
+                    LintFix("delete", s)
+                    for s in delete_segments
+                    if s is not edit_coalesce_target
+                ]
                 return LintResult(
                     anchor=segment.segments[expression_idx],
                     fixes=fixes,


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L043.yml b/test/fixtures/rules/std_rule_cases/L043.yml
index 54b447677..2b37b3973 100644
--- a/test/fixtures/rules/std_rule_cases/L043.yml
+++ b/test/fixtures/rules/std_rule_cases/L043.yml
@@ -83,3 +83,25 @@ test_11:
     select
         not coalesce(not fab > 0 or tot > 0, false) as is_fab
     from fancy_table
+test_12:
+  fail_str: |
+    select
+        subscriptions_xf.metadata_migrated,
+
+        case  -- BEFORE L043 FIX
+            when perks.perk is null then false
+            else true
+        end as perk_redeemed,
+
+        perks.received_at as perk_received_at
+
+    from subscriptions_xf
+  fix_str: |
+    select
+        subscriptions_xf.metadata_migrated,
+
+        not coalesce(perks.perk is null, false) as perk_redeemed,
+
+        perks.received_at as perk_received_at
+
+    from subscriptions_xf