Subject | Re: [firebird-support] Numeric(18,4) calculations |
---|---|

Author | Geoff Worboys |

Post date | 2006-05-25T02:37:24Z |

Hi t.s.

calculations become:

case 1 : '123,456,789.0000' * '100.0000' is OK.

case 2 : '123,456,789.0000' * '1000.0000' will bomb.

case 3 : '123,456,789.0000' * '10000.0000' will bomb.

The result of each of these will be a value that needs 8

decimal places. Since the largest numeric available with FB

is NUMERIC(18, *) the result becomes NUMERIC(18,8) - and hence

you only have 10 digits to the right of the decimal. (Actually

11 digits but not in the full range.)

client? What data type are you going to use. It is one thing

to choose an appropriate storage value, but that is only useful

if the client can represent it (and manipulate it) without loss

of precision (or type).

while your data is relatively small. When you begin to hit

the extremes additional precautions must be taken.

You need to understand that NUMERIC data types are scaled

integer data types, the implementation is a strange mix of

integer and floating point rules - presumably based on the SQL

standards but I dont know for sure. If your data starts to get

large you will have to take some additional precautions in

regard to casting interim responses back to the necessary data

types, and perhaps even doing "interesting" things with your

calculations (such as purposely dropping or rounding some of

the decimal places) to avoid overflow during interim values in

calculations.

precision - but has a fixed decimal place - compared to

double's 15 bits of precision - but has a floating point.

It is true that, while ever your data has no decimal places,

the difference becomes 15 to 14. But the accuracy of your

results are still limited to 15 digits (decimal or otherwise).

If you really have no decimal places then BIGINT (or

NUMERIC(18,0)) may be the better option as then you have a

full 18 digits of precision to play with.

REALLY hairy when you start to hit the extremes.

is required on your currency values. It is common for

financial systems to use statistical rounding (sometimes called

bankers rounding), but some situations may have quite specific

rounding requirements. For example the tax office in Australia

used use some weird stuff but has now reverted to either

truncating cents or standard (0.5 goes up) rounding.

You must also take care that you round at appropriate times.

Many storage fields should probably carry values rounded to the

cent (not expected to carry additional precision), other fields

may need to carry the extra precision. Similarly in your

calculations you must find out at what point rounding is

required (or appropriate). If you are starting to hit the

limits of NUMERIC or double precision then you may be truly

constrained as to the final accuracy - unless you find some way

to implement your own currency data type in the database.

I have a payroll application that still uses double (was built

before IB/FB supported int64 beneath their large NUMERICs).

But this application takes much care to perform explicit

rounding at (hopefully) appropriate places. Even with such

precautions you can end up with curious results if you perform

something like:

SELECT SUM(AFIELD) FROM ...

over a very large number of rows (the lack of exactness in the

double type can lead to a cent or two either way in the result,

although so far my explicit rounding has minimised the impact

of this issue).

I am afraid that many programmers do indeed implement financial

systems without fully understanding the issues resolved. You

do need to be very careful, and usually quite explicit, if you

want the system to respond as needed by your clients.

--

Geoff Worboys

Telesis Computing

> From my testing, the 'limit' seems to be around 11-12And because the values are defined as NUMERIC(18,4) the actual

> digits. So :

> case 1 : '123,456,789' * '100' is OK.

> case 2 : '123,456,789' * '1000' will bomb.

> case 3 : '123,456,789' * '10000' will bomb.

> ...

> There were no fractional parts involved in the calculation,

> and yet the maximum scale i can achieve is 11-12 digits

> instead of the supposed 14. Both numbers are fetched from a

> field/column with numeric(18,4) datatype.

calculations become:

case 1 : '123,456,789.0000' * '100.0000' is OK.

case 2 : '123,456,789.0000' * '1000.0000' will bomb.

case 3 : '123,456,789.0000' * '10000.0000' will bomb.

The result of each of these will be a value that needs 8

decimal places. Since the largest numeric available with FB

is NUMERIC(18, *) the result becomes NUMERIC(18,8) - and hence

you only have 10 digits to the right of the decimal. (Actually

11 digits but not in the full range.)

