Odd Stream Aggregate behaviour

The aggregate is a scalar aggregate (no group by clause). These are defined in SQL Server to always produce a row, even if the input is empty.

For a scalar aggregate, MAX of no rows is NULL, COUNT of no rows is zero, for example. The optimizer knows all about this, and can transform an outer join into an inner join in suitable circumstances.

-- NULL for a scalar aggregate
SELECT MAX(V.v) FROM (VALUES(1)) AS V (v) WHERE V.v = 2;

-- No row for a vector aggregate
SELECT MAX(V.v) FROM (VALUES(1)) AS V (v) WHERE V.v = 2 GROUP BY ();

For more about aggregates, see my article Fun With Scalar and Vector Aggregates.

The thing to remember here is that execution plans suck the data through.

So the Nested Loop operator calls the Stream Aggregate 4 times. The Stream Aggregate calls the Filter 4 times as well, but only gets a value twice.

So the Stream Aggregate gives four values. Twice it gives a value, and twice it gives Null.