I was reading the WorkflowFaq Blog today, where they discuss when 1 != 1 in Oracle.
Of I did some testing with this. First of course I reproduced the issue easy enough:
SQL> select 1/3*3 from dual;
1/3*3 ---------- 1
SQL> select * from dual where 1/3*3=1;
No rows selected.
It is obviously a rounding issue. So what happens if we force Oracle to round the result?
SQL> select * from dual where round(1/3*3)=1;
D - X
If the rounding went wrong, it will most likely be on the low side (0.3333……*3=0.9999……)
SQL> select trunc(1/3*3) from dual where ceil(1/3*3)=1;
D - 0
We can see the real contents by dumping the result:
SQL> select dump(1/3*3) d from dual 2 union 3 select dump(1) d from dual;
D -------------------------------------------------------------------------------- Typ=2 Len=21: 192,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,10 0,100,100,100,100
Typ=2 Len=2: 193,2
I will go into the internal number format in a while. For now, it is sufficient to say that the 1/3*3 is indeed 0.9999999…….
Now how many decimals can we get?
SQL> select to_char(1/3*3,'9.99') from dual;
TO_CH ----- 1.00
SQL> select to_char(1/3*3,'9.99999999999999999999999999999999999999999') from dual;
TO_CHAR(1/3*3,'9.999999999999999999999999999 -------------------------------------------- .99999999999999999999999999999999999999990
There is the real value of 1/3*3.
The Oracle documentation says that the precision of the number format is 126 binary digits, approximately 38 decimals digits.
So when we force a mask with more decimals, Oracle will show the real value.
Àn interesting 'feature'. And definitely something to keep in mind.
One last note on the dumping of numbers.
dump(<number>,10) will dump the decimal value.
dump(<number>,16) will dump the hexadecimal value.
How do we translate it back to the real value then?
Let's do the excercise for a few numbers: 1234.5678 and -1234.5678.
There is some difference between positive and negative values. So let's do the positive one first.
SQL> select dump(1234.5678,10),dump(1234.5678,16) from dual;
DUMP(1234.5678,10) DUMP(1234.5678,16) ---------------------------- -------------------------- Typ=2 Len=5: 194,13,35,57,79 Typ=2 Len=5: c2,d,23,39,4f
We start with the second to the last numbers. And we subtract 1:
194,12,34,56,78
Ignore the 194 for now, and put the decimal . after the second number: 12.345678
Now subtract 193 from the first value: 194-193=1.
Multiply the decimal value this number of times by 100: 12.345678 * 100 = 1234.5678.
(In our case we multiply by 100 once if the first value would be higher, mulitply more often).
The negative values are a little different:
SQL> select dump(-1234.5678,10),dump(-1234.5678,16) from dual;
DUMP(-1234.5678,10) DUMP(-1234.5678,16) ------------------------------- ------------------------------ Typ=2 Len=6: 61,89,67,45,23,102 Typ=2 Len=6: 3d,59,43,2d,17,66
The 102 at the end means it is a negative value. Discard the 102 for the rest of the calculation.
61,89,67,45,23.
Subtract the second to the last number from 101.
101-89=12, 101-67=34, 101-45=56,101-23=78 gives
61,12 34 56 78
Put the decimal . after the second number. So we get 12.345678
Now subtract 62 from the first value and multiply by 100: 61-62 = -1.
Finally we divide the value this number of times by 100. In our case we have a negative, so we
multiply instead:
12.345678 * 100 = 1234.5678 -> And we had a negative. So -1234.5678