Subject Re: [firebird-support] Re: Uneven results from Round(x, 3) function
Author Svein Erling Tysvær
Dmitry has (of course) given you the correct answer.

What you may overlook, is that DOUBLE PRECISION is a floating point number, and floating point numbers are often approximate and not exact. DOUBLE PRECISION is exact to 15 digits, after that random values may occur. NUMERIC, on the other hand is exact. Generally speaking, people are advised to use DOUBLE PRECISION for things you measure (e.g. meters) and NUMERIC for things you count. Don't compare for equality between floating point numbers, but ranges (e.g. COALESCE(TABLE1.QTY, 0.285) BETWEEN 0.284999999999 and 0.285000000001, the simpler COALESCE(TABLE1.QTY, 0.285) = 0.285 can return false )

>In Firebird's documentation about ROUND function, there is a an example with 
>"ROUND(123.654, 1) returns 123.700", showing that 5 is rounded to the upper digit as standard rounding works not as Banker's rounding.

No, bankers rounding only comes into effect when things are exactly 5, not when they're 54.


2016-06-08 11:01 GMT+02:00 Dmitry Yemanov dimitr@... [firebird-support] <>:
08.06.2016 11:36, m.djorov@... wrote:
> First  I thought it's because of the results' type after the different
> operations, but even in cases when the result before ROUND is a DOUBLE
> PRECISION (according IBExpert) the round gives different results if the
> NULL in the COALESCE function comes from a table's field or if I write
> it manually.

COALESCE derives the resulting datatype based on input arguments:

   - COALESCE(NULL, 0.285) returns numeric
   - COALESCE(TABLE1.QTY, 0.285) returns double precision

In the second case, 0.285 gets implicitly converted to double precision
and may cause insignificant digits appearing in intermediate calculations:

coalesce(TABLE1.QTY, 0.285) - coalesce(null, 0.285)


coalesce(col, 0.285) * 12.5 - coalesce(null, 0.285) * 12.5


This difference obviously affects the ROUND results.


Posted by: Dmitry Yemanov <dimitr@...>


Visit and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at


Yahoo Groups Links

<*> To visit your group on the web, go to:

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    (Yahoo! ID required)

<*> To change settings via email:

<*> To unsubscribe from this group, send an email to:

<*> Your use of Yahoo Groups is subject to: