2

I have a 4.3 float value in a table.

When sending that request :

SELECT * FROM mytable WHERE floatfield = 4.3

is return an empty result.

Even when trying ;

SELECT * FROM mytable WHERE floatfield = '4.3'

What is the problem ?

mskfisher
  • 3,291
  • 4
  • 35
  • 48
Oliver
  • 23,072
  • 33
  • 138
  • 230

3 Answers3

4

Floating-point does not have infinite precision, and 4.3 cannot be represented exactly in a finite number of binary digits.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
  • Binary floating point (which nearly every computer system in the world uses) cannot represent 4.3 precisely in a finite number of bits. – Donal Fellows May 07 '11 at 14:20
2

Use Column LIKE float instead of Column = float. Don't get the terminology behind this, it probably just matching up from the starting of the value or cut out the unseen precision digit and treat it like varchar.

Gäng Tian
  • 1,588
  • 2
  • 18
  • 26
  • Answer works in my case, but a technical explanation of exactly how it works would be welcomed. – Nilzor Mar 20 '13 at 10:14
0

You can use the following function:

DELIMITER $$
DROP FUNCTION IF EXISTS `x_cast_to_float`$$
CREATE FUNCTION `x_cast_to_float`(number FLOAT) RETURNS FLOAT
    NO SQL
    BEGIN
        RETURN number;
    END$$
DELIMITER ;

SELECT * FROM mytable WHERE floatfield = x_cast_to_float(4.3)<br />
Petr R.
  • 1,247
  • 2
  • 22
  • 30
iilii
  • 29