Thread: Alias in the HAVING clause
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; It seems that Postgres does not recognize the alias in the HAVING clause. Rewriting it like this works in postgres: SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING event_id % 3 <> 0; I am wondering if I am missing something because this looks like my expression (id % 3) is getting evaluated twice when it really only should be done once. Now, this query is obviously watered down from what I am really doing and the expression is fairly lengthy and complex so I would prefer to not have to evaluate it more times than necessary. Is this the correct way to do this, or is there a better way / a way to get PostgreSQL to recognize an alias in the HAVING clause? Thanks, Nate
Oops, that second query should be SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING id % 3 <> 0; On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher@gmail.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; > > It seems that Postgres does not recognize the alias in the HAVING > clause. Rewriting it like this works in postgres: > > SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING event_id % 3 <> 0; > > I am wondering if I am missing something because this looks like my > expression (id % 3) is getting evaluated twice when it really only > should be done once. Now, this query is obviously watered down from > what I am really doing and the expression is fairly lengthy and > complex so I would prefer to not have to evaluate it more times than > necessary. > > Is this the correct way to do this, or is there a better way / a way > to get PostgreSQL to recognize an alias in the HAVING clause? > > Thanks, > Nate >
On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher@gmail.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; > > It seems that Postgres does not recognize the alias in the HAVING > clause. Rewriting it like this works in postgres: > > SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING event_id % 3 <> 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. f1 doesn't exist at the time the group by fires. Proper execution sequence is: where clause, group by, select list, having clause. Mysql has likely taught you some bad habits, like selecting fields that aren't unique / grouped by, which is not legal in a grouped query. > I am wondering if I am missing something because this looks like my > expression (id % 3) is getting evaluated twice when it really only > should be done once. Now, this query is obviously watered down from > what I am really doing and the expression is fairly lengthy and > complex so I would prefer to not have to evaluate it more times than > necessary. Please come up with a test case query that really shows what you're trying to do, the edited query is not workable, and I'm not sure which errors you're introducing now or when you first wrote the query.
On Tue, May 13, 2008 at 4:18 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher@gmail.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; >> >> It seems that Postgres does not recognize the alias in the HAVING >> clause. Rewriting it like this works in postgres: >> >> SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING event_id % 3 <> 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. f1 > doesn't exist at the time the group by fires. Proper execution > sequence is: where clause, group by, select list, having clause. Nevermind, I misunderstood which error I was getting there. Still, while it's nice to simplify queries for posting, it's a good idea to create a test example. i.e. create table... insert into... select ... to show the problem so it's easy to reproduce. I wouldn't worry about the possible double re-evaluation, it's no big loss. As for the having clause, I'm pretty sure it can only operate on real columns, not aliases.
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher@gmail.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
On Tue, May 13, 2008 at 5:43 PM, Nathan Thatcher <n8thatcher@gmail.com> wrote: > Is this the correct way to do this, or is there a better way / a way > to get PostgreSQL to recognize an alias in the HAVING clause? As Tom pointed out, f1's not in scope for the HAVING clause. If you're that concerned about expression duplication, you could move the calculation into a sub-select: SELECT COUNT(*), f1 FROM (SELECT id % 3 AS f1 FROM table) t1 GROUP BY f1 HAVING f1 <> 0; -- - David T. Wilson david.t.wilson@gmail.com
Thank you all for the insight. PG is obviously my first choice (that is why I am switching)... the hope is to do so without having to change everything. Thanks for the solution David - it did the trick. Nate On Tue, May 13, 2008 at 5:01 PM, David Wilson <david.t.wilson@gmail.com> wrote: > On Tue, May 13, 2008 at 5:43 PM, Nathan Thatcher <n8thatcher@gmail.com> wrote: > > > Is this the correct way to do this, or is there a better way / a way > > to get PostgreSQL to recognize an alias in the HAVING clause? > > As Tom pointed out, f1's not in scope for the HAVING clause. If you're > that concerned about expression duplication, you could move the > calculation into a sub-select: > > SELECT COUNT(*), f1 FROM (SELECT id % 3 AS f1 FROM table) t1 GROUP BY > f1 HAVING f1 <> 0; > > -- > - David T. Wilson > david.t.wilson@gmail.com >
On Tuesday 13 May 2008 18:43:25 Tom Lane wrote:
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
> > On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher@gmail.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
Robert Treat <xzilla@users.sourceforge.net> writes:
> ... 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?
The key point is that we only allow output-list aliases (in either
GROUP BY or ORDER BY) as the *single* component of a grouping/ordering
item --- that is, these are equivalent to the "ORDER BY 1" type of
column-number shortcut. This provides useful functionality for
grouping/ordering, but not so much for HAVING. As in the OP's
example "HAVING f1 <> 0", you'd not be able to provide what is asked
for unless you allow the aliases to be buried within expressions.
And at that point you have got a really serious problem with ambiguity
against column names that are exposed by the FROM clause.
The long and the short of it is that allowing aliases in GROUP BY at all
was a mistake, and we're not going to enlarge that mistake by the amount
that would be needed to do what the OP asks.
regards, tom lane