SELECT from MySQL view with HAVING clause. Returns an empty result set.

Question

My business partner and I have problems choosing from a MySQL view that has a HAVING clause.

The query simply selects several fields from the view, dynamically determines the distance with several calculations, and pseudonizes it as “distance” - then limits the results for these rows with a distance less than the supplied variable.

Distance is calculated using the Haversin formula referenced by Google Maps: https://developers.google.com/maps/articles/phpsqlsearch

Here is what I know:

1) When the HAVING clause is removed from the query, it returns all the results in the view successfully, including the calculated "distance" for each row

2) When a HAVING clause is added to the query, it returns an empty result set

3) We also tried to replace the variable in the HAVING clause with a static number - this also returned an empty result set

The content of the view seems inconsequential, since everything works without a HAVING clause.

Here is the request:

SELECT restaurantName, restaurantID, locationID, locationCity, locationState, locationAddress, locationLatitude, locationLongitude, ( 3959 * acos( cos( radians('%s') ) * cos( radians( locationLatitude ) ) * cos( radians( locationLongitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( locationLatitude ) ) ) ) AS distance FROM newView HAVING distance < '%s' ORDER BY distance 

Remember, the view calculates the "distance" for each row selected perfectly without a HAVING clause, so we are convinced that the problem is there ... when we take it out, everything works, but every row in the view is returned.

Any ideas why the HAVING clause returns an empty set? Is a HAVING clause incompatible with views?

Answer 1

Try the following:

 select * from ( SELECT restaurantName, restaurantID, locationID, locationCity, locationState, locationAddress, locationLatitude, locationLongitude, ( 3959 * acos( cos( radians('%s') ) * cos( radians( locationLatitude ) ) * cos( radians( locationLongitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( locationLatitude ) ) ) ) AS distance FROM newView ) S where distance < '%s' ORDER BY distance 

Answer 2

The HAVING is for use with aggregated data when you group rows together using the GROUP BY . Since you work on each line separately, you must replace HAVING with a WHERE . See this example for more details.

Using HAVING in non-aggregate columns in your SELECT list is non-standard behavior supported by MySQL, but behavior you should not rely on. Even the MySQL link refers to it:

Do not use HAVING for elements that should be in a WHERE clause. For example, do not write the following:

SELECT col_name FROM tbl_name HAVING col_name > 0;

Instead, write:

SELECT col_name FROM tbl_name WHERE col_name > 0;

Aside: if you pass arguments from the user to your request (using %s ), make sure you look at prepared statements. Otherwise, you may have an obvious lack of security at your fingertips.