On Thu, Sep 17, 2009 at 2:47 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Jonathan <jharahush@xxxxxxxxx> writes:Sigh, you've been misled by MySQL's nonstandard behavior. You cannot
> 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",
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@xxxxxxxxx
