SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Unexpected results with float variable math
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

EMCascade
Starting Member

2 Posts

Posted - 08/16/2013 :  11:41:03  Show Profile  Reply with Quote
When I use a float variable to do some math, SQL is not seeing the result as equal to the same math if I were to hardcode the number instead. Compare the results from the two queries below to see what I mean.

DECLARE @recordnumber float
SET @recordnumber = 4060.012
SELECT
@recordnumber AS SelectedRecord
,(@recordnumber + .001) AS MathNextRecord
,4060.013 AS HardCodeNextRecord
,CASE
WHEN (@recordnumber + .001) = 4060.013
THEN 'Yes'
ELSE 'No'
END AS Equal


This returns the following:
SelectedRecord: 4060.012
MathNextRecord: 4060.013
HardCodeNextRecord: 4060.013
Equal: No (???)

If I adjust the code to use the same math, but instead hardcode '4060.012' instead of using the variable, it works as expected.

	SELECT
4060.012 AS SelectedRecord
,(4060.012 + .001) AS MathNextRecord
,4060.013 AS HardCodeNextRecord
,CASE
WHEN (4060.012 + .001) = 4060.013
THEN 'Yes'
ELSE 'No'
END AS Equal


This returns the following:
SelectedRecord: 4060.012
MathNextRecord: 4060.013
HardCodeNextRecord: 4060.013
Equal: Yes

What in the world is going on here? I need to be able to use variables to do calculations, but it's not very helpful when SQL doesn't see the resulting number as equal to what you would expect.

Any explanation on why this might be happening and/or how to fix this would be very helpful!

UPDATE:
I found out that a 4-byte float variable does work, but the default 8-byte float variable doesn't work.

DECLARE @recordnumber float(24)
Works
DECLARE @recordnumber float
Doesn't work

"Float(n): Floating precision number data from -1.79E + 308 to 1.79E + 308.
The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53."
http://www.w3schools.com/sql/sql_datatypes.asp

I still would like some insight to this strange behavior. Besides guess-and-check, is there any way to know when to use a special parameter for my float variable(s)?

Edited by - EMCascade on 08/16/2013 11:53:28

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/16/2013 :  11:58:18  Show Profile  Reply with Quote
I assume it is because Float and Real are approximate-number data types. If you need to do comparisons you should be using another data type like Decimal/Numeric.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 08/16/2013 :  12:00:49  Show Profile  Reply with Quote
FLOAT uses approximate represenation of the numbers. If you want to represent a number exactly, use decimal.
DECLARE @recordnumber DECIMAL(19,3)
SET @recordnumber = 4060.012
SELECT
@recordnumber AS SelectedRecord
,(@recordnumber + .001) AS MathNextRecord
,4060.013 AS HardCodeNextRecord
,CASE
WHEN (@recordnumber + .001) = 4060.013
THEN 'Yes'
ELSE 'No'
END AS Equal
Go to Top of Page

EMCascade
Starting Member

2 Posts

Posted - 08/16/2013 :  12:16:29  Show Profile  Reply with Quote
I gotcha, this is very helpful. I'll be sure to use DECIMAL data types instead of FLOATs going forward for any comparisons I need to do.

Thanks!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 08/16/2013 :  13:49:47  Show Profile  Reply with Quote
There are caveats and gotchas on both sides - whether you use decimals or floats. The key is to be aware of the limitions and use them appropriately. Info in this thread may be useful. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187543

Usually in numerical computations, you try to avoid testing for logical equality between two (non-integer) numbers. You define an acceptable epsilon and then compare if the difference is greater than that epsilon. So for the example you posted, one might do this:
DECLARE @tolerance FLOAT;
SET @tolerance = 1.0e-8;

DECLARE @recordnumber FLOAT
SET @recordnumber = 4060.012
SELECT  @recordnumber AS SelectedRecord ,
        ( @recordnumber + .001 ) AS MathNextRecord ,
        4060.013 AS HardCodeNextRecord ,
        CASE WHEN ( @recordnumber + .001 ) = 4060.013 THEN 'Yes'
             ELSE 'No'
        END AS Equal,
        CASE WHEN ABS(( @recordnumber + .001 ) - 4060.013) < @tolerance THEN 'Yes'
			ELSE 'No'
		END AS WithinTolerance



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000