> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Michael Glaesemann > Sent: Wednesday, August 22, 2007 5:17 PM > To: Ben Tilly > Cc: firstname.lastname@example.org > Subject: Re: [HACKERS] SQL feature requests > > > On Aug 22, 2007, at 18:45 , Ben Tilly wrote: > > > 1. Just a minor annoyance, but why must subqueries in FROM clauses > > have an alias? > > It's required by the SQL standard, AIUI. I wonder what EnterpriseDB > does? > > > 2. Why is 'non-integer constant in GROUP BY' an error? > > > This works for now: > > > > case when true then true end > > > > but I don't know whether some future version of postgres might break > > my code by banning that as well.
1. The SQL standard requires an alias for the subquery, but many real-world SQL implementations relax this requirement in the case where it is unambiguous. The standard doesn't say you have to reject statements without the alias, it says only that you must accept the ones that do. PostgreSQL has many things in its SQL where we accept things that the standard doesn't require, and I don't see a good argument why it shouldn't allow this. 2. The SQL standard only allows column names in group by lists, NOT expressions. PostgreSQL extends the standard by allowing some, but not all, expressions in the group by list (somewhat inconsistent, in my view). Expressions in the group by list is actually a quite common extension. But PostgreSQL also extends the standard by attaching special meaning to simple integer constants in the group by list, and treating them as column numbers from the select list. As far as I remember, the SQL standard only allows that on ORDER BY clauses. Anyway, it seems reasonable to allow expressions in the group by, such as: Select a*10, sum(b) from x group by a*10; But once you allow that, it seems like you should allow any expression, even degenerate ones such as select 'foo',sum(b) from x group by 'foo'; ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match