Why is the column distance unknown in the where clause of my MySQL query?

Question

I am currently trying to use the Haversine formula to make a locator for my business directory site. My query currently returns the error Unknown column distance in where clause. I have no idea why. This should work since I have defined the distance alias directly after the Haversine formula. Any ideas?

SELECT 
     l.listing_id AS id, l.category_id, l.title AS listing_title, l.description, l.address, l.city, l.zip, UNIX_TIMESTAMP(l.date_submitted) AS date_submitted, l.latitude, l.longitude,
     ( 6371 * acos( cos( radians(40.293861) ) * cos( radians( l.latitude ) ) * cos( radians( l.longitude ) - radians(-76.600252) ) + sin( radians(40.293861) ) * sin( radians( l.longitude ) ) ) ) AS distance, 
     c.category_id AS cat_id, c.title AS cat_title, c.slug AS cat_slug, 
     r.region_id AS region_id, r.title AS region_title, r.slug AS region_slug 
FROM listings AS l
LEFT JOIN categories AS c ON l.category_id = c.category_id
LEFT JOIN regions AS r ON l.region_id = r.region_id 
WHERE distance < 10
ORDER BY l.date_submitted DESC

see http://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order — Paul Maxwell, Feb 06 '16 at 05:19
You can use `HAVING`, but I suspect `WHERE (6371...)` will be faster. Also, a procedure will be easier to read, and a function will be faster still. (I assume indexes are already optimised) — Strawberry, Feb 06 '16 at 08:36
@shoeLace1291 - Please accept the answer which best suits your requirement so it could be closed. Thanks. — Utsav, Feb 11 '16 at 14:14

score 1 · Answer 1 · answered Feb 06 '16 at 05:03

As said above, you can not use an alias in the WHERE clause because the value is still not known.

What I want to point out is that, this query as you show it, won't scale up as you have more and more rows, since MySQL won't have any way to narrow down the number of possible matches.

A good approach will be to add a WHERE condition you'll use to define a square area having the circle area inside. In that way your SQL won't scan the whole world, but will filter those rows where the area is "close enough" to the circle area; and then calculate the distance lower than 10.

For example, if you're looking for distance 10 of a point X,Y; you can say

WHERE
latitude < (X+5) AND latitude > (X-5) AND longitude < (Y+5) AND longitude > (Y-5)

This means you're ignoring a set of rows, and then doing the expensive calculations over a subset of rows; which is much faster than doing the expensive calculations over the entire dataset.

I hope this helps you to get a faster and more scalable application.

Need to point out that (X+5) should be an actual number and not an expression. Otherwise you'll end up in a similar situation as before. Your language should prepare those 4 numbers, and SQL should receive constants and not expressions. — Nico Andrade, Feb 06 '16 at 19:00

score 1 · Answer 2 · answered Feb 06 '16 at 05:14

The most common workaround is to treat the query as a derived table, then use the column alias in the outer query, e.g.

SELECT
      d.*
FROM (
      SELECT
            l.listing_id AS id
          , l.category_id
          , l.title AS listing_title
          , l.description
          , l.address
          , l.city
          , l.zip
          , UNIX_TIMESTAMP(l.date_submitted) AS date_submitted
          , l.latitude
          , l.longitude
          , (6371 * ACOS(COS(RADIANS(40.293861)) * COS(RADIANS(l.latitude)) * COS(RADIANS(l.longitude) - RADIANS(-76.600252)) + SIN(RADIANS(40.293861)) * SIN(RADIANS(l.longitude)))) AS distance
          , c.category_id AS cat_id
          , c.title AS cat_title
          , c.slug AS cat_slug
          , r.region_id AS region_id
          , r.title AS region_title
          , r.slug AS region_slug
      FROM listings AS l
            LEFT JOIN categories AS c ON l.category_id = c.category_id
            LEFT JOIN regions AS r ON l.region_id = r.region_id
      ) d
WHERE d.distance < 10
ORDER BY
      d.date_submitted DESC

A HAVING clause is not a substitute for the where clause, the HAVING clause exists to allow filtering by aggregated values (e.g. having SUM(sales) > 1000 )

score 0 · Answer 3 · answered Feb 06 '16 at 04:34

0

You simply can not use an ALIAS in the WHERE clause.

MySQL Manual, B.5.4.4 Problems with Column Aliases:

“Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.”

You will have to repeat the formula that calculates the value in your WHERE clause.

answered Feb 06 '16 at 04:34

CBroe

91,630
14
92
150

After googling your explanation, you are correct in the sense that you cannot use an alias in the where clause. You can, however, use an alias in a HAVING clause, so I just replaced WHERE with HAVING and did not have to repeat the formula. – ShoeLace1291 Feb 06 '16 at 04:37
Yes, you can use an ALIAS in the HAVING clause – but don’t think WHERE and HAVING are just arbitrarily interchangeable, they work in different ways resp. at different times, which can affect performance drastically given specific circumstances. So be careful, and make sure to take some measurements. – CBroe Feb 06 '16 at 04:39

score 0 · Answer 4 · answered Feb 06 '16 at 05:45

So as already pointed out in other answers, there are 3 generic ways which I can think of. I tried all in this fiddle

http://sqlfiddle.com/#!9/c8e411/6

Use a subquery do derive the column and use where in outer query.
Use the same condition in where as u used to derive the column
Use group by on all the columns and use having clause. If query is already using group by then you have to adjust it accordingly.

Why is the column distance unknown in the where clause of my MySQL query?

4 Answers4