Commented by: @hvlad

eps_double = 1e-14 was chosen to be as much as possible near the lowest number which can be represented as double to avoid wrong evaluations because of too big addendum.
Does you tried your example with second UT_ROUND_11's argument more than 2 ?
Can you explain why 1e-11 but not 1e-10 or 5.987789e-11 ?

Commented by: Ruben Marti (monmariola)

My English is very poor.

In order to calculate the value of epsilon I tested with the procedure EPSILON and obtain a result of 1.776E-15.

It is certain that 1e-14 is a correct value to solve the problem of the numbers stored in floating point, but like can be verified in the initial example,

is not valid for numbers that are been results from calculations in floating point.

As you comment, it is easy to modify the code and to divide between 100 to calculate to 1 decimal, or between 10000 to calculate round to 3 decimal, or

between 100000 to calculate round to 4 decimal. The certain thing, is that in the tests that I have made with a value of 1e-11 it has been sufficient to

give back a correct value. In some cases with 1e-12 or 1e-13 it has been sufficient, but the value smaller than always has worked to me has been 1e-11.
Also I have looked for in google other programs the solutions for round and I have been all type of cases, from 1e-11 to 1e-14.

Where is used eps_double? Only in the cast of floating point to integer or numeric. eps_double is not used in the operations of floating point.

The problem exists and is demonstrable easily. It is really stranger who nobody has been noticed until now.

I have meditated much on if it is correct to make the change. For new databases I am not going to have problems, but in databases in production it can have

serious problems when such not obtaining the same results according to uses a motor or another one. For example, the sum of the lines of an invoice would

not have the same results, which would force to me to make internal adjustments.

I would like that people with more experience discussed on the convenience of changing the value of eps_double.

Ruben Marti.

----------
CREATE PROCEDURE EPSILON
RETURNS(
EPSILON DOUBLE PRECISION)
AS
DECLARE VARIABLE E DOUBLE PRECISION;
DECLARE VARIABLE I INTEGER;
BEGIN
E = 0.0;
EPSILON = 1.0;
I = 0;
while (E <> 1.0 and i < 50) do
begin
EPSILON = EPSILON / 2;
E = 1 + EPSILON;
I = I + 1;
end
EPSILON = EPSILON * 2;
SUSPEND;
END;

Commented by: Ruben Marti (monmariola)

I have investigated a little more on the subject.

Microsoft for foxpro recommends to make a conversion to string and later to number to avoid the problem of round: http://support.microsoft.com/kb/157954/en-us

Also I have looked for in the sources of the library of TurboC of Borland to understand the problem and that solution uses.

The problem is very complex to solve. The value of epsilon to store value 1 is approximately of 2e-15. But this value increases as we have more digits to the left of the point decimal. Approximately for the value of 10 is 2e-14, for 100 is 2e-13, etc. It's possible to be verified with this procedure:

CREATE PROCEDURE EPSILON(
VALOR DOUBLE PRECISION)
RETURNS(
EPSILON DOUBLE PRECISION)
AS
DECLARE VARIABLE E DOUBLE PRECISION;
DECLARE VARIABLE I INTEGER;
BEGIN
E = 0.0;
EPSILON = 1;
I = 0;
while (E <> VALOR and i < 50) do
begin
EPSILON = EPSILON / 2;
E = VALOR + EPSILON;
I = I + 1;
end
EPSILON = EPSILON * 2;
SUSPEND;
END;

Therefore, the function that correctly round in all the cases values of up to 15 digits to the left of the point decimal is in Turbo C:

double myround(valor , decimales)
double valor;
int decimales;
{
double potencia , epsilon , temp;
int digitos , signo;

/* Sign */
if (valor < 0) {
signo = -1;
valor = -valor;
}else {
signo = 1;
}

/* To calculate the digits to the left of the point decimal */
temp = valor;
digitos = 0;
while (temp >= 10.0) {
temp /= 10.0;
++digitos;
}

/* To calculate Epsilon according to the digits to the left of the point decimal */
epsilon = 2e-15 * pow10(digitos);

/* To calculate power */
potencia = pow10(decimales);

/* Round number */
valor = floor((valor + epsilon) * potencia + 0.5) / potencia;

/* Adjust sign */
if (signo < 0) {
valor = -valor;
}

return(valor);
}

I have made hundreds of tests with values and functions and the result is always correct compared with a method similar to FoxPro of Microsoft who I used with my programs in C.

A procedure with it himself code is:

CREATE PROCEDURE UT_REDONDEA (
NUMERO DOUBLE PRECISION,
DECIMALES SMALLINT)
RETURNS (
VALOR DOUBLE PRECISION)
AS
declare variable POTENCIA double precision;
declare variable EPSILON double precision;
declare variable TEMP double precision;
declare variable SIGNO smallint;
declare variable I smallint;
BEGIN
/* Sign */
if (NUMERO < 0) then
begin
NUMERO = -NUMERO;
SIGNO = -1;
end
else
SIGNO = 1;
/* To calculate Epsilon according to the digits to the left of the point decimal */
EPSILON = 2E-15;
TEMP = NUMERO;
while (TEMP >= 10.0) do
begin
TEMP = TEMP / 10.0;
EPSILON = EPSILON * 10.0;
end
/* To calculate power */
POTENCIA = 1;
if (DECIMALES >= 0) then
I = DECIMALES;
else
I = -DECIMALES;
while (I > 0) do
begin
POTENCIA = POTENCIA * 10.0;
I = I - 1;
end
/* Round number */
if (DECIMALES >= 0) then
begin
VALOR = cast((NUMERO + EPSILON) * POTENCIA as numeric(18,0));
VALOR = VALOR / POTENCIA;
end
else
begin
VALOR = cast((NUMERO + EPSILON) / POTENCIA as numeric(18,0));
VALOR = VALOR * POTENCIA;
end
/* Adjust sign */
if (SIGNO < 0) then
VALOR = -VALOR;
SUSPEND;
END

However, it can have some indirect effect to modify the rounds (cast to numeric)?

Just came across this in 3.10. Did not personally test with 4 apparently same results
Round() is broken for FLOAT and DOUBLE PRECISION.

select round(cast(4.015 as DOUBLE PRECISION) , 2) from RDB$DATABASE;
ROUND
=============
4.010000000000000

Expected: 4.02

select round(cast(4.015 as FLOAT) , 2) from RDB$DATABASE;
ROUND
=======
4.0100002

Expected: 4.02

4.015 can't be exactly represented as double precision.
It is represented as 4.0149999999999997.
This number is correctly rounded to the 4.01
The same for float representation.

You may check it with many tools, for example.
https://www.binaryconvert.com/result_double.html?decimal=052046048049053

Thanks...Put notes in #7303 because I'm not convinced this old bug is exactly what my issue was.

Thank you for understanding.

Actually, I see no bug here and consider to close this old ticket.