SELECT From MySQL View With HAVING Clause Returns Empty Result Set

Question

My business partner and I are having issues selecting from a MySQL view that has a HAVING clause.

The query simply selects a few fields from the view, determines a distance dynamically with a few calculations, and aliases it as 'distance' - then limits the results to those rows with a distance less than a supplied variable.

The distance is calculated using the Haversine 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 the HAVING clause is added to the query, it returns an empty result set

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

The contents of the view seem irrelevant since everything works without the HAVING clause.

Here is the query:

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 that the view calculates the 'distance' for every selected row perfectly without the HAVING clause, so we are convinced the problem lies there... when we take it out, everything works but every row in the view is returned.

Any ideas why the HAVING clause is returning an empty set? Is the HAVING clause not compatible with views?

score 3 · Answer 1 · edited May 23 '17 at 12:30

3

The HAVING clause is meant to be used on aggregated data when you are grouping rows together using the GROUP BY clause. Since you are operating on each row individually, you should replace HAVING with a WHERE clause. See this example for details.

Using HAVING on non-aggregate columns in your SELECT list is non-standard behaviour which MySQL supports, but behaviour that shouldn't be relied on. Even the MySQL reference discourages it:

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

SELECT col_name FROM tbl_name HAVING col_name > 0;

Write this instead:

SELECT col_name FROM tbl_name WHERE col_name > 0;

As an aside: if you are passing arguments from the user to your query (with the %s), make sure you look into prepared statements. Otherwise you may have a glaring security flaw on your hands.

edited May 23 '17 at 12:30

Community

1
1

answered Mar 16 '12 at 02:19

AerandiR

5,260
2
20
22

Thanks for your answer, however that is not correct. The HAVING clause can also be used with aggregate functions that are not grouped, which we have in our example - 'distance'. When we weren't using a view, we had this exact query and it was working as intended except for the fact that we needed a view to simplify the query. Check out Google's documentation that I linked to in the question and you will see how they use the HAVING clause to limit the 'distance' - which is a calculated field. – David Denton Mar 16 '12 at 02:21
I tried replacing the HAVING with a WHERE and instead of getting an empty result set, I got a MySQL error. Here is Google's query from the documentation above: `SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;` – David Denton Mar 16 '12 at 02:25
Using `HAVING` in that way is non-standard behaviour which MySQL supports, but behaviour that I wouldn't rely on. "Do not use HAVING for items that should be in the WHERE clause. For example, do not write the following: `SELECT col_name FROM tbl_name HAVING col_name > 0;` Write this instead: `SELECT col_name FROM tbl_name WHERE col_name > 0;`" ([source](http://dev.mysql.com/doc/refman/5.6/en/select.html)) Note also that you have no aggregate functions in your example. The trigonometric functions used to calculate distance all operate on a single row's columns. – AerandiR Mar 16 '12 at 02:38
Thanks again for your input. How would you recommend structuring the query then? Since the calculated value 'distance' is not in the view, using the WHERE clause in place of HAVING causes a MySQL error. – David Denton Mar 16 '12 at 02:54
You're welcome, you can thank me by up-voting my answer. :) Take a look at [this solution](http://stackoverflow.com/questions/6975669/using-the-haversine-formula-with-postgresql-and-pdo) to your problem. Also, if you are passing arguments from the user to your query (with the %s), make sure you look into prepared statements. Otherwise you may have a glaring security flaw on your hands. – AerandiR Mar 16 '12 at 03:15

score 2 · Accepted Answer · answered Mar 16 '12 at 03:28

2

Try this:

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

answered Mar 16 '12 at 03:28

Mosty Mostacho

42,742
16
96
123

1

Thanks for your answer. I used the idea here (a derived table) with the HAVING clause instead of the where clause and it is working perfectly now. Many thanks. – David Denton Mar 16 '12 at 14:04

SELECT From MySQL View With HAVING Clause Returns Empty Result Set

2 Answers2

Linked

Related