[SOLVED] Firebird 3.0.4 Math Problem
lazarus / 16-Databases

----------------------------------------------------------------------
patyi · 2019-02-02 18:15

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

----------------------------------------------------------------------
zeljko · 2019-02-02 18:20

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

----------------------------------------------------------------------
zeljko · 2019-02-02 18:22

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

----------------------------------------------------------------------
patyi · 2019-02-02 18:35

In my opinion, Firebird should also know if it is a floating point
result!

Thanks Zeljko for the quick response !

----------------------------------------------------------------------
patyi · 2019-02-02 18:54

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 !

----------------------------------------------------------------------
patyi · 2019-02-02 19:17

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

----------------------------------------------------------------------
sash · 2019-02-02 19:21

Quote from: patyi on February 02, 2019, 07:54:21 pminvolves an exact
numeric operand
Clearly this is not your case: you have integers.
Exact numeric are defined like NUMERIC(10, 4).

----------------------------------------------------------------------
patyi · 2019-02-02 19:29

So my modified test query should be :

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

----------------------------------------------------------------------
patyi · 2019-02-03 09:22

See also Firebird FAQ : http://www.firebirdfaq.org/faq219/

----------------------------------------------------------------------
zeljko · 2019-02-09 15:49

Quote from: patyi on February 02, 2019, 08:17:38 pmI 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

----------------------------------------------------------------------
sash · 2019-02-09 22:22

Quote from: zeljko on February 09, 2019, 04:49:45 pmThat 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.