Thread: Alias in the HAVING clause

Alias in the HAVING clause

From
"Nathan Thatcher"
Date:
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

Re: Alias in the HAVING clause

From
"Nathan Thatcher"
Date:
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
>

Re: Alias in the HAVING clause

From
"Scott Marlowe"
Date:
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.

Re: Alias in the HAVING clause

From
"Scott Marlowe"
Date:
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.

Re: Alias in the HAVING clause

From
Tom Lane
Date:
"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

Re: Alias in the HAVING clause

From
"David Wilson"
Date:
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

Re: Alias in the HAVING clause

From
"Nathan Thatcher"
Date:
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
>

Re: Alias in the HAVING clause

From
Robert Treat
Date:
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

Re: Alias in the HAVING clause

From
Tom Lane
Date:
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