Sfrgttk

Greatest common substring

How will losing mobility of one hand affect my career as a programmer?

Melting point of aspirin, contradicting sources

Can somebody explain Brexit in a few child-proof sentences?

Do the concepts of IP address and network interface not belong to the same layer?

Can I use my Chinese passport to enter China after I acquired another citizenship?

Proving a function is onto where f(x)=|x|.

How do I extrude a face to a single vertex

When quoting, must I also copy hyphens used to divide words that continue on the next line?

How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?

Bob has never been a M before

Open a doc from terminal, but not by its name

Ridge Regression with Gradient Descent Converges to OLS estimates

Global amount of publications over time

A Permanent Norse Presence in America

Indicating multiple different modes of speech (fantasy language or telepathy)

Is it possible to use .desktop files to open local pdf files on specific pages with a browser?

Did US corporations pay demonstrators in the German demonstrations against article 13?

Can I sign legal documents with a smiley face?

Does the Mind Blank spell prevent the target from being frightened?

Why does Async/Await work properly when the loop is inside the async function and not the other way around?

Engineer refusing to file/disclose patents

Could solar power be utilized and substitute coal in the 19th Century

Why did the EU agree to delay the Brexit deadline?

Odd Stream Aggregate behaviour

Optimising plans with XML readersHow to determine cause of runtime increase given two query plans with SpillToTempDb warningSHOWPLAN does not display a warning but “Include Execution Plan” does for the same queryEvery query plan statistic says my query should be faster, but it is notIncorrect statistics are used on index seekStream Aggregate for Sort?Join on NULL Key Column Optimizing as Table and Index ScansAre clustered indexes in Azure data warehouse ever a good idea on large tables?How to Replace Hash Match (Partial Aggregate) with Stream AggregateWhy would a Clustered Index Seek return a higher “Actual Number of Rows” than there are rows in the underlying table?

11

2

Query:

declare @X xml = '

<item ID = "0"/>

<item ID = "1"/>

<item/>

<item/>';



select I.X.value('@ID', 'int')

from @X.nodes('/item') as I(X);

Result:

-----------

0

1

NULL

NULL

Execution plan:

The top branch shreds the XML to four rows and the bottom branch fetches the value for the attribute ID.

What strikes me as odd is the number of rows returned from the Stream Aggregate operator. The 2 rows that comes from the Filter is the ID attribute from the first and second item nodes in the XML. The Stream Aggregate returns four rows, one for each input row, effectively turning the Inner Join to an Outer Join.

Is this something that Stream Aggregate does in other circumstances as well or is it just something odd going on when doing XML queries?

I can not see any hints in the XML version of the query plan that this Stream Aggregate should behave any differently than any other Stream Aggregate I have noticed before.

sql-server execution-plan

share|improve this question

asked May 30 '16 at 11:59

Mikael ErikssonMikael Eriksson

17.9k34688

add a comment | 

11

2

Query:

declare @X xml = '

<item ID = "0"/>

<item ID = "1"/>

<item/>

<item/>';



select I.X.value('@ID', 'int')

from @X.nodes('/item') as I(X);

Result:

-----------

0

1

NULL

NULL

Execution plan:

The top branch shreds the XML to four rows and the bottom branch fetches the value for the attribute ID.

What strikes me as odd is the number of rows returned from the Stream Aggregate operator. The 2 rows that comes from the Filter is the ID attribute from the first and second item nodes in the XML. The Stream Aggregate returns four rows, one for each input row, effectively turning the Inner Join to an Outer Join.

Is this something that Stream Aggregate does in other circumstances as well or is it just something odd going on when doing XML queries?

I can not see any hints in the XML version of the query plan that this Stream Aggregate should behave any differently than any other Stream Aggregate I have noticed before.

sql-server execution-plan

share|improve this question

asked May 30 '16 at 11:59

Mikael ErikssonMikael Eriksson

17.9k34688

add a comment | 

Query:

declare @X xml = '

<item ID = "0"/>

<item ID = "1"/>

<item/>

<item/>';



select I.X.value('@ID', 'int')

from @X.nodes('/item') as I(X);

Result:

-----------

0

1

NULL

NULL

Execution plan:

The top branch shreds the XML to four rows and the bottom branch fetches the value for the attribute ID.

What strikes me as odd is the number of rows returned from the Stream Aggregate operator. The 2 rows that comes from the Filter is the ID attribute from the first and second item nodes in the XML. The Stream Aggregate returns four rows, one for each input row, effectively turning the Inner Join to an Outer Join.

Is this something that Stream Aggregate does in other circumstances as well or is it just something odd going on when doing XML queries?

I can not see any hints in the XML version of the query plan that this Stream Aggregate should behave any differently than any other Stream Aggregate I have noticed before.

sql-server execution-plan

share|improve this question

asked May 30 '16 at 11:59

Mikael ErikssonMikael Eriksson

17.9k34688

declare @X xml = '

<item ID = "0"/>

<item ID = "1"/>

<item/>

<item/>';



select I.X.value('@ID', 'int')

from @X.nodes('/item') as I(X);

-----------

0

1

NULL

NULL

share|improve this question

asked May 30 '16 at 11:59

Mikael ErikssonMikael Eriksson

17.9k34688

share|improve this question

asked May 30 '16 at 11:59

Mikael ErikssonMikael Eriksson

17.9k34688

asked May 30 '16 at 11:59

Mikael ErikssonMikael Eriksson

17.9k34688

asked May 30 '16 at 11:59

Mikael ErikssonMikael Eriksson

17.9k34688

2 Answers
2

active

oldest

votes

13

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.

share|improve this answer

edited 7 mins ago

answered May 30 '16 at 12:25

Paul White♦Paul White

53.6k14285458

add a comment | 

10

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.

share|improve this answer

answered May 30 '16 at 12:28

Rob FarleyRob Farley

14k12549

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f139912%2fodd-stream-aggregate-behaviour%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

13

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.

share|improve this answer

edited 7 mins ago

answered May 30 '16 at 12:25

Paul White♦Paul White

53.6k14285458

add a comment | 

13

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.

share|improve this answer

edited 7 mins ago

answered May 30 '16 at 12:25

Paul White♦Paul White

53.6k14285458

add a comment | 

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.

share|improve this answer

edited 7 mins ago

answered May 30 '16 at 12:25

Paul White♦Paul White

53.6k14285458

-- 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 ();

share|improve this answer

edited 7 mins ago

answered May 30 '16 at 12:25

Paul White♦Paul White

53.6k14285458

answered May 30 '16 at 12:25

Paul White♦Paul White

53.6k14285458

answered May 30 '16 at 12:25

Paul White♦Paul White

53.6k14285458

10

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.

share|improve this answer

answered May 30 '16 at 12:28

Rob FarleyRob Farley

14k12549

add a comment | 

10

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.

share|improve this answer

answered May 30 '16 at 12:28

Rob FarleyRob Farley

14k12549

add a comment | 

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.

share|improve this answer

answered May 30 '16 at 12:28

Rob FarleyRob Farley

14k12549

share|improve this answer

answered May 30 '16 at 12:28

Rob FarleyRob Farley

14k12549

answered May 30 '16 at 12:28

Rob FarleyRob Farley

14k12549

answered May 30 '16 at 12:28

Rob FarleyRob Farley

14k12549