Re: [GENERAL] Alias in the HAVING clause
On Tuesday 13 May 2008 18:43:25 Tom Lane wrote:
Scott Marlowe [EMAIL PROTECTED] writes:
On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher [EMAIL PROTECTED]
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias in the HAVING clause
Robert Treat [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias in the HAVING clause
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 [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias in the HAVING clause
On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher [EMAIL PROTECTED] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias in the HAVING clause
On Tue, May 13, 2008 at 4:18 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher [EMAIL PROTECTED] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias in the HAVING clause
Scott Marlowe [EMAIL PROTECTED] writes: On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias in the HAVING clause
On Tue, May 13, 2008 at 5:43 PM, Nathan Thatcher [EMAIL PROTECTED] 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 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias in the HAVING clause
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 [EMAIL PROTECTED] wrote: On Tue, May 13, 2008 at 5:43 PM, Nathan Thatcher [EMAIL PROTECTED] 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 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
