Decimal Rounding Differs Between Versions

Question

Post reply

Decimal Rounding Differs Between Versions

jonathanmarenus

Mr or Mrs. 500

Points: 508
More actions
September 16, 2022 at 8:40 pm

#4088654

I recently discovered that the query below returns a different result between SQL 2014 and 2016 (and higher). Was there a known change to this behavior, or does this seem like a bug.
select 2.0297367828042510/cast(65 AS FLOAT)
except
select 2.029736782804251/cast(65 AS FLOAT)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 2

even on the same version but with different server specs it can return different results - I would not consider it a bug but rather a consequence of using float (or real)

kaj SSCommitted Points: 1605 More actions · Answer 3

frederico_fonseca wrote:

even on the same version but with different server specs it can return different results - I would not consider it a bug but rather a consequence of using float (or real)

I'm guessing you mean different CPU/FPU hardware or maybe also different BIOS versions etc.?

jonathanmarenus Mr or Mrs. 500 Points: 508 More actions · Answer 4

I'm not sure how server specs would translate to different behavior in the query engine. I did actually verify that the problem is version- specific. And a calculation with a float does not change, given the same values. Math is math.

Ken McKelvey SSCoach Points: 18843 More actions · Answer 5

Float is an inexact data type so should probably not be used if you want accuracy.

Microsoft changed the inplementation of float in SQL2016. See Converting float and real data in the follwoing:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15

jonathanmarenus Mr or Mrs. 500 Points: 508 More actions · Answer 6

Thank you Ken. This is exactly what I was trying to confirm - Microsoft changing the functionality of float.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 7

jonathanmarenus wrote:

I'm not sure how server specs would translate to different behavior in the query engine. I did actually verify that the problem is version- specific. And a calculation with a float does not change, given the same values. Math is math.

I had a case on my current company - we had a VM (vmware) with SQL 2016 (4 cores 16GB ram) - moved to a physical box (16 cores 256 GB ram) with same SQL version (including patch) and when doing the initial runs to ensure the move didn't have any negative impact this difference did pop up - some compares of floats were giving different results from both servers.

in the end they did what I had been telling them long ago - use decimal instead of float - and everything worked fine.

jonathanmarenus Mr or Mrs. 500 Points: 508 More actions · Answer 8

The problem I identified was actually found in legacy code. I have always used decimal, rather than float. Therefore, my knowledge of float is fairly limited. I just thought it would be odd to have the same formula and values run with different results, unless there was a difference in the product itself (SQL Server). So thank you for your example.