Subject | Re: [firebird-support] Weird Integer Overflow |
---|---|

Author | Helen Borrie |

Post date | 2003-12-19T02:09:11Z |

At 08:38 AM 19/12/2003 +0800, you wrote:

integers. Each multiplication or division increases the scale of the

result, until you overflow the precision (stored as precision 18 but

limited by your definition). For simple examples:

1.50 * 1.50 = 2.2500

(1.50 * 1.50) / (.01 * .01) = 22500.00000000

I suggest you use CAST(YourResult as numeric(15,2)) throughout, to keep the

scale correct; or use double precision numbers and build in your own

rounding algorithm; or convert some of your multiplier factors to integer

and then divide and recast the final result.

If you haven't done so far, read Geoff Worboys' paper at the IBO TechInfo

site on this topic.

I don't think use F_fixedpoint is a great solution. It returns a string,

which might or might not need recasting for further calculations,

anyway. Plus it means installing FreeUDFLib, which makes your database

non-portable.

Complex expressions need to be designed in terms of the accuracy of

results, not just in terms of whether they do or don't cause data type

exceptions in the engine. How you resolve this really gets down to running

lots of *good* typical testing data using various algorithms, and comparing

the results with calculator results, examining variances, etc., etc.

/heLen

>Hi when I execute this sql queryIt isn't weird - it is expected behaviour. Numerics are just scaled

>

>select (("TotalAmount" * (1-("Discount"*0.01)) * (1-("Discount2"*0.01))

>* (1-("Discount3"*0.01)) - "Less" - "Adjustment")) as "GrandTotal"

>from "invoice"

>

>Iam getting a Interger Overflow error. The result of an integer

>operation cause the most significant bit of the result to carry. But I

>don't have any integer field here ... All of them are Numerics(15,2)

>expcept for the discounts field which are numerics(4,2)

>

>

>

>Hint:

>1. I limit the number of records to be included with "where

>"TotalAmount" < 100000" and everything works well. I have a

>"TotalAmount" = 560000 as the biggest value.

>

>2. I have try this sql and it works on all records

>

>select "TotalAmount"* (1-("Discount"*0.01)) * (1-("Discount2"*0.01))

>from "invoice"

>

>or

>

>select "TotalAmount"* (1-("Discount2"*0.01)) * (1-("Discount3"*0.01))

>from "invoice"

>

>What is this error?

integers. Each multiplication or division increases the scale of the

result, until you overflow the precision (stored as precision 18 but

limited by your definition). For simple examples:

1.50 * 1.50 = 2.2500

(1.50 * 1.50) / (.01 * .01) = 22500.00000000

I suggest you use CAST(YourResult as numeric(15,2)) throughout, to keep the

scale correct; or use double precision numbers and build in your own

rounding algorithm; or convert some of your multiplier factors to integer

and then divide and recast the final result.

If you haven't done so far, read Geoff Worboys' paper at the IBO TechInfo

site on this topic.

I don't think use F_fixedpoint is a great solution. It returns a string,

which might or might not need recasting for further calculations,

anyway. Plus it means installing FreeUDFLib, which makes your database

non-portable.

Complex expressions need to be designed in terms of the accuracy of

results, not just in terms of whether they do or don't cause data type

exceptions in the engine. How you resolve this really gets down to running

lots of *good* typical testing data using various algorithms, and comparing

the results with calculator results, examining variances, etc., etc.

/heLen