Why is "1.66" not the same as 1.66 in MySQL in a double field?

Question

I wonder why my queries generated in Perl code using DBI did not return the correct entries. I had simple queries like:

my $query = "SELECT id FROM table WHERE title = ? AND price = ?"; 

I executed it as:

 my $sth = $dbh->prepare( $query ); my ($id) = $dbh->selectcol_arrayref( $sth, undef, $title, $price ); 

In most cases, everything worked fine, but in some rare cases I did not get the expected results. So I also tried from the CLI. Invalid query with dubious values:

 SELECT id FROM table WHERE title = 'Some title' AND price = 1.66; 

And I got the necessary records. After this value I specified :

 SELECT id FROM table WHERE title = 'Some title' AND price = '1.66'; 

And got the result zero .

After that, I tried to use other price values, for example "1.67", and they worked fine.

What is the problem here? Why do such simple queries fail? To reproduce the behavior, I put a simple schema and query before sqlfiddle . As you can see, a simple query is SELECT * FROM Table1 WHERE price = '1.66' OR price = '1.67'; gives only one result.

I feel that this may be due to the nature of the double data type (1.66 is represented as 1.66666666 ...?), But how should I form my queries then when I use placeholders in DBI?

Answer 1

You should not compare floating point numbers for equality. You must use clearance. Use either tolerance or the transition from floating point numbers (to numeric , text or cents in an integer ).