MySQL - BETWEEN operator with FLOAT(10,6) is acting like > and <, instead of >= and <=

Question

I have a table with latitudes and longitudes of locations. I was using the BETWEEN clause successfully until I reached cases where the values being searched for where the same as those in the database. In these cases it is not returning results. Here is an example, where as:

SELECT 
  `Location`.`latitude`,
  `Location`.`longitude`
FROM
  `locations` AS `Location` 
WHERE `latitude` >= 40.735619 
  AND `latitude` <= 40.736561 
  AND `longitude` >= -74.033882 
  AND `longitude` <= -74.030861;

Returns:

"latitude"  "longitude"
"40.736561" "-74.033882"
"40.735619" "-74.030861"

If I use the BETWEEN CLAUSE (Notice I've even tried this):

SELECT   
  `Location`.`latitude`,
  `Location`.`longitude`
FROM
  `locations` AS `Location` 
WHERE `latitude` BETWEEN LEAST(40.735619, 40.736561)
  AND GREATEST(40.736561, 40.735619)
  AND `longitude` BETWEEN LEAST(- 74.033882, - 74.030861)
  AND GREATEST(- 74.030861, - 74.033882)

I get 0 results. Oh, whats more, if I add and/or subtract 0.000001 to each value Ex. "BETWEEN (40.735619-0.00001)" etc. If I do this it does return the two results.

Fine, I'll use >= and <= but what I don't understand is why BETWEEN is acting like > and < when in the docs its pretty clear:

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1

Answer 1

You should use a decimal data type rather than a float. Equality, and hence between, for floating point values is imprecise

Answer 2

I suspect that this has to do with roundoff errors in floating point conversions. According to the docs, The expression expr BETWEEN min AND max is equivalent to (expr <= max AND expr >= min) only when all three arguments are the same type. Otherwise type conversion is applied to all arguments. It would be during this conversion that roundoff errors would occur.