How to rewrite CASE expression to short-circuit evaluation

Question

I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase?

Demo data:

CREATE TABLE #Docs (
    ID INT NOT NULL
    ,DocType TINYINT NOT NULL
    );

CREATE TABLE #DocsItems (
    IDDocs INT NOT NULL
    ,Amount NUMERIC(19,6)
    );

INSERT INTO #Docs(ID, DocType) VALUES(1,1),(2,1),(3,2),(4,2),(5,2),(6,2);
INSERT INTO #DocsItems(IDDocs,Amount) VALUES(3,50.),(3,25.),(3,33.),(4,44.),(4,123.),(6,11.);

Subject query:

SELECT
    -- expression
    SumAmount = CASE 
                    WHEN D.DocType <> 1 THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

Query plan:

If I rewrite the query (on purpose) to:

SELECT
    -- expression
    SumAmount = CASE 
                    WHEN 2 = 1 /* rewrite*/  THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1

The plan goes the way it should / could / would in the original query:

Answer 1

What is currently happening

When running your query, the table scan, stream agg & compute scalar operators are not evaluated at runtime.

---

Why is it happening

The apply NL join means that for each row in #Docs, return a row from #Docsitems that matches the predicate. This predicate should be WHERE IDDocs = D.ID

But the compute scalar operator (EXPR1007) next to the select (the actual case statement) is calling the scalar function in the inner side of the NL apply (EXPR1005) only when Doctype <> 1. This as you know cannot happen, and they both return NULL.

Compute scalar between NL & SELECT:

Compute scalar on the inner side of the join:

This all seems to be due to how the CASE statement functions & removing literals. (difference between CASE 2 = 1 vs. CASE WHEN D.DocType <> 1)

---

Resolving

If you change the query to this:

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

You should get the execution plan you desire:

---

Removing the SELonLOJ rule that is changing the plan on the rewrite.

Guessing

Reverting the rule that was applied to get closer to the CASE WHEN query can be done by adding the hint: OPTION( QUERYRULEOFF SELonLOJ )

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF SELonLOJ   );

Showing the same situation (minus filter & left join) as the case statement, but the runtime elimination is not occuring.

&

---

Another rule that seems to get closer to the case when when turning it off is JoinPredNorm

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF JoinPredNorm   )

With the filtering happening on the #Docs table as expected.