> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] On Behalf Of Michael Glaesemann
> Sent: Wednesday, August 22, 2007 5:17 PM
> To: Ben Tilly
> Cc: pgsql-hackers@postgresql.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';