Subject Uneven results from Round(x, 3) function
Author
In this scenario:
CREATE DOMAIN DOM_QTY DOUBLE PRECISION;

CREATE TABLE TABLE1(ID INTEGER, QTY DOM_QTY);

INSERT INTO TABLE1 VALUES (1, NULL);

SELECT
    12.5 * 0.285,
    ROUND(12.5 * 0.285, 3),

    COALESCE(NULL, 0.285),
    COALESCE(TABLE1.QTY, 0.285),

    COALESCE(NULL      , 0.285) * 12.5,
    COALESCE(TABLE1.QTY, 0.285) * 12.5,

    ROUND(COALESCE(NULL      , 0.285)  * 12.5, 3),
    ROUND(COALESCE(TABLE1.QTY, 0.285)  * 12.5, 3),

    CAST(COALESCE(NULL      , 0.285) AS DOUBLE PRECISION) * 12.5,
    CAST(COALESCE(TABLE1.QTY, 0.285) AS DOUBLE PRECISION) * 12.5,

    ROUND(CAST(COALESCE(NULL      , 0.285) AS DOUBLE PRECISION) * 12.5, 3),
    ROUND(CAST(COALESCE(TABLE1.QTY, 0.285) AS DOUBLE PRECISION) * 12.5, 3),

    CAST((COALESCE(NULL      , 0.285) * 12.5) AS DOUBLE PRECISION),
    CAST((COALESCE(TABLE1.QTY, 0.285) * 12.5) AS DOUBLE PRECISION),

    ROUND(CAST((COALESCE(NULL      , 0.285) * 12.5) AS DOUBLE PRECISION), 3),
    ROUND(CAST((COALESCE(TABLE1.QTY, 0.285) * 12.5) AS DOUBLE PRECISION), 3),

    CAST(0.285 AS DOUBLE PRECISION),
    CAST(0.285 AS DOUBLE PRECISION) * 12.5,

    ROUND(CAST(0.285 AS DOUBLE PRECISION), 3),
    ROUND(CAST(0.285 AS DOUBLE PRECISION) * 12.5, 3)
FROM
    TABLE1

I'm getting different results from the ROUND function for the value 3.5625 : 3.562 and 3.563.


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.


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.


Here is my original query in which I found the problem


SELECT
    ROUND(CAST((0.285 * 12.5) AS DOUBLE PRECISION), 3) AS QTY,
    ROUND(CAST(COALESCE(SC.MIN_QTY, 0.285) * 12.5 AS DOUBLE PRECISION), 3) AS MIN_QTY,
    ROUND(CAST(COALESCE(SC.MAX_QTY, 0.285) * 12.5 AS DOUBLE PRECISION),3) AS MAX_QTY
FROM
    SC
WHERE
    SC.ID = 10491


The SC' fields MIN_QTY and MAX_QTY are NULL.


The results are 3.563, 3.562 ,3.562, all with type DOUBLE PRECISION, and in the app where I'm using it, the comaparisson of the values gives error.


Am I doing something wrong or it's a bug and need to be reported?