Re: 'real' strange problem in 7.1.3

reina(at)nsi(dot)edu (Tony Reina) writes:
> db02=# select distinct arm from ellipse where ellipse_ratio = 1.8;
> arm
> -----
> (0 rows)

You realize that floating-point values aren't exact? Probably the "1.8"
in the database is a few bits off in the seventh decimal place, and so
it's not exactly equal to the "1.8" you've given as a constant. In
fact, seeing that you've actually written the constant as a float8, it's
almost certain that the float4 value in the database will not promote to
exactly that float8. On my machine I get

regression=# select (1.8::float4)::float8;
float8
------------------
1.79999995231628
(1 row)

regression=# select 1.8::float4 - 1.8::float8;
?column?
-----------------------
-4.76837158647214e-08
(1 row)

regression=# select 1.8::float4 = 1.8::float8;
?column?
----------
f
(1 row)

You *might* find that writing "where ellipse_ratio = 1.8::float4"
selects your database row, or you might not --- if the 1.8 in the
database was the result of a calculation, and didn't arise directly
from input conversion of the exact string "1.8", then the odds are
it won't match. (Your example with putting single quotes around the
1.8 is equivalent to this explicit coercion, BTW.)

In any case, any programming textbook will tell you that doing exact
comparisons on floats is folly. Consider something like

... where abs(ellipse_ratio - 1.8) < 1.0e-6;

regards, tom lane