Antonio Carlos Ribeiro wrote:

>
> What's wrong with those calculations?:

see calculations below.

First, this question should probably be addressed to the support
group firebird-support@yahoogroups.com.

Second, without having looked very closely at what you are
doing, I suspect that what you're seeing is artifacts of
the fact that calculations are done using the precision
and scale of the operands. That can lead to truncation
and unexpected results. In some cases, there's internal
overflow.


Regards,


Ann

>
> 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
>
>
>
>