Non-deterministic sum of floats The Next CEO of Stack OverflowAre RANK() and DENSE_RANK() deterministic or non-deterministic?SQL join with multiple columns as FK to same list tableMySQL returns floats roundedSelect data divided in groups evenly distributed by valueRound-tripping column datatype causes size of table to growCan I make this multiple join query faster?A Dynamic where clause in MS SQL ServerPerformance gap between WHERE IN (1,2,3,4) vs IN (select * from STRING_SPLIT('1,2,3,4',','))How to move a Daily Partition to a Monthly Partitioned TableTrying to insert only unique items into one table and then create a many to many relationship in a join table only if that join does not exist

Why do remote companies require working in the US?

Why don't programming languages automatically manage the synchronous/asynchronous problem?

Does it take more energy to get to Venus or to Mars?

sp_blitzCache results Memory grants

What connection does MS Office have to Netscape Navigator?

Plot of histogram similar to output from @risk

Are there any limitations on attacking while grappling?

What was the first Unix version to run on a microcomputer?

How to invert MapIndexed on a ragged structure? How to construct a tree from rules?

How does the Z80 determine which peripheral sent an interrupt?

Can you replace a racial trait cantrip when leveling up?

Preparing Indesign booklet with .psd graphics for print

How long to clear the 'suck zone' of a turbofan after start is initiated?

Why do professional authors make "consistency" mistakes? And how to avoid them?

Are there any unintended negative consequences to allowing PCs to gain multiple levels at once in a short milestone-XP game?

Why does standard notation not preserve intervals (visually)

What benefits would be gained by using human laborers instead of drones in deep sea mining?

Is it possible to search for a directory/file combination?

Sending manuscript to multiple publishers

Inappropriate reference requests from Journal reviewers

What does convergence in distribution "in the Gromov–Hausdorff" sense mean?

Is "for causing autism in X" grammatical?

Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?

Real integral using residue theorem - why doesn't this work?



Non-deterministic sum of floats



The Next CEO of Stack OverflowAre RANK() and DENSE_RANK() deterministic or non-deterministic?SQL join with multiple columns as FK to same list tableMySQL returns floats roundedSelect data divided in groups evenly distributed by valueRound-tripping column datatype causes size of table to growCan I make this multiple join query faster?A Dynamic where clause in MS SQL ServerPerformance gap between WHERE IN (1,2,3,4) vs IN (select * from STRING_SPLIT('1,2,3,4',','))How to move a Daily Partition to a Monthly Partitioned TableTrying to insert only unique items into one table and then create a many to many relationship in a join table only if that join does not exist










9















Let me state the obvious fist: I completely understand that floating point types cannot accurately represent decimal values. This is not about that! Nevertheless, floating point calculations are supposed to be deterministic.



Now that this is out of the way, let me show you the curious case I observed today. I have a list of floating-point values, and I want to sum them up:



CREATE TABLE #someFloats (val float);
INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

