Re: SQL feature requests

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Florian G. Pflug
> Sent: Thursday, August 23, 2007 11:04 AM
> To: Ben Tilly
> Cc: Michael Glaesemann; Gregory Stark; pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] SQL feature requests
>
> Ben Tilly wrote:
> > On 8/22/07, Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:
> >> On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
> >>
> >>> If your implementation accepts:
> >>>
> >>> group by case when true then 'foo' end
> >> What would that mean? Regardless of whether or not it's accepted, it
> >> should have *some* meaning.
> >
> > To my eyes it has a very clear meaning, we're grouping on an
> > expression that happens to be a constant. Which happens to be the
> > same for all rows. Which is a spectacularly useless thing to
> actually
> > do, but the ability to do it happens to be convenient when I'm
> looking
> > for something to terminate a series of commas in a dynamically built
> > query.
>
> Which is the same very clear meaning that "group by 1" has - we're
> grouping on a expression which happens to be the constant 1. Hey,
> wait a second. This isn't what "group by 1" means at all - it
> rather means group by whatever the fist column in the select list is.
>
> So, yes, "group by 'foo'" *seems* to have a very clear meaning - but
> that clearness vanishes as soon as you take into account what "group by
> 1"
> means.
>
> greetings, Florian Pflug
>

Except "group by 1" meaning "group by column 1" is a PostgreSQL extension, not a SQL standard feature, if I recall.

Anyway, I suppose this should work like ORDER BY... For some reason, we allow all expressions in ORDER BY *except* the degenerate case of a constant (ugly).

Expressions in ORDER BY are a PostgreSQL extension also... Not sure why we disallow the case of a constant, except somebody was worried that it would confuse users, because simple integer constants are treated special.

But it seems strange that this is legal in PostgreSQL:

Select * from x order by trim('foo');

But this is illegal:

Select * from x order by 'foo';

And this is accepted, but orders on the constant "1" rather than on column 1:

select * from x order by 1::int;