Oracle is under the impression that 1/19 * 19 - 1 = -6 x 10^(-40). You can replace 19 with 19 multiplied by any positive power of 100 and the problem remains. (When you multiply 19 by an odd power of 10, the answer is zero. For example, 1/1900 * 1900 - 1 = -6x10^(-40), but 1/190 * 190 - 1 = 0.) I understand it has to do with the way numbers are stored internally, but is there a simple way around it?
Note that this also happens in version 10.2.4
That happens with many numbers - it happens with all man made representation of numbers - it happens all of the time in computers (especially with floating point numbers - they are horrible)
(this isn't specific to Oracle, spreadsheets - all computer programs I've ever seen - everything is subject to this somewhere)
An Oracle number has up to 38 digits of precision. (A float has about 6 and a double about 13 - so the Oracle number is much more precise than those types)
The problem arises anytime in computer programs when you start doing things with really small numbers and really big numbers - we blow out the precision of the intermediate result - we cannot hold all of the digits.
The problem starts with 1/19. We cannot represent that number "perfectly"
ops$tkyte%ORA11GR2> select 1/19 from dual;
1/19
------------------------------------------------
0.052631578947368421052631578947368421052600000
(we run out of places to hold the decimal - that is not exactly 1/19, it is close). And when we multiply by 19 - we get into another bind - we have 38 digits of precision - but our numbers are so far apart - we'd need more than 38 to do it precisely so:
ops$tkyte%ORA11GR2> select 1/19*19 from dual;
1/19*19
------------------------------------------------
0.999999999999999999999999999999999999999400000
And now you see the issue. If we were to do this in floating point - it would work (with these numbers) by accident:
ops$tkyte%ORA11GR2> select 1f/19f from dual;
1F/19F
------------------------------------------------
0.052631579300000000000000000000000000000000000
<b>you see the massively reduced precision...</b>
ops$tkyte%ORA11GR2> select 0.0526315793f * 19f from dual;
0.0526315793F*19F
------------------------------------------------
1.000000000000000000000000000000000000000000000
<b>and it appears to work - but only because of the reduced precision - for you see, using more "precise" numbers:</b>
ops$tkyte%ORA11GR2> select 0.0526315793 * 19 from dual;
0.0526315793*19
------------------------------------------------
1.000000006700000000000000000000000000000000000
<b>that isn't really 1, the error is actually much larger - but you lost it because you had a number with 11 digits to the right of the "." and a number with two digits to the left - the reduced precision arithmetic "lost" the error (by accident)
The only way I know "around it", is the old programming technique of not using "=", but using a tolerance. You cannot use another type of number (like a float or double) as they'll get errors as well - using different numbers.
instead of "if (x = 0) then"
you would "if (abs(x-0) < tolerance) then"
ops$tkyte%ORA11GR2> declare
2 l_float binary_float;
3 l_testf binary_float;
4
5 l_double binary_float;
6 l_testd binary_float;
7 begin
8 for i in 1 .. 100
9 loop
10 l_testf := i;
11 l_float := 1f/l_testf*l_testf-1f;
12 if ( l_float <> 0f ) then dbms_output.put_line( i|| ') l_float <> 0' ); end if;
13
14 l_testd := i;
15 l_double := 1d/l_testd*l_testd-1d;
16 if ( l_double <> 0d ) then dbms_output.put_line( i|| ') l_double <> 0' ); end if;
17 end loop;
18 end;
19 /
41) l_float <> 0
47) l_float <> 0
49) l_double <> 0
55) l_float <> 0
61) l_float <> 0
82) l_float <> 0
83) l_float <> 0
94) l_float <> 0
97) l_float <> 0
98) l_double <> 0
PL/SQL procedure successfully completed.