Danger! Danger! Float value!

2018-07-28

I’m not saying that floating point numbers are the devil, and I’m not saying that the float data type should be avoided. But they should only be used when it’s entirely necessary and appropriate. The hidden danger of improper use can can become a ticking time bomb!

Most (if not all) dangers come from FLOATs representing tiny fractions, rather than a value of 0.

When and why to use a FLOAT

The floating point number is an approximation of a number, it’s true power and use is in fractions. A classic case for fractions in computing is when we divide 1 by three, then add the result to itself three times (three thirds making a whole).

The below example creates a value of 1 in the CTE. This value is stored in a temporary table, including dividing by three using both implicit and explicit type casting for comparison.

-- Drop the temp table if it exists
IF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL
    DROP TABLE #tmp_data;

-- Create a value of 1 (cast as a float)
-- Then divide it by 3, storing the result as DECIMAL and as FLOAT
WITH data_set AS (
SELECT CAST(1 AS FLOAT) AS [value]
)
SELECT [value],
       [value]/3                        AS [value_d3],
       CAST([value]/3 AS DECIMAL(10,5)) AS [value_DEC],
       CAST([value]/3 AS FLOAT)         AS [value_FL]
  INTO #tmp_data
  FROM data_set
;

-- View the resulting numbers
SELECT *
  FROM #tmp_data
;

value	value_d3	value_DEC	value_FL
1	0.3333333333333333	0.33333	0.3333333333333333

The results in that table will look largely consistent. The key difference between them will only come when we then want to multiply our thirds by three to get 1.

-- Multiply DECIMAL and FLOAT values by 3
SELECT [value_DEC] * 3 AS [value_DEC_MULT],
       [value_FL]  * 3 AS [value_FL_MULT]
  FROM #tmp_data
;

value_DEC_MULT	value_FL_MULT
0.99999	1

The result for a DECIMAL value isn’t quite what we might expect. The float gives us a nice tidy 1, but the decimal gives us a little less than 1. This is because the DBMS is able to see that the float is an approximation of a third. But the decimal value is a literal 0.33333.

So, use a FLOAT when we want a fraction to behave like a fraction. In all other cases, use an INTEGER or DECIMAL datatype.

It’s worth looking at these examples in SQL Fiddle, SQL Server and Oracle behave the same, but MySQL returns a different result. The DBMS doesn’t realise that it’s looking at a third, and multiplies the fraction leaving us with a small variance. In this instance (and many like it) we can get around the variance by CASTing the result:

Danger 1, FLOATs are aproximate

As useful as a FLOAT is for fractions and for results of mathematical equations, remember that a FLOAT is an approximation, and not an actual number! Floating point numbers handle precision by effectively dropping off the most significant digits (for a fraction) or the least significant bits (for a large number), by shifting the decimal point and storing a range of the number.

In most cases with a number over 30 digits long, the two least significant digits probably don’t carry much business meaning, but it’s a problem to be aware of (for example, if we are trying to get to Mars). With larger numbers, the addition (or subtraction) of a much smaller number can end up being ignored.

Take the following example, where a small value is added to a large value, which is also subtracted from the total. A large value added to 50 then subtract the large value should equal 50. But in the first example it returns 0:

                 --1234567890123456789012345678901234567890
DECLARE @a FLOAT = 500000000000000000000000000000000000
SELECT @a + 50 - @a -- Doesn't work as expected
GO

                 --1234567890123456789012345678901234567890
DECLARE @a FLOAT = 500000000000000000000000000000000000
SELECT @a - @a + 50 -- Works as expected

GO

                         --1234567890123456789012345678901234567890
DECLARE @b DECIMAL(38,2) = 500000000000000000000000000000000000
SELECT @b + 50 - @b -- Works as expected

GO
                         --1234567890123456789012345678901234567890
DECLARE @b DECIMAL(38,2) = 500000000000000000000000000000000000
SELECT @b - @b + 50 -- Works as expected

GO

Results:

SQL Fiddle – SQL Server: FLOAT Precision Problem Example

Danger 2, checking for a sum bigger than 0

There are stories of companies sending people bills which tell people that they are about to be cut off from a service for an unpaid bill, and that they must pay the outstanding balance of 0.00 immediately! Although the stories are often the stuff of legend, the cause is all too easy.

A common cause of this is that a FLOAT is used to store financial values, and for example a percentage discount or fee is applied somewhere. When that happens it’s easy to add/subtract numbers and end up with a tiny difference. This could be a non 0 value even though for all business reasons it should be 0.

The below example shows a regular bill of 10.99, and then a subsequent one where there was a discount of 1/3 applied (this is a simplified example of a bad way of doing things).

NORMAL BILL
------ ----
    Bill: 10.99
 Payment: 10.99

DISCOUNT BILL
-------- ----
    Bill: 10.99
 Payment:  7.33   -- Represented in the system as: 7.32666666666666 (two thirds)
Discount:  3.66   -- Represented in the system as: 3.66333333333333 (one third)

If we look at the amounts which people would see in bills below, the 7.33 and 3.66. We will get the 10.99 expected. However, when we use the floating point values, we get a result which should be 10.99 but isn’t quite (it’s off by an infinitely small decimal value).

DECIMAL NUMBERS
------- -------
   7.33
+  3.66
  -----
= 10.99

FLOAT NUMBERS
----- -------
   7.32666666666666
+  3.66333333333333
  -----------------
= 10.98999999999999

If a decimal value is used to store the results of the calculations then we lose the infinite recurring values.

