List:General Discussion« Previous MessageNext Message »
From:Sairam Krishnamurthy Date:October 25 2010 6:20pm
Subject:Re: Query precision issue
View as plain text  
That is exactly the problem. It is float. Thanks for your input.

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 10/25/2010 01:00 PM, Dan Nelson wrote:
> In the last episode (Oct 25), Sairam Krishnamurthy said:
>
>    
>> I have simple query like 'select * from table1 where
>> column1=-107.6898780000'. This returns an empty set. But there is data
>> corresponding to this value of column.
>>
>> When I looked more into it, it seems like a precision issue. The value
>> for column1 is -107.689878.
>>
>> More interesting is that the following query fetches the row,
>>
>> 'select * from table1 where column1=-107.689878000'
>>
>> Note that there are only three trailing zeros in the second query while
>> there were four in the first.
>>
>> Can somebody help me to find out the problem? I can very well truncate the
>> trailing zeros when querying, but I am interested in finding why an
>> additional trailing zero returns an empty set.
>>      
> If column1 is a FLOAT field, try converting it to DECIMAL instead.
> Floating-point fractions are stored in base-2 and there are inherent
> rounding problems when converting to base-10 that make exact comparisons
> difficult:
>
> http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html
> http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html
>
> mysql>  create table n ( c_float float(20,10), c_decimal decimal(20,10) );
> Query OK, 0 rows affected (0.01 sec)
>
> mysql>  insert into n values ( -107.689878, -107.689878 );
> Query OK, 1 row affected (0.00 sec)
>
> mysql>  select * from n;
> +-----------------+-----------------+
> | c_float         | c_decimal       |
> +-----------------+-----------------+
> | -107.6898803711 | -107.6898780000 |
> +-----------------+-----------------+
> 1 row in set (0.00 sec)
>
>
>    

Thread
Query precision issueSairam Krishnamurthy25 Oct
  • Re: Query precision issueCarlos Proal25 Oct
  • Re: Query precision issueDan Nelson25 Oct
    • Re: Query precision issueSairam Krishnamurthy25 Oct
  • Re: Query precision issueJoerg Bruehe25 Oct
  • Re: Query precision issueMichael Satterwhite26 Oct