Re: Alias in the HAVING clause

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Nathan Thatcher" <n8thatcher(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Alias in the HAVING clause
Date: 2008-05-13 22:43:25
Message-ID: 7402.1210718605@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher(at)gmail(dot)com> wrote:
>> I am in the middle of switching a bunch of queries over from MySQL to
>> PostgreSQL and have hit a little snag. The following query works fine
>> in MySQL but raises an error in postgres:
>>
>> SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING f1 <> 0;

> I think you're editing your queries to show to us. There's no way
> that query would run, as you're selecting id and grouping by f1.

Depressingly enough, it *does* run in mysql. There are assorted spec
violations and undefined behaviors involved, but that's more or less
what you've got to expect with mysql.

Not that we're entirely pristine ourselves. We should reject "GROUP BY
f1", since per spec that alias isn't in scope in GROUP BY either. But
a long time ago we decided that GROUP BY should act as much as possible
like ORDER BY, and I doubt we want to change it now.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Wilson 2008-05-13 23:01:12 Re: Alias in the HAVING clause
Previous Message Reece Hart 2008-05-13 22:22:24 Re: Unloading a table consistently