This example demonstrates the problem and the solution:

Remember the classic maths/computing joke, 1 + 1 = 3 (for large values of 1).

Danger 3, division by not quite 0

This is how I had a report billing for just over 70 trillion GBP! Sadly for me, the customer didn’t actually owe me that much cash.

The danger here is when one divides a number by a small fraction, it results in a large multiplication. e.g. 1/0.000000001 = 1,000,000,000

If a value which should be 0, results in a FLOAT representing a small fraction, we get this error. This sort of multiplication can occur easily, and without warning.

Another common cause for getting very small fractions, is subtracting a value which is nearly a fraction, from a value which is about a fraction (DECIMAL and FLOAT). We can end up with a number which is small enough that it should be 0, but instead it’s slightly higher (or lower) than 0.

-- Create a value of 1 (cast as a float)
-- Then divide it by 3, storing the result as DECIMAL and as FLOAT
WITH data_set AS (
SELECT CAST(1 AS FLOAT) AS [value]
)
SELECT CAST([value]/3 AS DECIMAL(10,5)) AS [value_DECIMAL],
       CAST([value]/3 AS FLOAT)         AS [value_FLOAT]
  INTO tmp_data
  FROM data_set
;

-- View the results of combining data types
SELECT [value_DECIMAL],
       [value_FLOAT],
       [value_DECIMAL] - [value_DECIMAL] AS [result_DEC_sub_DEC],
       [value_FLOAT]   - [value_FLOAT]   AS [result_FL_sub_FL],
       [value_DECIMAL] - [value_FLOAT]   AS [result_DEC_sub_FL], -- Note that this should be 0, but it isn't
       [value_FLOAT]   - [value_DECIMAL] AS [result_FL_sub_DEC]  -- Note that this should be 0, but it isn't
  FROM tmp_data
;

-- Divide 1 by a fraction (created from combining data types)
SELECT 1 / ([value_FLOAT]  - [value_DECIMAL]) AS [result_FL_sub_DEC]
  FROM tmp_data
;

SQL Fiddle – SQL Server: division danger

Solution 1

Cast the result of the subtraction as DECIMAL:

-- View the results of combining data types
SELECT [value_DECIMAL],
       [value_FLOAT],
       CAST([value_DECIMAL] - [value_FLOAT]   AS DECIMAL(10,4)) AS [DEC_result_DEC_sub_FL], -- This now returns 0
       CAST([value_FLOAT]   - [value_DECIMAL] AS DECIMAL(10,4)) AS [DEC_result_FL_sub_DEC]  -- This now returns 0
  FROM tmp_data
;

-- Divide 1 by a fraction (this time cast as DECIMAL), and including the NULLIF to avoid divide by 0 errors
SELECT 1 / 
       NULLIF(CAST([value_FLOAT]   - [value_DECIMAL] AS DECIMAL(10,4)),
              0) AS [result_FL_sub_DEC]
  FROM tmp_data
;

SQL Fiddle – SQL Server: safe division by FLOATs

Solution 2

This is a little less elegant, but also saves any division by a value less than 1. Therefore we can’t end up with a multiplication.

-- View the results of combining data types
SELECT [value_DECIMAL],
       [value_FLOAT],
       [value_DECIMAL] - [value_FLOAT]   AS [result_DEC_sub_FL], -- Not 0, but a fraction
       [value_FLOAT]   - [value_DECIMAL] AS [result_FL_sub_DEC]  -- Not 0, but a fraction
  FROM tmp_data
;

-- Divide 1 by a fraction (this time cast as DECIMAL), and including the NULLIF to avoid divide by 0 errors
SELECT CASE WHEN ([value_FLOAT]   - [value_DECIMAL]) < 1
            THEN NULL
            ELSE 1 / ([value_FLOAT]   - [value_DECIMAL])
        END AS [safe_result]
  FROM tmp_data
;

SQL Fiddle – SQL Server: Safe division by FLOATs using CASE

Danger 4, division by 0 error

This is now less common, more recent versions of SQL server appear to have patched this issue. I haven’t been able to recreate it. The cause is often the same as Danger 3 above (a value which should be 0 but isn’t).

Commonly, to avoid divide by 0 errors, the value divided by is checked to see if it’s 0. If it is 0, the division is ignored. This is usually by setting the divisor to NULL (which causes a result of NULL), or using a WHERE condition to check for 0. The method is described in more detail here SQL SERVER – How to Fix Error 8134 Divide by Zero Error Encountered.

Just like checking for a value which is bigger than 0. There can be problems when checking if a value is 0!

-- Check for divisor of 0
SELECT CASE [value_to_divide_by]
         WHEN 0 THEN 0
         ELSE [value_to_divide]/[value_to_divide_by]
       END                                               AS [method_1_result],
       [value_to_divide]/NULLIF([value_to_divide_by], 0) AS [method_2_result]
  FROM [db].[schema].[table “” not found /]


;

This very rare problem comes up when one of the above methods decides that the number is not 0. So starts to perform the action. Then the computation decides that the number is 0 and as a result throws the error.

Solution

The simple solution (if one is faced with this problem) is to check for a very small number. Rather than checking for 0, check if the number is smaller than an arbitrary small number e.g.

SELECT CASE WHEN [value_to_divide_by] < 0.001 THEN 0 -- This keeps us safe from fractions which should be 0
         ELSE [value_to_divide]/[value_to_divide_by]
       END                                               AS [method_3_result]
  FROM [db].[schema].[table “” not found /]


;