Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Rounding error [CORE6232] #6476

Closed
firebird-automations opened this issue Jan 17, 2020 · 9 comments
Closed

Rounding error [CORE6232] #6476

firebird-automations opened this issue Jan 17, 2020 · 9 comments
Labels
affect-version: 3.0.5 component: api / client library priority: critical resolution: wontfix type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: at (artur_sh)

Attachments:
round-using-floor-plus-0_5.7z

In dialect 1of FB3 I've created two samples functions test_round1 and test_round2. They give two DIFFERENT results 654.08 i 654.07. Why? Arithmetically results should be the same 654.08.
I know that dialect1 have problems with precision and I should migrate to dialect 3, but in the same dialect I should receive the same results (even rounded incorrectly).

set term ^ ;
create or alter function test_round1
returns double precision
as
declare variable x numeric(15,2);
declare variable y numeric(15,2);
begin
x= 68.85*9.50;
y = 1.0;
return round(x*y, 2);
end^

create or alter function test_round2
returns double precision
as
declare variable x numeric(15,2);
declare variable y numeric(15,2);
begin
x= 68.85;
y = 1.0*9.50;
return round(x*y, 2);
end^
set term ; ^
@firebird-automations
Copy link
Collaborator Author

Modified by: at (artur_sh)

description: In dialect 1of FB3 I've created two samples functions test_round1 and test_round2. They give two DIFFERENT results 654.08 i 654.07. Why? Arithmetically results should be the same 654.08.
I know that dialect1 have problems with precision and I should migrate to dialect 3, but in then same dialect I should recived same results (even rounded incorrectly).

set term ^ ;
create or alter function test_round1
returns double precision
as
declare variable x numeric(15,2);
declare variable y numeric(15,2);
begin
x= 68.85*9.50;
y = 1.0;
return round(x*y, 2);
end^

create or alter function test_round2
returns double precision
as
declare variable x numeric(15,2);
declare variable y numeric(15,2);
begin
x= 68.85;
y = 1.0*9.50;
return shudf_roundfloat(x*y, 2);
end^
set term ; ^

=>

In dialect 1of FB3 I've created two samples functions test_round1 and test_round2. They give two DIFFERENT results 654.08 i 654.07. Why? Arithmetically results should be the same 654.08.
I know that dialect1 have problems with precision and I should migrate to dialect 3, but in the same dialect I should receive the same results (even rounded incorrectly).

set term ^ ;
create or alter function test_round1
returns double precision
as
declare variable x numeric(15,2);
declare variable y numeric(15,2);
begin
x= 68.85*9.50;
y = 1.0;
return round(x*y, 2);
end^

create or alter function test_round2
returns double precision
as
declare variable x numeric(15,2);
declare variable y numeric(15,2);
begin
x= 68.85;
y = 1.0*9.50;
return round(x*y, 2);
end^
set term ; ^

environment: Win7 => Win7

summary: rounding error => Rounding error

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

This is more a support question that should be posted to the firebird-support list. In any case, in dialect 1, calculations with NUMERIC(15,x) will use double precision, which comes with its own rounding issues, and will be susceptible to minor rounding differences depending on the order of evaluation.

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Having different results when performing operations with FP values in different manner is normal case, that happens due to specific format of that values used in CPUs. That's not a bug. The problem is kind of solved in FB4 (you can use decimal float values) but you will anyway have to switch to dialect 3.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> I've created two samples functions test_round1 and test_round2.

Did you consider another kind of func which does not use ROUND() at all:

create or alter function alter_round( x numeric(15,4) ) returns double precision as
begin
return floor( x * 100 + .5) / 100.00;
end

-- ?
(though, i can`t understand why you have to return double precision and not numeric type).

Script with some "interesting" numbers which usually cause problems with round() function and results can be seen in attached .7z

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: round-using-floor-plus-0_5.7z [ 13415 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: at (artur_sh)

Pavel has written: though, i can`t understand why you have to return double precision and not numeric type.
I think that in Dialect 1 number with scale greater than zero is internally stored as double precision, so imho there is no difference between e.g. numeric(15,2), numeric(15,4), double precision and so on.
Am I wrong?

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> in Dialect 1 number with scale greater than zero is internally stored as double precision

Yes, but if your target is migfrating to dialect 3 then why you want to keep double precision as type that will be in use ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 3.0.5 component: api / client library priority: critical resolution: wontfix type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations