Ask Reuben

Precision

Why is my numeric expression not returning the value I expect? 

Why are these two numeric expressions not returning the same value? 

What datatype is used in intermediate calculations?

We have a new promotional video out that has lots of interesting numbers around the usage of Genero.  It does make me wonder how many times certain functions I might have written going back to the 90’s have been executed.  No doubt you all have similar functions that have been executed a large number of times.  If you have some interesting numbers for a second edition of the video, send them your local support contacts way.

Whilst the video was dealing with large numbers, it also reminded me of some very small numbers we saw in an interesting support case recently.  The interesting thing was that if you have

a-b = c

is it equally true that

a/d -b/d = c/d   ?

Remember This

Two things to remember.

  • Computers deal with 1’s and 0’s.  It is not possible to express all numbers (rational or irrational) as a finite number of 0’s and 1’s.
  • Secondly, when Genero is evaluating numeric expressions,  the datatype of the resultant expression maybe an INTEGER or a DECIMAL as per this page on numeric expression.  A DECIMAL with no precision or scale is a DECIMAL(16).  Any intermediate values in a numeric expression may have datatype INTEGER, or DECIMAL(16), if DECIMAL that means be accurate to 16 digits.

New Functionality Which Helps Explain

With Genero 4.00 which is in the middle of the Early Access Program at the moment, there are two new pieces of new functionality that help illustrate this.  In the following example …

IMPORT reflect
MAIN

    VAR x = 1.0 / 3.0
    DISPLAY "0.123456789012345678901234567890"
    DISPLAY x USING "&.&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&"
    DISPLAY reflect.Type.typeOf(x).toString()
END MAIN

the variable created by VAR has as its datatype, the datatype of  the result of the expression

and the reflect.Type.typeOf(variable-name).toString() method allows you to interrogate and display the datatype of a variable.

