Query: optimize nested CASE blocks

[maumar]

Some nested CASE blocks, e.g.:

CASE 
    WHEN (condition1)
    THEN 
    CASE
        WHEN (condition2)
        THEN result1
        ELSE result2 
    END
    ELSE result2
END

can be optimized to:

CASE
    WHEN condition1 AND condition2
    THEN result1
    ELSE result2
END

In practice we get those kinds of chained CASE blocks in the following scenarios:

context.Set<Product>().OrderBy(p => p.UnitsInStock > 10 ? p.ProductID > 40 : p.ProductID <= 40)

[smitpatel]

Duplicate of #2881

[maumar]

this is somewhat different scenario, where nested block happens in the THEN part, not the ELSE part

[ranma42]

There are 3 ways in which CASE statements could be nested, each with a slightly different optimization:

ELSE

CASE 
    WHEN t1 THEN r1
    ELSE CASE
        WHEN t2 THEN r2
        ELSE r3
    END
END

is already being optimized into

CASE 
    WHEN t1 THEN r1
    WHEN t2 THEN r2
    ELSE r3
END

The optimization could be extended to also allow flattening of ELSE CASEs with the same operand as the outer CASE (as long as side-effects are not possible/considered).

THEN

CASE 
    WHEN t1 THEN CASE
        WHEN t2 THEN r1
        ELSE r2
    END
    ELSE r2
END

can be simplified to

CASE 
    WHEN t1 AND t2 THEN r1
    ELSE r2
END

(the main target of this issue). This requires matching r2 results and can only be applied (in a trivial way) to the last clause.

CASE

CASE 
    WHEN CASE
        WHEN t1 THEN TRUE -- b1
        ELSE FALSE -- b2
    END THEN r1
    ELSE r2
END

can be simplified to

CASE 
    WHEN t1 THEN r1
    ELSE r2
END

This also works when b2 is NULL; it can easily be extended to also work when b1 is NULL or FALSE and b2 is TRUE by negating t1 in the result.