Recent

Author Topic: [SOLVED] Firebird 3.0.4 Math Problem  (Read 1629 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: 1052
    • http://wiki.lazarus.freepascal.org/User:Zeljan
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: 1052
    • http://wiki.lazarus.freepascal.org/User:Zeljan
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

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

  • 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: 266
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.2 FPC 3.0.4 x86_64-linux-gtk2 -- Ubuntu 19.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 »
See also Firebird FAQ : http://www.firebirdfaq.org/faq219/

zeljko

  • Hero Member
  • *****
  • Posts: 1052
    • http://wiki.lazarus.freepascal.org/User:Zeljan
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: 266
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.2 FPC 3.0.4 x86_64-linux-gtk2 -- Ubuntu 19.04 XFCE