> The argument for using numeric(18,4) was because the need toThe thing to watch here is... What is going to happen at the

> support USD. As far as i understand it, USD needs at least 2

> digits on the right of the decimal points (the cents), so the

> 'safest' way to ensure accuracy is to use 2x as many digits.

> (Whatchacallit, Nyquist Rate?)

client? What data type are you going to use. It is one thing

to choose an appropriate storage value, but that is only useful

if the client can represent it (and manipulate it) without loss

of precision (or type).

> Thank you for the pointers. This is the 'conclusion' part atI guess the point is more that you can make various assumptions

> the end of that document:

> ....

> It is not essential to use numeric data types (although

> when the is scale is 0 or the values stored are small you

> are reasonably safe). Instead, you can implement your

> monetary values using double precision. This has a

> significance of only 15, but that is often sufficient.

> ...

> If i understand that paragraph correctly, using numeric data

> types are only recommended (or "safe") if the values are

> small. Exactly my case until recently, when the database has

> grown 'big' enough to accumulate some values.

while your data is relatively small. When you begin to hit

the extremes additional precautions must be taken.

You need to understand that NUMERIC data types are scaled

integer data types, the implementation is a strange mix of

integer and floating point rules - presumably based on the SQL

standards but I dont know for sure. If your data starts to get

large you will have to take some additional precautions in

regard to casting interim responses back to the necessary data

types, and perhaps even doing "interesting" things with your

calculations (such as purposely dropping or rounding some of

the decimal places) to avoid overflow during interim values in

calculations.

> The second recommendation is to use double precision typeNo, you misunderstand. NUMERIC(18,4) gives 18 digits of

> (with the caveat of having only 15 digits). Given that

> numeric(18,4) only gives me 14 digits, i think this is a

> good deal :).

precision - but has a fixed decimal place - compared to

double's 15 bits of precision - but has a floating point.

It is true that, while ever your data has no decimal places,

the difference becomes 15 to 14. But the accuracy of your

results are still limited to 15 digits (decimal or otherwise).

If you really have no decimal places then BIGINT (or

NUMERIC(18,0)) may be the better option as then you have a

full 18 digits of precision to play with.

> Please tell me whether this is a good idea before i go aheadGive yourself more than a couple of days, this stuff gets

> and spend a couple of days updating the production database

> .... :)

REALLY hairy when you start to hit the extremes.

> The next paragraph in that document is rather ominousI am hoping that you already have some idea of what rounding

> though...

> ...

> However, if you use double precision you will probably

> need to implement a UDF library containing rounding

> functions, so that you can set up your triggers and

> stored procedures to force the stored value to be

> rounded as required.

> ...

> I have a hunch that a couple of weeks from now, i'm gonna

> give the nice people here more trouble because i fail to

> understand what "to be rounded as required" *really* means :)

is required on your currency values. It is common for

financial systems to use statistical rounding (sometimes called

bankers rounding), but some situations may have quite specific

rounding requirements. For example the tax office in Australia

used use some weird stuff but has now reverted to either

truncating cents or standard (0.5 goes up) rounding.

You must also take care that you round at appropriate times.

Many storage fields should probably carry values rounded to the

cent (not expected to carry additional precision), other fields

may need to carry the extra precision. Similarly in your

calculations you must find out at what point rounding is

required (or appropriate). If you are starting to hit the

limits of NUMERIC or double precision then you may be truly

constrained as to the final accuracy - unless you find some way

to implement your own currency data type in the database.

I have a payroll application that still uses double (was built

before IB/FB supported int64 beneath their large NUMERICs).

But this application takes much care to perform explicit

rounding at (hopefully) appropriate places. Even with such

precautions you can end up with curious results if you perform

something like:

SELECT SUM(AFIELD) FROM ...

over a very large number of rows (the lack of exactness in the

double type can lead to a cent or two either way in the result,

although so far my explicit rounding has minimised the impact

of this issue).

I am afraid that many programmers do indeed implement financial

systems without fully understanding the issues resolved. You

do need to be very careful, and usually quite explicit, if you

want the system to respond as needed by your clients.

--

Geoff Worboys

Telesis Computing