Bug #29496 Queries against correctly stored FLOAT columns are imprecise

• Submitted: 2 Jul 2007 19:14
• Modified: 2 Jul 2007 20:25
• Reporter: matthew schiros
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.0.32
• OS: Linux (debian i386)
• CPU Architecture: Any
• Tags: dec, floating point, math error

[2 Jul 2007 19:14] matthew schiros

Description:
When querying against a correctly stored value in a FLOAT( 10,2 ) column, searches for the exact value, or a value greater than or equal to, that in the column will not return the row in question.  This does not happen with DEC( 10,2 ) columns.

How to repeat:
---------
CREATE TABLE `float_test` (
  `float_amount` float(10,2) NOT NULL,
  `dec_amount` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `float_test`
-- 

INSERT INTO `float_test` (`float_amount`, `dec_amount`) VALUES 
(79.99, 79.99);
---------

mysql> select * FROM float_test WHERE float_amount >= 79.99;
Empty set (0.00 sec)

mysql> select * FROM float_test WHERE dec_amount >= 79.99;
+--------------+------------+
| float_amount | dec_amount |
+--------------+------------+
|        79.99 |      79.99 |
+--------------+------------+
1 row in set (0.00 sec)

[2 Jul 2007 20:25] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about Problems with Floating-Point Comparisons at http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html and http://dev.mysql.com/doc/refman/5.0/en/no-matching-rows.html