Thread: Fields float4 don't return any row when selecting a value with a .

Hi all,
I'm using the 7.4 cvs version on cygwin and I noticed that if I have a 
table with a field of float4 type and try to do a simple select:
select * from table where field_float4 = 4.4

it doesn't return any rows.
In the same table I have a float8 field and it works ok.

If I try with a value without the . in the float4 field it also works.

The only way I could get the field_float4 to work was to add '' in the 
value.

Summary of results:
Table created as: create table tableD(field_float4 float4, field_float8 
float8);

insert into tableD values (3.3, 4.4);
insert into tableD values (4, 3);


select * from tableD where field_float4 = 3.3 ==> 0 rows returned
select * from tableD where field_float8 = 4.4 --> 1 row returned.
select * from tableD where field_float4 = '3.3' ==> 1 row returned.

Can someone confirm that for me?
Thanks in advance.



-- 
Regards,

Francisco Figueiredo Jr.

------
"My grandfather once told me that there are two
kinds of people: those
who work and those who take the credit. He told me
to try to be in the
first group; there was less competition there."
- Indira Gandhi

On Tue, Jun 17, 2003 at 23:26:07 -0300, "Francisco Figueiredo Jr." <fxjrlists@yahoo.com.br> wrote:
> 
> 
> Hi all,
> I'm using the 7.4 cvs version on cygwin and I noticed that if I have a 
> table with a field of float4 type and try to do a simple select:
> select * from table where field_float4 = 4.4
> 
> it doesn't return any rows.
> In the same table I have a float8 field and it works ok.
> 
> If I try with a value without the . in the float4 field it also works.

I believe that is because the constant 4.4 is originally a float 8 value
and the comparison to the float4 value is done by promoting to float8.
And the nearest float4 value to 4.4 is not the same as the nearest
float8 value to 4.4. I think both quoting the 4.4 or casting it to
float4 will solve your problem.

Note that under many circumstances equality tests between floating
values doesn't make a lot of sense. If you really want to do this,
you might want to consider using the numeric type.

Bruno Wolff III wrote:

> On Tue, Jun 17, 2003 at 23:26:07 -0300,
>   "Francisco Figueiredo Jr." <fxjrlists@yahoo.com.br> wrote:
> 
>>
>>Hi all,
>>I'm using the 7.4 cvs version on cygwin and I noticed that if I have a 
>>table with a field of float4 type and try to do a simple select:
>>select * from table where field_float4 = 4.4
>>
>>it doesn't return any rows.
>>In the same table I have a float8 field and it works ok.
>>
>>If I try with a value without the . in the float4 field it also works.
> 
> 
> I believe that is because the constant 4.4 is originally a float 8 value
> and the comparison to the float4 value is done by promoting to float8.
> And the nearest float4 value to 4.4 is not the same as the nearest
> float8 value to 4.4. I think both quoting the 4.4 or casting it to
> float4 will solve your problem.
> 

> Note that under many circumstances equality tests between floating
> values doesn't make a lot of sense. If you really want to do this,
> you might want to consider using the numeric type.
> 


Thanks!! I will use the quoting, as it could be used with both float4 
and float8 values.

-- 
Regards,
Francisco Figueiredo Jr.
---
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi