12

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):

plan with a stream agg

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):

plan without a stream agg

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
16

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.

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.