Subject Re: Uneven results from Round(x, 3) function
Author Dmitry Yemanov
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.