We have a big discussion in our company, because a SW-Vendor is using many FLOAT(126) datatypes within a Billing-module for money based columns.
I would like to clarify, if this is or can be an issue or not, because my idea would be to have clear NUMBER(n,s) data types.
There are many older similar discussions, but archived.
I've found an answer in AskTom from June 17, 2011 - 12:37 pm UTC with his excellent example showing the differences in internal representations between NUMBER and BINARY_FLOAT and BINARY_DOUBLE.
Now, I've extended his example by adding a column of FLOAT(126).
Oracle doc says, that FLOAT is internaly represented as NUMBER, so I expected no difference, but in the testcase, there are some and I don't understand why.
create table FT ( n number, bf binary_float, bd binary_double, f float(126));
insert into FT (n) select 100.0 + rownum/100 from all_users where rownum <=10;
insert into FT (n) select 100.0 + rownum/100/7 from all_users where rownum <=10;
REM assign values from number field to all float fields.
update FT set bf=n, bd=n, f=n;
commit;
set numformat 999.9999999999999999999999999999999999999
col bf_bad format a6
col bd_bad format a6
col f_bad format a5
set pagesize 100
select n, bf, bd, f,
case when n <> cast(bf as number) then '*' end bf_bad,
case when n <> cast(bd as number) then '*' end bd_bad,
case when n <> cast(f as number) then '*' end f_bad
from FT
;
N BF BD F BF_BAD BD_BAD F_BAD
----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ------ ------ -----
100.0100000000000000000000000000000000000 1.000100021E+002 1.0001E+002 100.0100000000000000000000000000000000000 * *
100.0200000000000000000000000000000000000 1.000199966E+002 1.0002E+002 100.0200000000000000000000000000000000000 *
100.0300000000000000000000000000000000000 1.000299988E+002 1.0003E+002 100.0300000000000000000000000000000000000 *
100.0400000000000000000000000000000000000 1.000400009E+002 1.0004E+002 100.0400000000000000000000000000000000000 * *
100.0500000000000000000000000000000000000 1.000500031E+002 1.0005E+002 100.0500000000000000000000000000000000000 *
100.0600000000000000000000000000000000000 1.000599976E+002 1.0006E+002 100.0600000000000000000000000000000000000 *
100.0700000000000000000000000000000000000 1.000699997E+002 1.0007E+002 100.0700000000000000000000000000000000000 *
100.0800000000000000000000000000000000000 1.000800018E+002 1.0008E+002 100.0800000000000000000000000000000000000 *
100.0900000000000000000000000000000000000 1.000899963E+002 1.0009E+002 100.0900000000000000000000000000000000000 *
100.1000000000000000000000000000000000000 1.000999985E+002 1.001E+002 100.1000000000000000000000000000000000000 * *
100.0014285714285714285714285714285714290 1.000014267E+002 1.000014286E+002 100.0014285714285714285714285714285714300 * * *
100.0028571428571428571428571428571428570 1.000028534E+002 1.000028571E+002 100.0028571428571428571428571428571428600 * * *
100.0042857142857142857142857142857142860 1.000042877E+002 1.000042857E+002 100.0042857142857142857142857142857142900 * * *
100.0057142857142857142857142857142857140 1.000057144E+002 1.000057143E+002 100.0057142857142857142857142857142857100 * * *
100.0071428571428571428571428571428571430 1.000071411E+002 1.000071429E+002 100.0071428571428571428571428571428571400 * * *
100.0085714285714285714285714285714285710 1.000085678E+002 1.000085714E+002 100.0085714285714285714285714285714285700 * * *
100.0100000000000000000000000000000000000 1.000100021E+002 1.0001E+002 100.0100000000000000000000000000000000000 * *
100.0114285714285714285714285714285714290 1.000114288E+002 1.000114286E+002 100.0114285714285714285714285714285714300 * * *
100.0128571428571428571428571428571428570 1.000128555E+002 1.000128571E+002 100.0128571428571428571428571428571428600 * * *
100.0142857142857142857142857142857142860 1.000142822E+002 1.000142857E+002 100.0142857142857142857142857142857142900 * * *
20 rows selected.
As you can see, I've saved just first column n and later update all the other columns with value of n to get the same value everywhere.
This ensures DB-internal operation and should exclude any other influences.
Comparison is made within SELECT and most FLOAT values are working well, but for some high precision floating numbers created as division by a prime number, the saved values are not equal anymore or the CAST doesn't work correctly.
It seems to me, that FLOAT(126) is not the same like NUMBER(*);
Any ideas, why FLOAT is sometimes different to NUMBER, if both are saved as NUMBER internaly?
Don't mix with the Cols 2 of BINARY_FLOAT and Col-3 of BINARY_DOUBLE. It is clear to me, that they are different in most cases. I've put them just for getting a clear picture.