Re: haversine formula with postgreSQL

On Thu, Sep 17, 2009 at 2:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jonathan <jharahush(at)gmail(dot)com> writes:
> > Here is my PHP with SQL:
> > $query = sprintf("SELECT 'ID', 'FACILITY', 'ADDRESS', latitude,
> > longitude, ( 3959 * acos( cos( radians('%s') ) * cos( radians
> > ( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin
> > ( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM
> > aaafacilities HAVING distance < '%s' ORDER BY dist LIMIT 0 OFFSET 20",
>
> Sigh, you've been misled by MySQL's nonstandard behavior. You cannot
> refer to output columns of a query in its HAVING clause; it's disallowed
> per spec and not logically sensible either. The simplest way to deal
> with it is just to repeat the expression in HAVING. If you really
> really don't want to write it twice, you can use a subquery.
>
> regards, tom lane
>

This practice is also a bad habit for MySQL users. I regularly see queries
from users that have conditions that logically belong in the WHERE clause
but the user shoves it into the HAVING. This is often done without a
specific GROUP BY. The MySQL optimizer does not deal with this well.

When would it make logical sense to have a HAVING clause that deals with a
column that is not inside a aggregating function?
--
Rob Wultsch
wultsch(at)gmail(dot)com