sql - numeric precision in Oracle

Question

I have a table with a FLOAT column, as defined in Oracle docs. I insert a number with 52 binary digits (not counting the initial 1 which is implicit), yet I see that oracle requires only 50 digits to correctly store it. How could that be?

create table numeric_types2(
    f1 float(60)
);
insert into numeric_types2 SELECT BIN_TO_NUM(
    1,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,1
                                      ) FROM DUAL;

and then:

select to_char(cast(f1 as float(49)), 'XXXXXXXXXXXXXXXXX'),
       to_char(cast(f1 as float(50)), 'XXXXXXXXXXXXXXXXX'),
       to_char(cast(f1 as float(51)), 'XXXXXXXXXXXXXXXXX')
from root.numeric_types2;

return:

10000000000004,    10000000000001,    10000000000001

why is that? am i missing some elementary floating-point math?

Answer 1

The docs also state:

The FLOAT data type is a subtype of NUMBER

So it's a number under the covers and

To convert from binary to decimal precision, multiply n by 0.30103

Plugging the numbers in:

49 * 0.30103 = 14.75047
50 * 0.30103 = 15.05150
51 * 0.30103 = 15.65356

So float(50) and float(51) correspond to number(16), whereas float(49) is number(15)

You can verify this by taking a dump of the values:

create table numeric_types2 (
    f1 float(60), n1 number
);
insert into numeric_types2 
with rws as (
  select BIN_TO_NUM(
    1,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,1) n from dual
)
  select n, n from rws;

select dump ( f1 ), dump ( n1 ), 
       dump ( cast ( n1 as float(50) ) ) df50, 
       dump ( cast ( n1 as float(49) ) ) df49
from   numeric_types2;

DUMP(F1)                                 DUMP(N1)                                 DF50                                     DF49                                  
Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=8: 200,46,4,60,97,28,38,6    

Note that

dump ( f1 ) = dump ( n1 ) = dump ( cast ( n1 as float(50) ) )

Only casting the number as float(49) gives a different value.

Finally note that the docs also contain this recommendation:

Oracle FLOAT is available for you to use, but Oracle recommends that you use the BINARY_FLOAT and BINARY_DOUBLE data types instead, as they are more robust