Mdthbs

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:

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.