# Non-deterministic sum of floats

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?

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 -- which SQL Server is usually free to do; if there is no `ORDER BY` in the appropriate clause, then the optimizer will choose whatever scan or seek or other query operator will be fastest, regardless of what order that performs the additions in -- then this could explain the behaviour you observe.
• Non-associativity of floating point addition leads to non-deterministic behaviour of the SQL Server aggregate function `SUM()`, would you agree @MooingDuck? Mar 30, 2019 at 1:39
• Integer division is non-associative and deterministic. Therefore, arithmetic operations associativity is not related to determinism. Therefore any non-associativity of `SUM()` must be irrelevant toward it's determinism. I agree that `SUM` appears to be non deterministic, but you should remove mentions of associativity, since that's unrelated. Mar 30, 2019 at 22:20