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

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开发者_JS百科.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

---

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)

---

The error is caused because the computer CANNOT represent the value 0.01 in floating point both in single and double precision. This value is rounded to the nearest representable value both in float and in double. So in both cases it's not 0.01, but only displayed to you as 0.01 in the real case (I don't know how does the ToString algorithm for floating point work, so can't tell you why it is converted to 0.01 in one case and 0.00999999977648258 in another).

The only thing I can tell you for sure - in the real case it was rounded to a representable value ABOVE 0.01 and in the double case it was rounded to a representable value BELOW 0.01. Therefore validation failed in the double precision case.

To overcome this problem you can change your validation test to be "smaller than 0.01 - epsilon" for some very small epsilon.