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

解决方案

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