Odd Stream Aggregate behaviour
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
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
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
asked May 30 '16 at 11:59
Mikael ErikssonMikael Eriksson
17.9k34688
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
sql-server execution-plan
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
asked May 30 '16 at 11:59
Mikael ErikssonMikael Eriksson
17.9k34688
asked May 30 '16 at 11:59
Mikael ErikssonMikael Eriksson
17.9k34688
17.9k34688
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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.
answered May 30 '16 at 12:25
Paul White♦Paul White
53.6k14285458
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.
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
});
}
});
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
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
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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.
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.
answered May 30 '16 at 12:25
Paul White♦Paul White
53.6k14285458
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.
answered May 30 '16 at 12:25
Paul White♦Paul White
53.6k14285458
edited 8 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
53.6k14285458
add a comment |
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.
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.
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.
answered May 30 '16 at 12:28
Rob FarleyRob Farley
14k12549
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.
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
answered May 30 '16 at 12:28
Rob FarleyRob Farley
14k12549
14k12549
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
