New rule: Nested CASEs with conditions on the same column

[iajoiner]

When conditions in iterative CASE statements are on the same column they should be simplified without any cost i.e.

CASE
  WHEN species = 'Cat' THEN 'Meow'
  ELSE
  CASE
     WHEN species = 'Dog' THEN 'Woof'
  END
END as sound

is really just

CASE
    WHEN species = 'Cat' THEN 'Meow'
    WHEN species = 'Dog' THEN 'Woof'
END as sound

[jpy-git]

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.

CASE
  WHEN species = “Cat” THEN “Meow”
  ELSE
  CASE
     WHEN number_of_legs = 8 THEN “<spider_sounds>”
  END
END as sound

is equivalent to

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.

[barrywhart]

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?

[barrywhart]

I've got a preliminary implementation of this rule in a branch. 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.