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
