### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: [SOLVED] Firebird 3.0.4 Math Problem  (Read 2304 times)

#### patyi

• Full Member
• Posts: 169
##### [SOLVED] Firebird 3.0.4 Math Problem
« 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
« Last Edit: February 02, 2019, 08:18:33 pm by patyi »

#### zeljko

• Hero Member
• Posts: 1144
##### Re: Firebird 3.0.4 Math Problem
« Reply #1 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

#### zeljko

• Hero Member
• Posts: 1144
##### Re: Firebird 3.0.4 Math Problem
« Reply #2 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

#### patyi

• Full Member
• Posts: 169
##### Re: Firebird 3.0.4 Math Problem
« Reply #3 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 !

#### patyi

• Full Member
• Posts: 169
##### Re: Firebird 3.0.4 Math Problem
« Reply #4 on: February 02, 2019, 07:54:21 pm »
From "The Firebird Book" :

"Arithmetic Mixing Fixed and Floating-Point Types

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

• Full Member
• Posts: 169
##### Re: Firebird 3.0.4 Math Problem
« Reply #5 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
« Last Edit: February 02, 2019, 08:26:33 pm by patyi »

#### sash

• Sr. Member
• Posts: 356
##### Re: Firebird 3.0.4 Math Problem
« Reply #6 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).
Lazarus 2.0.10 FPC 3.2.0 x86_64-linux-gtk2 @ Ubuntu 20.04 XFCE

#### patyi

• Full Member
• Posts: 169
##### Re: [SOLVED] Firebird 3.0.4 Math Problem
« Reply #7 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

#### patyi

• Full Member
• Posts: 169
##### Re: [SOLVED] Firebird 3.0.4 Math Problem
« Reply #8 on: February 03, 2019, 10:22:10 am »

#### zeljko

• Hero Member
• Posts: 1144
##### Re: Firebird 3.0.4 Math Problem
« Reply #9 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

#### sash

• Sr. Member
• Posts: 356
##### Re: [SOLVED] Firebird 3.0.4 Math Problem
« Reply #10 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.
Lazarus 2.0.10 FPC 3.2.0 x86_64-linux-gtk2 @ Ubuntu 20.04 XFCE