Lazarus

Programming => Databases => Topic started by: patyi on February 02, 2019, 07:15:45 pm

Title: [SOLVED] Firebird 3.0.4 Math Problem
Post by: patyi on February 02, 2019, 07:15:45 pm
Firebird 3.0.4 Math Problem - try these three queries:

1. SELECT 100 * (1-5.3 / 100) FROM rdb$database
     Result: 100 which is not right

2. SELECT 100 * (1-5.3 / 100.0) FROM rdb$database
     Result: 95 which is closer but not correct again

3. SELECT 100 * (1-5.3 / 100.00) FROM rdb$database
     Result: 94.7 which is finally right !

The conclusion is that dividing with 100 is only correct if I write with two decimals (100.00) !
Is this normal or can it be considered a bug ?

I tried the same with MySQL and all three queries give the right result (94.7), I would be interested in what PostreSQL would do, I would like to know what the result !

Thank you!

XUbuntu 18.04 / 64, Lazarus 1.8.5 / 64, FPC 3.0.5 / 64, Firebird 3.0.4 / 64
Title: Re: Firebird 3.0.4 Math Problem
Post by: zeljko on February 02, 2019, 07:20:20 pm
Yes you must write 100.00, postgresql works in same way.
eg. in postgres SELECT 100/51; returns 1 but SELECT 100.00/51.00; returns 1.9607843137254902
Title: Re: Firebird 3.0.4 Math Problem
Post by: zeljko on February 02, 2019, 07:22:53 pm
oh, you're right, now I just tested your first example under postgres:
SELECT 100 * (1-5.3 / 100) ; returns 94.700000000 , so indeed something is different with firebird
Title: Re: Firebird 3.0.4 Math Problem
Post by: patyi on February 02, 2019, 07:35:19 pm
In my opinion, Firebird should also know if it is a floating point result!

Thanks Zeljko for the quick response !
Title: Re: Firebird 3.0.4 Math Problem
Post by: patyi on February 02, 2019, 07:54:21 pm
From "The Firebird Book" :

"Arithmetic Mixing Fixed and Floating-Point Types

When a dyadic operation (addition, subtraction, multiplication, division) involves an
exact numeric operand and a floating-point operand, the result will be a DOUBLE PRECISION type."


My results are not in accordance with this !
Title: Re: Firebird 3.0.4 Math Problem
Post by: patyi on February 02, 2019, 08:17:38 pm
I found a correct answer to the problem:

https://stackoverflow.com/questions/26128353/why-does-firebird-truncate-decimal-places-when-dividing

So my modified test query should be :

SELECT 100 * (1-5.3 / 100E0) FROM rdb$database
Result: 94.700000
Title: Re: Firebird 3.0.4 Math Problem
Post by: sash on February 02, 2019, 08:21:03 pm
involves an exact numeric operand

Clearly this is not your case: you have integers.
Exact numeric are defined like NUMERIC(10, 4).
Title: Re: [SOLVED] Firebird 3.0.4 Math Problem
Post by: patyi on February 02, 2019, 08:29:04 pm
So my modified test query should be :

SELECT 100 * (1-5.3 / 100E0) FROM rdb$database
Result: 94.700000
Title: Re: [SOLVED] Firebird 3.0.4 Math Problem
Post by: patyi on February 03, 2019, 10:22:10 am
See also Firebird FAQ : http://www.firebirdfaq.org/faq219/
Title: Re: Firebird 3.0.4 Math Problem
Post by: zeljko on February 09, 2019, 04:49:45 pm
I found a correct answer to the problem:

https://stackoverflow.com/questions/26128353/why-does-firebird-truncate-decimal-places-when-dividing

So my modified test query should be :

SELECT 100 * (1-5.3 / 100E0) FROM rdb$database
Result: 94.700000

That looks like pretty nasty bug / mis-feature when comparing FB with other RDBMS
Title: Re: [SOLVED] Firebird 3.0.4 Math Problem
Post by: sash on February 09, 2019, 11:22:47 pm
That looks like pretty nasty bug / mis-feature when comparing FB with other RDBMS

Looks pretty insignificant to me (especially considering real use cases of such calculations).
Operations with NULLs also have `issues`.

It's just like novice programmers complains about Pascal's / and div.
TinyPortal © 2005-2018