Additional optimization suggestions for CASE

[maumar]

          Some generalizations/similar simplifications:

1. CASE WHEN t1 THEN x WHEN t2 THEN x ... WHEN tn THEN x ELSE x END → x
   This should be safe even for impure x 🎉
2. CASE WHEN boolExpr THEN TRUE ELSE FALSE END → boolExpr
3. CASE WHEN NOT (boolExpr) THEN x ELSE y END → CASE WHEN boolExpr THEN y ELSE x END
4. NOT (CASE WHEN nullOrBoolExpr THEN x ELSE y END) → CASE WHEN nullOrBoolExpr THEN NOT(x) ELSE NOT(y) END
   ⚠️ it can improve or worsen the complexity of the query
5. NOT (CASE WHEN nullOrBoolExpr THEN TRUE ELSE FALSE END) → CASE WHEN nullOrBoolExpr THEN FALSE ELSE TRUE END
   This is the query in the top comment and is relevant because it is emitted within EFCore; this specialization of the previous case always improves the query.
6. NOT (COALESCE(nullOrBoolExpr, FALSE)) -> COALESCE(NOT(nullOrBoolExpr), TRUE)
   is the COALESCE version of the previous rule. It does not improve the complexity of the query, but additional simplifications might be possible on NOT(nullOrBoolExpr)

Originally posted by @ranma42 in #33857 (comment)

[maumar]

@ranma42 opened a new issue, to track the potential optimizations you suggested, because the original issue is now fixes & closed

[ranma42]

See #18774 for optimizations related to nested CASE expressions.
The fourth optimization could be generalized to apply to most operations; as before, it is probably effective on constants:
CASE WHEN nullOrBoolExpr THEN 5 ELSE 9 END = 9
→ CASE WHEN nullOrBoolExpr THEN 5 = 9 ELSE 9 = 9 END
→ CASE WHEN nullOrBoolExpr THEN FALSE ELSE TRUE END