Re: Alias in the HAVING clause

On Tuesday 13 May 2008 18:43:25 Tom Lane wrote:
> "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.
>

Yeah, I am surprised to see the alias work in the group by (and I'm pretty
sure there are cases where it fails). That said, I think ease of use
arguments would trump spec compliance for the sake of spec compliance, though
I understand there are technical problems the spec is trying to keep you from
getting into... but I have to wonder, if we have established f1 by the time
we evaluate the group by, shouldn't we also be able to determine f1 at having
time, and therefore allow alias in having in this instance?

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL