Subject Re: [firebird-support] Re: Uneven results from Round(x, 3) function
Author Svein Erling Tysvær
Post date 2016-06-09T09:51:40Z
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.

HTH,
Set

2016-06-08 11:01 GMT+02:00 Dmitry Yemanov dimitr@... [firebird-support] <firebird-support@yahoogroups.com>:
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)

                SUBTRACT
=======================
       0.000000000000000

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

                SUBTRACT
=======================
  -4.440892098500626e-16

This difference obviously affects the ROUND results.


Dmitry




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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org 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 http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/firebird-support/join
    (Yahoo! ID required)

<*> To change settings via email:
    firebird-support-digest@yahoogroups.com
    firebird-support-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    firebird-support-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/us/yahoo/utos/terms/