Repository: sqlfluff/sqlfluff
Pull request: Simplify nested cases
<!--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
Fixes #1799 

This is the first new rule implemented using the functional API. 🎉 

This rule works a bit differently than was initially suggested in the issue -- it doesn't check (nor does it need to) look at the _condition_ of the `WHEN` clause, because the `CASE`/`WHEN`/`ELSE` _structure_ alone is sufficient to allow simplification. It does simplify the example provided in the issue. 👍

### Are there any other side effects of this change that we should be aware of?


### 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:
New rule: Nested CASEs with conditions on the same column
When conditions in iterative `CASE` statements are on the same column they should be simplified without any cost i.e. 

```sql
CASE
  WHEN species = 'Cat' THEN 'Meow'
  ELSE
  CASE
     WHEN species = 'Dog' THEN 'Woof'
  END
END as sound
```

is really just

```sql
CASE
    WHEN species = 'Cat' THEN 'Meow'
    WHEN species = 'Dog' THEN 'Woof'
END as sound
```

Hints from issue discussion:
Is there ever a situation where nested CASE statements are needed?

Since the CASE statement is effectively an IF/ELIF/ELSE structure you could reduce all nested case statements?
i.e.
```SQL
CASE
  WHEN species = “Cat” THEN “Meow”
  ELSE
  CASE
     WHEN number_of_legs = 8 THEN “<spider_sounds>”
  END
END as sound
```
is equivalent to
```SQL
CASE
  WHEN species = “Cat” THEN “Meow”
  WHEN number_of_legs = 8 THEN “<spider_sounds>”
END as sound
```

So we can make a rule to stop the use of nested case statements and replace them as above.
Nested `CASE` statements could be used as an alternative to `AND`, e.g.:
```
CASE
    WHEN number_of_sides = 4 THEN
        CASE
        WHEN side_lengths = "same" THEN
            CASE
            WHEN angle = 90 THEN "square"
            ELSE "rhombus"
        ELSE
            CASE
            WHEN angle = 90 THEN "rectangle"
            ELSE "parallelogram"
        END
    END
END
```

I think the generalized rule for "can this be de-nested" is: _Are the nested conditions mutually exclusive to the outer condition?_
I've got a preliminary implementation of this rule in a [branch](https://github.com/barrywhart/sqlfluff/tree/bhart-issue_1799_simplify_nested_cases). It builds on some unmerged work (PR #2126), so this new rule won't be completed until that other PR reviewed and merged. 

The approach I took for implementing it is to look for `CASE`/`ELSE` where the thing inside the `ELSE` is itself a `CASE` statement. IIUC, it should always be safe to "unnest" (i.e. move) the `WHEN` clauses of the inner `CASE` to the end of the outer `CASE`.

Commit message: Add back a blank line

Patch to review:
diff --git a/src/sqlfluff/rules/L058.py b/src/sqlfluff/rules/L058.py
new file mode 100644
index 00000000000..90cba2a1e6d
--- /dev/null
+++ b/src/sqlfluff/rules/L058.py
@@ -0,0 +1,89 @@
+"""Implementation of Rule L058."""
+
+from sqlfluff.core.parser import NewlineSegment, WhitespaceSegment
+from sqlfluff.core.rules.base import BaseRule, LintFix, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import document_fix_compatible
+from sqlfluff.core.rules.functional import sp
+
+
+@document_fix_compatible
+class Rule_L058(BaseRule):
+    """Nested CASE statements could be flattened.
+
+    | **Anti-pattern**
+    | In this example, the outer CASE's "ELSE" is another CASE.
+
+    .. code-block:: sql
+
+        SELECT
+          CASE
+            WHEN species = 'Cat' THEN 'Meow'
+            ELSE
+            CASE
+               WHEN species = 'Dog' THEN 'Woof'
+            END
+          END as sound
+        FROM mytable
+
+    | **Best practice**
+    | Move the body of the inner "CASE" to the end of the outer one.
+
+    .. code-block:: sql
+
+        SELECT
+          CASE
+            WHEN species = 'Cat' THEN 'Meow'
+            WHEN species = 'Dog' THEN 'Woof'
+          END AS sound
+        FROM mytable
+
+    """
+
+    def _eval(self, context: RuleContext) -> LintResult:
+        """Nested CASE should be simplified."""
+        segment = context.functional.segment
+        if segment.select(sp.is_type("case_expression")):
+            case1_children = segment.children()
+            case1_last_when = case1_children.last(sp.is_type("when_clause"))
+            case1_else_clause = case1_children.select(sp.is_type("else_clause"))
+            case2 = case1_else_clause.children(sp.is_type("expression")).children(
+                sp.is_type("case_expression")
+            )
+            if not case1_last_when or not case2:
+                return LintResult()
+
+            # Delete stuff between the last "WHEN" clause and the "ELSE" clause.
+            case1_to_delete = case1_children.select(
+                start_seg=case1_last_when.get(), stop_seg=case1_else_clause.get()
+            )
+
+            # Delete the nested "CASE" expression.
+            fixes = case1_to_delete.apply(lambda seg: LintFix.delete(seg))
+
+            # Determine the indentation to use when we move the nested "WHEN"
+            # and "ELSE" clauses, based on the indentation of case1_last_when.
+            # If no whitespace segments found, use default indent.
+            indent = (
+                case1_children.select(stop_seg=case1_last_when.get())
+                .reversed()
+                .select(sp.is_type("whitespace"))
+            )
+            indent_str = "".join(seg.raw for seg in indent) if indent else self.indent
+
+            # Move the nested "when" and "else" clauses after the last outer
+            # "when".
+            nested_clauses = case2.children(sp.is_type("when_clause", "else_clause"))
+            create_after_last_when = nested_clauses.apply(
+                lambda seg: [NewlineSegment(), WhitespaceSegment(indent_str), seg]
+            )
+            fixes.append(
+                LintFix.create_after(
+                    case1_last_when.get(),
+                    [item for sublist in create_after_last_when for item in sublist],
+                )
+            )
+
+            # Delete the outer "else" clause.
+            fixes.append(LintFix.delete(case1_else_clause.get()))
+            return LintResult(case2[0], fixes=fixes)
+        return LintResult()
diff --git a/test/fixtures/rules/std_rule_cases/L058.yml b/test/fixtures/rules/std_rule_cases/L058.yml
new file mode 100644
index 00000000000..bbf731f6008
--- /dev/null
+++ b/test/fixtures/rules/std_rule_cases/L058.yml
@@ -0,0 +1,189 @@
+rule: L058
+
+test_pass_1:
+  # The nested CASE is under a "WHEN", not an "ELSE".
+  pass_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN
+              CASE
+                  WHEN species = 'Dog' THEN 'Woof'
+              END
+        END AS sound
+    FROM mytable
+
+test_fail_1:
+  # Simple case.
+  fail_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            ELSE
+                CASE
+                    WHEN species = 'Dog' THEN 'Woof'
+                END
+        END AS sound
+    FROM mytable
+  pass_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            WHEN species = 'Dog' THEN 'Woof'
+        END AS sound
+    FROM mytable
+
+test_fail_2:
+  # The nested "CASE" has two "WHEN" clauses. Getting
+  # reasonable indentation is tricky.
+  fail_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            ELSE
+                CASE
+                    WHEN species = 'Dog' THEN 'Woof'
+                    WHEN species = 'Mouse' THEN 'Squeak'
+                END
+        END AS sound
+    FROM mytable
+  fix_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            WHEN species = 'Dog' THEN 'Woof'
+            WHEN species = 'Mouse' THEN 'Squeak'
+        END AS sound
+    FROM mytable
+
+test_fail_3:
+  fail_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            ELSE
+                CASE
+                    WHEN species = 'Dog' THEN 'Woof'
+                    WHEN species = 'Mouse' THEN 'Squeak'
+                    ELSE "Whaa"
+                END
+        END AS sound
+    FROM mytable
+  fix_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            WHEN species = 'Dog' THEN 'Woof'
+            WHEN species = 'Mouse' THEN 'Squeak'
+            ELSE "Whaa"
+        END AS sound
+    FROM mytable
+
+test_fail_4:
+  fail_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            ELSE
+                CASE
+                    ELSE "Whaa"
+                END
+        END AS sound
+    FROM mytable
+  fix_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            ELSE "Whaa"
+        END AS sound
+    FROM mytable
+
+test_fail_5:
+  # The nested "CASE" is a one-liner.
+  fail_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            ELSE
+                CASE WHEN species = 'Dog' THEN 'Woof' END
+        END AS sound
+    FROM mytable
+  pass_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN 'Squeak'
+            WHEN species = 'Dog' THEN 'Woof'
+        END AS sound
+    FROM mytable
+
+test_double_nesting_1:
+  fail_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN
+                CASE
+                    WHEN species = 'Dog' THEN 'Woof'
+                    ELSE
+                        CASE
+                            WHEN species = 'Bird' THEN 'tweet'
+                        END
+                END
+        END AS sound
+    FROM mytable
+  pass_str: |
+    SELECT
+    c1,
+    CASE
+        WHEN species = 'Rat' THEN
+            CASE
+                WHEN species = 'Dog' THEN 'Woof'
+                WHEN species = 'Bird' THEN 'tweet'
+            END
+    END AS sound
+    FROM mytable
+
+test_double_nesting_2:
+  # NOTE: This could be simplified more, but the current version of the rule
+  # only unnests "ELSE" statements. To do this safely, it'd have to verify
+  # that the various "WHEN" clauses are mutually exclusive.
+  fail_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN
+                CASE
+                    WHEN species = 'Dog' THEN 'Woof'
+                    ELSE
+                        CASE
+                            WHEN species = 'Bird' THEN 'tweet'
+                        END
+                END
+           ELSE
+                CASE
+                    WHEN species = 'Hyena' THEN 'Cackle'
+                END
+        END AS sound
+    FROM mytable
+  fix_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN
+                CASE
+                    WHEN species = 'Dog' THEN 'Woof'
+                    WHEN species = 'Bird' THEN 'tweet'
+                END
+            WHEN species = 'Hyena' THEN 'Cackle'
+        END AS sound
+    FROM mytable


