Something wrong with this SQL query?

Is there something wrong with this query?

SELECT 
    id,
    ( 6371 * acos(cos(radians(?1)) * cos(radians(latitud)) * cos(radians(longitud) - radians(?2)) + sin(radians(?1)) * sin(radians(latitud)))) AS distance 
FROM usuario_señales 
HAVING (distance < 0.1 AND ancho <= ?3) 
ORDER BY distance;

I have a web server in spring boot and I am trying to get the id of the rows in a radius of 1 metes according to the latitud and longitude and with the attribute ancho smaller than the one I pass:

@Query(nativeQuery = true, value="SELECT id,( 6371 * acos(cos(radians(?1)) * cos(radians(latitud)) * cos(radians(longitud) - radians(?2)) + sin(radians(?1)) * sin(radians(latitud)))) AS distance FROM usuario_señales HAVING (distance < 0.1 AND ancho <= ?3) ORDER BY distance")

The error that I get is the following:

java.sql.SQLSyntaxErrorException: Unknown column ‘ancho’ in ‘having clause’

There is something wrong with the having clause and how I use it. Any clue? Thank you in advanced.

Answer

From the description of your question, it does not look like you want an aggregation query. Instead, you are trying to use the having clause to avoid repeating the lengthy computation in the where clause: MySQL supports that (even without a group by clause), but in that case will let you use only columns from the select clause in the having clause.

You don’t need to use the having clause to filter on ancho, you can use a regular where clause. I think the query you meant to write was:

SELECT 
    id,
    ( 6371 * acos(cos(radians(?1)) * cos(radians(latitud)) * cos(radians(longitud) - radians(?2)) + sin(radians(?1)) * sin(radians(latitud)))) AS distance 
FROM usuario_señales 
WHERE ancho <= ?3
HAVING distance < 0.1
ORDER BY distance;

I am not fan of this syntax, which relies on MySQL extension to the standard SQL. I would use a subquery instead:

SELECT *
FROM (
    SELECT 
        id,
        ( 6371 * acos(cos(radians(?1)) * cos(radians(latitud)) * cos(radians(longitud) - radians(?2)) + sin(radians(?1)) * sin(radians(latitud)))) AS distance 
    FROM usuario_señales 
    WHERE ancho <= ?3
) t
WHERE distance < 0.1 
ORDER BY distance;