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

Why executing the f开发者_StackOverflow社区ollowing 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}

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.

There is difference between 32 bit (single precision) and 64 bit (double precision) representation of 0.1 decimal value. You can use online IEEE 754 calculator to see that.

32 bit: 0.09999999403953552 (3DCCCCCC)
64 bit: 0.1 (3FB999999999999A)

I checked IBM DB2 documentation and actually FLOAT function is a synonym for DOUBLE, so it returns 64-bit representation, which is exact showed there as approximated value 0.1.

The REAL function returns a single-precision floating-point representation of a number.

The FLOAT function returns a floating-point representation of a number. FLOAT is a synonym for DOUBLE.