Intriguing sql query (not in select)

I have the following sql query in mysql:

SELECT * 
FROM _t_test
WHERE pret NOT 
IN ( 2.6700, 2.6560, 1.8200 ) 

I would expect the rows with the value 1.8200 not to be shown, yet I s开发者_C百科till get them. Am I missing something? The field "pret" is double(16,4).

This is a rounding error. A double is not an exact value, so 1.8200 isn't represented exactly, so the values are not exactly the same.

For MYSQL floating points, see http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

The correct way to do floating-point number comparison is to first decide on an acceptable tolerance for differences between the numbers and then do the comparison against the tolerance value. For example, if we agree that floating-point numbers should be regarded the same if they are same within a precision of one in ten thousand (0.0001), the comparison should be written to find differences larger than the tolerance value

See http://en.wikipedia.org/wiki/Double_precision_floating-point_format