floatの丸め誤差

gistfile1.txt

-- Oracleにおける数値データ型の誤差の調査

CREATE TABLE t1
(a number
,b binary_float
,c float)

 insert into t1 values(1.75, 1.75, 1.75);


 SELECT 
    to_char(a*1000000000000,'9999999999999') as "number",
    to_char(b*1000000000000,'9999999999999') as "binary_float",
    to_char(c*1000000000000,'9999999999999') as "float"
FROM t1;


number                       binary_float                 float
---------------------------- ---------------------------- ----------------------------
 1750000000000                1749999940000                1750000000000


declare
    v_number number;
    v_bfloat binary_float;
    v_float float;
begin
    select a,b,c
    into   v_number, v_bfloat, v_float
    from t1;

    dbms_output.put_line('x1');
    if v_number = v_bfloat then
        dbms_output.put_line('eq!');
    end if;

    dbms_output.put_line('x10000000000000');
    if v_number*10000000000000 = v_bfloat*10000000000000 then
        dbms_output.put_line('eq!');
    end if;
end;
/


SQL> select * from t1 where a=b;

         A          B          C
---------- ---------- ----------
      1.75  1.75E+000       1.75



SQL> select * from t1 where a*10000000000000 = b*10000000000000;

レコードが選択されませんでした。




-- DB2

create table t1
(a int
,b float
,c double);

insert into t1 values(1.75, 1.75, 1.75);

db2 => select * from t1;

A           B                        C
----------- ------------------------ ------------------------
          1   +1.75000000000000E+000   +1.75000000000000E+000


SELECT 
    to_char(a*1000000000000000,'9999999999999999') as "int",
    to_char(b*1000000000000000,'9999999999999999') as "float",
    to_char(c*1000000000000000,'9999999999999999') as "double"
FROM t1;


db2 => SELECT
db2 (続き) =>     to_char(a*1000000000000000,'9999999999999999') as "int",
db2 (続き) =>     to_char(b*1000000000000000,'9999999999999999') as "float",
db2 (続き) =>     to_char(c*1000000000000000,'9999999999999999') as "double"
db2 (続き) => FROM t1;

int                           float                                               double
----------------------------- --------------------------------------------------- -----------------------------
 1000000000000000                                               1750000000000000               1750000000000000