The output of the above program is (the 0.12345678901234567890″ helps you count the digits …

0.123456789012345678901234567890
0.333333333333333300000000000000
DECIMAL

… so we can see that x is a DECIMAL with no precision or scale, so therefore DECIMAL(16), and we can see that x is not exactly equal to 1/3.  It is equal to 0.3333333333333333 ( a precision of 16 digits) and so is very very slightly smaller than 1/3.

Interesting Case

So back to the interesting case, if you run the following …

MAIN
    DEFINE A,B,C DECIMAL(18,2)
    DEFINE D DECIMAL(15,8)

    LET A = 23280.86
    LET B = 23280.85
    LET C = 0.01
    LET D = 1.22915

    DISPLAY IIF( (A - B) < C , "Less Than", "Greater Than or Equal To")
    DISPLAY IIF((A/D) - (B/D) < (C/D), "Less Then", "Greater Than or Equal To")
END MAIN

... the output is ...

Greater Than or Equal To
Less Then

... and your immediate thought, is how can this be?  Mathematically dividing all the numbers by the same value should not alter the result of the comparison.

Explanation

If we slow it down and look at the value of the intermediate expressions with this Genero 4.00 code (please sign upto the EAP if you have not done so already) ...

IMPORT reflect

MAIN
    DEFINE A,B,C DECIMAL(18,2)
    DEFINE D DECIMAL(15,8)

    LET A = 23280.86 
    LET B = 23280.85
    LET C = 0.01
    LET D = 1.22915

    DISPLAY "A/D"
    VAR AD = A/D
    DISPLAY reflect.Type.typeOf(AD).toString()
    DISPLAY AD USING "&&&&&.&&&&&&&&&&&&&&&&&&&"

    DISPLAY "B/D"
    VAR BD = B/D
    DISPLAY reflect.Type.typeOf(BD).toString()
    DISPLAY BD USING "&&&&&.&&&&&&&&&&&&&&&&&&&"

    DISPLAY "A/D-B/D"
    VAR AD_BD = AD-BD
    DISPLAY reflect.Type.typeOf(AD_BD).toString()
    DISPLAY AD_BD USING "&&&&&.&&&&&&&&&&&&&&&&&&&"

    DISPLAY "C/D"
    VAR CD = C/D
    DISPLAY reflect.Type.typeOf(CD).toString()
    DISPLAY CD USING "&&&&&.&&&&&&&&&&&&&&&&&&&"

    DISPLAY IIF( (A - B) < C , "Less Than", "Greater Than or Equal To")
    DISPLAY IIF((A/D) - (B/D) < (C/D), "Less Than", "Greater Than or Equal To")
    DISPLAY IIF( AD_BD < CD, "Less Than", "Greater Than or Equal To")
END MAIN

... we get the following output ...

A/D
DECIMAL
18940.6174998983000000000
B/D
DECIMAL
18940.6093641947700000000
A/D-B/D
DECIMAL
00000.0081357035300000000
C/D
DECIMAL
00000.0081357035349631860
Greater Than or Equal To
Less Than
Less Than

... and we can see that the intermediate expressions are DECIMAL(16), and where the accuracy is lost in the intermediate expressions.  Note how A/D is 18940.6174998983 and B/D is 18940.60936419477 as DECIMAL(16), and so the result of A/D-B/D also DECIMAL (16) is 0.00813570353 which is just a little bit smaller than   0.0081357035349631860, the value of C/D as a DECIMAL(16).  Hence the numbers have contrived such at A/D-B/D < C/D when A-B=C

What Can You Do?

The first thing is to be aware that this can occur.  If necessary, use intermediate variables where you explicitly state the precision and scale you are interested in.

So in this example, I expressly place the intermediate results in variables that cater for 6 decimal places.

IMPORT reflect

MAIN
    DEFINE A,B,C DECIMAL(18,2)
    DEFINE D DECIMAL(15,8)

    DEFINE AD6, BD6, CD6, AD_BD6 DECIMAL(22,6)

    LET A = 23280.86
    LET B = 23280.85
    LET C = 0.01
    LET D = 1.22915

    LET AD6 = A/D
    LET BD6 = B/D
    LET CD6 = C/D
    LET AD_BD6 = AD6-BD6

    DISPLAY reflect.Type.typeOf(AD6).toString()
    DISPLAY AD6 USING "&&&&&.&&&&&&&&&&&&&&&&&&&"

    DISPLAY reflect.Type.typeOf(BD6).toString()
    DISPLAY BD6 USING "&&&&&.&&&&&&&&&&&&&&&&&&&"

    DISPLAY reflect.Type.typeOf(AD_BD6).toString()
    DISPLAY AD_BD6 USING "&&&&&.&&&&&&&&&&&&&&&&&&&"

    DISPLAY reflect.Type.typeOf(CD6).toString()
    DISPLAY CD6 USING "&&&&&.&&&&&&&&&&&&&&&&&&&"

    DISPLAY IIF((A - B) < C , "Less Than", "Greater Than or Equal To")
    DISPLAY IIF( AD_BD6 < CD6 , "Less Than", "Greater Than or Equal To")
END MAIN

The result of this is ...

DECIMAL(22,6)
18940.6175000000000000000
DECIMAL(22,6)
18940.6093640000000000000
DECIMAL(22,6)
00000.0081360000000000000
DECIMAL(22,6)
00000.0081360000000000000
Greater Than or Equal To
Greater Than or Equal To

Now both sides have value of 0.008136 and are seen as equal.

I also could have coded it as (A-B)/D and then both sides would be comparing 0.01/D. So you maybe able to rearrange so that no loss of precision occurs.

Summary

Don't fall into the trap of thinking that intermediate expressions are the same datatype as the input parameters.  If necessary break up the expression into smaller pieces and explicitly perform the calculation to the precision you would like.  Consider how the expression is arranged and maybe express it in a different way to avoid loss of precision.