Re: Trouble with float4 after upgrading from 6.5.3 to 7.0.2

"Romanenko Mikhail" <mikhail@angg.ru> writes:
> testfloat=# update tftbl set f1=10.1 where f1=10 and f2=20;
> UPDATE 1
> testfloat=# update tftbl set f2=20.2 where f1=10.1 and f2=20;
> UPDATE 0

The second update is failing to find any tuple that satisfies f1 = 10.1,
because f1 is a float4 variable whereas 10.1 is implicitly a float8
constant.  6.5 also treated 10.1 as float8, but managed to find equality
anyway.

I think this change in behavior is my fault :-(.  About a year ago
I cleaned up some ugly coding in float.c and (without thinking about
it very carefully) changed float48eq and related operators so that
float4-vs-float8 comparisons are done in float8 arithmetic not float4.
The old code truncated the double input down to float and did a float
equality check, while the new code promotes the float input to double
and does a double-precision comparison.

This behavior is arguably more correct than the old way from a purely
mathematical point of view, but now that I think about it, it's not
clear that it's more useful than the old way.  In particular, in an
example like the above, it's now impossible for any float4 value to be
considered exactly equal to the float8 constant 10.1, because the float4
value just hasn't got the right low-order bits after widening.

Perhaps the old way of considering equality only to float accuracy
is more useful, even though it opens us up to problems like overflow
errors in "float4var = 1e100".  Comments anyone?

A general comment on your table design though: anyone who expects exact
equality tests on fractional float values to succeed is going to get
burnt sooner or later.  If you must use this style of coding then
I recommend using numeric fields not float fields, and certainly not
float4 fields.

            regards, tom lane