Query: optimize NOT (CASE WHEN x THEN 1 ELSE 0) by pushing NOT into the THEN/ELSE

[maumar]

pointed out by @ranma42

WHERE NOT (CASE
    WHEN instr("c"."CompanyName", "c"."ContactName") > 0 THEN 1
    ELSE 0
END)

can be simplified to:

WHERE CASE
    WHEN instr("c"."CompanyName", "c"."ContactName") > 0 THEN 0
    ELSE 1
END

can be safely done for constants for sure, need to think about/test more complex cases, e.g. if THEN itself is an expression, like a comparison.

[ranma42]

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)