Subject | Uneven results from Round(x, 3) function |
---|---|
Author | |
Post date | 2016-06-08T08:36:24Z |
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?