On Wed, Jan 07, 2009 at 08:12:44PM +0530, Nikhil Sontakke wrote:
> Hi,
>
> Consider the following with latest CVS sources:
>
> postgres=# create table temp(val float4);
> CREATE TABLE
> postgres=# insert into temp values (415.1);
> INSERT 0 1
> postgres=# select * from temp where val = 415.1;
> val
> -----
> (0 rows)
>
> !?
>
> The reason seems to be that 415.1 ends up being treated as a numeric and is
> converted into float8 (why not float4? - it could have helped to use the
> float4eq function then)
>
> The float8in function uses strtod which for some reason converts '415.1'
> into 415.10000000000002 causing the subsequent comparison to fail. I guess
> there are ample cases of float/strtod weirdness around? Needless to mention,
> I was mighty surprised on seeing the output for the first time around :)
>
> Casting to float4 works as expected:
> postgres=# select * from rel where x = 415.1::float4;
> x
> -------
> 415.1
> (1 row)
>
> Regards,
> Nikhils
> --
> http://www.enterprisedb.com
The traditional approach to equality test with floating point is
to do the check plus-or-minus some value epsilon. Otherwise, such
seemingly bizarre behavior results.
Cheers,
Ken