More on 1 != 1

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

Leave a Reply

Your email address will not be published. Required fields are marked *