"Scott Marlowe" <scott.marlowe@xxxxxxxxx> writes: > On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher@xxxxxxxxx> 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
