Non-deterministic aggregated result

Why is my query non-deterministic?

I have query that do the following:

select sum(float1*float2*coalesce(float3,1)) from table

When I run this query I get a deterministic result, but it seems not be the case when the query is run many times.

The last integer in the result differ between different runs.

All columns are (float,null)

Should a float truncation be the same for all runs?

Multiplying approximate values will multiply errors: sometimes they will cancel but usually they will exaggerate

float is also accurate to 15 significant figures at most: as you multiply and SUM you can quickly hit this and lose accuracy.

I guess that the changes in results will depend on how what order the data is accessed (changes with eg new rows, row overflow, page splits etc) so the series of calculations involved will change order each with their own rounding.

Edit: had a thought.

Each core/socket will do float calculations differently.

Try these to see if we can make this deterministic (note this isn't the same as "correct")

Add MAXDOP 1 to the query and see how many different results you get. I'm expecting no more then the number of cores or sockets. This reduces the number of permutations with parallelism

Then try processor affinity to always use the same core for all queries to force the query to run on one core/cpu

To fix it, use decimal or bigint.

StackExchange Database Administrators Q#4810, answer score: 5