Why '1.66' does not match 1.66 in MySQL in double-field?

Question

I wondered, why my queries formed in Perl code using DBI did not returned correct records. I had simple queries like :

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

I executed it like:

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

Most time everything worked fine but in some rare cases i did not get results i expected. So i tried from CLI too. Fulled query with dubious values:

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

And got desired records back. After that i quoted price value too:

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

And got zero records as result.

After that I tried with other quoted price values, like '1.67' and they worked fine.

What is problem here? Why such simple queries fail? To reproduce behaviour i put simple schema and query up to sqlfiddle, As you may see simple query SELECT * FROM Table1 WHERE price = '1.66' OR price = '1.67'; gives only one result.

I feel, it may be related to the nature of double datatype (1.66 is represented as 1.66666666... ?), but how should i form then my queries, when i use placeholders in DBI?

Answer 1

You should never compare floating point numbers for equality. You should use a tolerance. Either use a tolerance or switching from floating point numbers (to numeric, text or cents in an integer).