08.06.2016 11:36, m.djo...@yahoo.com 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