float4 problem
May be I miss something, but seems there is a problem with float4
in 7.2.3 and 7.3RC1 (6.53 works fine):
test=# create table t ( a float4);
CREATE TABLE
test=# insert into t values (0.1);
INSERT 32789 1
test=# select * from t where a=0.1;
a
---
(0 rows)
test=# select * from t where a=0.1::float4;
a
-----
0.1
(1 row)
No problem with float8
test=# create table t8 ( a float8);
CREATE TABLE
test=# insert into t8 values (0.1);
INSERT 32792 1
test=# select * from t8 where a=0.1;
a
-----
0.1
(1 row)
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
May be I miss something, but seems there is a problem with float4
in 7.2.3 and 7.3RC1 (6.53 works fine):test=# create table t ( a float4);
CREATE TABLE
test=# insert into t values (0.1);
INSERT 32789 1
test=# select * from t where a=0.1;
a
---
(0 rows)
I'm guessing this is because 0.1 is not directly representable as a
binary floating point number, and literal floating constants are
float8 not float4, and 0.1::float4 != 0.1::float8. Same problem that
causes queries on int2 fields not to use an index unless you cast the
constants in the query...
-Doug
Import Notes
Reply to msg id not found: OlegBartunovsmessageofWed20Nov2002183822+0300MSK
Doug McNaught <doug@mcnaught.org> writes:
Oleg Bartunov <oleg@sai.msu.su> writes:
May be I miss something, but seems there is a problem with float4
in 7.2.3 and 7.3RC1 (6.53 works fine):test=# create table t ( a float4);
CREATE TABLE
test=# insert into t values (0.1);
INSERT 32789 1
test=# select * from t where a=0.1;
a
---
(0 rows)
I'm guessing this is because 0.1 is not directly representable as a
binary floating point number, and literal floating constants are
float8 not float4, and 0.1::float4 != 0.1::float8.
Right.
I think that this particular form of the problem will go away in 7.4.
Currently, "a = 0.1" is resolved as float4=float8, and there's no way
for the float4 approximation of 0.1 to exactly equal the float8
approximation of it. However, if we eliminate cross-datatype
comparison operators as I've proposed, the comparison should be resolved
as float4 = float4 and it would work.
Nonetheless, expecting exact equality tests to succeed with float values
is generally folly. I really do not believe the claim that this worked
in 6.5.3.
regards, tom lane