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