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) -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
