Lazarus
Programming => Databases => Topic started 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
-
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
-
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
-
In my opinion, Firebird should also know if it is a floating point result!
Thanks Zeljko for the quick response !
-
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 !
-
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
-
involves an exact numeric operand
Clearly this is not your case: you have integers.
Exact numeric are defined like NUMERIC(10, 4).
-
So my modified test query should be :
SELECT 100 * (1-5.3 / 100E0) FROM rdb$database
Result: 94.700000
-
See also Firebird FAQ : http://www.firebirdfaq.org/faq219/
-
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
-
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.