Sql-server – Non-deterministic sum of floats

floating pointsql server

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?

Best 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 -- 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.

Addition is always deterministic: you put in the same two floats, you get the same float out. But adding floats together in a different order can give a different result.

Related Question