Query precision issue

--------------070704060401080602040801
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

All,

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.

--
Thanks,
Sairam Krishnamurthy
+1 612 859 8161


--------------070704060401080602040801--

Re: Query precision issue

Thats because float columns store approximate data values, you may need
to use an error range in comparison, or at your own discretion use the
decimal data type.
You can get more info in:
http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html
http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.h tml

Carlos Proal

On 10/25/2010 12:15 PM, Sairam Krishnamurthy wrote:
> All,
>
> 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.
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Query precision issue

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.h tml

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)


--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Query precision issue

--------------030409030507010400020300
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.h tml
>
> 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)
>
>
>

--------------030409030507010400020300--

Re: Query precision issue

Sairam,


sorry to be so blunt, and please don't feel offended:

Sairam Krishnamurthy wrote:
> [[...]]
>=20
> 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.

You have not understood the concept of floating point data and of their
binary representation.
For a starter, please try to write the *exact* value 1.0/3.0 with a
finite number of decimal digits.

A basic paper on this was available here:
http://cch.loria.fr/documentation/IEEE754/ACM/goldberg.pdf

Any attempt to solve your problem by giving a different number of
trailing zeros is bound to fail. You best drop the approach of using
equality predicates for floating point data, it will not work in the
general case.


Jörg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
(+49 30) 417 01 487
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Query precision issue

This is a problem that is as old as computers. Floating point decimal data
does not translate directly into binary. Whole numbers are (usually) fine, but
fractions bring out this problem. I remember writing comparisons where I
wanted a value of "10.203" correct to 3 decimal places as

if abs(x -10.203) < 0.00001



On Monday, October 25, 2010 12:15:49 pm Sairam Krishnamurthy wrote:
> All,
>
> 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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org