Unexpected results with float variable math

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Unexpected results with float variable math

Author

Topic

EMCascade
Starting Member

2 Posts

Posted - 2013-08-16 : 11:41:03

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."
[url]http://www.w3schools.com/sql/sql_datatypes.asp[/url]

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)?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-16 : 11:58:18

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-16 : 12:00:49

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

EMCascade
Starting Member

2 Posts

Posted - 2013-08-16 : 12:16:29

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!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-16 : 13:49:47

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources