Query returns no results when it should.

[acroporas]

Using MySQL 4.1.22-standard

 

 

The following code works when $fuzzy >= 0.125

 

But when $fuzzy <= .05, the first seems to be working as expected, but the second query returns no results.  Anyone know why, or how to make it work with smaller values for $fuzzy?

 

 

Query 1

$sql = "SELECT AVG( user_lat ) AS AVG_Lat, AVG( user_long ) AS AVG_Lon, count( * ) AS Number_Users, `user_id`, ROUND( user_lat / $fuzzy ) * $fuzzy AS fuzzy_lat, ROUND( user_long / $fuzzy ) * $fuzzy AS fuzzy_lon
FROM `phpbb_users` 
WHERE user_lat != ''
GROUP BY fuzzy_lat, fuzzy_lon
ORDER BY Number_Users DESC";

for each row returned I then run a second query using the fields fuzzy_lat and fuzzy_lon which were returned from the previous query.

	$sql = "SELECT `user_id`, `username`
		FROM `phpbb_users` 
		WHERE ROUND( user_lat / $fuzzy ) * $fuzzy =  $fuzzy_lat
		AND  ROUND( user_long / $fuzzy ) * $fuzzy = $fuzzy_lon;

[Barand]

It could be a result of using floating point numbers where an expected result of, say, 0.5 is actually stored as 0.500000001.

 

Equality with FP numbers is an inexact science. Instead of

 

where fp1 = fp2

 

try

 

where abs(fp1 - fp2) < 0.0001

 

to see if they are sufficiently close to be considered equal.