SQL Real vs Float

Question

Let's say I have the following 2 queries:

select sum(cast(2666 as float)) * cast(.3 as float)
select sum(cast(2666 as real)) * cast(.3 as real)

The 1st query returns: 799.8
The 2nd query returns: 799.800031781197

Why does the 2nd query not return the same thing as the 1st?

Answer 1

Binary floating point types (like real and float) cannot exactly represent decimal numbers. In particular it is not possible to exactly store 0.3 as a binary floating point number. Instead a number very close to 0.3 is stored. This is called a representation error.

The size of the error is different for real and float because they have different precision.

If you want to store decimal numbers more accurately, consider using decimal or numeric. But note that even though these types can accurately store decimal values up to a certain number of digits, calculations can still produce numbers that cannot be represented exactly. For example the result of 0.1 / 0.3 can not be stored exactly in a decimal even though both 0.1 and 0.3 can. In this case the result will be rounded to the nearest value that can be stored in the type (e.g. 0.333333333 depending on the precision).