Search code examples
sqlfloating-pointdb2approximation

Why does SELECT FLOAT(0.1) return 0.1 in DB2?

Why executing the following SQL statement on DB2 returns * 0.1?

select FLOAT(0.1) from sysibm.sysdummy1

I was expecting an approximated result like for REAL(0.1);
Why and where does the rounding happen?

* Using Visual Explain

Solution

  • This is just a guess, but many systems, when formatting floating-point values for output, will print the shortest number that is closer to the result than to any other floating-point number. Since FLOAT is double-precision and REAL is single precision, they will round "0.1" to different binary approximations, and it may well be that for one of them, 0.1 is closer to the rounded value than to any other value of the format, while for the other, it is not.