Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
Can you add sample data: I assume data is static too... and what about sum(float1), sum(float2) etc. – gbn Aug 19 '11 at 12:36

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
up vote 4 down vote

Floats, as others mentioned, are pretty much non-deterministic. There's not really anything you can do about that, unfortunately.

For example, if you throw in the number 5 into a table and then truncate it, you may end up with 4.000, since 5 may end up getting represented in memory as 4.9999.

If you try to work around that by Rounding, this is the same problem as 5.5 may actually get stored as 5.49999, which would round to 5.

You could try little tricks like adding 0.25 and then truncating, but I think that this is completely missing the point of FLOATS.

Ultimately, they are--on their own--pretty non-deterministic. This is, interestingly enough, a problem with the architecture and will spread across all use of floats within the operating system (meaning all languages and applications, including databases).

share|improve this answer
If I do exact the same calculation/sum on the same machine the results are equal when I use query analyzer. But when I use C# with hibernate the results differ. – Amir Rezaei Aug 20 '11 at 18:36
That doesn't surprise me at all. .Net has default values for all value-type variables. Also, as you pass data from a database to a C# object and then pass that object and value around, there's more and more variations that come into play, since you're doing more and more with the data type. Float is an approximation. The more assignments you make, the more approximate you have to presume that it is. – Richard Aug 20 '11 at 19:37
up vote 2 down vote

My first thought would be because float is an approximate numeric, making that a nondeterministic function.

Float is not an exact data type.

share|improve this answer
But why is result different when runs are very large, but I cant see the difference in query analyzer. – Amir Rezaei Aug 19 '11 at 12:14
float should be predictably bad with the same bad results time after time given identical hardware and environment. It isn't "nondeterministic" as such: just, er, crap – gbn Aug 19 '11 at 14:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.