Odd Stream Aggregate behaviour












11















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:



enter image description here



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.










share|improve this question



























    11















    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:



    enter image description here



    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.










    share|improve this question

























      11












      11








      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:



      enter image description here



      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.










      share|improve this question














      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:



      enter image description here



      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













      share|improve this question











      share|improve this question




      share|improve this question










      asked May 30 '16 at 11:59









      Mikael ErikssonMikael Eriksson

      17.9k34688




      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

































            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























              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


















              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









              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






























                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




























                  13












                  13








                  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















                  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














                  share|improve this answer



                  share|improve this answer








                  edited 8 mins ago

























                  answered May 30 '16 at 12:25









                  Paul WhitePaul White

                  53.6k14285458




                  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




























                        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


























                          10












                          10








                          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













                          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












                          share|improve this answer



                          share|improve this answer










                          answered May 30 '16 at 12:28









                          Rob FarleyRob Farley

                          14k12549




                          14k12549






























                              draft saved

                              draft discarded




















































                              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.




                              draft saved


                              draft discarded














                              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





















































                              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







                              Popular posts from this blog

                              Liste der Baudenkmale in Friedland (Mecklenburg)

                              Single-Malt-Whisky

                              Czorneboh