Thread: float8 strtod weirdness
Hi, <br /><br />Consider the following with latest CVS sources:<br /><br />postgres=# create table temp(val float4);<br />CREATETABLE<br />postgres=# insert into temp values (415.1);<br />INSERT 0 1<br />postgres=# select * from temp where val= 415.1;<br /> val<br />-----<br />(0 rows)<br /><br />!?<br /><br />The reason seems to be that 415.1 ends up beingtreated as a numeric and is converted into float8 (why not float4? - it could have helped to use the float4eq functionthen) <br clear="all" /><br />The float8in function uses strtod which for some reason converts '415.1' into 415.10000000000002causing 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 :)<br /><br />Casting tofloat4 works as expected:<br />postgres=# select * from rel where x = 415.1::float4;<br /> x<br />-------<br /> 415.1<br/>(1 row)<br /><br />Regards,<br /> Nikhils<br />-- <br /><a href="http://www.enterprisedb.com" target="_blank">http://www.enterprisedb.com</a><br/>
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) > > !? No "!?" at all. The "=" operation on floats has never been one to count on. If you need "=" not to give "surprising" results, you need to use some other data type such as numeric. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
"Nikhil Sontakke" <nikhil.sontakke@enterprisedb.com> writes:
> 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;
Anybody who works with float arithmetic can tell you that exact equality
tests are usually a bad idea.
regards, tom lane
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
On Wed, Jan 07, 2009 at 09:56:48AM -0500, Tom Lane wrote: > "Nikhil Sontakke" <nikhil.sontakke@enterprisedb.com> writes: > > 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; > > Anybody who works with float arithmetic can tell you that exact equality > tests are usually a bad idea. This example does seem to be confounded by PG's somewhat eccentric type system. Things would "just work" (in this case, and there have been other cases recently[1]) if type decisions could be delayed slightly. Both of these cases are handled very nicely in Haskell; the expedient feature being parametric polymorphism. Parametric polymorphism allows the type system to say that the field "val" above and the constant "415.1" should have the same type. The lexer would interpret "415.1" as a literal representing a "real number" but wouldn't know which type to give it (this unknown type would typically be labeled as "alpha"). When it had made its way through the parser and through to type checking we would get to the equality operator and realize that we had to give the literal a concrete type. At this point we'd realize that if the LHS of the equality was of type float4 then the RHS should be as well and everything would just work. In example[1] the lexer would interpret the literal "" as of unknown type (and assign the type parameter alpha again) and everything would pass through until type checking at which point the two sides of the UNION would be forced to unify. As normal in type theory, the two type parameters would unify and you then just have to decide which concrete type to give them. This could either be an error, or you could do as PG does at the moment and default to some arbitrary type. Another example would be: SELECT '1' UNION SELECT '1' UNION SELECT 1; In PG 8.3 this gives "UNION types text and integer cannot be matched", again because types are being checked too early. Type parameters also allow the more accurate specification of functions, compare: subscript(anyarray, integer) returns anyelement with (better syntax is needed than just using a type name of alpha): subscript(alpha[], integer) returns alpha arbitrary numbers of independent type parameters are normally supported. One classic example is the fold function, which makes its appearance in database systems as an aggregation operator. In PG we define a aggregation by specify the following: the input type: alpha the state type: beta the final type: delta a transition function: function(beta,alpha) returns betaa finalization function: function(beta) returns delta for example, AVG(INT) would be: CREATE TYPE avg_numeric AS ( num INTEGER, sum NUMERIC ); CREATE FUNCTION avg_trans(avg_numeric,INT) RETURNS avg_numeric LANGUAGE SQL AS $$ SELECT (($2).num+1,($2).sum+$1)$$; CREATE FUNCTION avg_final(avg_numeric) RETURNS NUMERIC LANGUAGE SQL AS $$ SELECT ($1).sum / ($1).num; $$; that's all the definitions out the way, we're now OK to specify the aggregation: (INT,avg_numeric,NUMERIC,avg_trans,avg_final) PG can then ensure that all the type parameters match up OK and every thing's set to go. Parametric polymorphism would be quite an invasive change, but would be a laudable goal. I'd be personally interested in helping with this but because of the size of the change it'd probably not be a good task for me unless I had some guidance. Sam [1] http://archives.postgresql.org/pgsql-general/2009-01/msg00065.php
Sam Mason <sam@samason.me.uk> writes:
> This example does seem to be confounded by PG's somewhat eccentric type
> system. Things would "just work" (in this case, and there have been
> other cases recently[1]) if type decisions could be delayed slightly.
There's been previous speculation about having numeric literals be
initially typed as "unknown_numeric", and then the existing preference
for resolving "var op unknown" as a same-types operator could do the
trick here. However, this would help only for float4 --- float8 tends
to behave as expected already. Given that anyone working in float4 had
better be well aware of its limited precision, I'm not convinced that
there's much to be gained by fooling with this.
regards, tom lane