Repository: sqlfluff/sqlfluff
Pull request: Fix L043 issue when trying to autofix functions
<!--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.-->
Fixes #2047.  L043 was throwing an error when trying to incorrectly apply the fix for the IS NULL reduction case. This should only be applied when a column is compared to NULL in the condition expression, however, it was attempting to apply to functions. I have added the step to filter out the non-column null comparisons.

### Are there any other side effects of this change that we should be aware of?
No, bugfix

### 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 `python test/generate_parse_fixture_yml.py` or by running `tox` locally).
  - 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:
Applying rule L043 threw an Exception
<!--If this is a parsing or linting issue, please include a minimal SQL example which reproduces the issue, along with the `sqlfluff parse` output, `sqlfluff lint` output and `sqlfluff fix` output when relevant.-->

## Expected Behaviour
No exception

## Observed Behaviour
```
CRITICAL   [L043] Applying rule L043 threw an Exception:                                                                                                    
Traceback (most recent call last):                                                                                                                          
  File "/Users/tekumara/code/jaffle-shop/.venv/lib/python3.9/site-packages/sqlfluff/core/rules/base.py", line 324, in crawl
    res = self._eval(
  File "/Users/tekumara/code/jaffle-shop/.venv/lib/python3.9/site-packages/sqlfluff/rules/L043.py", line 218, in _eval
    column_reference_segment = next(
StopIteration
```

## Steps to Reproduce
_models/test.sql_
```
SELECT dv_runid
        ,CASE
                WHEN LEAD(dv_startdateutc) OVER (
                                PARTITION BY rowid ORDER BY dv_startdateutc
                                ) IS NULL
                        THEN 1
                ELSE 0
                END loadstate
FROM d
```
## Dialect

## Version
sqlfluff, version 0.8.2
python 3.9

## Configuration

Default out-of-the-box config.

## Notes

This exception isn't thrown by sqlfluff 0.8.1

Hints from issue discussion:
can confirm that I also see this error, I'll take a look this evening

reformatted to only raise the L043 exception
```sql
SELECT
    dv_runid,
    CASE
        WHEN LEAD(dv_startdateutc) OVER (
            PARTITION BY rowid ORDER BY dv_startdateutc
        ) IS NULL
        THEN 1
        ELSE 0
    END AS loadstate
FROM d
```
Additional test case courtesy of @pwildenhain 

```sql
select
    field_1,
    field_2,
    field_3,
    case
        when coalesce(field_2, field_3) is null then 1 else 0
    end as field_4
from my_table
```

Commit message: Merge branch 'main' into fix_L043_issue

Patch to review:
diff --git a/src/sqlfluff/rules/L043.py b/src/sqlfluff/rules/L043.py
index 52592bc19a1..34e05462026 100644
--- a/src/sqlfluff/rules/L043.py
+++ b/src/sqlfluff/rules/L043.py
@@ -216,11 +216,18 @@ def _eval(self, context: RuleContext) -> Optional[LintResult]:
 
                 # Locate column reference in condition expression.
                 column_reference_segment = next(
-                    segment
-                    for segment in condition_expression.segments
-                    if segment.type == "column_reference"
+                    (
+                        segment
+                        for segment in condition_expression.segments
+                        if segment.type == "column_reference"
+                    ),
+                    None,
                 )
 
+                # Return None if no column reference is detected (this condition does not apply to funcitons).
+                if not column_reference_segment:
+                    return None
+
                 # Check if we can reduce the CASE expression to a single coalesce function.
                 if (
                     not is_not_prefix
diff --git a/test/fixtures/rules/std_rule_cases/L043.yml b/test/fixtures/rules/std_rule_cases/L043.yml
index 9702b3ea7e6..d91b6630746 100644
--- a/test/fixtures/rules/std_rule_cases/L043.yml
+++ b/test/fixtures/rules/std_rule_cases/L043.yml
@@ -85,6 +85,28 @@ test_pass_case_cannot_be_reduced_10:
             else bar
         end as test
     from baz;
+test_pass_case_cannot_be_reduced_11:
+  pass_str: |
+    SELECT
+        dv_runid,
+        CASE
+            WHEN LEAD(dv_startdateutc) OVER (
+                PARTITION BY rowid ORDER BY dv_startdateutc
+            ) IS NULL
+            THEN 1
+            ELSE 0
+        END AS loadstate
+    FROM d;
+test_pass_case_cannot_be_reduced_12:
+  pass_str: |
+    select
+        field_1,
+        field_2,
+        field_3,
+        case
+            when coalesce(field_2, field_3) is null then 1 else 0
+        end as field_4
+    from my_table;
 test_fail_unnecessary_case_1:
   fail_str: |
     select


Code review comments:
diff_hunk: @@ -216,11 +216,18 @@ def _eval(self, context: RuleContext) -> Optional[LintResult]:
 
                 # Locate column reference in condition expression.
                 column_reference_segment = next(
-                    segment
-                    for segment in condition_expression.segments
-                    if segment.type == "column_reference"
+                    (
+                        segment
+                        for segment in condition_expression.segments
+                        if segment.type == "column_reference"
+                    ),
+                    None,
                 )
 
+                # Return None if no column reference is detected (this condition does not apply to funcitons).
path: src/sqlfluff/rules/L043.py
text: @user1:
```suggestion
                # Return None if no column reference is detected (this condition does not apply to functions).
```

@author:
actually "funcitons" is the hot new programming paradigm 😛 

@author:
fixed it now 😄