# 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