Re: Alias in the HAVING clause

"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