Re: SQL feature requests - Mailing list pgsql-hackers

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] On Behalf Of Chuck McDevitt
> Sent: Wednesday, August 22, 2007 11:53 PM
> To: Michael Glaesemann; Ben Tilly
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] SQL feature requests
>
> > -----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';
>

Just wanted to point out that the group by thing is really just
syntactic sugar.
You can always get a SQL standard compliant system to accept the
constants this way:

Select z,sum(b) from (Select 'foo',b from x) a1 (z,b)
Group by z;

This will work (although with PostgreSQL, you might have to cast the
string constant to text, because it is "unknown" datatype).