Csdrhrt

Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script):

SELECT MAX(Revision)

FROM dbo.TheOneders

WHERE Id = 1 AND 1 = (SELECT 1);

Note: the "AND 1 = (SELECT 1) is just to keep this query from being auto-parameterized, which I felt like was confusing the issue - it actually gets the same plan with or without that clause though

And here's the plan (paste the plan link):

Since there is a "top 1" there, I was surprised to see the stream aggregate operator. It doesn't seem necessary to me, since there is guaranteed to only be one row.

To test that theory, I tried out this logically equivalent query:

SELECT MAX(Revision)

FROM dbo.TheOneders

WHERE Id = 1

GROUP BY Id;

Here's the plan for that one (paste the plan link):

Sure enough, the group by plan is able to get by without the stream aggregate operator.

Notice that both queries read "backwards" from the end of the index and do a "top 1" to get the max revision.

What am I missing here? Is the stream aggregate actually doing work in the first query, or should it be able to be eliminated (and it's just a limitation of the optimizer that it's not)?

By the way, I realize this is not an incredibly practical problem (both queries report 0 ms of CPU and elapsed time), I'm just curious about the internals / behavior being exhibited here.

Here's the setup code I ran before running the two queries above:

DROP TABLE IF EXISTS dbo.TheOneders;

GO



CREATE TABLE dbo.TheOneders

(

    Id INT NOT NULL,

    Revision SMALLINT NOT NULL,

    Something NVARCHAR(23),



    CONSTRAINT PK_TheOneders PRIMARY KEY NONCLUSTERED (Id, Revision)

);

GO



INSERT INTO dbo.TheOneders

    (Id, Revision, Something)

SELECT DISTINCT TOP 1000 

    1, m.message_id, 'Do...'

FROM sys.messages m

ORDER BY m.message_id

OPTION (MAXDOP 1);



INSERT INTO dbo.TheOneders

    (Id, Revision, Something)

SELECT DISTINCT TOP 100 

    2, m.message_id, 'Do that thing you do...'

FROM sys.messages m

ORDER BY m.message_id

OPTION (MAXDOP 1);

GO

You can see the role of this aggregate if no rows match the WHERE clause.

SELECT MAX(Revision)

FROM   dbo.TheOneders

WHERE  Id = 1

       AND 1 = 1 /*To avoid auto parameterisation*/

       AND Id%3 = 4  /*always false*/

In that case zero rows go into the aggregate but it still emits one as the correct semantics are to return NULL in this case.

This is a scalar aggregate as opposed to a vector one.

Your "logically equivalent" query is not equivalent. Adding GROUP BY Id would make it a vector aggregate and then the correct behaviour would be to return no rows.

See Fun with Scalar and Vector Aggregates for more about this.