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
INNER JOIN works as expected here.
This is inconvenient, because it means that a
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?