Code review comments:
diff_hunk: @@ -0,0 +1,189 @@
+rule: L058
+
+test_pass_1:
+  # The nested CASE is under a "WHEN", not an "ELSE".
+  pass_str: |
+    SELECT
+        c1,
+        CASE
+            WHEN species = 'Rat' THEN
+              CASE
+                  WHEN species = 'Dog' THEN 'Woof'
+              END
path: test/fixtures/rules/std_rule_cases/L058.yml
text: @user1:
I don't understand this example? Instead of returning 'squeak' we go into another case (also for species, but a different one, so this can never be true). I get it's just an example but should we pick a better one that makes sense?

```suggestion
            WHEN species = 'Rat' THEN
              CASE
                  WHEN colour = 'Black' THEN 'Growl'
                  WHEN colour = 'Grey' THEN 'Squeak'
              END
```

@author:
Ha, great point! I was in "robotic example mode." 🤪

diff_hunk: @@ -0,0 +1,89 @@
+"""Implementation of Rule L058."""
+
+from sqlfluff.core.parser import NewlineSegment, WhitespaceSegment
+from sqlfluff.core.rules.base import BaseRule, LintFix, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import document_fix_compatible
+from sqlfluff.core.rules.functional import sp
+
+
+@document_fix_compatible
+class Rule_L058(BaseRule):
+    """Nested CASE statements could be flattened.
+
+    | **Anti-pattern**
+    | In this example, the outer CASE's "ELSE" is another CASE.
path: src/sqlfluff/rules/L058.py
text: @user1:
```suggestion
    | In this example, the outer CASE's "ELSE" is an unnecessary other CASE.
```