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 [email protected] [firebird-support] <[email protected]>: > 08.06.2016 11:36, [email protected] 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 <[email protected]> > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > 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 > > > >
