FP precision error on converting XML -> Table & processing it

Question

In SQL Server 2008, I have the value "0.01" in an XML attribute. Using OPENXML, I shred the XML into a temp table. If the applicable column is of type real (single precision), it comes out as 0.01 in the table. Good. However, if the precision is float (double precision), it comes out as 0.00999999977648258. This makes no sense. Why is it doing this?

My next question is that regardless of how the value is represented in the temp table, when I run an aggregate function on it, it always comes back as 0.00999999977648258. This is causing validation errors: the procedure is reporting the input is too small (< 0.01), which is not true.

Any ideas why these rounding errors are happening and how to overcome them?

Already tried: make the column a varchar.

EDIT2:

Based on answers, I understand the problem is due to the fact that IEEE numbers cannot represent 0.01 exactly. Therefor my next question:

"WHERE {computed} < 0.01", why is that 0.01 not also being rounded here? If it were, the equation would eval as expected (i.e. 0.00999999977648258 is not < 0.00999999977648258)

EDIT: Sample code shown

This code will produce the error. Change the indicated float to real & the error "disappears". At least so far as the temp table goes.

DECLARE @XMLText varchar(max)

SET @XMLText = 
'<query prodType="1">
  <param type="1" lowMin="10" hiMax="300">
    <item low="18" hi="20" mode="1" weight="1" />
    <item low="220" hi="220" mode="0" weight="1" />
  </param>
  <param type="2" lowMin="4" hiMax="6">
    <item low="5" hi="5" mode="1" weight="1" />
    <item low="6" hi="6" mode="0" weight="0.01" />
  </param>
  <param type="3" lowMin="0" hiMax="300">
    <item low="34" hi="34" mode="1" weight="0.75" />
    <item low="40" hi="60" mode="1" weight="0.25" />
  </param>
</query>'

DECLARE @hxml int, @sp INT, @StartXCount int

EXEC sp_xml_preparedocument @hxml OUTPUT, @XMLText

IF @sp != 0 BEGIN
    SET @Result = '0'
    RETURN
END

DECLARE @t table (
    LowMin         real,
    HiMax          real,
    ParamTypeID    int,
    ParamWeight    float, -- real <<<
    Low            real,
    Hi             real,
    Mode           tinyint          
)

INSERT INTO @t
SELECT      *
FROM        OPENXML (@hxml, '/query/param/item', 2)
WITH        (
                LowMin       real     '../@lowMin',
                HiMax        real     '../@hiMax',
                ParamTypeID  int      '../@type',
                ParamWeight  real     '@weight',
                Low          real     '@low',
                Hi           real     '@hi',
                Mode         tinyint  '@mode'
            )

SELECT * FROM @t

Answer 1

0.01 can't be stored exactly in an IEEE type, since it's not representable with a fraction with a power of 2 in denominator.

However, what I can reproduce is the opposite of what you are saying:

SELECT  CAST(0.01 AS FLOAT) AS value
FOR XML PATH(''), TYPE

<value>1.000000000000000e-002</value>

 

SELECT  CAST(0.01 AS REAL) AS value
FOR XML PATH(''), TYPE

<value>9.9999998e-003</value>

Could you please post your exact query?

Update:

I get the same results with your code: 0,01 when ParamWeight is FLOAT, 0,00999999977648258 when it's REAL.

Update 2:

IEEE types are stored as a sign, mantissa and a significand. For a 32-bit value, mantissa is the binary logarithm of the greatest power of 2 (least than the value), and a significand is a 23-bit binary fraction (a number from 1 to 2, the leading 1 is not stored.).

In your case it's -7 for the mantissa (2^-7 = 1/128 = 0,0078125), and 1.01000111101011100001010 for the significand (= 1 + 2348810 / 8388608 = 1,2799999713897705078125).

The resulting number is a product of these numbers which is close to 0.01 but still not close enough to avoid errors in 15'th digit (which precision SQL Server considers important)