0

I am using Firebird 1.5.x database and it has problems with variables that are stored in double precision or numeric(15,2) fields. E.g. I can issue update (field_1 and field_2 are declared as numeric(15,2)):

update test_table set
  field_1=0.34,
  field_2=0.69;

But when field_1 and field_2 is read into variables var_1 and var_2 inside the SQL stored procedure, then var_1 and var_2 assume values 0.340000000000000024 and 0.689999999999999947 respectively. The multiplication var_1*var_2*25 gives 5.8649999999999999635 which can be rounded as 5.86. This ir wrong apparently, because the correct final value is 5.87.

The rounding is done with user defined function which comes from C++ DLL which I develop. The idea is to detect situations with represenation error, make correction and apply the rounding procedure to the corrected values only.

So, the question is - how to detect and correct represenation errors. E.g. detect that 0.689999999999999947 should be corrected to 0.69 and detect, that 0.340000000000000024 should be corrected to 0.34. Generally there can be situation when the number of significant numbers after point is more or less than 2, e.g. 0.23459999999999999854 should be corrected to 0.2346.

Is if possible to do in C++ and maybe some solutions already exist for this?

p.s. I tested this case in more recent Firebird versions 2.x and there is no problem with reading database fields into variables in stored procedure. But I can guess that nevertheless the representation errors can arise in more recent Firebird versions too during some lengthy calculations.

Thanks!

TomR
  • 2,696
  • 6
  • 34
  • 87
  • 4
    You might want to review things like http://floating-point-gui.de and linked, first. – pvg Mar 14 '16 at 08:31
  • I have read [Python](https://stackoverflow.com/questions/34641374/rounding-floats-with-representation-error-to-the-closest-and-correct-result) that in similar situation Python round function can identify the represenation error and do the correct rounding. Maybe (the more recent) C++ has standartd round function with this capability as well? I am cheking right now... – TomR Mar 14 '16 at 08:39
  • 1
    this is not 'representation error', it's the actual representation. – pvg Mar 14 '16 at 08:48
  • Re pvg. Client requires solution not the theory about floating point arithmetic. Excel can do this, my software must do this as well. – TomR Mar 14 '16 at 09:09
  • 1
    If your `field_1` and `field_2` are `NUMERIC(15,2)`, then don't assign it to `DOUBLE PRECISION`. And your argument re behavior of Excel is invalid: Excel likely stores the same value (as all numbers in Excel are double precision), but it applies some formatting tricks on displaying. – Mark Rotteveel Mar 14 '16 at 10:49
  • You kind of have to understand the (even very basics of) theory in order to produce a meaningful solution. SO is a site for answering programming questions not a proxy for your client (who, most likely pays you, unlike your relationship with SO users). – pvg Mar 14 '16 at 16:29

1 Answers1

1

This problem exists in all programming languages where floating point variables are used.

You need to understand that there is no accurate way to display 1/3. Any display of that value is a compromise that must be agreed to between the concerned parties. The required precision is what must be agreed to. If you understand that, we can move on.

So how do we (for example) develop accounting systems with accuracy? One approach is to round(column_name, required_precision) all values as they are used in calculations. Also round the result of any division to the same precision. It is important to constently use the agreed-to precision throughout the application.

Another alternative is to always multiply the floating-point values by 100 (if your values represent currency) and assign the resulting values to Integer variables. Again, the result of a division must be rounded to 0 decimal places before it is assigned to it's integer storage. Values are then divided by 100 for display purposes. This is as good as it gets.

Freddie Bell
  • 2,186
  • 24
  • 43
  • nitpick here, you can *display* 1/3 just fine ("1/3"). There's just no accurate *representation* of 1/3 within the floating point IEEE 754 notation – kmdreko Mar 28 '16 at 18:59
  • 1
    Pointless nitpick. The _intention_ of my use of "1/3", that is to represent 0.333 recurring, is clear from the context in which I used it in the text. What is most important is that @TomR understands that the representation and presentation (display) of that number is the result of a compromise. – Freddie Bell Mar 28 '16 at 19:08