SELECT STR(SUM(#someFloats.val), 30, 15) FROM #someFloats;

DROP TABLE #someFloats;

-- yields:
-- 13.600000000000001


So far, so good - no surprises here. We all know that 1.2 can't be represented exactly in binary representation, so the "imprecise" result is expected.



Now the following strange thing happens when I left-join another table:



CREATE TABLE #A (a int);
INSERT INTO #A (a) VALUES (1), (2);

CREATE TABLE #someFloats (val float);
INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

SELECT #A.a, STR(SUM(#someFloats.val), 30, 15)
FROM #someFloats LEFT JOIN #A ON 1 = 1
GROUP BY #A.a;

DROP TABLE #someFloats;
DROP TABLE #A;

-- yields
-- 1 13.600000000000001
-- 2 13.599999999999998


(sql fiddle, you can also see the execution plan there)



I have the same sum over the same values, but a different floating-point error. If I add more rows to table #A, we can see that the value alternates between those two values. I was only able to reproduce this issue with a LEFT JOIN; INNER JOIN works as expected here.



This is inconvenient, because it means that a DISTINCT, GROUP BY or PIVOT sees them as different values (which is actually how we discovered this issue).



The obvious solution is to round the value, but I'm curious: Is there a logical explanation for this behavior?










share|improve this question


























    9















    Let me state the obvious fist: I completely understand that floating point types cannot accurately represent decimal values. This is not about that! Nevertheless, floating point calculations are supposed to be deterministic.



    Now that this is out of the way, let me show you the curious case I observed today. I have a list of floating-point values, and I want to sum them up:



    CREATE TABLE #someFloats (val float);
    INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

    SELECT STR(SUM(#someFloats.val), 30, 15) FROM #someFloats;

    DROP TABLE #someFloats;

    -- yields:
    -- 13.600000000000001


    So far, so good - no surprises here. We all know that 1.2 can't be represented exactly in binary representation, so the "imprecise" result is expected.



    Now the following strange thing happens when I left-join another table:



    CREATE TABLE #A (a int);
    INSERT INTO #A (a) VALUES (1), (2);

    CREATE TABLE #someFloats (val float);
    INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

    SELECT #A.a, STR(SUM(#someFloats.val), 30, 15)
    FROM #someFloats LEFT JOIN #A ON 1 = 1
    GROUP BY #A.a;

    DROP TABLE #someFloats;
    DROP TABLE #A;

    -- yields
    -- 1 13.600000000000001
    -- 2 13.599999999999998


    (sql fiddle, you can also see the execution plan there)



    I have the same sum over the same values, but a different floating-point error. If I add more rows to table #A, we can see that the value alternates between those two values. I was only able to reproduce this issue with a LEFT JOIN; INNER JOIN works as expected here.



    This is inconvenient, because it means that a DISTINCT, GROUP BY or PIVOT sees them as different values (which is actually how we discovered this issue).



    The obvious solution is to round the value, but I'm curious: Is there a logical explanation for this behavior?










    share|improve this question
























      9












      9








      9


      1






      Let me state the obvious fist: I completely understand that floating point types cannot accurately represent decimal values. This is not about that! Nevertheless, floating point calculations are supposed to be deterministic.



      Now that this is out of the way, let me show you the curious case I observed today. I have a list of floating-point values, and I want to sum them up:



      CREATE TABLE #someFloats (val float);
      INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

      SELECT STR(SUM(#someFloats.val), 30, 15) FROM #someFloats;

      DROP TABLE #someFloats;

      -- yields:
      -- 13.600000000000001


      So far, so good - no surprises here. We all know that 1.2 can't be represented exactly in binary representation, so the "imprecise" result is expected.



      Now the following strange thing happens when I left-join another table:



      CREATE TABLE #A (a int);
      INSERT INTO #A (a) VALUES (1), (2);

      CREATE TABLE #someFloats (val float);
      INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

      SELECT #A.a, STR(SUM(#someFloats.val), 30, 15)
      FROM #someFloats LEFT JOIN #A ON 1 = 1
      GROUP BY #A.a;

      DROP TABLE #someFloats;
      DROP TABLE #A;

      -- yields
      -- 1 13.600000000000001
      -- 2 13.599999999999998


      (sql fiddle, you can also see the execution plan there)



      I have the same sum over the same values, but a different floating-point error. If I add more rows to table #A, we can see that the value alternates between those two values. I was only able to reproduce this issue with a LEFT JOIN; INNER JOIN works as expected here.



      This is inconvenient, because it means that a DISTINCT, GROUP BY or PIVOT sees them as different values (which is actually how we discovered this issue).



      The obvious solution is to round the value, but I'm curious: Is there a logical explanation for this behavior?










      share|improve this question














      Let me state the obvious fist: I completely understand that floating point types cannot accurately represent decimal values. This is not about that! Nevertheless, floating point calculations are supposed to be deterministic.



      Now that this is out of the way, let me show you the curious case I observed today. I have a list of floating-point values, and I want to sum them up:



      CREATE TABLE #someFloats (val float);
      INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

      SELECT STR(SUM(#someFloats.val), 30, 15) FROM #someFloats;

      DROP TABLE #someFloats;

      -- yields:
      -- 13.600000000000001


      So far, so good - no surprises here. We all know that 1.2 can't be represented exactly in binary representation, so the "imprecise" result is expected.



      Now the following strange thing happens when I left-join another table:



      CREATE TABLE #A (a int);
      INSERT INTO #A (a) VALUES (1), (2);

      CREATE TABLE #someFloats (val float);
      INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

      SELECT #A.a, STR(SUM(#someFloats.val), 30, 15)
      FROM #someFloats LEFT JOIN #A ON 1 = 1
      GROUP BY #A.a;

      DROP TABLE #someFloats;
      DROP TABLE #A;

      -- yields
      -- 1 13.600000000000001
      -- 2 13.599999999999998


      (sql fiddle, you can also see the execution plan there)



      I have the same sum over the same values, but a different floating-point error. If I add more rows to table #A, we can see that the value alternates between those two values. I was only able to reproduce this issue with a LEFT JOIN; INNER JOIN works as expected here.



      This is inconvenient, because it means that a DISTINCT, GROUP BY or PIVOT sees them as different values (which is actually how we discovered this issue).



      The obvious solution is to round the value, but I'm curious: Is there a logical explanation for this behavior?







      sql-server floating-point






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 16 hours ago









      HeinziHeinzi

      1,3721532




      1,3721532




















          1 Answer
          1






          active

          oldest

          votes


















          9














          Actually, the link you're referring to does not say that floating point arithmetic calculations are always deterministic. In fact, in one of the answers it's mentioned that addition is not associative (meaning (a + b) + c does not necessarily equal a + (b + c)), which is also said in this answer.



          If stream aggregation happens to process rows of each group in different order, this could explain the behaviour you observe.






          share|improve this answer























          • Associativity has no relation to determinism, so that bit is misleading.

            – Mooing Duck
            5 hours ago











          • Non-associativity of floating point addition leads to non-deterministic behaviour of the SQL Server aggregate function SUM(), would you agree @MooingDuck?

            – mustaccio
            4 hours ago











          • No? Integer Division is a clear counterexample. It is non-associative, but entirely deterministic. Likewise, floating point division should be non-associative and still deterministic. From that, we conclude it's reasonable for addition to be non-associative and still deterministic. That being said, if the order of additions isn't deterministic, then the result will likewise not be deterministic, so your first and last sentence are still correct regardless.

            – Mooing Duck
            1 hour ago











          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%2f233513%2fnon-deterministic-sum-of-floats%23new-answer', 'question_page');

          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          9














          Actually, the link you're referring to does not say that floating point arithmetic calculations are always deterministic. In fact, in one of the answers it's mentioned that addition is not associative (meaning (a + b) + c does not necessarily equal a + (b + c)), which is also said in this answer.



          If stream aggregation happens to process rows of each group in different order, this could explain the behaviour you observe.






          share|improve this answer























          • Associativity has no relation to determinism, so that bit is misleading.

            – Mooing Duck
            5 hours ago











          • Non-associativity of floating point addition leads to non-deterministic behaviour of the SQL Server aggregate function SUM(), would you agree @MooingDuck?

            – mustaccio
            4 hours ago











          • No? Integer Division is a clear counterexample. It is non-associative, but entirely deterministic. Likewise, floating point division should be non-associative and still deterministic. From that, we conclude it's reasonable for addition to be non-associative and still deterministic. That being said, if the order of additions isn't deterministic, then the result will likewise not be deterministic, so your first and last sentence are still correct regardless.

            – Mooing Duck
            1 hour ago















          9














          Actually, the link you're referring to does not say that floating point arithmetic calculations are always deterministic. In fact, in one of the answers it's mentioned that addition is not associative (meaning (a + b) + c does not necessarily equal a + (b + c)), which is also said in this answer.



          If stream aggregation happens to process rows of each group in different order, this could explain the behaviour you observe.






          share|improve this answer























          • Associativity has no relation to determinism, so that bit is misleading.

            – Mooing Duck
            5 hours ago











          • Non-associativity of floating point addition leads to non-deterministic behaviour of the SQL Server aggregate function SUM(), would you agree @MooingDuck?

            – mustaccio
            4 hours ago











          • No? Integer Division is a clear counterexample. It is non-associative, but entirely deterministic. Likewise, floating point division should be non-associative and still deterministic. From that, we conclude it's reasonable for addition to be non-associative and still deterministic. That being said, if the order of additions isn't deterministic, then the result will likewise not be deterministic, so your first and last sentence are still correct regardless.

            – Mooing Duck
            1 hour ago













          9












          9








          9







          Actually, the link you're referring to does not say that floating point arithmetic calculations are always deterministic. In fact, in one of the answers it's mentioned that addition is not associative (meaning (a + b) + c does not necessarily equal a + (b + c)), which is also said in this answer.



          If stream aggregation happens to process rows of each group in different order, this could explain the behaviour you observe.






          share|improve this answer













          Actually, the link you're referring to does not say that floating point arithmetic calculations are always deterministic. In fact, in one of the answers it's mentioned that addition is not associative (meaning (a + b) + c does not necessarily equal a + (b + c)), which is also said in this answer.



          If stream aggregation happens to process rows of each group in different order, this could explain the behaviour you observe.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 15 hours ago









          mustacciomustaccio

          9,98772238




          9,98772238












          • Associativity has no relation to determinism, so that bit is misleading.

            – Mooing Duck
            5 hours ago











          • Non-associativity of floating point addition leads to non-deterministic behaviour of the SQL Server aggregate function SUM(), would you agree @MooingDuck?

            – mustaccio
            4 hours ago











          • No? Integer Division is a clear counterexample. It is non-associative, but entirely deterministic. Likewise, floating point division should be non-associative and still deterministic. From that, we conclude it's reasonable for addition to be non-associative and still deterministic. That being said, if the order of additions isn't deterministic, then the result will likewise not be deterministic, so your first and last sentence are still correct regardless.

            – Mooing Duck
            1 hour ago

















          • Associativity has no relation to determinism, so that bit is misleading.

            – Mooing Duck
            5 hours ago











          • Non-associativity of floating point addition leads to non-deterministic behaviour of the SQL Server aggregate function SUM(), would you agree @MooingDuck?

            – mustaccio
            4 hours ago











          • No? Integer Division is a clear counterexample. It is non-associative, but entirely deterministic. Likewise, floating point division should be non-associative and still deterministic. From that, we conclude it's reasonable for addition to be non-associative and still deterministic. That being said, if the order of additions isn't deterministic, then the result will likewise not be deterministic, so your first and last sentence are still correct regardless.

            – Mooing Duck
            1 hour ago
















          Associativity has no relation to determinism, so that bit is misleading.

          – Mooing Duck
          5 hours ago





          Associativity has no relation to determinism, so that bit is misleading.

          – Mooing Duck
          5 hours ago













          Non-associativity of floating point addition leads to non-deterministic behaviour of the SQL Server aggregate function SUM(), would you agree @MooingDuck?

          – mustaccio
          4 hours ago





          Non-associativity of floating point addition leads to non-deterministic behaviour of the SQL Server aggregate function SUM(), would you agree @MooingDuck?

          – mustaccio
          4 hours ago













          No? Integer Division is a clear counterexample. It is non-associative, but entirely deterministic. Likewise, floating point division should be non-associative and still deterministic. From that, we conclude it's reasonable for addition to be non-associative and still deterministic. That being said, if the order of additions isn't deterministic, then the result will likewise not be deterministic, so your first and last sentence are still correct regardless.

          – Mooing Duck
          1 hour ago





          No? Integer Division is a clear counterexample. It is non-associative, but entirely deterministic. Likewise, floating point division should be non-associative and still deterministic. From that, we conclude it's reasonable for addition to be non-associative and still deterministic. That being said, if the order of additions isn't deterministic, then the result will likewise not be deterministic, so your first and last sentence are still correct regardless.

          – Mooing Duck
          1 hour ago

















          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%2f233513%2fnon-deterministic-sum-of-floats%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

          Styletc

          Tórshavn Kliima | Partnerstääden | Luke uk diar | Nawigatsjuun62° 1′ N, 6° 46′ W62° 1′ 0″ N, 6° 46′ 0″ WWMOTórshavn