The result seems OK to me. I'll post a reply explaining a bit more to firebird-support (the correct place to ask your question) in a few seconds.

Set

-----Original Message-----
From: Firebird-general@yahoogroups.com [mailto:Firebird-general@yahoogroups.com] On Behalf Of Antonio Carlos Ribeiro
Sent: 21. februar 2008 21:13
To: Firebird-general@yahoogroups.com
Subject: [Firebird-general] Precision

I'm using Firebird 1.54.

What's wrong with those calculations?:

This one should return 14000 but returns 13999,20:

select
cast( 12000 as DECIMAL( 18, 2 ) )

*
(
cast( 20 as DECIMAL( 18, 2 ) )
/ ( cast( 20 as DECIMAL( 18, 2 ) )
+ 100
)
+ cast( 1 as DECIMAL( 18, 2 ) )
)

from rdb$database

Converting to double precision, with floor, fixes it:

select
cast( 12000 as DECIMAL( 18, 2 ) )

*
( floor(1) *
cast( 20 as DECIMAL( 18, 2 ) )
/ ( cast( 20 as DECIMAL( 18, 2 ) )
+ 100
)
+ cast( 1 as DECIMAL( 18, 2 ) )
)

from rdb$database --> returns 14000

-----------------------

In worst case we get an "Integer overflow" error:

select
cast( 1 as double precision )
*
( ( cast( 12000 as DECIMAL( 18, 5 ) )
* cast( 1 as DECIMAL( 18, 4 ) )
)
* ( cast( 20 as DECIMAL( 18, 4 ) )
/ ( cast( 20 as DECIMAL( 18, 4 ) )
+ 100
)
)
)
from rdb$database

- Changing 12000 to 120 'fixes' the error, but we still don't get the
right calculation that should be 20 but returns 19,9999992:

select
cast( 1 as double precision )
*
( ( cast( 120 as DECIMAL( 18, 5 ) )
* cast( 1 as DECIMAL( 18, 4 ) )
)
* ( cast( 20 as DECIMAL( 18, 4 ) )
/ ( cast( 20 as DECIMAL( 18, 4 ) )
+ 100
)
)
)
from rdb$database


Anyone could explain this behaviour?

Is there a way to protect the database against that, without changing
data types of all fields?

We have many clients, that write their own queries, and it's not
simple to explain and ask them to remember that they have to convert
every calculation to double precision.

Thank you!

Antonio Carlos



Community email addresses:
Post message: Firebird-general@yahoogroups.com
Subscribe: Firebird-general-subscribe@yahoogroups.com
Unsubscribe: Firebird-general-unsubscribe@yahoogroups.com
List owner: Firebird-general-owner@yahoogroups.com

Shortcut URL to this page:
http://www.yahoogroups.com/community/Firebird-general
Yahoo! Groups Links